In the last post we looked at how you can patch a Postgres Plus Advanced server. Wouldn’t it be nice, in a standby configuration, to patch the standby first without touching the master, then do a controlled switchover and finally patch the old master? In case this is a configuration with EDB Failover Manager the only downtime which would happen is the relocation of the VIP from one node to another (if you use a VIP). Without using a VIP but using pgpool-II the downtime is even less. Lets see if it works by starting from my usual EDB Failover Manager configuration.
This is the current status of my failover cluster:
[root@edbbart efm-2.1]# /usr/efm-2.1/bin/efm cluster-status efm Cluster Status: efm VIP: 192.168.22.250 Automatic failover is disabled. Agent Type Address Agent DB Info -------------------------------------------------------------- Witness 192.168.22.244 UP N/A Master 192.168.22.243 UP UP Standby 192.168.22.245 UP UP Allowed node host list: 192.168.22.244 192.168.22.245 192.168.22.243 Membership coordinator: 192.168.22.245 Standby priority host list: 192.168.22.245 Promote Status: DB Type Address XLog Loc Info -------------------------------------------------------------- Master 192.168.22.243 0/4A000140 Standby 192.168.22.245 0/4A000140 Standby database(s) in sync with master. It is safe to promote.
All is fine, I have one master, one standby and one witness. Going straight forward lets shutdown the standby (please notice that I have disabled auto failover):
Shutdown the standby database:
postgres@edbppasstandby:/home/postgres/ [pg950] pg_ctl -D /u02/pgdata/PGSITE2 stop -m fast waiting for server to shut down.... done server stopped
What happened to my cluster?
[root@edbbart efm-2.1]# /usr/efm-2.1/bin/efm cluster-status efm Cluster Status: efm VIP: 192.168.22.250 Automatic failover is disabled. Agent Type Address Agent DB Info -------------------------------------------------------------- Witness 192.168.22.244 UP N/A Master 192.168.22.243 UP UP Standby 192.168.22.245 UP UP Allowed node host list: 192.168.22.244 192.168.22.245 192.168.22.243 Membership coordinator: 192.168.22.245 Standby priority host list: 192.168.22.245 Promote Status: DB Type Address XLog Loc Info -------------------------------------------------------------- Master 192.168.22.243 0/4A000140 Unknown 192.168.22.245 UNKNOWN Connection to 192.168.22.245:4445 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections. One or more standby databases are not in sync with the master database. [root@edbbart efm-2.1]#
Not really surprising EFM complains that the standby is not reachable anymore. Thats fine. Lets patch the standby:
postgres@edbppasstandby:/u01/app/postgres/software/ [pg950] chmod +x postgresplusas-9.5.4.9-1-linux-x64.run postgres@edbppasstandby:/u01/app/postgres/software/ [pg950] ./postgresplusas-9.5.4.9-1-linux-x64.run --extract-only true --prefix /u01/app/postgres/product/95/db_1/9.5AS/ Language Selection Please select the installation language [1] English - English [2] Japanese - 日本語 [3] Simplified Chinese - 简体中文 [4] Traditional Chinese - 繁体中文 [5] Korean - 한국어 Please choose an option [1] : 1 ---------------------------------------------------------------------------- Welcome to the Postgres Plus Advanced Server Setup Wizard. ---------------------------------------------------------------------------- Please specify the directory where Postgres Plus Advanced Server will be installed. Installation Directory [/u01/app/postgres/product/95/db_1/9.5AS]: ---------------------------------------------------------------------------- Setup is now ready to begin installing Postgres Plus Advanced Server on your computer. Do you want to continue? [Y/n]: Y ---------------------------------------------------------------------------- Please wait while Setup installs Postgres Plus Advanced Server on your computer. Installing Database Server 0% ______________ 50% ______________ 100% ######################################### ---------------------------------------------------------------------------- Setup has finished installing Postgres Plus Advanced Server on your computer. postgres@edbppasstandby:/u01/app/postgres/software/ [pg950]
… bring it up again:
postgres@edbppasstandby:/home/postgres/ [pg950] pg_ctl -D /u02/pgdata/PGSITE2 start server starting
… checking the PostgreSQL log file all is fine, streaming restarted:
2016-10-05 11:35:25.745 GMT - 2 - 4984 - - @ LOG: entering standby mode 2016-10-05 11:35:25.751 GMT - 3 - 4984 - - @ LOG: consistent recovery state reached at 0/4A000108 2016-10-05 11:35:25.751 GMT - 4 - 4984 - - @ LOG: redo starts at 0/4A000108 2016-10-05 11:35:25.751 GMT - 5 - 4984 - - @ LOG: invalid record length at 0/4A000140 2016-10-05 11:35:25.751 GMT - 4 - 4982 - - @ LOG: database system is ready to accept read only connections 2016-10-05 11:35:25.755 GMT - 1 - 4988 - - @ LOG: started streaming WAL from primary at 0/4A000000 on timeline 8
What is the status of EFM?
postgres@edbppasstandby:/home/postgres/ [pg950] efmstat Cluster Status: efm VIP: 192.168.22.250 Automatic failover is disabled. Agent Type Address Agent DB Info -------------------------------------------------------------- Witness 192.168.22.244 UP N/A Master 192.168.22.243 UP UP Idle 192.168.22.245 UP UNKNOWN Allowed node host list: 192.168.22.244 192.168.22.245 192.168.22.243 Membership coordinator: 192.168.22.245 Standby priority host list: (List is empty.) Promote Status: DB Type Address XLog Loc Info -------------------------------------------------------------- Master 192.168.22.243 0/4A000140 No standby databases were found. Idle Node Status (idle nodes ignored in XLog location comparisons): Address XLog Loc Info -------------------------------------------------------------- 192.168.22.245 0/4A000140 DB is in recovery.
Status “Idle” for the standby which is fine, just resume:
postgres@edbppasstandby:/home/postgres/ [pg950] sudo /usr/efm-2.1/bin/efm resume efm Resume command successful on local agent. postgres@edbppasstandby:/home/postgres/ [pg950] efmstat Cluster Status: efm VIP: 192.168.22.250 Automatic failover is disabled. Agent Type Address Agent DB Info -------------------------------------------------------------- Witness 192.168.22.244 UP N/A Standby 192.168.22.245 UP UP Master 192.168.22.243 UP UP Allowed node host list: 192.168.22.244 192.168.22.245 192.168.22.243 Membership coordinator: 192.168.22.245 Standby priority host list: 192.168.22.245 Promote Status: DB Type Address XLog Loc Info -------------------------------------------------------------- Master 192.168.22.243 0/4A000140 Standby 192.168.22.245 0/4A000140 Standby database(s) in sync with master. It is safe to promote.
… and everything is back as it should be. Time to switchover:
postgres@edbppasstandby:/home/postgres/ [PGSITE2] sudo /usr/efm-2.1/bin/efm promote efm -switchover Promote/switchover command accepted by local agent. Proceeding with promotion and will reconfigure original master. Run the 'cluster-status' command for information about the new cluster state.
The master and the standby should have switched its roles:
postgres@edbppasstandby:/home/postgres/ [PGSITE2] efmstat Cluster Status: efm VIP: 192.168.22.250 Automatic failover is disabled. Agent Type Address Agent DB Info -------------------------------------------------------------- Master 192.168.22.245 UP UP Witness 192.168.22.244 UP N/A Standby 192.168.22.243 UP UP Allowed node host list: 192.168.22.244 192.168.22.245 192.168.22.243 Membership coordinator: 192.168.22.245 Standby priority host list: 192.168.22.243 Promote Status: DB Type Address XLog Loc Info -------------------------------------------------------------- Master 192.168.22.245 0/4B0001A8 Standby 192.168.22.243 0/4B0001A8 Standby database(s) in sync with master. It is safe to promote. postgres@edbppasstandby:/home/postgres/ [PGSITE2]
Same procedure again, stop the standby:
postgres@edbppas:/home/postgres/ [PGSITE1] pg_ctl -D /u02/pgdata/PGSITE1 stop -m fast waiting for server to shut down.... done server stopped
postgres@edbppasstandby:/home/postgres/ [PGSITE2] efmstat Cluster Status: efm VIP: 192.168.22.250 Automatic failover is disabled. Agent Type Address Agent DB Info -------------------------------------------------------------- Standby 192.168.22.243 UP UP Witness 192.168.22.244 UP N/A Master 192.168.22.245 UP UP Allowed node host list: 192.168.22.244 192.168.22.245 192.168.22.243 Membership coordinator: 192.168.22.245 Standby priority host list: 192.168.22.243 Promote Status: DB Type Address XLog Loc Info -------------------------------------------------------------- Master 192.168.22.245 0/4B0001A8 Unknown 192.168.22.243 UNKNOWN Connection to 192.168.22.243:4445 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections. One or more standby databases are not in sync with the master database.
Apply the patch:
postgres@edbppas:/u01/app/postgres/software/ [PGSITE1] ./postgresplusas-9.5.4.9-1-linux-x64.run --extract-only true --prefix /u01/app/postgres/product/95/db_1/9.5AS/ Language Selection Please select the installation language [1] English - English [2] Japanese - 日本語 [3] Simplified Chinese - 简体中文 [4] Traditional Chinese - 繁体中文 [5] Korean - 한국어 Please choose an option [1] : 1 ---------------------------------------------------------------------------- Welcome to the Postgres Plus Advanced Server Setup Wizard. ---------------------------------------------------------------------------- Please specify the directory where Postgres Plus Advanced Server will be installed. Installation Directory [/u01/app/postgres/product/95/db_1/9.5AS]: ---------------------------------------------------------------------------- Setup is now ready to begin installing Postgres Plus Advanced Server on your computer. Do you want to continue? [Y/n]: y ---------------------------------------------------------------------------- Please wait while Setup installs Postgres Plus Advanced Server on your computer. Installing Database Server 0% ______________ 50% ______________ 100% ######################################### ---------------------------------------------------------------------------- Setup has finished installing Postgres Plus Advanced Server on your computer.
Startup again:
postgres@edbppas:/u01/app/postgres/software/ [PGSITE1] pg_ctl -D /u02/pgdata/PGSITE1 start server starting
Streaming restarted:
2016-10-05 11:45:36.807 GMT - 2 - 4883 - - @ LOG: entering standby mode 2016-10-05 11:45:36.810 GMT - 3 - 4883 - - @ LOG: consistent recovery state reached at 0/4B0000C8 2016-10-05 11:45:36.810 GMT - 4 - 4883 - - @ LOG: redo starts at 0/4B0000C8 2016-10-05 11:45:36.810 GMT - 5 - 4883 - - @ LOG: invalid record length at 0/4B0001A8 2016-10-05 11:45:36.810 GMT - 4 - 4881 - - @ LOG: database system is ready to accept read only connections 2016-10-05 11:45:36.815 GMT - 1 - 4887 - - @ LOG: started streaming WAL from primary at 0/4B000000 on timeline 9
Same status “Idle” as before:
postgres@edbppasstandby:/home/postgres/ [PGSITE2] efmstat Cluster Status: efm VIP: 192.168.22.250 Automatic failover is disabled. Agent Type Address Agent DB Info -------------------------------------------------------------- Idle 192.168.22.243 UP UNKNOWN Witness 192.168.22.244 UP N/A Master 192.168.22.245 UP UP Allowed node host list: 192.168.22.244 192.168.22.245 192.168.22.243 Membership coordinator: 192.168.22.245 Standby priority host list: (List is empty.) Promote Status: DB Type Address XLog Loc Info -------------------------------------------------------------- Master 192.168.22.245 0/4B0001A8 No standby databases were found. Idle Node Status (idle nodes ignored in XLog location comparisons): Address XLog Loc Info -------------------------------------------------------------- 192.168.22.243 0/4B0001A8 DB is in recovery.
Resume:
postgres@edbppas:/home/postgres/ [PGSITE1] sudo /usr/efm-2.1/bin/efm resume efm Resume command successful on local agent.
Fully back:
postgres@edbppasstandby:/home/postgres/ [PGSITE2] efmstat Cluster Status: efm VIP: 192.168.22.250 Automatic failover is disabled. Agent Type Address Agent DB Info -------------------------------------------------------------- Standby 192.168.22.243 UP UP Witness 192.168.22.244 UP N/A Master 192.168.22.245 UP UP Allowed node host list: 192.168.22.244 192.168.22.245 192.168.22.243 Membership coordinator: 192.168.22.245 Standby priority host list: 192.168.22.243 Promote Status: DB Type Address XLog Loc Info -------------------------------------------------------------- Master 192.168.22.245 0/4B0001A8 Standby 192.168.22.243 0/4B0001A8 Standby database(s) in sync with master. It is safe to promote.
Works like a charm. The organizational overhead is much more than what you actually need to do. Technically this is a task of a few minutes.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)