Обсуждение: Upgrading Postgres question
Hello all,
I am in the process of updating my DB on Postgres 8.1.11 to 8.3.4. I also use Slony 1.2.14 for replication.
Is there a safe path on how to accomplish this, please advice on what steps I will need to consider. Bear in mind that I am planning to skip from Postgres 8.1.x to 8.3.x and I use Slony to replicate my production DB into two more boxes simultaneously.
Thanks,
Tony Fernandez
On Wed, 2008-11-05 at 15:08 -0600, Tony Fernandez wrote: > Hello all, > > > > I am in the process of updating my DB on Postgres 8.1.11 to 8.3.4. I > also use Slony 1.2.14 for replication. > > > > Is there a safe path on how to accomplish this, please advice on what > steps I will need to consider. Bear in mind that I am planning to > skip from Postgres 8.1.x to 8.3.x and I use Slony to replicate my > production DB into two more boxes simultaneously. > I don't know about slony.... but, one way to do this is by (roughly): a) dump all contents of your pg with pg_dumpall b) install pg8.3 in a test server c) restore the dump into the test server (su postgres; psql -f my_dump); d) if all went well you can purge the 8.1 database from the disc or u can skip to the next step and install the new db in some alternative directory, in case you need to revert to pg8.1 latter. e) install 8.3 on the main server (maybe you will have to use initdb) f) restore the dump into the new 8.3 in the production server. This is how I do it :) Hope it fits your needs I also noticed some SQL parsing changes (like the need to explicitlly cast from text to numeric)... you shold try all your apps running against the test server before purging the old db the mais issue here is that, from 8.1 to 8.3 the underlying database files have changed format... so u need the dump/restore. u shld rd this: http://www.postgresql.org/docs/current/static/install-upgrading.html Joao > > > Thanks, > > > > Tony Fernandez > > > > >
Thanks Joao, That is what I have done, but wanted to see if there was any other known potential risks. The fact about including an extra backup to go back if ever needed was underestimated, so I will consider it but not in my live servers. Regards, Tony Fernandez -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Joao Ferreira gmail Sent: Monday, November 10, 2008 10:36 AM To: pgsql-general Subject: Re: [GENERAL] Upgrading Postgres question On Wed, 2008-11-05 at 15:08 -0600, Tony Fernandez wrote: > Hello all, > > > > I am in the process of updating my DB on Postgres 8.1.11 to 8.3.4. I > also use Slony 1.2.14 for replication. > > > > Is there a safe path on how to accomplish this, please advice on what > steps I will need to consider. Bear in mind that I am planning to > skip from Postgres 8.1.x to 8.3.x and I use Slony to replicate my > production DB into two more boxes simultaneously. > I don't know about slony.... but, one way to do this is by (roughly): a) dump all contents of your pg with pg_dumpall b) install pg8.3 in a test server c) restore the dump into the test server (su postgres; psql -f my_dump); d) if all went well you can purge the 8.1 database from the disc or u can skip to the next step and install the new db in some alternative directory, in case you need to revert to pg8.1 latter. e) install 8.3 on the main server (maybe you will have to use initdb) f) restore the dump into the new 8.3 in the production server. This is how I do it :) Hope it fits your needs I also noticed some SQL parsing changes (like the need to explicitlly cast from text to numeric)... you shold try all your apps running against the test server before purging the old db the mais issue here is that, from 8.1 to 8.3 the underlying database files have changed format... so u need the dump/restore. u shld rd this: http://www.postgresql.org/docs/current/static/install-upgrading.html Joao > > > Thanks, > > > > Tony Fernandez > > > > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.175 / Virus Database: 270.9.0/1776 - Release Date: 11/8/2008 6:49 PM
Tony Fernandez wrote: > Hello all, > > > > I am in the process of updating my DB on Postgres 8.1.11 to 8.3.4. I > also use Slony 1.2.14 for replication. The other option is to use slony itself - it will replicate between versions. -- Richard Huxton Archonet Ltd
On Wed, Nov 5, 2008 at 2:19 PM, Tony Fernandez <Tony.Fernandez@vocalocity.com> wrote: > Hello all, > > > > I am in the process of updating my DB on Postgres 8.1.11 to 8.3.4. I also > use Slony 1.2.14 for replication. Then you're set. One of the primary purposes of slony is upgrading in place. Take one of your backup slaves offline, upgrade it to 8.3, recreate it as a slony slave, and let it run for a few days. When all seems well, swap the two servers. downtime measured in seconds.
On Nov 10, 2008, at 10:03 AM, Scott Marlowe wrote: > On Wed, Nov 5, 2008 at 2:19 PM, Tony Fernandez > <Tony.Fernandez@vocalocity.com> wrote: >> Hello all, >> >> >> >> I am in the process of updating my DB on Postgres 8.1.11 to 8.3.4. >> I also >> use Slony 1.2.14 for replication. > > Then you're set. One of the primary purposes of slony is upgrading in > place. Take one of your backup slaves offline, upgrade it to 8.3, > recreate it as a slony slave, and let it run for a few days. When all > seems well, swap the two servers. downtime measured in seconds. True. However, Tony, if you've never used Slony and you're just looking to get replication set up for the purpose of upgrading then I'd recommend going with Londiste (part of the Skytools package). It's dirt simple to get set up and running. Here's the rundown: 1. Install psycopg2 and skytools on both hosts 2. On the master (provider in Londiste terminology) create a pgq.ini file for PgQ, this just has the provder db connection info along with the locations for the PgQ ticker pid and log files. 3. Install PgQ on the master and start the ticker: pgqadmin.py pgq.ini install pgqadmin.py -d pgq.ini ticker 4. On the slave (subscriber in Londiste terminology), create a repl.ini file, this has the connection info for both the provider and subscriber along with pid and log filenames 5. Install Londiste on the provider and subscriber dbs (run londiste commands from the subscriber): londiste.py repl.ini provider install londiste.py repl.ini subscriber install 6. Add your tables and sequences to both the provider and subscriber sets: londiste.py repl.ini provider add --all londiste.py repl.ini provider add-seq --all londiste.py repl.ini subscriber add --all londiste.py repl.ini subscribet add-seq --all 9. Start replicating! londiste -d repl.ini Then, when you're ready to failover: 1. Shut off access from your app to the provider 2. Watch the londiste log until you see a string of 0s for events replayed 3. Stop replication: londiste.py -s repli.ini 4. Point your app at your new upgraded db! So, that's the gist of a Londiste replicated upgrade. I typically create a separate superuser account named londiste_db for the PgQ and Londiste connections and disable all access to the provider from any other users while replication is running. Of course, Londiste can be used for normal, constantly running master/ slave replication but for that you'd want to get more familiar with Londiste's various commands. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k