How to set up replication between two postgreSQL servers

Compared to Oracle, setup of a postgreSQL physical standby database is quite simple. With two identically provisioned posgreSQL 13 servers created, the below steps must be performed.

Master: test01-sandbox-pgsql.mydomain.com
Slave: test02-sandbox-pgsql.mydomain.com

1. Configure the master database cluster As the postgres software owner, execute the following:

echo "CREATE ROLE replicate WITH REPLICATION LOGIN password 'mypassword';" | psql
echo "grant app_user to replicate;" | psql
echo "alter system set wal_level = replica;" | psql
echo "select pg_reload_conf();" | psql
echo "alter system set synchronous_commit = local;" | psql
echo "alter system set track_commit_timestamp = on;" |  psql

2. Restart the postgres server

systemctl restart postgresql-13.service

3. Configure the slave database cluster

As the root user, stop the postgres server

systemctl stop postgresql-13.service

4. Remove any files created in the deployment of the slave. They will be replaced anyway

rm -rf /data/*

5. take a base backup of the master database cluster

pg_basebackup -h test01-sandbox-pgsql.mydomain.com -D /data/ -P -U replicate --wal-method=stream

6. Set some parameters

echo "hot_standby = on" >> /pgconfig/postgresql.auto.conf 

echo "
primary_conninfo      = 'host=masterhost.mydomain.com port=5432 user=replicate password=mypassword'
promote_trigger_file  = '/tmp/MasterNow'
" >  /pgconfig/postgresql.conf  

7. Create a file necessary for the master/slave role determination

touch /data/standby.signal

8. Start the postgres server

systemctl start postgresql-13.service

9. Verification checks

On the master, to check that everything works fine, use the dictionary view pg_stat_replication, which, according to the documentation "contain one row per WAL sender process, showing statistics about replication to that sender's connected standby server. Only directly connected standbys are listed; no information is available about downstream standby servers."

 echo 'select usename,state,sync_state,sync_priority,client_hostname from pg_stat_replication;' | psql
  usename  |   state   | sync_state | sync_priority |          client_hostname
-----------+-----------+------------+---------------+-----------------------------------
 replicate | streaming | async      |             0 | til0dbgr-sandbox-pgsql02.skead.no
(1 row)

The check the slave status, use the dictionary view pg_stat_wal_receiver. According to the documentation the view "contain only one row, showing statistics about the WAL receiver from that receiver's connected server":

psql
psql (13.6)
Type "help" for help.

postgres=# \x
Expanded display is on.
postgres=# select status,sender_host,conninfo from pg_stat_wal_receiver;
-[ RECORD 1 ]--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
status      | streaming
sender_host | test01-sandbox-pgsql.mydomain.com
conninfo    | user=replicate password=******** channel_binding=prefer dbname=replication host=test01-sandbox-pgsql.mydomain.com port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any

Check that recovery is in progress:

 echo 'SELECT pg_is_in_recovery();' | psql
 pg_is_in_recovery
-------------------
 t
(1 row)

The equivalent query on the master node would state "f" (false). Read more about

pg_stat_replication

pg_stat_wal_receiver

Note that some important changes to replication were introduced in PostgreSQL version 12