Guidelines for upgrading from pgsql7.4.xxx server to pgsql8.xxx server

Luki Rustianto wrote:

Hi All,

All of our application now runs on postgresql7.4.xxx servers, I never
tried it on version 8.xxx

I wonder if there are any guidelines / step by step / special
considerations whether it applies to database structure or the
application it self if we want to upgrade to version 8.xxx ?

Depends on if your app depends on the (hidden) OID fields that were
included by default before 8.0. If not, then no real concerns that I
know of, if so, you may want to first upgrade to the most recent 7.4
release to ensure that the oid fields get noted properly in the dumps.

Best Wishes,
Chris Travers
Metatron Technology Consulting

Chris Travers <chris@verkiel.metatrontech.com> writes:

Luki Rustianto wrote:

I wonder if there are any guidelines / step by step / special
considerations whether it applies to database structure or the
application it self if we want to upgrade to version 8.xxx ?

Depends on if your app depends on the (hidden) OID fields that were
included by default before 8.0.

That's just one of numerous minor incompatibilities. Read the release
notes and test your applications --- but by all means fix whatever
application issues you need to fix, and get moved over. 8.1 is way
better than 7.4 by any number of measures; even more to the point, we
won't be supporting 7.4 forever.

regards, tom lane

As others have pointed out, OID dependant tables may need special attention.

We recently upgraded from 7.4 to 8.1.x and found some issues with encoding -- 8.1 defaulted to a different encoding and we had some data that was invalid; we manually fixed the old data before retrying the export.

Make sure you read the manual's section on the configuration parameters as well; there are some changes / improvements.

Some SQL may need adjustment -- there are some stricter checks in 8.1 which will fail to run SQL that 7.4 would accept (look for the "Missing WHERE clause" stuff in the manual, for instance.)

Our upgrade went smoothly (both runtime with postGIS data and the billing side of things) and I'd upgrading; 8.1 has some substantial improvements.

Greg Williamson
DBA
GlobeXplorer LLC

-----Original Message-----
From: pgsql-general-owner@postgresql.org on behalf of Luki Rustianto
Sent: Tue 3/28/2006 6:53 PM
To: pgsql-general@postgresql.org
Cc:
Subject: [GENERAL] Guidelines for upgrading from pgsql7.4.xxx server to pgsql8.xxx server

Hi All,

All of our application now runs on postgresql7.4.xxx servers, I never
tried it on version 8.xxx

I wonder if there are any guidelines / step by step / special
considerations whether it applies to database structure or the
application it self if we want to upgrade to version 8.xxx ?

Thanks.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

!DSPAM:4429f70e308891228024673!

On Tue, 2006-03-28 at 20:53, Luki Rustianto wrote:

Hi All,

All of our application now runs on postgresql7.4.xxx servers, I never
tried it on version 8.xxx

I wonder if there are any guidelines / step by step / special
considerations whether it applies to database structure or the
application it self if we want to upgrade to version 8.xxx ?

There's this page:

http://www.postgresql.org/docs/8.1/static/install-upgrading.html

And take a look at this one too:

http://www.postgresql.org/docs/8.1/static/release-8-1.html

which covers a few issues like encoding problems you might see. In
versions before 8.0 charset checking was lax, and you might have
characters in your database that are illegal for UTF-8 encoding. Simple
fix is to let iconv fix that. Linux has it, not sure about other OSes,
but someone could always download a knoppix disc and boot from it just
long enough to convert the dumps if you don't run linux in your shop.