Switchover/Switchback in PostgreSQL 9.5 Database
Step 1: Shutdown postgresql service of Primary Database
[root@pg_primary ~]# su - postgres
Last login: Mon Dec 11 05:44:38 EST 2017 on pts/0
-bash-4.2$ psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
application_name | state | sync_priority | sync_state
------------------+-----------+---------------+------------
walreceiver | streaming | 1 | sync
(1 row)
-bash-4.2$ exit
logout
[root@pg_primary ~]# systemctl stop postgresql-9.5.service
[root@pg_primary ~]#
Step 2: Check for sync status and recovery status of Standby before promoting it:
[root@pg_standby data]# su - postgres
Last login: Mon Dec 11 05:42:01 EST 2017 on pts/1
-bash-4.2$ psql -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/40000098 | 0/40000098 | t
(1 row)
-bash-4.2$
-bash-4.2$ psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
application_name | state | sync_priority | sync_state
------------------+-------+---------------+------------
(0 rows)
-bash-4.2$
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/9.5/data/recovery.conf
trigger_file = '/tmp/postgresql.trigger.5432'
-bash-4.2$ touch /tmp/postgresql.trigger.5432
-bash-4.2$ psql -c "select pg_is_in_recovery();"
pg_is_in_recovery
-------------------
f
(1 row)
-bash-4.2$
-bash-4.2$ tail -f /var/lib/pgsql/9.5/data/pg_log/postgresql-Mon.log
< 2017-12-11 06:29:54.114 EST >LOG: redo done at 0/40000028
< 2017-12-11 06:29:54.115 EST >LOG: last completed transaction was at log time 2017-12-11 05:55:43.441569-05
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/000000010000000000000040’: No such file or directory
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000002.history’: No such file or directory
< 2017-12-11 06:29:54.138 EST >LOG: selected new timeline ID: 2
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000001.history’: No such file or directory
< 2017-12-11 06:29:54.492 EST >LOG: archive recovery complete
< 2017-12-11 06:29:54.830 EST >LOG: MultiXact member wraparound protections are now enabled
< 2017-12-11 06:29:54.911 EST >LOG: database system is ready to accept connections
< 2017-12-11 06:29:54.936 EST >LOG: autovacuum launcher started
---Now Standby become Primary Database----
[root@pg_standby data]# su - postgres
Last login: Mon Dec 11 05:42:01 EST 2017 on pts/1
-bash-4.2$ psql
psql (9.5.10)
Type "help" for help.
postgres=# select * from sebl;
org_name | address
----------+---------
(0 rows)
postgres=# insert into sebl values('Southeast Bank Ltd.','Eunoos Center');
INSERT 0 1
postgres=#
Standby has been promoted as master and a new timeline followed which you can notice in logs
Note: In Primary (Old Standby) recovery.conf file become recovery.done after switchover
--===========************=========---------
Prepare New Standby for data sync with Primary
--===========************=========---------
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$ cat recovery.conf
recovery_target_timeline = 'latest'
standby_mode = 'on'
primary_conninfo = 'host=192.168.159.135 port=5432 user=repuser password=repuser@application_name=pgslave001'
restore_command = 'cp /var/lib/pgsql/9.5/main/archive/%f %p'
trigger_file = '/tmp/postgresql.trigger.5432'
archive_cleanup_command = '/usr/pgsql-9.5/bin/pg_archivecleanup -d /var/lib/pgsql/9.5/main/archive %r >> cleanup.log'
-bash-4.2$
Slave Server (old Primary) Configurations: postgresql.conf:
hot_standby = on
Step 5. Verify the new Standby status.
-bash-4.2$ psql -p 5432 -c "select pg_is_in_recovery();"
pg_is_in_recovery
-------------------
t
(1 row)
-bash-4.2$
-------------Testing------
1. Insert data in primary
2. Check the data in primary and standby
3. try to insert data in standby
--=====================================================--
--------=========Switchback Steps==========--------------
--=====================================================--
Step 1: Check the Sync Status in Primary
postgres=# insert into sebl values('Prime Bank Ltd','Motijill');
INSERT 0 1
postgres=# select application_name, state, sync_priority, sync_state from pg_stat_replication;
application_name | state | sync_priority | sync_state
------------------+-----------+---------------+------------
walreceiver | streaming | 0 | async
(1 row)
postgres=#
Step 2: Shutdown postgresql service of Primary Database
[root@pg_standby data]# systemctl stop postgresql-9.5.service
[root@pg_standby data]#
Step 3: Check for sync status and recovery status of Standby before promoting it:
[root@pg_primary data]# su - postgres
Last login: Wed Dec 13 00:15:09 EST 2017 on pts/3
-bash-4.2$ psql -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/4A000098 | 0/4A000098 | t
(1 row)
-bash-4.2$ psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
application_name | state | sync_priority | sync_state
------------------+-------+---------------+------------
(0 rows)
-bash-4.2$
Step 4: Open the Standby as new Primary by pg_ctl promote or creating a trigger file.
-bash-4.2$ grep trigger_file /var/lib/pgsql/9.5/data/recovery.conf
trigger_file = '/tmp/postgresql.trigger.5432'
-bash-4.2$ touch /tmp/postgresql.trigger.5432
-bash-4.2$ cat /tmp/postgresql.trigger.5432
cat: /tmp/postgresql.trigger.5432: No such file or directory
-bash-4.2$ psql -c "select pg_is_in_recovery();"
pg_is_in_recovery
-------------------
f
(1 row)
-bash-4.2$ cat /tmp/postgresql.trigger.5432
cat: /tmp/postgresql.trigger.5432: No such file or directory
-bash-4.2$
-----Check the log file of new Primary---
[root@pg_primary pg_log]# tail -f postgresql-Wed.log
< 2017-12-13 00:36:38.466 EST >LOG: redo done at 0/4A000028
< 2017-12-13 00:36:38.466 EST >LOG: last completed transaction was at log time 2017-12-13 00:30:26.653672-05
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000002000000000000004A’: No such file or directory
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000003.history’: No such file or directory
< 2017-12-13 00:36:38.488 EST >LOG: selected new timeline ID: 3
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000002.history’: No such file or directory
< 2017-12-13 00:36:38.615 EST >LOG: archive recovery complete
< 2017-12-13 00:36:38.621 EST >LOG: MultiXact member wraparound protections are now enabled
< 2017-12-13 00:36:38.623 EST >LOG: database system is ready to accept connections
< 2017-12-13 00:36:38.624 EST >LOG: autovacuum launcher started
---Now Standby become Primary Database----
[root@pg_primary data]# su - postgres
Last login: Wed Dec 13 00:34:10 EST 2017 on pts/3
-bash-4.2$ psql
psql (9.5.10)
Type "help" for help.
postgres=# select * from sebl;
org_name | address
---------------------+---------------
Southeast Bank Ltd. | Eunoos Center
Estern Bank Ltd. | Gulshan
Bangladesh Bank | Motijill
Prime Bank Ltd | Motijill
(4 rows)
postgres=# insert into sebl values('City Bank Ltd','Gulshan');
^CCancel request sent
WARNING: canceling wait for synchronous replication due to user request
DETAIL: The transaction has already committed locally, but might not have been replicated to the standby.
INSERT 0 1
postgres=# select * from sebl;
org_name | address
---------------------+---------------
Southeast Bank Ltd. | Eunoos Center
Estern Bank Ltd. | Gulshan
Bangladesh Bank | Motijill
Prime Bank Ltd | Motijill
City Bank Ltd | Gulshan
(5 rows)
postgres=#
Standby has been promoted as master and a new timeline followed which you can notice in logs
Note: In Primary (Old Standby) recovery.conf file become recovery.done after switchover
--***************--
[root@pg_primary data]# ls -lrt reco*
-rw-r--r-- 1 postgres postgres 607 Dec 11 23:11 recovery.done
[root@pg_primary data]# pwd
/var/lib/pgsql/9.5/data
[root@pg_primary data]#
--===========************=========---------
Step 5: Prepare New Standby for data sync with Primary
--===========************=========---------
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$ cat recovery.conf
recovery_target_timeline = 'latest'
standby_mode = 'on'
primary_conninfo = 'host=192.168.159.135 port=5432 user=repuser password=repuser@application_name=pgslave001'
restore_command = 'cp /var/lib/pgsql/9.5/main/archive/%f %p'
trigger_file = '/tmp/postgresql.trigger.5432'
archive_cleanup_command = '/usr/pgsql-9.5/bin/pg_archivecleanup -d /var/lib/pgsql/9.5/main/archive %r >> cleanup.log'
-bash-4.2$
Slave Server (old Primary) Configurations: postgresql.conf:
hot_standby = on
Step 6: Start Postgresql service in Standby
[root@pg_standby data]# systemctl start postgresql-9.5.service
[root@pg_standby data]#
---Check the log file of Standby and Primary---------
-------------Standby-----------
[root@pg_standby pg_log]# tail -f postgresql-Wed.log
< 2017-12-13 00:53:13.857 EST >LOG: replication terminated by primary server
< 2017-12-13 00:53:13.857 EST >DETAIL: End of WAL reached on timeline 2 at 0/4A000098.
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000003.history’: No such file or directory
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000004.history’: No such file or directory
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000003.history’: No such file or directory
< 2017-12-13 00:53:13.897 EST >LOG: new target timeline is 3
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000003000000000000004A’: No such file or directory
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000002000000000000004A’: No such file or directory
< 2017-12-13 00:53:13.903 EST >LOG: restarted WAL streaming at 0/4A000000 on timeline 3
< 2017-12-13 00:53:17.037 EST >LOG: redo starts at 0/4A000098
------------Primary---------
[root@pg_primary pg_log]# tail -f postgresql-Wed.log
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000003.history’: No such file or directory
< 2017-12-13 00:36:38.488 EST >LOG: selected new timeline ID: 3
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000002.history’: No such file or directory
< 2017-12-13 00:36:38.615 EST >LOG: archive recovery complete
< 2017-12-13 00:36:38.621 EST >LOG: MultiXact member wraparound protections are now enabled
< 2017-12-13 00:36:38.623 EST >LOG: database system is ready to accept connections
< 2017-12-13 00:36:38.624 EST >LOG: autovacuum launcher started
< 2017-12-13 00:40:43.387 EST >WARNING: canceling wait for synchronous replication due to user request
< 2017-12-13 00:40:43.387 EST >DETAIL: The transaction has already committed locally, but might not have been replicated to the standby.
< 2017-12-13 00:53:13.298 EST >LOG: standby "walreceiver" is now the synchronous standby with priority 1
Step 7. Verify the new Standby status.
[root@pg_standby data]# su - postgres
Last login: Wed Dec 13 00:30:07 EST 2017 on pts/1
-bash-4.2$ psql -p 5432 -c "select pg_is_in_recovery();"
pg_is_in_recovery
-------------------
t
(1 row)
-bash-4.2$
-------------Testing------
1. Insert data in primary
2. Check the data in primary and standby
3. try to insert data in standby
