PostgreSQL 9.6 Streaming Replication Advanced Configuration with graceful Switchover and Switchback Operation.
In case of Plan outage you can take Switchover activity in which role of database will change within in minimum downtime in few minutes
Once Plan outage is completed then You can Switchback to original database without rebuild from Master backup onwards PostgreSQL 9.3.
Application High Availability will be achieve using JDBC Failover by pointing it with available Master database.
Implementing Streaming Replication
Lab Details
Primary: Master(10.151.1.X)
-bash-4.2$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.3 (Maipo)
postgres=# select version();
version
———————————————————————————————————-
PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
(1 row)
Standby: Slave(10.151.1.Y)
-bash-4.2$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.3 (Maipo)
postgres=# select version();
version
———————————————————————————————————-
PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
(1 row)
Step-1 Create an user named replication with REPLICATION privileges.
Master:-
-bash-4.2$ psql
psql (9.6.3)
Type “help” for help.
postgres=# CREATE ROLE replication WITH REPLICATION PASSWORD ‘password’ LOGIN;
CREATE ROLE
Standby:-
-bash-4.2$ psql
psql (9.6.3)
Type “help” for help.
postgres=# CREATE ROLE replication WITH REPLICATION PASSWORD ‘password’ LOGIN;
CREATE ROLE
Step-2 Set up connections and authentication on the primary so that the standby server can successfully connect to the replication pseudo-database on the primary.
Master:-
-bash-4.2$ cat postgresql.conf | grep listen
listen_addresses = ‘10.151.1.16’ # what IP address(es) to listen on;
-bash-4.2$ vi pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
host all all all md5
host replication postgres all trust
Standby:-
-bash-4.2$ vi postgresql.conf
-bash-4.2$ cat postgresql.conf | grep listen
listen_addresses = ‘10.151.1.18’ # what IP address(es) to listen on;
-bash-4.2$ vi pg_hba.conf
-bash-4.2$
# TYPE DATABASE USER ADDRESS METHOD
host all all all md5
host replication postgres all trust
host replication replication all trust
Make sure connectivity:-
-bash-4.2$ psql -h 10.151.1.16 -U replication
psql (9.6.3)
Type “help” for help.
replication=> \q
-bash-4.2$ psql -h 10.151.1.18 -U replication
psql (9.6.3)
Type “help” for help.
replication=> \q
Step 3. Set up the streaming replication related parameters on the primary server.
Required directory for tbs and archivelogs at master and standby
# cd /opt/
# mkdir postgres
# cd postgres/
#
# mkdir tbs archive
# chown -R postgres:postgres /opt/postgres/
# chmod -R 776 /opt/postgres/
$ $EDITOR postgresql.conf
# To enable read-only queries on a standby server, wal_level must be set to
# “hot_standby”. But you can choose “archive” if you never connect to the
# server in standby mode.
wal_level = hot_standby
# Set the maximum number of concurrent connections from the standby servers.
max_wal_senders = 5
# To prevent the primary server from removing the WAL segments required for
# the standby server before shipping them, set the minimum number of segments
# retained in the pg_xlog directory. At least wal_keep_segments should be
# larger than the number of segments generated between the beginning of
# online-backup and the startup of streaming replication. If you enable WAL
# archiving to an archive directory accessible from the standby, this may
# not be necessary.
wal_keep_segments = 32
# Enable WAL archiving on the primary to an archive directory accessible from
# the standby. If wal_keep_segments is a high enough number to retain the WAL
# segments required for the standby server, this is not necessary.
archive_mode = on
archive_command = ‘cp %p /opt/postgres/archive/%f’
Step 4. Start postgres on the primary server.
/usr/pgsql-9.6/bin/pg_ctl -D /var/lib/pgsql/9.6/data/ start
Step 5. Make a base backup by copying the primary server’s data directory to the standby server.
-bash-4.2$ psql -c “SELECT pg_start_backup(‘label’, true)”
pg_start_backup
—————–
0/5000028
(1 row)
-bash-4.2$ rsync -ac /var/lib/pgsql/9.6/data 10.151.1.18:/var/lib/pgsql/standby/ –exclude postmaster.pid
The authenticity of host ‘10.151.1.18 (10.151.1.18)’ can’t be established.
ECDSA key fingerprint is 35:a4:cc:07:fd:d4:9d:f8:40:3c:42:09:70:19:53:f8.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘10.151.1.18’ (ECDSA) to the list of known hosts.
postgres@10.151.1.18’s password:
Permission denied, please try again.
postgres@10.151.1.18’s password:
Permission denied, please try again.
postgres@10.151.1.18’s password:
-bash-4.2$ psql -c “SELECT pg_stop_backup()”
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
pg_stop_backup
—————-
0/5000130
(1 row)
If archives are stored outside the data directory then it is mandatory to rsync that path also.
Step 6 Enable read-only queries on the standby server.
$ $EDITOR postgresql.conf
hot_standby = on
Step 7 Create a recovery command file in the standby server; the following parameters are required for streaming replication
-bash-4.2$ cd data/
-bash-4.2$ ls
backup_label pg_commit_ts pg_log pg_replslot pg_stat_tmp PG_VERSION postmaster.opts
base pg_dynshmem pg_logical pg_serial pg_subtrans pg_xlog
global pg_hba.conf pg_multixact pg_snapshots pg_tblspc postgresql.auto.conf
pg_clog pg_ident.conf pg_notify pg_stat pg_twophase postgresql.conf
-bash-4.2$ vi postgresql.conf
-bash-4.2$ vi recovery.conf
-bash-4.2$ cat recovery.conf
recovery_target_timeline = ‘latest’
standby_mode = ‘on’
primary_conninfo = ‘host=10.151.1.16 port=5432 user=postgres’
trigger_file = ‘/tmp/trigger.txt’
restore_command = ‘cp /opt/postgres/archive/%f %p’
archive_cleanup_command = ‘/usr/pgsql-9.6/bin/pg_archivecleanup /opt/postgres/archive %r’
Step 8 Start postgres in the standby server. It will start streaming replication.
-bash-4.2$ vi postgresql.conf
listen_addresses = ‘10.151.1.18’
-bash-4.2$
-bash-4.2$ cd
-bash-4.2$
-bash-4.2$ /usr/pgsql-9.6/bin/pg_ctl -D /var/lib/pgsql/standby/data/ start
server starting
-bash-4.2$ < 2017-06-06 16:21:37.902 IST > LOG: redirecting log output to logging collector process
< 2017-06-06 16:21:37.902 IST > HINT: Future log output will appear in directory “pg_log”.
Master:
-bash-4.2$ psql -p 5432 -c “select pg_is_in_recovery();”
pg_is_in_recovery
——————-
f
(1 row)
Standby:
-bash-4.2$ psql -p 5432 -c “select pg_is_in_recovery();”
pg_is_in_recovery
——————-
t
(1 row)
Switchover
Step 1. Do clean shutdown of Primary[5432] (-m fast or smart)
psql -c “select pg_is_in_recovery()”;
Master:-
-bash-4.2$ psql -c “select pg_is_in_recovery()”;
pg_is_in_recovery
——————-
f
(1 row)
Standby:-
-bash-4.2$ psql -c “select pg_is_in_recovery()”;
pg_is_in_recovery
——————-
t
(1 row)
-bash-4.2$ /usr/pgsql-9.6/bin/pg_ctl -D /var/lib/pgsql/9.6/data/ stop -mf
waiting for server to shut down…. done
server stopped
Step 2. Check for sync status and recovery status of Standby[5432] before promoting it:
-bash-4.2$ psql -p 5432 -c ‘select pg_last_xlog_receive_location() “receive_location”,
pg_last_xlog_replay_location() “replay_location”,
pg_is_in_recovery() “recovery_status”;’
receive_location | replay_location | recovery_status
——————+—————–+—————–
0/C1000098 | 0/C1000098 | t
(1 row)
Step 3. Open the Standby as new Primary by pg_ctl promote or creating a trigger file.
-bash-4.2$ grep trigger_file /var/lib/pgsql/standby/data/recovery.conf
trigger_file = ‘/tmp/trigger.txt’
-bash-4.2$
-bash-4.2$ touch /tmp/trigger.txt
-bash-4.2$ psql -p 5432 -c “select pg_is_in_recovery();”
pg_is_in_recovery
——————-
f
(1 row)
Step 4. Restart old Primary as standby and allow to follow the new timeline by passing “recovery_target_timline=’latest'” in $PGDATA/recovery.conf file.
-bash-4.2$ pwd
/var/lib/pgsql/standby
-bash-4.2$
-bash-4.2$ cd data/
-bash-4.2$
-bash-4.2$ mv recovery.done recovery.conf
-bash-4.2$ vi recovery.conf
-bash-4.2$ cat recovery.conf
recovery_target_timeline = ‘latest’
standby_mode = on
primary_conninfo = ‘host=10.151.1.18 port=5432 user=postgres’
trigger_file = ‘/tmp/trigger.txt’
restore_command =’cp /opt/postgres/archive/%f %p’
archive_cleanup_command = ‘/usr/pgsql-9.6/bin/pg_archivecleanup /opt/postgres/archive %r’
-bash-4.2$ /usr/pgsql-9.6/bin/pg_ctl -D /var/lib/pgsql/9.6/data/ start
server starting
-bash-4.2$ < 2017-06-07 15:39:50.196 IST > LOG: redirecting log output to logging collector process
< 2017-06-07 15:39:50.196 IST > HINT: Future log output will appear in directory “pg_log”.
-bash-4.2$ psql -p 5432 -c “select pg_is_in_recovery();”
pg_is_in_recovery
——————-
t
(1 row)
Master:
-bash-4.2$ psql -p 5432 -c “select pg_is_in_recovery();”
pg_is_in_recovery
——————-
f
(1 row)
-bash-4.2$
-bash-4.2$ psql -p 5432 -c “insert into test select * from test”
INSERT 0 11264
Standy:
-bash-4.2$ psql -p 5432 -c “select pg_is_in_recovery();”
pg_is_in_recovery
——————-
t
(1 row)
-bash-4.2$ psql -c “select count(*) from test;”
count
——-
22528
(1 row)
SWITCHBACK
Step 1. Do clean shutdown of new Primary [5432]:
-bash-4.2$ /usr/pgsql-9.6/bin/pg_ctl -D /var/lib/pgsql/standby/data/ stop -mf
waiting for server to shut down…. done
server stopped
Step 2. Check for sync status of new Standby [5432] before promoting.
psql -p 5432 -c ‘select pg_last_xlog_receive_location() “receive_location”,
> pg_last_xlog_replay_location() “replay_location”,
> pg_is_in_recovery() “recovery_status”;’
receive_location | replay_location | recovery_status
——————+—————–+—————–
0/C9000098 | 0/C9000098 | t
(1 row)
Step 3. Open the new Standby [5432] as Primary by creating trigger file or pg_ctl promote.
-bash-4.2$ grep trigger_file /var/lib/pgsql/9.6/data/recovery.conf
trigger_file = ‘/tmp/trigger.txt’
-bash-4.2$
-bash-4.2$ touch /tmp/trigger.txt
-bash-4.2$
-bash-4.2$ psql -p 5432 -c “select pg_is_in_recovery();”
pg_is_in_recovery
——————-
t
(1 row)
-bash-4.2$ psql -p 5432 -c “select pg_is_in_recovery();”
pg_is_in_recovery
——————-
f
(1 row)
Step 4. Restart stopped new Primary [5432] as new Standby.
-bash-4.2$ mv recovery.done recovery.conf
-bash-4.2$ more recovery.conf
recovery_target_timeline = ‘latest’
standby_mode = ‘on’
primary_conninfo = ‘host=10.151.1.16 port=5432 user=postgres’
trigger_file = ‘/tmp/trigger.txt’
restore_command = ‘cp /opt/postgres/archive/%f %p’
-bash-4.2$ /usr/pgsql-9.6/bin/pg_ctl -D /var/lib/pgsql/standby/data/ start
server starting
-bash-4.2$ < 2017-06-07 15:48:01.026 IST > LOG: redirecting log output to logging collector process
< 2017-06-07 15:48:01.026 IST > HINT: Future log output will appear in directory “pg_log”.
-bash-4.2$ psql -p 5432 -c “select pg_is_in_recovery();”
pg_is_in_recovery
——————-
t
(1 row)
Testing:
Master:
-bash-4.2$ psql -p 5432 -c “select pg_is_in_recovery();”
pg_is_in_recovery
——————-
f
(1 row)
-bash-4.2$
-bash-4.2$ psql -c “select count(*) from test;”
count
——-
22528
(1 row)
-bash-4.2$ psql -c “insert into test select * from test;”
INSERT 0 22528
Standby:
-bash-4.2$ psql -p 5432 -c “select pg_is_in_recovery();”
pg_is_in_recovery
——————-
t
(1 row)
-bash-4.2$
-bash-4.2$ psql -c “select count(*) from test;”
count
——-
45056
(1 row)
Technology Blog 