Postgresql – Upgrade Postgres without doing a pg_dumpall

postgresqlupgrade

I'm upgrading from postgres 8.4 to 9.0 and looking at the manual it looks like this is my only choice:

pg_dumpall -p 5432 | psql -d postgres -p 6543

Which means I'll need to stop any writes to the database and wait whilst a couple of gb's of data is transferred.

Is there a better way in which I can use the Write Ahead Log (WAL) to replay any new SQL onto the new database?

Best Answer

Since 8.4, pg_upgrade is the alternate choice to dump-restore for upgrades between major versions. It is actually linked at the end of the doc page on migration you already mentioned.

As for replaying WAL files from an older version, the doc says we can't:

In general, log shipping between servers running different major PostgreSQL release levels is not possible

(in Log-Shipping Standby Servers).