PostgreSQL 13 stream replication configuration using rsync in Linux


Two VM hosts with PostgreSQL 13 preinstalled.

Master Node: pg13-master;

Standby Node: pg13-standby;

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/ 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';
  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
      <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 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 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
      # 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();
      (1 row)
      postgres=# show hot_standby;
      (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;

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.