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