Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id AEB929FB269 for ; Fri, 6 Oct 2006 02:03:25 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 84339-04 for ; Fri, 6 Oct 2006 02:03:22 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.229]) by postgresql.org (Postfix) with ESMTP id 872D29FB1E2 for ; Fri, 6 Oct 2006 02:03:22 -0300 (ADT) Received: by wx-out-0506.google.com with SMTP id t13so810122wxc for ; Thu, 05 Oct 2006 22:03:21 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=J6/249k1k5JgIvE+K+X7LPAjs2kujPXFriBhw9ZDhaSdVo5kmbtXYIksHwh6smv8tjNUeNnIjJNNc84Z6q3brsmhMdTqaNGHHk5tEfcP7EnsQmzkMMHNMckBZ+2s86X3rBriF8FuWfgCElJxz6JaTLWNt2CPHcsbZYbzuTS3lCU= Received: by 10.90.105.19 with SMTP id d19mr1428644agc; Thu, 05 Oct 2006 22:03:21 -0700 (PDT) Received: by 10.90.94.18 with HTTP; Thu, 5 Oct 2006 22:03:21 -0700 (PDT) Message-ID: <1c66bda80610052203y3cd23b1dh7246666e124d8c22@mail.gmail.com> Date: Fri, 6 Oct 2006 18:03:21 +1300 From: adey To: "Benjamin Krajmalnik" Subject: Re: pg_dump/pg_restore problem Cc: pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_127117_20010618.1160111001540" References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.04 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, HTML_10_20, HTML_MESSAGE, SPF_PASS X-Spam-Level: * X-Archive-Number: 200610/35 X-Sequence-Number: 23338 ------=_Part_127117_20010618.1160111001540 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline I had a similar experience when we upgraded from 7.4 to 8.1.4. When we attempted the restore in 8.1.4 / UTF8, it failed and told us the offending row. We edited the original database to correct the data and retried. There were several ofending rows, but fortunatley for the exact same value, so we could search the dump, find all the rows (about 6 of them), correct the original database, pgdump, and restoer successfully. Our problem was the original database was sql_ascii and didn't care what was loaded (?), whereas 8.1.4 was more strict. On 10/6/06, Benjamin Krajmalnik wrote: > > I have a database which has UTF8 encoding enabled (why? I am really not > sure why I did tihs other than the source of the data is windows and I > had some issues with characters > ascii 128 being sent across from some > of the Windows event logs). > The problem which I am having is as follows: > > The data is passed via the ODBC driver to a stored procedue, and it made > it successfully into the tables. > I can create a pg_dump without any problem, but pg_restore is giving the > following error: > > pg_restore: ERROR: invalid byte sequence for encoding "UTF8": 0x80 > > CONTEXT: COPY tblksalerts, line 22736 > > I have tried running pg_dump changing the encoding to Latin1 and Latin9. > When creating the dunp, it is giving an error that there is no > equivalent in the character set. > The problem is that, as it stands, pg_dump/pg_restore cannot be used to > easily backup/restore. > In the past, I perfrmd singe table dumps and ran them so I could > identify which line was the problem, went back to the database, deleted > the offending line, and so forth, but this is a very long process. > > I was initially runnin 8.1.2. I am now running 8.1.4. I was hoping > that 8.1.4 would alleviate the problem (since some encoding issues were > addressed). > > Any ideas how to easily identify the offending rows and remove them > easily? > > I need to move the database to a new server with higher performance, and > this is currently a sticking point. > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > ------=_Part_127117_20010618.1160111001540 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
I had a similar experience when we upgraded from 7.4 to 8.1.4.
When we attempted the restore in 8.1.4 / UTF8, it failed and told us the offending row. We edited the original database to correct the data and retried. There were several ofending rows, but fortunatley for the exact same value, so we could search the dump, find all the rows (about 6 of them), correct the original database, pgdump, and restoer successfully.
 
Our problem was the original database was sql_ascii and didn't care what was loaded (?), whereas 8.1.4 was more strict.

 
On 10/6/06, Benjamin Krajmalnik <kraj@illumen.com> wrote:
I have a database which has UTF8 encoding enabled (why?  I am really not
sure why I did tihs other than the source of the data is windows and I
had some issues with characters > ascii 128 being sent across from some
of the Windows event logs).
The problem which I am having is as follows:

The data is passed via the ODBC driver to a stored procedue, and it made
it successfully into the tables.
I can create a pg_dump without any problem, but pg_restore is giving the
following error:

pg_restore: ERROR: invalid byte sequence for encoding "UTF8": 0x80

CONTEXT: COPY tblksalerts, line 22736

I have tried running pg_dump changing the encoding to Latin1 and Latin9.
When creating the dunp, it is giving an error that there is no
equivalent in the character set.
The problem is that, as it stands, pg_dump/pg_restore cannot be used to
easily backup/restore.
In the past, I perfrmd singe table dumps and ran them so I could
identify which line was the problem, went back to the database, deleted
the offending line, and so forth, but this is a very long process.

I was initially runnin 8.1.2.  I am now running 8.1.4.  I was hoping
that 8.1.4 would alleviate the problem (since some encoding issues were
addressed).

Any ideas how to easily identify the offending rows and remove them
easily?

I need to move the database to a new server with higher performance, and
this is currently a sticking point.





---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

------=_Part_127117_20010618.1160111001540--