X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 478409FB2F1 for ; Wed, 9 Aug 2006 15:21:22 -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 45919-10 for ; Wed, 9 Aug 2006 18:21:21 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from ausimss.pervasive.com (ausimss.pervasive.com [66.45.103.246]) by postgresql.org (Postfix) with ESMTP id 3C7F09FB2C4 for ; Wed, 9 Aug 2006 15:21:20 -0300 (ADT) Received: from ausbayes2.aus.pervasive.com ([172.16.8.6]) by ausimss.pervasive.com with InterScan Messaging Security Suite; Wed, 09 Aug 2006 13:21:15 -0500 Received: from AUSOWA.aus.pervasive.com ([172.16.4.8]) by ausbayes2.aus.pervasive.com with Microsoft SMTPSVC(5.0.2195.6713); Wed, 9 Aug 2006 13:21:15 -0500 Received: from ausmailid.aus.pervasive.com ([172.16.4.64]) by AUSOWA.aus.pervasive.com with Microsoft SMTPSVC(6.0.3790.1830); Wed, 9 Aug 2006 13:20:07 -0500 X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C6BBE0.711B7B62" Subject: Re: Strange UTF8 issue with pg_dump/pg_restore Date: Wed, 9 Aug 2006 13:20:06 -0500 Message-ID: <4D27CB1096EF1C408F4BFAB0046EC7B6099FF5@ausmailid.aus.pervasive.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [ADMIN] Strange UTF8 issue with pg_dump/pg_restore Thread-Index: Aca7XyGHmXi2dNGRTWCrYu79L/ghGQABSJIKAB8AAlA= From: "Jim Nasby" To: "Benjamin Krajmalnik" Cc: "Pgsql-Admin \(E-mail\)" X-OriginalArrivalTime: 09 Aug 2006 18:20:07.0308 (UTC) FILETIME=[713624C0:01C6BBE0] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200608/118 X-Sequence-Number: 22661 This is a multi-part message in MIME format. ------_=_NextPart_001_01C6BBE0.711B7B62 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Please include the list in your replies so other people can help. -----Original Message----- From: Benjamin Krajmalnik [mailto:kraj@illumen.com] Sent: Tuesday, August 08, 2006 10:31 PM To: Jim Nasby Subject: RE: [ADMIN] Strange UTF8 issue with pg_dump/pg_restore I had corrupt rows inserted on 8.1.4/Windows. I had truncated the = table, and it startd collecting data again. I have since moved to 8.1.4/FreeBSD. I have been running the data for 2 = days now, so the odds of having a corrupt record is pretty good if the = problem exists. I will wait a few more days and will pg_dump the table = which had the problems and attempt to restor it on a dev server to see = if I see a problem. =20 Is iconv a utility that comes woth PG? How can I run the data through = it (I am doing pg_dump in custom format). =20 No, it's a unix utility. You'd have to dump in plain text output, and = then run that through iconv. There's info about this in the 8.1 release = notes. _____ =20 From: Jim Nasby [mailto:jnasby@pervasive.com] Sent: Tue 8/8/2006 10:48 AM To: Benjamin Krajmalnik Cc: pgsql admin Subject: Re: [ADMIN] Strange UTF8 issue with pg_dump/pg_restore On Aug 7, 2006, at 9:08 PM, Benjamin Krajmalnik wrote: > I have a database whose records are inserted via ODBC. > I turned on logging, and the client is explicitly setting the=20 > client encoding to UTF. > > Rows are inserted into the tables. When I do a pg_dump, I do not=20 > get any errors. When I try tor restore, I am getting an error=20 > regarding an invalid UTF-8 character sequence. > Of course, this renders compressed backups useless, since I have no=20 > way of manually fixing the file. The process of fixing the file=20 > is, of course, painful. > > Is this a known issue? I am running PG 8.1.4. The problem=20 > happened on PG Windows. > Tomorrow I will try to see if the problem exists in the FreeBSD=20 > compile. If I get it to reproduce, I will be able to send some=20 > data dumps, but will need to know who to send it to. Are the dumps from an older version? There's a known issue that older=20 versions weren't doing proper checking, in which case you can have=20 invalid UTF8 characters. The fix is to run the dump through iconv. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com = work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ------_=_NextPart_001_01C6BBE0.711B7B62 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Re: [ADMIN] Strange UTF8 issue with pg_dump/pg_restore
Please=20 include the list in your replies so other people can = help.
-----Original Message-----
From: Benjamin = Krajmalnik=20 [mailto:kraj@illumen.com]
Sent: Tuesday, August 08, 2006 = 10:31=20 PM
To: Jim Nasby
Subject: RE: [ADMIN] Strange UTF8 = issue=20 with pg_dump/pg_restore

I had corrupt rows inserted = on=20 8.1.4/Windows.  I had truncated the table, and it startd = collecting data=20 again.
I have since moved to=20 8.1.4/FreeBSD.  I have been running the data for 2 days now, so = the odds=20 of having a corrupt record is pretty good if the problem exists.  = I will=20 wait a few more days and will pg_dump the table which had the problems = and=20 attempt to restor it on a dev server to see if I see a = problem.
 
Is iconv a utility that = comes woth=20 PG?  How can I run the data through it (I am doing pg_dump in = custom=20 format).
 
No,=20 it's a unix utility. You'd have to dump in plain text output, and then = run that=20 through iconv. There's info about this in the 8.1 release=20 notes.


From: Jim Nasby=20 [mailto:jnasby@pervasive.com]
Sent: Tue 8/8/2006 10:48=20 AM
To: Benjamin Krajmalnik
Cc: pgsql=20 admin
Subject: Re: [ADMIN] Strange UTF8 issue with=20 pg_dump/pg_restore

On Aug 7, 2006, at 9:08 PM, Benjamin Krajmalnik = wrote:
>=20 I have a database whose records are inserted via ODBC.
> I = turned on=20 logging, and the client is explicitly setting the 
> client = encoding to UTF.
>
> Rows are inserted into the = tables.  When=20 I do a pg_dump, I do not 
> get any errors.  When I = try tor=20 restore, I am getting an error 
> regarding an invalid = UTF-8=20 character sequence.
> Of course, this renders compressed backups = useless, since I have no 
> way of manually fixing the = file. =20 The process of fixing the file 
> is, of course,=20 painful.
>
> Is this a known issue?  I am running PG=20 8.1.4.  The problem 
> happened on PG Windows.
> = Tomorrow I will try to see if the problem exists in the = FreeBSD 
>=20 compile.  If I get it to reproduce, I will be able to send=20 some 
> data dumps, but will need to know who to send it=20 to.

Are the dumps from an older version? There's a known issue = that=20 older 
versions weren't doing proper checking, in which case = you can=20 have 
invalid UTF8 characters. The fix is to run the dump = through=20 iconv.
--
Jim C. Nasby, Sr. Engineering=20 Consultant      = jnasby@pervasive.com
Pervasive=20 Software      http://pervasive.com   = work:=20 512-231-6117
vcard: http://jim.nasby.net/pervasiv= e.vcf      =20 cell: 512-569-9461

------_=_NextPart_001_01C6BBE0.711B7B62--