Postgres Streaming Replication Configuration

Primary - Existing Test Server:
=======
Hostname: nijam-1
IP Address: 53.99.198.11

Secodary - New Test Server
========
Hostname: nijam-2
IP Address: 53.99.198.12

=====================================================
Step 1. stop the slave server

 /optt/10.5/bin/pg_ctl -D  /DATA stop 

Step 2. move the slave data directory to backup location:

mv /DATA/* /tmp/backup

Step 3. change below parameter on master postgresql.conf & pg_hba.conf files .

listen_addresses = 'localhost,53.99.198.11'  
wal_level = replica                     # minimal, replica, or logical
archive_mode = on 
archive_command =  'cp %p /ARCHIVES/%f'
max_wal_senders = 10 
wal_keep_segments = 25

On pg_hba.conf:

host    replication     postgres        53.99.198.12/24          trust
host    replication     postgres        53.99.198.11/24          trust

Step 4. Restart the master server:

/optt/10.5/bin/pg_ctl -D  /DATA restart

Step 5. taking consistancy backup:

psql -c "select pg_start_backup('initial_backup');"
rsync -cva --inplace /DATA/* postgres@53.99.198.12:/DATA/
psql -c "select pg_stop_backup();"

Step 5. change the below parameter on slave server.

on postgresql.conf:

listen_addresses = 'localhost,53.99.198.12'  
wal_level = replica                     # minimal, replica, or logical
archive_mode = on 
archive_command = '/bin/cp -av %p /ARCHIVES/%f'
max_wal_senders = 10 
wal_keep_segments = 25
hot_standby = on

on pg_hba.conf:

host    replication     postgres        53.99.198.12/24          trust
host    replication     postgres        53.99.198.11/24          trust

On recovery.conf :

standby_mode = 'on'      # to enable the standby (read-only) mode.
primary_conninfo = 'host=53.99.198.11 port=5444 user=postgres'
                         # to specify a connection info to the master node.
trigger_file = '/tmp/pg_failover_trigger'
                         # to specify a trigger file to recognize a fail over.
restore_command = 'cp /ARCHIVES/%f "%p"'
archive_cleanup_command = '/optt/10.5/bin/pg_archivecleanup /ARCHIVES/%r'

Step 6. Restart the slave server:

/optt/10.5/bin/pg_ctl -D  /DATA start

If you get any error like archive missing or wal log sequence missing ...take the missing archive/wall backup and move them to slave else take fresh backup of postgres master server.

Below script will help you to take archive and xlog backup

psql -c "select pg_start_backup('initial_backup');"
rsync -cva --inplace --exclude=pg_hba.conf --exclude=postgresql.conf --exclude=recovery.conf /DATA/* postgres@53.99.198.12:/DATA
rsync -cva /DATA/xlog/* postgres@53.99.198.12:/DATA/xlog/
rsync -cva /ARCHIVES/* postgres@53.99.198.12:/ARCHIVES
psql -c "select pg_stop_backup();"

Step 7. MONITORING STREAMING REPLICATION:

At MASTER:

1. Create some dummy table check the slave server whether is replicated properly or not.

postgres=# create table t(id int);
CREATE TABLE

-- Check master server whether is recovery mode or not,

 postgres=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 f
(1 row)

master will not be recovery mode slave only will be recovery mode.


2. Using pg_stat_replication views

postgres=# select client_addr,client_hostname,client_port,state,sent_lsn,write_lsn,flush_lsn,replay_lsn,write_lag,replay_lag,flush_lag,sync_state from pg_stat_replication;
 client_addr | client_hostname | client_port |   state   | sent_lsn  | write_lsn | flush_lsn | replay_lsn | write_lag | replay_lag | flush_lag | sync_state
-------------+-----------------+-------------+-----------+-----------+-----------+-----------+------------+-----------+------------+-----------+------------
 192.168.2.3 |                 |       60000 | streaming | 0/8017A28 | 0/8017A28 | 0/8017A28 | 0/8017A28  |           |            |           | async
(1 row)

3. Using linux command check the wal sender process whether started or not:

[postgres@mster ~]$ ps -ef|grep postgres
root      82472  82437  0 08:34 pts/1    00:00:00 su postgres
postgres  82473  82472  0 08:34 pts/1    00:00:00 bash
root      94549  94494  0 20:36 pts/3    00:00:00 su postgres
postgres  94550  94549  0 20:36 pts/3    00:00:00 bash
postgres  94582      1  0 20:36 pts/3    00:00:00 /optt/10.5/bin/postgres -D /DATA/
postgres  94584  94582  0 20:36 ?        00:00:00 postgres: logger process
postgres  94586  94582  0 20:36 ?        00:00:00 postgres: checkpointer process
postgres  94587  94582  0 20:36 ?        00:00:00 postgres: writer process
postgres  94588  94582  0 20:36 ?        00:00:00 postgres: wal writer process
postgres  94589  94582  0 20:36 ?        00:00:00 postgres: autovacuum launcher process
postgres  94591  94582  0 20:36 ?        00:00:00 postgres: stats collector process
postgres  94592  94582  0 20:36 ?        00:00:00 postgres: bgworker: logical replication launcher
postgres  94741  94582  0 20:43 ?        00:00:00 postgres: wal sender process postgres 192.168.2.3(60000) streaming 0/8017B08
postgres  95178  94550  0 21:08 pts/3    00:00:00 ps -ef
postgres  95179  94550  0 21:08 pts/3    00:00:00 grep --color=auto postgres

-- Monitoing current wal sequence .

$ ps -ef|grep sender
postgres   7585   3383  0 15:59 ?        00:00:00 postgres: wal sender process postgres 192.168.2.2(42586) streaming 0/18017CD8
postgres   7598   6564  0 15:59 pts/2    00:00:00 grep --color=auto sender

AT SLAVE SERVER:

1. Check the previously created table whether is replicated or not

postgres=# \dt
             List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+----------
 public | qrtransaction | table | postgres
 public | t             | table | postgres
(2 rows)

yes! it is successfully replicated the table.

-- Checking server mode.

postgres=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 t
(1 row)

-- Try to load the data on slave server, slave is a read only mode it will not permitted to load data .

postgres=# insert into t values(1);
ERROR:  cannot execute INSERT in a read-only transaction

2. Using pg_stat_replication view:

postgres=# select status,receive_start_lsn,received_lsn,last_msg_send_time,latest_end_lsn,latest_end_time,conninfo from pg_stat_wal_receiver ;
  status   | receive_start_lsn | received_lsn |        last_msg_send_time        | latest_end_lsn |         latest_end_time          |                                                           conninfo
-----------+-------------------+--------------+----------------------------------+----------------+----------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 streaming | 0/6000000         | 0/8017B08    | 2018-05-26 21:26:34.577733+05:30 | 0/8017B08      | 2018-05-26 21:06:32.309548+05:30 | user=postgres password=******** dbname=replication host=192.168.2.2 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any
(1 row)

3. Using linux command you can monitor wether wal receiver is started or not.this linux command will be helpful for finding out the postgres data directory as well as postgres utility path means bin path

[postgres@slave data]$ ps -ef|grep postgres
root       3971   3573  0 10:27 pts/0    00:00:00 su postgres
postgres   3972   3971  0 10:27 pts/0    00:00:00 bash
root      45410  45321  0 20:36 pts/1    00:00:00 su postgres
postgres  45411  45410  0 20:36 pts/1    00:00:00 bash
postgres  45606      1  0 20:43 pts/1    00:00:00 /optt/10.5/bin/postgres -D /DATA/
postgres  45607  45606  0 20:43 ?        00:00:00 postgres: logger process
postgres  45608  45606  0 20:43 ?        00:00:00 postgres: startup process   recovering 000000010000000000000008
postgres  45610  45606  0 20:43 ?        00:00:00 postgres: checkpointer process
postgres  45611  45606  0 20:43 ?        00:00:00 postgres: writer process
postgres  45612  45606  0 20:43 ?        00:00:06 postgres: wal receiver process   streaming 0/8017B08
postgres  45613  45606  0 20:43 ?        00:00:00 postgres: stats collector process
postgres  45995  45411  0 21:16 pts/1    00:00:00 ps -ef
postgres  45996  45411  0 21:16 pts/1    00:00:00 grep --color=auto postgres

4. This command will be helpful for how many wal segment get postgres receiver

[postgres@slave data]$ ps -ef|grep receiver;
postgres  45612  45606  0 20:43 ?        00:00:06 postgres: wal receiver process   streaming 0/8017B08
postgres  46018  45411  0 21:18 pts/1    00:00:00 grep --color=auto receiver

5. If the slave is up in hot standby mode, you can tell the time in seconds the delay of transactions applied on the slave with this query:

postgres=# select now() - pg_last_xact_replay_timestamp() AS replication_delay;
 replication_delay
-------------------
 00:18:35.207663
(1 row)