Re: Older version of PGSQL help - Mailing list pgsql-general

Thank you very much for your help Greg ...
I'll do as you say and install version 7.3 from rpm on a new Centos4.5
system and try to import the dump file.

If I'm sucsessful in getting it working on version 7.3 can you suggest the
next version I should upgrade to after that?

Very grateful

Ed

-----Original Message-----
From: Greg Smith [mailto:gsmith@gregsmith.com]
Sent: December 4, 2007 11:54 PM
To: Ed Burgstaler
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Older version of PGSQL help

On Mon, 3 Dec 2007, Ed Burgstaler wrote:

> Would anyone be able to direct me as to whether or not there is a
> pgadmin for windows utility available that will work for a PostgreSQL
> v7.0.3 database?

From reading the rest of your message, I think you're under the impression
that you need pgadmin in order to migrate the database to a newer platform.
You do not; the main utility you need is pg_dump, and you should be able to
run it just fine directly from the command prompt (when logged in as the
database user) on your RH6.2 system, or from a newer installation connecting
to the old one.  The output from that is a text file which you can then copy
anywhere, or reload into a newer version (with some restrictions as
discussed below).

Before you do anything else, it would be wise to login to the old server,
run pg_dump to dump everything, and copy that file elsewhere.  That will at
least give you some sort of backup if the whole system dies on you before
you can migrate to a newer version.

On Tue, 4 Dec 2007, Andrew Sullivan wrote:

> My suggestion is to download and compile a more recent release -- 7.3
> is about to become unsupported, but you may need to go through that
> version anyway in order to get off 7.0.  Compiling from source isn't
> hard, but it may be frustrating on such an old installed system

Ed should be able to get 7.3 running on his target CentOS boxes using the
RPMs at ftp://ftp.postgresql.org/pub/binary/v7.3.20/linux/rpms/redhat/

The issue Andrew is bringing up here is that really new PostgreSQL versions
probably aren't necessairly backward compatible talking to or reading dumps
from your 7.0 system, so your odds are better trying to upgrade to 7.3
instead of a really recent one.  But be warned that 7.3 is going away
relatively soon as well.

I think the path of least resistance here is:
1) Pick a target CentOS system.  Make sure there is no current PostgreSQL
installed.
2) Grab the 7.3 RPMs from ftp.postgresql.org, install them
3) Create a new database cluster ('service postgresql start' may be all you
need)
4) Run pg_dump against the old system.  See
http://www.postgresql.org/docs/7.3/static/app-pgdump.html for details.
You want to do something like

pg_dump <dbname> -h <oldserver> > dump.sql

You may need to specify the port and username if they're not at the default
as well.  Hopefully the old server is setup to be accessed over the network
like this.

5) If that goes OK, import into the new version with 'psql -f dump.sql'

That should get you started in the right direction.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD