PostgreSQL 13 stream replication configuration using rsync in Linux

 Prerequisite:

• Two VM hosts with PostgreSQL 13 preinstalled.
• Master Node: pg13-master; 192.168.56.69
• Standby Node: pg13-standby; 192.168.56.70
• rsync should be installed on the nodes to transfer WAL files to the standby node.

Configuration Steps:

Step 1: Configure passwordless authentication as continuous WAL archiving will be performed using rsync from master to standby node. On the master, execute ssh-keygen with the empty passphrase to generate public and private keys and then transfer the public key to the standby node using the ssh-copy-id command.

[root@pg13-master ~]# su - postgres

[postgres@pg13-master ~]$ ssh-keygen -t rsa

[postgres@pg13-master ~]$ ls -ltr .ssh

[postgres@pg13-master ~]$ ssh-copy-id -i ~/.ssh/id_rsa.pub postgres@pg13-standby

[postgres@pg13-master ~]$ ssh 'postgres@pg13-standby'

Step 2: Create a replication role or user to connect to the master cluster from the standby cluster.

[postgres@pg13-master ~]$ psql

postgres=#CREATE USER replicat REPLICATION PASSWORD 'Replicat@123';

CREATE ROLE

Step 3: Configure master server parameters by editing the postgresql.conf file.

[postgres@pg13-master ~]$ cd  /var/lib/pgsql/13/data

[postgres@pg13-master data]$ vi postgresql.conf

archive_command = 'test ! -f postgres@pg13-standby:/home/postgres/wal_archive/%f && rsync -a %p postgres@pg13-standby:/home/postgres/wal_archive/%f'

archive_mode = on

wal_keep_size = 500

max_slot_wal_keep_size = 1024

wal_log_hints=on

Step 4: Restart the PostgreSQL server and validate the settings.

[postgres@pg13-master ~]$ systemctl restart postgresql-13

select name,setting,unit from pg_settings where name in ('wal_level','archive_command','archive_mode','max_wal_senders','wal_keep_size','wal_log_hints','max_slot_wal_keep_size');

Step 5: Verify the WAL archiving is working fine by manually switching the WAL on the master node and listing the standby WAL directory.

[postgres@pg13-master ~]$ psql

postgres=# select pg_switch_wal();

[postgres@pg13-standby ~]$ cd /home/postgres/wal_archive/

[postgres@pg13-standby wal_archive]$ ls -ltr

Step 6: On the master server pg_hba.conf file makes an entry for the standby server to take remote base backup of the master server from the standby server. Reload the Postgres server for the changes to take effect.

[postgres@pg13-master ~]$ cd /var/lib/pgsql/13/data/

[postgres@pg13-master data]$ vi pg_hba.conf

host replication all 192.168.56.70/32 scram-sha-256 

[postgres@pg13-master data]$ pg_ctl reload

Step 7: Stop the standby server and remove all the contents of its data directory before taking the base backup of the master server.

[postgres@pg13-standby ~]$ systemctl stop postgresql-13

[postgres@pg13-standby ~]$ cd /var/lib/pgsql/13/data/

[postgres@pg13-standby data]$ rm -rf *

Step 8: Take the base backup of the master server from the standby server by using the pg_basebackup utility with options to create a replication slot and primary connection information, this will also create the standby.signal file. After the backup is taken successfully view the postgresql.auto.conf file to view the entries.

[postgres@pg13-standby data]$ pg_basebackup -h pg13-master -p 5432 -U replicat -c fast -C -S streaming_slot1 -R -D /var/lib/pgsql/13/data -Fp -Xs -P -v

[postgres@pg13-standby data]$ ls -ltr

[postgres@pg13-standby data]$ cat postgresql.auto.conf

# Do not edit this file manually!

# It will be overwritten by the ALTER SYSTEM command.

primary_conninfo = 'user=replicat password=''Replicat@123'' channel_binding=prefer host=''pg13-master'' port=5432 sslmode=prefer sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'

primary_slot_name = 'streaming_slot1'

Step 9: Start the standby PostgreSQL server and monitor the PostgreSQL log file.

[postgres@pg13-standby data]$ systemctl start postgresql-13

[postgres@pg13-standby data]$ cd /var/lib/pgsql/13/data/log

[postgres@pg13-standby log]$ tail -100f postgresql-Day.log

2022-01-11 11:54:27.690 +06 [2352] LOG: database system was interrupted; last known up at 2022-01-11 11:43:56 +06

2022-01-11 11:54:28.012 +06 [2352] LOG: entering standby mode

2022-01-11 11:54:28.022 +06 [2352] LOG: redo starts at 0/8000028

2022-01-11 11:54:28.047 +06 [2352] LOG: consistent recovery state reached at 0/8000100

2022-01-11 11:54:28.047 +06 [2349] LOG: database system is ready to accept read-only connections

2022-01-11 11:54:28.065 +06 [2356] LOG: started streaming WAL from primary at 0/9000000 on timeline 1

Step 10: Check the standby is in recovery mode.

[postgres@pg13-standby ~]$ psql

postgres=# select pg_is_in_recovery();

pg_is_in_recovery

-------------------

t

(1 row)

postgres=# show hot_standby;

hot_standby

-------------

on

(1 row)

Step 11: Standby configuration has been completed now it’s time to test the configuration, create a table and insert some data on it to see if it is replicating to the standby server. As this standby is configured on the streaming method the changes on the master are immediate on the replica or standby node.

On Master:

[postgres@pg13-master ~]$ psql

postgres=# CREATE TABLE TST(ID integer);

postgres=# INSERT INTO TST VALUES (100);

postgres=# INSERT INTO TST VALUES (200);

postgres=# SELECT pg_switch_wal();

On Standby:

[postgres@pg13-standby ~]$ psql

postgres=# select * from TST;