PG HA failover automation
Current possibilities:
-
repmgrd/pgbouncer:
-
Run pgbouncer on application nodes
-
Application communicates with pgbouncer for its database connection
-
pgbouncer communicates directly with master node
-
On master failure, repmgrd updates pgbouncer instances with new master
-
-
repmgrd/pgbouncer/{nginx,haproxy}:
-
Run pgbouncer on application nodes
-
Application communicates with pgbouncer for database connection
-
Pgbouncer communicates with load balancer
-
Load balancer communicates with master node
-
On master failure, repmgrd updates load balancer with new master
-
For both paths, the method for repmgrd to inform of updates is still a bit up in the air.
-
Using ssh keys to allow repmgrd to update other nodes is an option
-
Pros:
-
It should work with our existing package. No new software needs to be added.
-
We do not need to grant root access to repmgrd. Everything it needs should be able to be done by the sql user.
-
-
Cons:
- We enter an unknown state if notification to a node fails.
-
-
Utilize a key value store that repmgrd will update on new master promotion. Application or load balancer nodes will periodically check for a change and update their config accordingly.
-
Pros:
- Nodes are responsible for ensuring their own state is up to date
-
Cons:
- Another piece of software to manage in omnibus
-
-
TBD