Re: Upgrading postgresql from version 7.4.3

2013/5/9 Arun P.L <arunpl(at)hotmail(dot)com>:
> Hi,
>
> How can I upgrade to latest postgresql version 9.2 from my current version
> 7.4.3? How complicated this will be as the db contains large amount of data?
> I have installed the latest version 9.2 in new server and while restoring
> the dump from old version, data in some of the large tables are not copied
> and getting error.
>
> pg_restore: [archiver (db)] Error from TOC entry 1550 TABLE DATA
> table_name;
> pg_restore: [archiver (db)] COPY failed for table "table_name": ERROR:
> invalid byte sequence for encoding "UTF8": 0xa0
>
> What else can I do for this issue, or in general how can I upgrade from a
> lower major version to higher version? Should I first upgrade to version 8
> first and then go for the 9?
>
> Please provide your suggestion regarding this.

The latest supported 8.x version is 8.4, which is quite a long way ahead
of 7.4 in a whole number of ways. The gap between 8.4 and 9.2 is not
quite so great, IMHO, so you might as well shoot for that.

Providing you can solve the encoding problem, the largest potential issue
you might face is the tightening of typecasting between 8.2 and 8.3:

http://www.postgresql.org/docs/current/interactive/release-8-3.html#AEN124084

This is more something which will affect any applications which access your
database and which rely on implicit casting.

There are also some changes to PL/PgSQL which you will need to take into
account if your database uses them. I suggest taking some time to go through
the release notes.

I have previously upgraded a 7.4 installation to 9.0 without too many problems;
I did have to spend some time wading through the associated application code
to isolate any potential casting issues.

Regards

Ian Barwick