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

Dann Corbit wrote:
> 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.
>
Assuming you have a pretty good understanding of your schemas and
expected queries, I would:

1) Set aside a couple hours to carefully read all the release notes
between 7.1.3 and 8.3.5 - make notes of anything that might cause issues.

2) Create a test setup just for practicing 7.1.3 dump to 8.3.5 restore
including any additional changes you might make in the process (usual
recommendation, BTW, is to run the pg_dump from the target version of
the DB). Note/correct problems till you can pretty much script the
process. Problems I've experienced include handling the change from
SQL_ASCII on the old machine to UTF8 on the new one ("smart" quotes that
creep into the data are not your friend, here) as well as eliminating
OIDs on user tables.

3) Test your apps against the new version. (Remember, you may need new
libraries, recompilation of apps, etc. to the new version - the advice
I've been given is come time to pull the trigger, upgrade the clients
first. You might be able to move this step to the top if you don't have
problems with such a wide version mismatch.)

Obviously, there is a good chance of finding issues you will have to
correct at each stage. PostgreSQL has become stricter in many ways. You
may find, for example, that queries fail due to changes in the automatic
casting of variables. Overall this is a good thing but can involve some
extra work at upgrade time.

Cheers,
Steve