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?

  • From your link, "It is also possible to use certain replication methods, such as Slony, to create a standby server with the updated version of PostgreSQL. The standby can be on the same computer or a different computer. Once it has synced up with the master server (running the older version of PostgreSQL), you can switch masters and make the standby the master and shut down the older database instance. Such a switch-over results in only several seconds of downtime for an upgrade."
    – swasheck
    May 22 '12 at 14:57
  • Setting up slony is a major undertaking just to upgrade the binary. There has to be a better way? May 22 '12 at 18:21

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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.