Обсуждение: Upgrading databases
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 |
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> 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:
|
-----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-----