Postgres Pro
Downloads
Home   >   mailing lists

Thread: Managing PostgreSQL Streaming Replication Cluster

Managing PostgreSQL Streaming Replication Cluster

From
Wei Shan
Date:
Hi all,

I'm currently managing a PostgreSQL SR cluster with the following configuration.

1. PostgreSQL 9.2.4
2. Pacemaker/corosync as the cluster software
3. Streaming replication within 1 master and 2 slaves

If I want to change a parameter in postgresql.conf or any configuration which requires a reboot, how should I perform it without downtime? Also, if I want to perform rolling upgrade, what's the steps involve?

I tried googling for help but I couldn't find much on it.

Thanks!

--
Regards,
Ang Wei Shan

Re: Managing PostgreSQL Streaming Replication Cluster

From
Sameer Kumar
Date:


On Thu, May 14, 2015 at 11:39 PM Wei Shan <weishan.ang@gmail.com> wrote:
Hi all,

I'm currently managing a PostgreSQL SR cluster with the following configuration.

1. PostgreSQL 9.2.4
2. Pacemaker/corosync as the cluster software
3. Streaming replication within 1 master and 2 slaves

If I want to change a parameter in postgresql.conf or any configuration which requires a reboot, how should I perform it without downtime? Also, if I want to perform rolling upgrade, what's the steps involve?


You have not included too much details e.g. the replication setup (recovery.conf), target patch, OS etc. So I will make some assumption here and go ahead.

You should be applying the changes to the slaves first and then to the master. 
Though it is not likely but there are chances that wal format might get changed (even) in minor patches. But since PG community tries best to maintain backward compatibility the New version of PostgreSQL will still be able to read from a master which is on older version.

I am assuming that your replication setup is 

Master --> Slave 1   AND Master ---> Slave2 [not cascaded]

So stop the replication between master and 1st slave and apply the patch on slave1 and then repeat the process with Slave2.

Once done, change the Replication setup to Master--> Slave1-->Slave2 [cascaded].
Ensure that you have archiving in place since promoting a slave to master will cause a timeline switch and until v9.2 that info is not written in pg_xlog and goes only to archives and can be restored when you have restore_command in recovery.conf.

Now you can do a promotion so that Slave 1 becomes master. Once completed the patching of original Master add it back as Slave. You can reconfigure and do a controlled promotion again to bring the whole cluster in same state with original role of each node restored. 
 
I tried googling for help but I couldn't find much on it.

Thanks!


--
Regards,
Ang Wei Shan