tickets #159267
Updated by crameleon 3 months ago
Our primary/secondary PostgreSQL pair on mirrordb{1,2}.i.o.o is currently not capable of automatically switching the primary and secondary nodes. Even if the switch is performed manually, PgBouncer on hel{1,2} is still only connecting to mirrordb2.
This blocks us from performing maintenance on the database hosts without outage.
Automatic switchover between mirrordb1 and mirrordb2 must be implemented and hooked into the host= setting on PgBouncer.
There are some ideas:
https://2018.pgday.it/assets/PostgreSQL_High_Availability_using_repmgr_and_pgbouncer.pdf
https://blog.raveland.tech/post/postgresql_repmgr_pgbouncer_en/
Security considerations must be taken into account when granting SSH access between the four machines. All PostgreSQL and repmgr related commands should already be executable through the `postgres` user, for systemctl some NOPASSWD sudo rules will probably need to be added.