Infrastructure at your Service

Daniel Westermann

How to patch Postgres Plus Advanced Server in a Standby configuration

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.

Leave a Reply

Daniel Westermann
Daniel Westermann

Senior Consultant and Technology Leader Open Infrastructure