Re: How to upgrade from 9.1 to 9.2 with replication? - Mailing list pgsql-performance

On Sun, Oct 28, 2012 at 12:15 PM, Karl Denninger <karl@denninger.net> wrote:
> 4. pg_start_backup('Upgrading') and rsync the master to the NEW slave
> directory ex config files (postgresql.conf, recovery.conf and pg_hba.conf,
> plus the SSL keys if you're using it).  Do NOT rsync pg_xlog's contents or
> the WAL archive logs from the master.  Then pg_stop_backup().  Copy in the
> config files from your slave repository (very important as you must NOT
> start the slave server without the correct slave config or it will
> immediately destroy the context that allows it come up as a slave and you
> get to start over with #4.)
>
> 5. Bring up the NEW slave instance.  It will immediately connect back to the
> new master and catch up.  This will not take very long as the only data it
> needs to fetch is that which changed during #4 above.
>
> If you have multiple slaves you can do multiple rsync's (in parallel if you
> wish) to them between the pg_start_backup and pg_stop_backup calls.   The
> only "gotcha" doing it this way is that you must be keeping enough WAL
> records on the master to cover the time between the pg_start_backup call and
> when you bring the slaves back up in replication mode so they're able to
> retrieve the WAL data and come back into sync.  If you come up short the
> restart will fail.
>
> When the slaves restart they will come into consistency almost immediately
> but will be materially behind until the replication protocol catches up.

That's why I perform two rsyncs, one without pg_start_backup, and one
with. Without, you get no guarantees, but it helps rsync be faster
next time. So you cut down on the amount of changes that second rsync
will have to transfer, you may even skip whole segments, if your
update patterns aren't too random.

I still have a considerable amount of time between the start_backup
and end_backup, but I have minimal downtimes and it never failed.

Just for the record, we do this quite frequently in our pre-production
servers, since the network there is a lot slower and replication falls
irreparably out of sync quite often. And nobody notices when we
re-sync the slave. (ie: downtime at the master is nonexistent).