Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite
1

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?

share|improve this question
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? – Aaron Brown May 22 '12 at 18:21

1 Answer

up vote 4 down vote accepted

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

share|improve this answer
Thanks, I found this step-by-step blog post jcdba.com/upgrading-postgresql-8-4-to-9-1-using-pg_upgrade – Simon May 22 '12 at 22:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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