Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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









Comments

Popular posts from this blog

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

PostgreSQL Pgbadger Installation On Linux

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform