Postgresql Hot Standby Installation And Monitoring the Standby

In this tutorial  i explained about how to set up postgresql replication and how to monitor the postgres server step by step
Step.1 Keygen Generate And Copy

[postgres@pg01 ~] ssh-keygen -t rsa
[postgres@pg02 ~] ssh-keygen -t rsa
[postgres@pg01 ~] ssh-copy-id -i .ssh/id_rsa.pub postgres@pg02
[postgres@pg02 ~] ssh-copy-id -i .ssh/id_rsa.pub postgres@pg01

Step.2 Create Streaming Replication User on Primary DB

psql# createuser -U postgres -p 5432 repuser -P -c 10 –replication

Step.3 Primary DB pg_hba.conf configuration

[root@pg01 ~] vi /pgdata/data/postgresql.conf
--Add below line
host replication repuser 192.168.10.12/32 md5
[root@pg01 ~] systemctl reload postgresql.service

Step.4 Create Archive Directory on Hot Standby Server

[root@pg02 ~] mkdir -p /pgdata/ARCHIVELOG

Step.5 Postgresql.conf configuration on Primary DB

[root@pg01 ~] vi /pgdata/data/postgresql.conf

--Parameters change like below
listen_addresses = ‘192.168.10.10’
wal_level = hot_standby # minimal, archive, hot_standby, or logical
archive_mode = on # enables archiving; off, on, or always
archive_command = ‘rsync -a %p postgres@192.168.10.12:/pgdata/ARCHIVELOG/%f’ # command to use to archive a logfile segment
max_wal_senders = 3 # max number of walsender processes
wal_keep_segments = 1000 # in logfile segments, 16MB each; 0 disables
max_replication_slots = 4 # max number of replication slots(change requires restart)

[root@pg01 ~] systemctl restart postgresql.service

Step.6 Base Backup

You can use pg_basebackup when primary db is start or stop. Stop status is more confident.

[root@pg01 ~] systemctl stop postgresql.service
/pgdata/data directory has to be empty on hot standby server. pg_basebackup command will fill up that directory.
[root@pg02 ~] /usr/pgsql-9.5/bin/pg_basebackup -h 192.168.10.10 -D /pgdata/data -U repuser -p 5432 -v -P –xlog-method=stream

Step.7 Postgresql.conf configuration on Hot Standby Server

Primary DB configuration has to be disabled with # mark.
#wal_level = hot_standby # minimal, archive, hot_standby, or logical
#archive_mode = on # enables archiving; off, on, or always
#archive_command = ‘rsync -a %p postgres@192.168.10.12:/pgdata/ARCHIVELOG/%f’ # command to use to archive a logfile segment
#max_wal_senders = 3 # max number of walsender processes
#wal_keep_segments = 1000 # in logfile segments, 16MB each; 0 disables
#max_replication_slots = 4
listen_addresses = ‘*’
hot_standby = on

Step.8 Create Recovery.conf file on Hot Standby Server

[root@pg02 ~] vi /pgdata/data/recovery.conf
restore_command = ‘cp /pgdata/ARCHIVELOG/%f %p’ # e.g. ‘cp /mnt/server/archivedir/%f %p’
archive_cleanup_command = ‘/usr/pgsql-9.5/bin/pg_archivecleanup /pgdata/ARCHIVELOG/%r’
standby_mode = on
primary_conninfo = ‘host=192.168.10.10 port=5432 user=repuser password=repuserpass’
trigger_file = ‘/pgdata/data/failover.uygula’

If you want to delay to apply committed values. You can use recovery_min_apply_delay parameter in recovery.conf like below.

recovery_min_apply_delay = 5min

This parameter provide 5 minutes delay. When you commit a transaction at primary side, hot standby will apply this transaction 5 minutes later.

Step.9 Start Hot Standby PostgreSQL Service

[root@pg02 ~] systemctl start postgresql.service

Step.10 Making Test the Standby Postgres Server

Primary DB:

Replication Views For Monitorin

psql# select * from pg_stat_replication ;

Sender process check

ps -ef | grep sender
postgres 18388 18298 0 17:04 ? 00:00:00 postgres: wal sender process repuser 192.168.10.12(33700) streaming 0/9000348

psql# create table test (name text);
psql# insert into test values(‘nijam’);

Hot Standby DB:

Recovery mode check

psql# select pg_is_in_recovery();

Receiver process check

ps -ef | grep receiver
postgres 20936 20919 0 17:04 ? 00:00:00 postgres: wal receiver process streaming 0/9000268

psql# select * from test;
name
———–
nijam