Re: Upgrading from PG 8.2.5 to 9.1.13

Hi Kevin,

Thank you for your reply!

I do not understand step 2) in your list: When all these REINDEX and
Upgrading extra steps are prevented when using full dumps and psql, why
is it necessary to update to 8.2.23 first?

Best regards,
Marc

Am Freitag, 8. August 2014 16:20:37 schrieb Kevin Grittner:
> 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
>