Upgrading databases

On Wednesday 11 November 2009 11:38:31 Mark Richardson wrote:

Sorry if this is in the wrong list, but this is the closest I could find...

I have a 8.2.4 database and I installed 8.3.7 and tried using the same
/pgsql/data/base directory and got an error about incompatiblity.
The database currently has billions of records but it's sparsely
populated. It took about 4 months to just populate the original database
and I REALLY don't want to do that again.
Is there a conversion routine to change the files themselves? I know I can
dump and reload - but that takes large disk space and time. Any other way
around?
Thanks,
Mark

It's common to use SLONY to do this:

1) install 8.3.7 and create a DDL only dump of the 8.2.4 db and install it
into the new 8.3.7 system data dir (so maybe the 8.2 cluster lived in
/var/lib/pgsql/data and the 8.3 cluster lives in /var/lib/pgsql/8.3/data)

2) setup SLONY and replicate all tables and sequences from the 8.2 cluster
into the 8.3 cluster

3) once the db's are completely in sync, remove slony , drop (or at least
shut down) the 8.2 cluster, and move the users to the new cluster.

Mark Richardson wrote:

Sorry if this is in the wrong list, but this is the closest I could find...

I have a 8.2.4 database and I installed 8.3.7 and tried using the same
/pgsql/data/base directory and got an error about incompatiblity.

The database currently has billions of records but it's sparsely populated.
It took about 4 months to just populate the original database and I REALLY
don't want to do that again.

Is there a conversion routine to change the files themselves? I know I can
dump and reload - but that takes large disk space and time. Any other way
around?

There is no 'in-situ' DB upgrade between major updates from what I can
tell. There is, however, an alternative to the 'dump to disk' approach
which is to pipe the dump from the old DB to the new DB as outlined in
the migration guide here:
<http://www.postgresql.org/docs/8.3/static/migration.html&gt;

HTH.

Bosc.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

I have a 8.2.4 database and I installed 8.3.7 and tried using the
same /pgsql/data/base directory and got an error about incompatiblity.

...

Is there a conversion routine to change the files themselves? I know I can dump
and reload - but that takes large disk space and time. Any other way around?

There are basically four ways to upgrade:

1. Dump and restore. As you mention, it is slow and non-concurrent. Very large
block of client downtime.

2. pg_migrator. Conversion of your current system to the new one. Much faster
than dump and restore, but only works for certain versions and has some minor
caveats. Requires medium client downtime.

3. Slony. Setup a slave database, get everything in sync, then switch over to the
new database. Very minimal downtime (seconds to minutes). Requires all tables
have primary keys.

4. Bucardo. Similar to the steps of Slony above. More forgiving of interruptions
in the original replication event.

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200911141026
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkr+zJcACgkQvJuQZxSWSsgd+gCfbqJX/XQ4+tGSHCC7rE6D/Q+j
f/kAoJpdva3ylipMmDF45jIsmqC8TTR+
=ZlaQ
-----END PGP SIGNATURE-----

On Sat, Nov 14, 2009 at 3:28 PM, Greg Sabino Mullane <greg@turnstep.com> wrote:

3. Slony. Setup a slave database, get everything in sync, then switch over to the
new database. Very minimal downtime (seconds to minutes). Requires all tables
have primary keys.

4. Bucardo. Similar to the steps of Slony above. More forgiving of interruptions
in the original replication event.

I've never done this process myself. I'm wondering how flexible Slony
and Bucardo are with the versions of Postgres they support.

Does the Slony version run on the new version have to match the Slony
version on the old one? Do old versions of Slony work on new versions
of Postgres? Or do you have to update the version of Slony on the old
database before you can move to a new database version because the new
database version requires a newer version of Slony?

I'm even less familiar with Bucardo but have the same questions there too.

--
greg

I didn't actually run Sloany, so their may be some better people to ask.
 
However, the versions of Sloany depend on different versions of postgres - so if you use the latest version of Postgres, then you'll need the latest version of Sloany.  From what I could tell, you run Sloany on your master (which ever database you want to propogate - probably an old database) and it updates/populates the slaves (the new database).  So you'll have to get an older version os Sloany to do this.  Again, I never ran it so take my advice for what it's worth.

--- On Sat, 11/14/09, Greg Stark <gsstark@mit.edu> wrote:

From: Greg Stark <gsstark@mit.edu>
Subject: Re: Upgrading databases
To: "Greg Sabino Mullane" <greg@turnstep.com>
Cc: pgsql-interfaces@postgresql.org, markmapo@yahoo.com
Date: Saturday, November 14, 2009, 11:48 AM

On Sat, Nov 14, 2009 at 3:28 PM, Greg Sabino Mullane <greg@turnstep.com> wrote:

3. Slony. Setup a slave database, get everything in sync, then switch over to the
new database. Very minimal downtime (seconds to minutes). Requires all tables
have primary keys.

4. Bucardo. Similar to the steps of Slony above. More forgiving of interruptions
in the original replication event.

I've never done this process myself. I'm wondering how flexible Slony
and Bucardo are with the versions of Postgres they support.

Does the Slony version run on the new version have to match the Slony
version on the old one? Do old versions of Slony work on new versions
of Postgres? Or do you have to update the version of Slony on the old
database before you can move to a new database version because the new
database version requires a newer version of Slony?

I'm even less familiar with Bucardo but have the same questions there too.

--
greg

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

Greg quoting Greg asks:

3. Slony. Setup a slave database, get everything in sync, then
switch over to the new database. Very minimal downtime (seconds
to minutes). Requires all tables have primary keys.

4. Bucardo. Similar to the steps of Slony above. More forgiving of
interruptions in the original replication event.

I've never done this process myself. I'm wondering how flexible Slony
and Bucardo are with the versions of Postgres they support.

Does the Slony version run on the new version have to match the Slony
version on the old one? Do old versions of Slony work on new versions
of Postgres? Or do you have to update the version of Slony on the old
database before you can move to a new database version because the new
database version requires a newer version of Slony?

ObNote: Interfaces is not really the correct mailing list for this, so I'm
cc'ing it over to pgsql-general.

Yes, Slony must be the same. Older versions of Slony will not work against
new versions of Postgres (nor will newer versions of Slony work against
older versions of Postgres).

I'm even less familiar with Bucardo but have the same questions there too.

Bucardo can connect to any Postgres 8.1 or greater. It may even work against
older versions, but nobody has tested with versions that old that I know of.
The source and target can be different versions of Postgres, no problem.
Unlike Slony, there is only a single master Bucardo daemon that can live
anywhere, even a box that is not the master or slave, as long as it can
talk to both.

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200911141738
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkr/MiEACgkQvJuQZxSWSsgGcQCfVCr0jIciY5b9N4OljPn61cMo
BoEAn03wsZ5EDrqZ2WRZIOPsAa/nXsk0
=L94s
-----END PGP SIGNATURE-----