Upgrading postgresql from version 7.4.3

On 5/9/2013 2:02 AM, Arun P.L wrote:

pg_restore: [archiver (db)] Error from TOC entry1550 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?

your problem is, you have data in your database that isn't valid UTF8.

what encoding was the 7.4 system ?

--
john r pierce 37N 122W
somewhere on the middle of the left coast

2013/5/9 Arun P.L <arunpl@hotmail.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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Since there was some issue with hotmail I couldn't reply earlier.

Encoding in version 7.4 was SQL_ASCII. What's the best way to deal with this?

I hope changing the encoding to UTF-8 while taking dump will work.

Surely I will have an eye on release notes in order to deal with the typecasting issues and I am on it now.

Thank you very much for your suggestions...

On 5/9/2013 9:12 PM, Arun P.L wrote:

Since there was some issue with hotmail I couldn't reply earlier.

Encoding in version 7.4 was SQL_ASCII. What's the best way to deal
with this?
I hope changing the encoding to UTF-8 while taking dump will work.

SQL_ASCII means that postgres has no encoding, it just treats it as
bytes. any values could be stored, and its quite likely that they
aren't UTF8 compatible

--
john r pierce 37N 122W
somewhere on the middle of the left coast

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi,

And the way to deal with this? Any ideas on that? Whether changing encoding to utf-8 in dump work?

Regards~
Arun

"Arun P.L" <arunpl@hotmail.com> writes:

And the way to deal with this? Any ideas on that?

If you were happy with SQL_ASCII encoding before, keep using it.
Other encoding choices are basically constraints to ensure your
data is valid according to that encoding --- if you don't feel
like trying to clean up the data encoding right now, then don't.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, May 9, 2013 at 9:41 PM, Arun P.L <arunpl@hotmail.com> wrote:

Hi,

And the way to deal with this? Any ideas on that? Whether changing
encoding to utf-8 in dump work?

Yes, run initdb -E SQL_ASCII

This will create your database cluster with no encoding restrictions.
Whether this is a good thing or a bad thing depends on circumstances (it's
usually not desirable) but in your case it may be a good thing.

Best Wishes,
Chris Travers