Re: Upgrading from PG 8.2.5 to 9.1.13

Marc Richter <mail@marc-richter.info> 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.

> [dozens of upgrades through various minor and major releases]

You're making this way too hard.  If I were in your shoes, this is
what I would do:

(1) Stop the database and make a filesystem-level copy of the
entire $PGDATA directory tree and any tablespaces.  Keep this
somewhere safe for the next year or two -- just in case you have
problems later.

(2) Install the 8.2.23 software, built with the same options as
your current 8.2.5, and start the database.  Run REINDEX DATABASE
in each database.  (The REINDEX may not be strictly necessary,
since pg_dump[all] will normally be doing heap scans of the tables,
but it may eliminate any confusion if your run queries to check
results.)

(3) Install the latest minor release of the target major release.
I would seriously consider 9.2.9 or 9.3.5 instead of 9.1, to get an
extra year or two of supported usage before worrying about
upgrading or going out of support again.  If you go to 9.1, the
latest is now 9.1.14.  You don't need to worry about
storage-compatible build options, since pg_dump[all] will re-create
the database objects through source code.  Create an empty cluster.

(4) Using the new version's pg_dump and/or pg_dumpall, dump the 8.2
databases and restore them into the new cluster.  Personally, I
have usually done this by using pg_dumpall with -g to get the
global objects (like users) onto the new systems, then one at a
time I have created target databases and run pg_dump piped to psql
to copy across without a dump file.  Some prefer to have the dump
file.

(5) I strongly recommend running VACUUM FREEZE ANALYZE in each new
database after the load and before using it.

There is no need to go through all of those intermediate versions,
but do be careful to test.  There are differences in string literal
handling, bytea literals, and casting rules which may require
adjustments in your software.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company