Older version of PGSQL help

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? I had installed the latest Pgadmin version 1.8 but it needs a
minimum of PostgreSQL v7.3 to work so I don't know what to do now. Would one
of the older versions work?

I'm not very database literate so unless an upgrade of the PostgreSQL
database was idiot proof I guess I'm screwed.
I am administering a network for a company that had their IT guy quit and it
happens to be a newspaper subscriber database that is heavily used and
relied upon. Unfortunately, the hardware that the server runs on is very old
and I'm afraid that it will crash any day which would leave them screwed so
I'm trying to figure out how I could get this over to a new Linux server.
The current Linux version that it is running on is Red Hat Linux release 6.2
(Zoot) Kernel 2.2.18 on a 2-processor i686 and I would like to get it
switched over to an recent distro like CentOS4.5 or 5.

Thanks in advance for any light that you may be able to shed on this for me.

Ed

On Mon, Dec 03, 2007 at 03:20:45PM -0600, 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?

Not to my knowledge. But I wouldn't spend any time on it -- you need to get
off that PostgreSQL release pronto. Really, really pronto.

> I'm not very database literate so unless an upgrade of the PostgreSQL
> database was idiot proof I guess I'm screwed.

The upgrades are mostly not that hard. Just read the release notes
carefully for any compatibility issues with your application. Generally,
they're a matter of pg_dump database | psql newdatabase.

> relied upon. Unfortunately, the hardware that the server runs on is very old
> and I'm afraid that it will crash any day which would leave them screwed so

It'll leave them "more screwed" if it happens on that release. 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 (because probably some of the necessary support files
aren't installed, and you will have a hard time getting the necessary
packages.

My suggestion is to go out and buy a reasonable server for this system, and
move it all there as soon as you can. If you need help with how to do that,
the pgsql-admin or -general lists are probably the right place to ask
questions, once you've read the documentation (which is pretty good on this
topic).

A

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

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(at)gregsmith(dot)com]
Sent: December 4, 2007 11:54 PM
To: Ed Burgstaler
Cc: pgsql-general(at)postgresql(dot)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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

On Wed, Dec 05, 2007 at 12:53:45AM -0500, Greg Smith wrote:
> 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.

Right. Note that you probably do want to test your client against versions
later than 7.0 -- I seem to recall we ran into some corner cases in
upgrading from 7.1 to 7.2, so 7.0 to 7.3 may well have a similar issue.

A