Marc Richter wrote:
> First of all, I'm sorry for the length of this mail, but I want to get a
> full description of steps which might be necessary to proceed to upgrade
> a PostgreSQL server from 8.2.5 to 9.1.13. And these are all steps I
> found, studying the docs.
>
> I'm quite unexperienced with PostgreSQL and need to do an upgrade from
> 8.2.5 to something recent. Since we are running Debian wheezy as
> default, which uses 9.1.13 currently, this is the destination version to
> upgrade to.
[...]
> As a general rule of thumb, I understand that one should use that
> version of pg_dump/pg_dumpall and psql that ships with the newer version
> when updating from a major release to another.
Right.
> When I have read the release notes from 8.2.5 to 9.1.13 [1] correctly, I
> have to do the following in the given order:
[long list with upgrade issues from all minor releases between the versions]
> This all looks like a huge amount of steps which all have to be applied
> OK to not corrupt the data of the database ... and especially for
> someone like me, who not is that familiar with PostgreSQL.
> Because of that, I really could use some advice.
You really made an effort there, but it is much simpler than that.
There are basically two kinds of upgrade:
- Minor release upgrade from version x.y.a to version x.y.b, in
which all you normally do is replace the binaries and restart the database.
- Major release upgrade from x.a.y to x.b.y (with b > a) or
from a.x.y to b.x.y (with b > a).
These are done with pg_dumpall and restore or (in later versions)
with pg_upgrade.
All of the issues you picked from the release notes ar only relevant for
minor release upgrades to fix the database data.
With pg_dumpall you create a logical copy of the database, and the
database is rebuilt from scratch during restore, so all of these problems
will not occur (e.g., Indexes will be rebuilt and cannot be corrupt).
So it all boils own to pg_dump and restore, an the relevant part of the
release notes is the one that points out incompatibilities to previous versions.
These might bite your application after the upgrade.
Yours,
Laurenz Albe