Prerequisite:

Two VM hosts with PostgreSQL 13 preinstalled.

Master Node: pg13-master; 192.168.56.67

Standby Node: pg13-standby; 192.168.56.68

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. Configure master server parameters by editing the postgresql.conf file for the continuous archiving of WAL to the standby location.

   • [postgres@pg13-master ~]$ cd  /var/lib/pgsql/13/data
     [postgres@pg13-master data]$ vi postgresql.conf
     wal_level = replica
     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 
     archive_timeout = 60
     <ESC> <Shift> ZZ

3. 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','archive_timeout');

4. 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
     

5. On the master server pg_hba.conf file make 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.68/32 scram-sha-256 
     <ESC> <Shift> ZZ
     
     [postgres@pg13-master data]$ pg_ctl reload

6. 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

7. Take the base backup of the master server from the standby server by using the pg_basebackup utility.

   • [postgres@pg13-standby data]$ pg_basebackup -h pg13-master -p 5432 -U postgres -D /var/lib/pgsql/13/data -Fp -Xs -P -v
     [postgres@pg13-standby data]$ ls -ltr

8. Configure the standby parameters by editing its postgresql.conf file.

   • [postgres@pg13-standby ~]$ cd /var/lib/pgsql/13/data/
     [postgres@pg13-standby ~]$ vi postgresql.conf
     restore_command = 'cp /home/postgres/wal_archive/%f %p'
     archive_cleanup_command = 'pg_archivecleanup -d /home/postgres/wal_archive %r'
     recovery_target_timeline = 'latest'
     <ESC> <Shift> ZZ

9. Create the standby.signal file and start the standby PostgreSQL server and monitor the PostgreSQL log file.

   • [postgres@pg13-standby ~]$ cd /var/lib/pgsql/13/data/
     [postgres@pg13-standby data]$ touch standby.signal
     [postgres@pg13-standby data]$ systemctl start postgresql-13
     [postgres@pg13-standby data]$ cd /var/lib/pgsql/13/data/log
     [postgres@pg13-standby log]$ more postgresql-Day.log

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 based on the log shipping-based configuration so the changes on the master will be replicated when the WAL on the master is archived, generally WAL is archived when it is 16MB in size. After the data has been inserted manually switch the log file so that it is archived instead of waiting for the log file size to be reached. If not manually switched then the WAL will be archived every 60 seconds as per the configuration of archive_timeout variable.

    • 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;