Re: Upgrading PostgreSQL 7.1.3

On Wed, 25 Aug 2004 10:04:54 +0200 Michal Taborsky <michal(at)taborsky(dot)cz> sat
down, thought long and then wrote:

> ruben20(at)superguai(dot)com wrote:
> > What is the most advisable version I should upgrade to? My top goal is
> > reliability. Do I have to install/reinstall or upgrade? Do I need to be
> > concerned of any special feature when dumping/restoring data? Is there
> > any good doc about this?
>

...
>
> This worked for me. It is a long way from 7.1 to 7.4 so expect some
> fiddling with the pg_dump parameters and maybe some sed-ing in between.

If I had to do this long jump, I would use INSERTs instead of COPYs, that is,
the option -D (full inserts) on pg_dump. It takes much longer to read the data
in, but IMHO it´s the more secure way to get the data without trouble. If your
server is big enough, do it within a single transaction, or do it with one
transaction per (big) table to save time and trouble with half filled tables,
if something goes wrong..

And I would do the data insert separately from the database and table creation,
that is, first a pg_dump -s and restore to the new engine, then, when the
structure is ready, insert data you dumped with pg_dump -a -D.

At least this worked for me rather smoothly from 7.1 to 7.3.
--
Frank Finner

Memory follows memory, memory defeats memory; some things are banished
only into the realms of our rich imaginings - but this does not mean
that they do not or cannot or will not exist - they exist! They exist!
(M. Moorcock, "The Revenge Of The Rose")