Master PostgreSQL 12: Configure Streaming Replication and WAL Archiving

In the previous article we covered building PostgreSQL 12 from source; this article explains how to configure master‑slave streaming replication and continuous archiving for PostgreSQL 12.

Two Forms of Replication

1) File‑based WAL shipping

High‑availability clusters can use continuous archiving: the primary runs in archive mode, the standby runs in continuous recovery mode and continuously reads WAL files from the primary. File‑based shipping transfers WAL segments one file at a time. It is asynchronous, so unshipped transactions may be lost if the primary fails; the loss window can be limited with archive_timeout, but setting it too low may exceed wal_keep_segments and cause data loss.

2) Streaming replication

Since PostgreSQL 9.x, the primary streams WAL records to the standby over TCP as soon as they are generated. By default it is asynchronous, but the delay is usually under one second, giving a smaller loss window than file‑based shipping. Starting with PostgreSQL 12, a standby.signal file (empty text file) replaces the old recovery.conf to designate a standby.

All operations should be performed as the postgres user.

Configure Master‑Slave Replication and Archiving

1) Set up password‑less SSH between the two machines

The archive_command and restore_command run as the postgres user, so password‑less SSH is required.

# Log in to the primary as postgres
ssh-keygen -t rsa               # press Enter for all prompts
scp /home/postgres/.ssh/id_rsa.pub [email protected]:/home/postgres/.ssh/authorized_keys

# Alternatively copy the public key to the standby
cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
chmod 700 /home/postgres/.ssh
chmod 600 /home/postgres/.ssh/authorized_keys
ssh [email protected]   # test login

# Repeat on the standby
ssh-keygen -t rsa
scp /home/postgres/.ssh/id_rsa.pub [email protected]:/home/postgres/.ssh/authorized_keys
chmod 700 /home/postgres/.ssh
chmod 600 /home/postgres/.ssh/authorized_keys
ssh [email protected]   # test login

2) Primary server configuration

# Create replication user
CREATE USER replica WITH REPLICATION LOGIN PASSWORD 'replication';
ALTER USER replica WITH PASSWORD 'replication';

# Edit pg_hba.conf to allow replication connections
host replication replica 10.10.0.0/16 md5

# Edit postgresql.conf (path may vary)
listen_addresses = "0.0.0.0"
max_connections = 200
wal_level = replica
archive_mode = on
archive_command = 'scp -o "StrictHostKeyChecking no" %p pgslave.ptcloud.t.home:/data/postgresql-12/archive/%f'
archive_cleanup_command = '/usr/local/postgresql-12/bin/pg_archivecleanup -d /data/postgresql-12/data/pg_wal %r >> /data/postgresql-12/log/archive_cleanup.log 2>&1'
max_wal_senders = 16
wal_keep_segments = 256
autovacuum_max_workers = 2
max_worker_processes = 16
max_logical_replication_workers = 10
log_destination = 'stderr'
logging_collector = on
log_directory = '/data/postgresql-12/log'
log_filename = 'postgresql-%w.log'
log_file_mode = 0600
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 1GB
log_min_messages = error
log_min_duration_statement = 300   # log statements >300ms
log_checkpoints = on
log_connections = on
log_disconnections = on
log_error_verbosity = verbose
log_hostname = on
log_line_prefix = '%m [%p] '
log_lock_waits = on
log_statement = 'ddl'

# Reload systemd and restart PostgreSQL
systemctl daemon-reload
systemctl restart postgresql

# Verify replication user
psql -c "\du;"

3) Standby server configuration

# Stop standby and remove old data directory
mkdir -p /opt/pgsqldata_backup
mv /data/postgresql-12/data/* /opt/pgsqldata_backup
pg_ctl -D /data/postgresql-12/data -l logfile stop
rm -rf /data/postgresql-12/data/*

4) Take a base backup from the primary

Copy the primary's data to the standby using pg_basebackup.

su - postgres
pg_basebackup -h 10.10.22.151 -p 5432 -U replica -W -R -Fp -Xs -Pv -D /data/postgresql-12/data/

Parameter description: -h primary host address -p primary port -U replication user -W password authentication (use replica's password) -Fp output plain directory -Xs use streaming replication for backup -Pv show detailed progress -R create recovery configuration (no longer needed after PostgreSQL 10) -D destination data directory

5) Adjust standby configuration files

The postgresql.conf copied from the primary must be edited to reflect standby‑specific settings.

# Replace the original standby config
cd /data/postgresql-12/data/
mv postgresql.conf postgresql.conf_master.bak
cp /opt/pgsqldata_backup/postgresql.conf postgresql.conf

# Edit postgresql.conf
listen_addresses = "0.0.0.0"
max_connections = 300
restore_command = 'cp /data/postgresql-12/archive/%f %p'
archive_cleanup_command = '/usr/local/postgresql-12/bin/pg_archivecleanup -d /data/postgresql-12/data/pg_wal %r && /usr/local/postgresql-12/bin/pg_archivecleanup -d /data/postgresql-12/archive %r >> /data/postgresql-12/log/archive_cleanup.log 2>&1'
wal_level = replica
max_wal_senders = 16
wal_keep_segments = 512
max_logical_replication_workers = 10
autovacuum_max_workers = 2
max_worker_processes = 16
hot_standby = on
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = on
log_destination = 'stderr'
logging_collector = on
log_directory = '/data/postgresql-12/log'
log_filename = 'postgresql-%w.log'
log_file_mode = 0600
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 1GB
log_min_messages = error
log_min_duration_statement = 300
log_checkpoints = on
log_connections = on
log_disconnections = on
log_error_verbosity = verbose
log_hostname = on
log_line_prefix = '%m [%p] '
log_lock_waits = on
log_statement = 'ddl'

6) Restart the standby

Ensure the data directory belongs to the postgres user and has permissions 0700, then restart.

su - postgres
pg_ctl -D /data/postgresql-12/data restart

Verify Replication

# Connect to primary
su - postgres
psql
SELECT client_addr, sync_state FROM pg_stat_replication;
-- Example output shows async replication from 10.10.22.152
SELECT pid, usename, application_name, client_addr, backend_start, client_port, state, sync_state FROM pg_stat_replication;

The output confirms that 10.10.22.152 is the standby receiving asynchronous streaming replication. At this point, master‑slave streaming replication and continuous archiving are configured.