Re: What is the best plan to upgrade PostgreSQL from an ancient version?

On Jan 26, 2009, at 6:33 PM, Tom Lane wrote:

> "Dann Corbit" <DCorbit@connx.com> writes:
>> My notion is to do a character mode database dump as SQL statements
>> and
>> then load into the new version by execution of psql against the sql
>> STATEMENTS.
>> What are the "gotchas" we can expect with this approach?
>> When I say 'ancient' I mean v7.1.3 and the target is v8.3.5.
>
> Yoi, that is a long way.  As already noted, you should use the 8.3
> version of pg_dump to pull the data from the old server; this should
> smooth some of the bumps, but there will be more.

ISTR there being some hard problems moving from something that
old to 8.2, and that doing it via an intermediate 7.4 installation (
use pg_dump 7.4 against the 7.1 installation, load it into a 7.4
installation, then use the 8.3 pg_dump against that) avoided some
problems. It's been a while, and I don't recall what the problems
were, so ICBW.

>
>
> Also, experiment with using the -d or -D options to pg_dump (ie
> dump data via INSERT not COPY) if you have problems.  I forget exactly
> when we got rid of the last risk factors for COPY-style dumps, but it
> might've been after 7.1.  This'll be slower though.
>
> I don't have too much else to add to what was already said, except
> to reinforce the advice to test your applications before you do the
> live
> migration.  You're almost certain to hit some compatibility issues.

+1

Cheers,
   Steve