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
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 | test02-sandbox-pgsql.mydomain.com
(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
