Re: How to upgrade from 9.1 to 9.2 with replication?

On Sun, Oct 28, 2012 at 9:40 PM, Claudio Freire <klaussfreire(at)gmail(dot)com>wrote:

> On Sun, Oct 28, 2012 at 12:15 PM, Karl Denninger <karl(at)denninger(dot)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.
>

I also think that's a good option for most case, but not because it is
faster, in fact if you count the whole process, it is slower. But the
master will be on backup state (between pg_start_backup and pg_stop_backup)
for a small period of time which make things go faster on the master
(nothing different on slave though).

> 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).
>
>
If you have incremental backup, a restore_command on recovery.conf seems
better than running rsync again when the slave get out of sync. Doesn't it?

Regards,
--
Matheus de Oliveira
Analista de Banco de Dados PostgreSQL
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres