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)
Comments
Post a Comment