On Wed, 22 Feb 2006, Shaun Thomas wrote:
> I'm in charge of a very large database, and we're using a highly
> decrepit version of Postgresql currently. After searching through the
> archives, Google, and trying out several replication engines, I have a
> question.
>
> I had originally considered Slony-I, as it doesn't seem to require
> version compatibility between nodes like pgCluster, so upgrading from
> 7.4.2 to 8.1.3 would be a possible, if slow process. But after looking
> into the level of micro-management necessary, such as defining sets of
> every table on a per-database level, then having it add artificial
> primary-keys to applicable tables, it just doesn't seem like a good
> choice. Not a fault of Slony-I, but several multi-gig databases hosting
> hundreds of tables would be a nightmare to use with Slony-I.
There are tools in the /tools directory. In particular, take a look at
/tools/altperl. You can use to set up slony and replicate all tables with
very little hassle. Slony adds the primary keys for you.
>
> Then I thought about the backup/recovery system and the WAL files.
> Would this scenario be possible:
>
> 1. Do a pg_dumpall on the existing database running 7.4.2.
> 2. Do a psql -f foo template1 on the new database running 8.1.3.
> 3. Wait a very long time while the new database loads.
> 4. Shut down old database.
> 5. Start the new database in restore mode, and point it to the WAL
> files from the old database.
> 6. Wait for restore to finish.
> 7. Restart the new database.
This is not possible. On your 7.4 systems tables have a unique object ID
to identify them. When you restore the dump on the 8.1 system they will
have different object IDs. There are several other issues of this
nature.
Also, the binary format of the log files has changed and the whole process
would be significantly more difficult than using slony to upgrade. The
size of your databases does not sound like an issue - lots of people have
done what you're doing with GB range databases.
>
> I wondered about this, as the pg_dumpall/restore would take a very long
> time for a 50GB database cluster, but theoretically the WAL files would
> continue to accumulate on the old db while this loading was taking
> place.
> If the WAL formats were compatible, the total upgrade time would only be
> restricted to how long it took to replay the WAL files in the new
> database. Does the current format of the WAL files make this possible?
> If not, is such an option for the future?
It is possible that someone could write a translation tool which
translates WAL entries into SQL, but there is some messiness to deal with
(backup blocks, create table foo; insert into foo; drop table foo; and
more). I think the best solution is an inplace upgrade tool which does all
the binary conversions, additions and subtractions itself. This could be
quite cheap because the conversion will often only affect system catalogs
not user tables.
Thanks,
Gavin