Re: Attempting upgrade path; is this possible?

Ühel kenal päeval, K, 2006-02-22 kell 16:02, kirjutas Shaun Thomas:
> 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.

Unfortunately Slony is still the only solution, if you need to upgrade
between major versions with minimal (i.e tens of seconds) downtime.

You could try to script the subscription process in Slony. There should
even be some ready-made perl scripts for that bundled with slony.

The requirement for (candidate) primary key's may still be prohibiting,
as currently creating an index on large table locks that table for
writes for the duration of create index.

> 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.
>
> 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.

This works not!

WAL files store actual page images, so if the WAL formats were
compatible, you would not need to do any data manipulation at all, as
the on-disk formats would be compatible.

Also, WAL is done on physical page image level, so the above scenario
would not even work for 7.4.2->7.4.2 copy.

-------------
Hannu