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:

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'

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

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

     <ESC> <Shift> ZZ

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');

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
     

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 
     <ESC> <Shift> ZZ
     
     [postgres@pg13-master data]$ pg_ctl reload

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 *
     [postgres@pg13-standby data]$ ls -ltr
     total 0

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'
     
     

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
     
     

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)

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 it is replicating to the standby server. As this standby is configured on the streaming method the changes on 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;