How To Configure PostgreSQL 12 Streaming Replication in CentOS 8?

PostgreSQL Streaming Replication continuously streams Write-Ahead Log (WAL) data from a primary (master) node to standby (slave) nodes, creating near-real-time database copies for high availability and fault tolerance.

Master Node Configuration

1. Enable WAL Archiving

Edit postgresql.conf ?

archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/12/archive/%f'
wal_level = replica
max_wal_senders = 3

2. Allow Replication Connections

Edit pg_hba.conf ?

# TYPE  DATABASE     USER      ADDRESS          METHOD
host    replication  repluser  192.168.1.20/32  md5

3. Create Replication User

CREATE ROLE repluser WITH REPLICATION LOGIN PASSWORD 'securepass';

Restart PostgreSQL on the master after these changes.

Slave Node Configuration

1. Base Backup from Master

sudo -u postgres pg_basebackup -h master_ip -D /var/lib/pgsql/12/data -U repluser -P -R

2. Configure recovery.conf

Create /var/lib/pgsql/12/data/recovery.conf ?

standby_mode = 'on'
primary_conninfo = 'host=master_ip port=5432 user=repluser password=securepass'
restore_command = 'cp /var/lib/pgsql/12/archive/%f "%p"'

3. Start Slave

sudo systemctl start postgresql-12
sudo systemctl enable postgresql-12

Verify Replication

Run on the master to check connected replicas ?

SELECT client_addr, state, sync_state
FROM pg_stat_replication;

  client_addr   |   state   | sync_state
----------------+-----------+------------
 192.168.1.20   | streaming | async

Manual Failover

If the master fails

• Disconnect replication from the failed master
• On the slave, set recovery_target_timeline = 'latest' in recovery.conf
• Restart PostgreSQL on the promoted slave
• Verify other slaves connect to the new master using pg_stat_replication

Conclusion

PostgreSQL 12 streaming replication on CentOS 8 involves configuring WAL archiving and replication access on the master, creating a base backup and recovery.conf on the slave, then verifying with pg_stat_replication. Manual failover promotes a slave to master when needed.