Setting Up Streaming Replication in PostgreSQL on RHEL 9

Streaming replication in PostgreSQL allows you to create one or more standby servers that continuously update their data from a primary (master) server. Here’s a step-by-step guide to setting up streaming replication between a primary and standby server on RHEL (Red Hat Enterprise Linux).

Prerequisites

• Two RHEL servers: Primary (psql-primary) and Standby (psql-standby)
• PostgreSQL 12 installed on both servers

Primary Server Configuration

Host and IP Configuration

Edit the /etc/hosts and /etc/hostname files to set the hostname and IP addresses for both servers.

cd /etc
vi hosts
vi hostname

Add the following entries:

Primary server: psql-primary (Host and Hostname) IP Address: 10.6.128.139
Standby server: psql-standby (Host and Hostname) IP Address: 10.6.128.94

pg_hba.conf Configuration

Edit the pg_hba.conf file to allow replication connections.

nano /var/lib/pgsql/12/data/pg_hba.conf

Add the following lines:

# replication privilege.
local replication all peer
host replication all 10.6.128.94/32 trust
host replication all ::1/128 ident

postgresql.conf Configuration

Edit the postgresql.conf file to configure replication settings.

vi /var/lib/pgsql/12/data/postgresql.conf

Add or modify the following settings:

# CONNECTIONS AND AUTHENTICATION
listen_addresses = '*'

# WRITE-AHEAD LOG
wal_level = replica
wal_log_hints = on
# REPLICATION
max_wal_senders = 10
wal_keep_size = 10
wal_receiver_status_interval = 5s
hot_standby_feedback = on

Create Replication User

Create a replication user on the primary server.

sudo -u postgres createuser -U postgres repuser -P --replication

Restart PostgreSQL

sudo systemctl restart postgresql-12

Standby Server Configuration

Stop PostgreSQL and Remove Data Directory

systemctl stop postgresql-12
yes | rm -r /var/lib/pgsql/12/data/

Create Data Directory and Set Permissions

mkdir /var/lib/pgsql/12/data/
chown postgres:postgres /var/lib/pgsql/12/data/

Initialize Standby Server from Primary

sudo -H -u postgres bash -c 'pg_basebackup --pgdata=/var/lib/pgsql/12/data/ --format=p --write-recovery-conf --checkpoint=fast --label=mffb --progress --host=10.6.128.139 --port=5432 --username=repuser'

Update postgresql.conf

vi /var/lib/pgsql/12/data/postgresql.conf

Add or modify the following settings:

# REPLICATION
primary_conninfo = 'host=10.6.128.139 port=5432 user=repuser password=*******'
primary_slot_name = 'standby1_slot'

Restart PostgreSQL

sudo systemctl restart postgresql-12

Monitoring Replication

On Standby Server

su - postgres
psql
\x
select * from pg_stat_wal_receiver;
select * from pg_stat_replication;

On Primary Server

bashCopy code

su - postgres
psql
\x
select * from pg_stat_replication;

Conclusion

You should now have a working streaming replication setup between your primary and standby servers on RHEL. This setup provides high availability and fault tolerance for your PostgreSQL database.