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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 ®
Cuda Systems LLC

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux