Thread: Upgrading PostgreSQL 7.1.3

Hi:

I'm planning to migrate a postgres database version 7.1.3 to a newer
version. There are some tables with up to 5 million records and I'm
begining to suffer from data corruption in indexes and tables. I did
some hardware checkings and everything seems ok. The last move, as
someone suggested in this list would be to upgrade postgres version.

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?

Thanks in advance for your vaulable help.
Ruben.

Date: Sun, 24 Aug 2003 23:25:24 +0200
                  ^^^^
The date's wrong on your computer.

"ruben20@superguai.com" <ruben20@superguai.com> wrote:
> Hi:
>
> I'm planning to migrate a postgres database version 7.1.3 to a newer
> version. There are some tables with up to 5 million records and I'm
> begining to suffer from data corruption in indexes and tables. I did
> some hardware checkings and everything seems ok. The last move, as
> someone suggested in this list would be to upgrade postgres version.
>
> 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?

Make absolutely sure you have a good dump of your data.  You would do
well to upgrade to the latest 7.4.5.  Be prepared to spend some time on
it, as the jump from 7.1 to 7.4 is a long way.

I don't know what specific problems you might encounter, but be prepared.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com

ruben20@superguai.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?

Go for the latest stable version (currently 7.4.5). Make backups! (More
than one preferably.) My suggestion is to install the new version
alongside the old one on the same machine and make it listen on
different port. Then you can do pg_dump from one directed immediately to
the other. If something fails (and it will, several times, before you
get the dump parameters right), you just ditch the new data directory
and start again with fresh initdb. Also make sure you are using tools
from the new version (pg_dump, psql, ...).

You can do this on live system too. Only after you succeed in
transfering the database, recreate the new one one last time, disable
connections to the original server except from pg_dump and perform the
transfer again (so that you make sure no data gets written to the
original database after you started dumping it.) Then switch the
servers, delete the old one and you are good to go with the new version.
Oh, and did I mention you should make backups?

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.

Hope this helps.

--
Michal Taborsky
http://www.taborsky.cz

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

> ruben20@superguai.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")