Re: 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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD