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 logicalarchive_mode = on archive_command = 'cp %p /ARCHIVES/%f'max_wal_senders = 10 wal_keep_segments = 25
On pg_hba.conf:
hostreplication postgres53.99.198.12/24 trusthostreplication postgres53.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 logicalarchive_mode = on archive_command = '/bin/cp -av %p /ARCHIVES/%f'max_wal_senders = 10 wal_keep_segments = 25hot_standby = on
on pg_hba.conf:
hostreplication postgres53.99.198.12/24 trusthostreplication postgres53.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:/DATArsync -cva /DATA/xlog/* postgres@53.99.198.12:/DATA/xlog/rsync -cva /ARCHIVES/* postgres@53.99.198.12:/ARCHIVESpsql -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 postgresroot 82472 82437 0 08:34 pts/100:00:00 su postgrespostgres 82473 82472 0 08:34 pts/100:00:00 bashroot 94549 94494 0 20:36 pts/300:00:00 su postgrespostgres 94550 94549 0 20:36 pts/300:00:00 bashpostgres 94582 1 0 20:36 pts/300:00:00 /optt/10.5/bin/postgres -D /DATA/postgres 94584 94582 0 20:36 ?00:00:00 postgres: logger processpostgres 94586 94582 0 20:36 ?00:00:00 postgres: checkpointer processpostgres 94587 94582 0 20:36 ?00:00:00 postgres: writer processpostgres 94588 94582 0 20:36 ?00:00:00 postgres: wal writer processpostgres 94589 94582 0 20:36 ?00:00:00 postgres: autovacuum launcher processpostgres 94591 94582 0 20:36 ?00:00:00 postgres: stats collector processpostgres 94592 94582 0 20:36 ?00:00:00 postgres: bgworker: logical replication launcherpostgres 94741 94582 0 20:43 ?00:00:00 postgres: wal sender process postgres 192.168.2.3(60000) streaming 0/8017B08postgres 95178 94550 0 21:08 pts/300:00:00 ps -efpostgres 95179 94550 0 21:08 pts/300:00:00 grep --color=auto postgres
-- Monitoing current wal sequence .
$ ps -ef|grep senderpostgres 7585 3383 0 15:59 ?00:00:00 postgres: wal sender process postgres 192.168.2.2(42586) streaming 0/18017CD8postgres 7598 6564 0 15:59 pts/200: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 postgresroot 3971 3573 0 10:27 pts/000:00:00 su postgrespostgres 3972 3971 0 10:27 pts/000:00:00 bashroot 45410 45321 0 20:36 pts/100:00:00 su postgrespostgres 45411 45410 0 20:36 pts/100:00:00 bashpostgres 45606 1 0 20:43 pts/100:00:00 /optt/10.5/bin/postgres -D /DATA/postgres 45607 45606 0 20:43 ?00:00:00 postgres: logger processpostgres 45608 45606 0 20:43 ?00:00:00 postgres: startup process recovering 000000010000000000000008postgres 45610 45606 0 20:43 ?00:00:00 postgres: checkpointer processpostgres 45611 45606 0 20:43 ?00:00:00 postgres: writer processpostgres 45612 45606 0 20:43 ?00:00:06 postgres: wal receiver process streaming 0/8017B08postgres 45613 45606 0 20:43 ?00:00:00 postgres: stats collector processpostgres 45995 45411 0 21:16 pts/100:00:00 ps -efpostgres 45996 45411 0 21:16 pts/100: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/8017B08postgres 46018 45411 0 21:18 pts/100: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)
0 comments:
Post a Comment