Changing from 8.0 to 8.2

[jschlach]

I currently run postgreSQL version 8.0.3 and wanted to upgrade to 8.2 or 8.3 because it is supposed to be faster. How do I do this and still maintain my current databases? Will I be able to delete the old version when I am done?

[WhiteRider]

I think that updating major versions (8.0 to 8.3) is quite involved, and you won't just be able to install and keep your existing databases. I think you'll have to do some convoluted backup somehow, in order to be able to restore them to the newer version.
I'm not sure of the process, but there are some postgres experts who post pretty regularly on here so I'd wait for their advice.

[keggler]

As a general rule, the internal data storage format is subject to change between major releases of Postgres (where the number after the first dot changes). This does not apply to different minor releases under the same major release (where the number after the second dot changes); these always have compatible storage formats. For example, releases 8.1.1, 8.2.3, and 8.3 are not compatible, whereas 8.2.3 and 8.2.4 are. When you update between compatible versions, you can simply replace the executables and reuse the data directory on disk. Otherwise you need to back up your data and restore it on the new server. This has to be done using pg_dump; file system level backup methods won't work.

In a nutsell, the least downtime can be achieved by installing the new server in a different directory and running both the old and the new servers in parallel, on different ports. Then you can use something like:

pg_dumpall -p 5432 | psql -d postgres -p 6543 to transfer your data. Or use an intermediate file if you want. Then you can shut down the old server and start the new server at the port the old one was running at. You should make sure that the old database is not updated after you begin to run pg_dumpall, otherwise you will lose that data.

see here for further info: http://www.postgresql.org/docs/current/ ... ation.html



susan

[WhiteRider]

Thanks - I've added a link to this in the Database / Postgres FAQ