Step-by-Step Guide: Setting Up PostgreSQL 12 Streaming Replication on 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.