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

Tom Lane wrote:
> "Dann Corbit" <DCorbit(at)connx(dot)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.

It's also worth thinking about doing a schema-only dump and get the
schema loading into the new database. Once that's working fine, you can
do a data-only dump and restore that into the already-loaded schema.

If you dump the data with -Fc you can do table-by-table restores under
your own control, which may be helpful in case of load ordering problems
related to foreign key constraints.

--
Craig Ringer