Welcome to the postgresql_cluster wiki!

This DB cluster is used by the NOC for Aruba Clearpass. Data is mostly MAC addresses but content questions should be directed to Kevin Schmidt.

• cpsql
• cpsql1
• cpsql2

See 'CPSQL Clearpass super admin' in Secrets server

Services

• HAProxy
  • configuration at /etc/haproxy/haprox.cfg
  • Accepts incoming on port 5000 from certain clients
    • see: /data/ansible/host_templates/cpsql/iptables.srv-sql.j2 on orchestration

Both are identical.

Services

• Postgresql Version 16
  • Only accecpts postgresql connections from cpsql
  • files are specifically installed at /bin/pgsql-16/
  • no systemd service, see patroni
• etcd
  • distributed key store, how nodes share cluster status
  • systemd service
• patroni
  • manages replication and failover
  • systemd service manages running postgres service
  • uses etcd
  • patronictl to interact and view status
• cron
• local backup to disk

In /etc/cron.daily/backup_postgresql

# delete backups older than 40 days
10 0 * * * /usr/bin/find /backup -name "*.backup" -type f -mtime +40 -delete
# run backup script
0 0 * * * /bin/bash /usr/local/bin/backup_pg.sh

Interacting with the cluster is done with patronictl

[root@cpsql1 ~]> patronictl list
+ Cluster: postgres-cluster (7441722871473781850) ----+-----------+-----------------+
| Member | Host            | Role    | State     | TL | Lag in MB | Tags            |
+--------+-----------------+---------+-----------+----+-----------+-----------------+
| cpsql1 | 128.111.123.161 | Leader  | running   | 12 |           | datacenter: dc1 |
| cpsql2 | 128.111.123.162 | Replica | streaming | 12 |         0 | datacenter: dc1 |
+--------+-----------------+---------+-----------+----+-----------+-----------------+

To manually failover to a particular node, cpsql1 in this example, run

patronictl failover --candidate cpsql1 postgres-cluster