Postgres Replication - client (ops) and internal ops (int) DBs

Both ops and int DBs run on the std Postgres port (5432) and are replicated from the Primary (active) host to one or more Remote hosts using Postgres Streaming.

This documentation covers both the initial set-up of the Replica, the fail-over process and the steps required to re-initialise a replica after fail-over.

To set up the Remote instance as a streaming read-only replica of the Primary server, you will configure replication settings on the remote host, take a fresh streaming basebackup, and start the Replica in standby mode.

1. Initial configuration - only required if replication never previously run

Configure PG Server Permissions. These configuration steps should be in ansible and part of initial set-up but for visibility.

Ensure the primary server allows replication connections and has WAL archiving/streaming enabled.

Set the following in /etc/postgresql/16/main/postgresql.conf on the Primary server:

wal_level = logical
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on

Ensure the remote server's pg_hba.conf allows replication connections from your Replica server.

Add this line to /etc/postgresql/16/main/pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    replication     repuser      CLIENT_IP/32            md5

Create the replication user and apply configuration changes:

-- Run in psql as superuser on the primary server
CREATE ROLE repuser WITH REPLICATION LOGIN PASSWORD 'repuser password';

Bash (root):

# Restart PostgreSQL to apply postgresql.conf and pg_hba.conf changes
systemctl restart postgresql@16-main.service

Ensure port 5432/tcp is open to the remote host in the firewall.

2. Take the base backup

To be performed on initial set-up or after fail-over on the new replica.

Typically we copy the BaseBackup files straight into the empty data directory on the Replica, all configuration and data is therefore identical to the Primary from which it originates. Although this could be directed to a backup dir and copied-over later if desirable. Note the "-R" flag which makes it a read-only replica on start-up.

Bash (root) — Shutdown the existing database on the Replica:

systemctl stop postgresql postgresql@16-main.service

Remove the default initialised data directories created by the install or from the existing out of date DB.

# Clean out default cluster directories
rm -rf /var/lib/postgresql/16/main/*

# Ensure strict ownership and permissions
chown postgres:postgres /var/lib/postgresql/16/main
chmod 700 /var/lib/postgresql/16/main

Run pg_basebackup (use tmux or similar). The flags stream Write-Ahead Logs (WAL) in real-time, show progress, and generate plain format output. It will require entry of repuser PW.

Bash (postgres):

pg_basebackup \
  -h REMOTE_HOST \
  -U repuser \
  -D /var/lib/postgresql/16/main \
  -Fp \
  -Xs \
  -P \
  -R \
  -v

Key Flags:

  • -Fp: Generates plain text layout (same format as the remote data directory). Use -Ft for tar archives.
  • -Xs: Streams WAL files while taking the backup to ensure point-in-time consistency.
  • -P: Enables progress reporting.
  • -R: Automatically creates a standby.signal file and writes connection settings into postgresql.auto.conf (useful if configuring a replica).

Verify Standby Connection Details on Replica

Verify that pg_basebackup generated standby.signal and added primary_conninfo to postgresql.auto.conf.

# Check that standby.signal exists
ls -la /var/lib/postgresql/16/main/standby.signal

# Verify connection parameters
cat /var/lib/postgresql/16/main/postgresql.auto.conf

Example output in postgresql.auto.conf:

primary_conninfo = 'user=repuser password=your_secure_password host=REMOTE_PRIMARY_IP port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'

Check /var/lib/postgresql/16/main/postgresql.auto.conf for correct ssl hostname and correct if necessary.

Start Replica DB and Check Status

Start the local main cluster and confirm it is streaming WAL logs from the primary.

Bash (root):

# Start Replica
systemctl start postgresql@16-main.service

# Check standby status via SQL
sudo -u postgres psql -p 5432 -c "SELECT pg_is_in_recovery();"
# Output should return t (true).

Check replication lag and streaming status:

sudo -u postgres psql -p 5432 -c "SELECT * FROM pg_stat_wal_receiver;"

3. Fail-over procedure

This is simply a restart of Postgres on the Replica after removing the flag file which will result in a read-write copy of the old Primary as of the point the last replication ran.

Bash (root):

# Stop Replica
systemctl stop postgresql@16-main.service
# Remove standby.signal
rm /var/lib/postgresql/16/main/standby.signal
# Start as Primary
systemctl start postgresql@16-main.service
# Check standby status via SQL
sudo -u postgres psql -p 5432 -c "SELECT pg_is_in_recovery();"
# Output should return f (false).

Once the old Primary has been recovered we re-establish our Replica on it by following the steps in section 2.

Then run prometheus.yml against the operations hosts to update the alerting to reflect the new Primary/Replica hostnames.