Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TSUag-0007fR-AM for pgsql-performance@postgresql.org; Sun, 28 Oct 2012 15:15:54 +0000 Received: from wsip-70-169-168-7.pn.at.cox.net ([70.169.168.7] helo=FS.denninger.net) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TSUae-0006ED-0a for pgsql-performance@postgresql.org; Sun, 28 Oct 2012 15:15:53 +0000 Received: from [127.0.0.1] (localhost [127.0.0.1]) by FS.denninger.net (8.14.4/8.13.1) with ESMTP id q9SFFoiu044751 for ; Sun, 28 Oct 2012 09:15:50 -0600 (CST) (envelope-from karl@denninger.net) Received: from [127.0.0.1] [192.168.1.40] by Spamblock-sys (LOCAL); Sun Oct 28 09:15:50 2012 Message-ID: <508D4C21.50503@denninger.net> Date: Sun, 28 Oct 2012 10:15:45 -0500 From: Karl Denninger User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:16.0) Gecko/20121010 Thunderbird/16.0.1 MIME-Version: 1.0 To: delongboy CC: pgsql-performance@postgresql.org Subject: Re: How to upgrade from 9.1 to 9.2 with replication? References: <1350598882643-5728941.post@n5.nabble.com> <50816749.6010804@denninger.net> <1351174324740-5729618.post@n5.nabble.com> In-Reply-To: <1351174324740-5729618.post@n5.nabble.com> X-Enigmail-Version: 1.4.5 Content-Type: multipart/alternative; boundary="------------000805030300030805090307" X-Antivirus: avast! (VPS 121028-0, 10/28/2012), Outbound message X-Antivirus-Status: Clean X-Pg-Spam-Score: -0.9 (/) X-Archive-Number: 201210/337 X-Sequence-Number: 48296 This is a multi-part message in MIME format. --------------000805030300030805090307 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-MIME-Autoconverted: from 8bit to quoted-printable by FS.denninger.net id q9SFFoiu044751 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 be= ing > 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. =20 > We will soon be tripling the size of our database as we move oracle dat= a > 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.=20 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. --=20 -- Karl Denninger /The Market Ticker =AE/ Cuda Systems LLC --------------000805030300030805090307 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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
--------------000805030300030805090307--