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