Eric Radman : a Journal

Failover Orchestration for PostgreSQL

The strong consistory guarantees of a relational database seems to make seamless promotion of a master tricky. Elaborate mechanisms can be conceived of to handle failover of a PostgreSQL master, but we can simplify this operation by by creating some rules:

  1. Read-write connections are handled by a proxy (port 6432). By insisting that update operations run through a proxy we can instantly redirect transactions to a new backend while we wait for DNS to converge. The proxy also enables us to pause connections for the 5-8 seconds it will take to transition to a new master.
  2. Read-only connections are handled directly by Postgres (port 5432). Applications that only need to read data will continue to operate on a standby that was promoted as a master.
  3. Only one master is enabled at any given point in time, which will prevent timelines from diverging.

Promoting a master

Assuming you have a small library of utility scripts, we simply need to test for a file the indicates that a server has been assigned the role of but is still running as a standby.

role=$(./ role_assignment $host)
pgver=$(./ pgver_for_host $host)

case $role in
        test -e /pg_data/${pgver}/standby.signal && \
            /usr/pgsql-${pgver}/bin/pg_ctl promote -D /pg_data/${pgver}

Now the orchestration consists of these steps:

  1. Stopping the current master
  2. Promoting the new master
  3. Redirecting pgbouncer
  4. Reconfiguring the former master as a standby


PgBouncer has the very nice property of pausing connections if the Postgresql server is not responding). By shutting down the former master we also avoid the danger of stray WAL statements during this transition.

To reconfigure pgbouncer we re-write pgbouncer.ini and signal a configuration change

replication_master=$(./ replication_master $host)

proxy_conn="localharvest = dbname=localharvest pool_mode=transaction"

cat > /etc/pgbouncer/pgbouncer.ini <<-EOF

systemctl restart pgbouncer

A Replication View

The following queries can be used to get collect some summary information about the state of replication

-- replication status
SELECT application_name, client_addr, state, sent_lsn, replay_lsn, sync_state
FROM pg_stat_replication;
-- checkpoint status
  pg_last_wal_receive_lsn () receive_location,
  pg_last_wal_replay_lsn() replay_location,
  date_trunc('second', now()-checkpoint_time) AS last_checkpoint,
  CASE WHEN pg_is_in_recovery()='t'
  THEN date_trunc('seconds', now()-pg_last_xact_replay_timestamp())
  END AS last_xact_replay
FROM pg_control_checkpoint();

Last updated on November 04, 2019