Re: Upgrading Postgres question

On Nov 10, 2008, at 10:03 AM, Scott Marlowe wrote:

> On Wed, Nov 5, 2008 at 2:19 PM, Tony Fernandez
> <Tony(dot)Fernandez(at)vocalocity(dot)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