Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 2BBB19FB21A for ; Thu, 5 Oct 2006 23:49:09 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 77947-10 for ; Fri, 6 Oct 2006 02:49:04 +0000 (UTC) X-Greylist: delayed 08:46:04.501492 by SQLgrey- Received: from fletch.stackdump.local (unknown [64.207.29.137]) by postgresql.org (Postfix) with ESMTP id 8BC599FA3A0 for ; Thu, 5 Oct 2006 23:49:03 -0300 (ADT) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Subject: Re: pg_dump/pg_restore problem x-mimeole: Produced By Microsoft Exchange V6.5 Date: Thu, 5 Oct 2006 20:49:00 -0600 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [ADMIN] pg_dump/pg_restore problem Thread-Index: AcboOYYfW2rMKB1nSQSRro4VuF6n1wAbY+6wABKn2GA= From: "Benjamin Krajmalnik" To: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.134 tagged_above=0 required=5 tests=FORGED_RCVD_HELO, SPF_PASS X-Spam-Level: X-Archive-Number: 200610/34 X-Sequence-Number: 23337 I finally found a workaround after much experimentatio, so here it is. Hopefully, it will save someone else the headaches I went through: 1. I created a target database with Latin1 encoding=20 2. pg_dumped using SQL_ASCII client encoding The initial restore failed because for some reason one of the columns was no longer large enough to hold the contents. I increased the size of the columns in question, and proceeded to truncate all of the tables which had data to prevent errors when building the indices, etc. I reran the restore, and this time all of the records came through.=20 > -----Original Message----- > From: pgsql-admin-owner@postgresql.org=20 > [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of=20 > Benjamin Krajmalnik > Sent: Thursday, October 05, 2006 12:03 PM > To: pgsql-admin@postgresql.org > Subject: [ADMIN] pg_dump/pg_restore problem >=20 > I have a database which has UTF8 encoding enabled (why? I am=20 > really not sure why I did tihs other than the source of the=20 > data is windows and I had some issues with characters > ascii=20 > 128 being sent across from some of the Windows event logs). > The problem which I am having is as follows: > =20 > The data is passed via the ODBC driver to a stored procedue,=20 > and it made it successfully into the tables. > I can create a pg_dump without any problem, but pg_restore is=20 > giving the following error: > =20 > pg_restore: ERROR: invalid byte sequence for encoding "UTF8": 0x80 >=20 > CONTEXT: COPY tblksalerts, line 22736 >=20 > I have tried running pg_dump changing the encoding to Latin1=20 > and Latin9. > When creating the dunp, it is giving an error that there is=20 > no equivalent in the character set. > The problem is that, as it stands, pg_dump/pg_restore cannot=20 > be used to easily backup/restore. > In the past, I perfrmd singe table dumps and ran them so I=20 > could identify which line was the problem, went back to the=20 > database, deleted the offending line, and so forth, but this=20 > is a very long process. >=20 > I was initially runnin 8.1.2. I am now running 8.1.4. I was=20 > hoping that 8.1.4 would alleviate the problem (since some=20 > encoding issues were addressed). >=20 > Any ideas how to easily identify the offending rows and=20 > remove them easily? >=20 > I need to move the database to a new server with higher=20 > performance, and this is currently a sticking point. >=20 >=20 > =20 >=20 >=20 > ---------------------------(end of=20 > broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >=20