Thread: Upgrading from 7.2.1 to 8.x

Question,

I have an ancient database that is running Postgresql 7.2.1 on AIX.  Is it safe to directly upgrade the database to 8.1?  If not, what steps do I need to take?

I have been pushing for 3 years to upgrade this database, and with some upcoming projects, I am going to do another push with management to upgrade this database.

Also, I know there are a ton of fixes/improvements from 7.2, but are there any major data losing bugs in this version that I can use as added pressure on management?

Thanks,

Chris

On 10/31/07, Chris Hoover <revoohc@gmail.com> wrote:
> Question,
>
> I have an ancient database that is running Postgresql 7.2.1 on AIX.  Is it
> safe to directly upgrade the database to 8.1?  If not, what steps do I need
> to take?

It might be better to upgrade to 7.2.xx where xx is the latest
version, as some bugs in the older version you have might make dumping
data out of it fail.

You can't just upgrade it in place to 8.x, you have to dump and
restore it.  It is standard to use the pg_dump from the later version
against the older version.  So, you should install 8.2.5 on another
directory and another port on your AIX machine (or a new machine if
you're migrating hardware at the same time) and run pg_dumpall from
8.2.5 against the 7.2.x database.

Generally speaking, you can do something like this:

/usr/local/pg82/bin/pg_dumpall -h oldmachine -p 5432 | psql -h
newmachine -p5433 template1

and pg_dumpall will handle the dependencies etc for you.

> I have been pushing for 3 years to upgrade this database, and with some
> upcoming projects, I am going to do another push with management to upgrade
> this database.

Push hard.  They'll thank you later.

> Also, I know there are a ton of fixes/improvements from 7.2, but are there
> any major data losing bugs in this version that I can use as added pressure
> on management?

Well, 7.2 is broken and not going to get fixed.  It WILL eventually
eat your data.  In fact, I'm honestly surprised it hasn't failed yet.

Besides that, 8.2.5 is about 1,000 times faster (ok, maybe not, but it
certainly feels that way).

Go right to 8.2.5.  8.2 has been out a year, it's stable, and as long
as upgrades seem to take in your shop, being one version further along
ain't gonna hurt.

"Chris Hoover" <revoohc@gmail.com> writes:
> I have an ancient database that is running Postgresql 7.2.1 on AIX.  Is it
> safe to directly upgrade the database to 8.1?  If not, what steps do I need
> to take?

That's a big jump.  You'll want to set up a test installation and do
some compatibility testing --- it seems quite likely to me that you'll
find a few things that need adjustment in your client apps and/or
SQL schema.

Why are you intending to migrate to 8.1 and not 8.2?

> Also, I know there are a ton of fixes/improvements from 7.2, but are there
> any major data losing bugs in this version that I can use as added pressure
> on management?

Egad, man.  7.2 was abandoned years ago because of unfixable bugs...
As a first approximation, you could suppose that every major bug
we've fixed in 7.3 since 7.3.10 is also in 7.2.  And should I point
out that several data-loss bugs were fixed post-7.2.1 before we
abandoned that branch?  If there's any valuable data in that DB,
to still be running 7.2.1 is lawsuit-worthy negligence.  Trawl the
release notes here:
http://developer.postgresql.org/pgdocs/postgres/release.html

            regards, tom lane

THINK BEFORE YOU PRINT - Save paper if you don't really need to print this e-mail.

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email.

Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments

>> I have been pushing for 3 years to upgrade this database, and with some
>> upcoming projects, I am going to do another push with management to upgrade
>> this database.
>
> Push hard.  They'll thank you later.

No they won't. They have been running this way fine for years - if they
haven't crashed so far, why would they stop driving home sans-seatbelts
after a knocking back a few at the nightly bar bash now? But introduce a
big change and all problems and frustrations involved in the change will
reflect on you.

It takes some upfront work, but I would write up a proposal. First,
scour the release notes and document all the security and data-eating
bugs that have been fixed. Mention that support for 7.2 long-since ended.

Second, identify features that have been added that would be of use in
your business or which would make development of your upcoming projects
easier. Include features that make maintenance and operation easier as
well (stuff like autovacuum).

Third, point out that the substantial performance improvements through
the versions will allow you to use less expensive hardware than would
otherwise be required.

Finally, be upfront about the fact that upgrading will require effort
and could introduce some initial glitches. But point out that long-term
you will be much better off tweaking your app to run on the latest
version of PG and developing from there than digging your hole deeper by
writing even more code tied to the old, old version.

Then it's their decision. If they say no, you have a document you can
refer to when things go bad (but with luck you will have found a new job
by then). If they say yes, you will have the same document pointing out
that they should expect some initial difficulties in the process.

Good luck.

Cheers,
Steve