Re: Managing PostgreSQL Streaming Replication Cluster

On Thu, May 14, 2015 at 11:39 PM Wei Shan <weishan(dot)ang(at)gmail(dot)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
>