Re: [INTERFACES] Upgrading databases
Kevin Kempter
> 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.
--
Sent via pgsql-interfaces mailing list (pgsql-in...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-interfaces
Bosco Rama
> 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>
HTH.
Bosc.
Greg Sabino Mullane
-----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 gr...@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-----
Greg Stark
> 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
Greg Sabino Mullane
-----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 gr...@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-----