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

On 10/25/2012 9:12 AM, delongboy wrote:
> I brought down the master then the slave and upgraded both. Then I did the
> rsync and brought both up.. This worked. However with the database being
> very large it took quite a while. It seemed rsync had to make a lot of
> changes.. this surprised me. I thought they would be almost identical.
> But in the end it did work. just took longer than I had hoped.
> We will soon be tripling the size of our database as we move oracle data
> in.. so this process may not be so feasible next time.
What I have done successfully is this.

1. Set up a SECOND instance of the slave with the NEW software version,
but do not populate it.

2. Turn off the original slave.

3. Upgrade the master. This is your "hard" downtime you cannot avoid.
Restart the master on the new version and resume operations.

3. At this point the slave cannot connect as it has a version mismatch,
so do NOT restart it.

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.

BTW this is /*much*/ faster than using pg_basebackup (by a factor of
four or more at my installation!) -- it appears that the latter does not
effectively use compression of the data stream even if your SSL config
is in use and would normally use it; rsync used with the "z" option does
use it and very effectively so.

--
-- Karl Denninger
/The Market Ticker ®/ <http://market-ticker.org>
Cuda Systems LLC