Обсуждение: pg_dump/pg_restore problem

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.

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
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.

> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of
> Benjamin Krajmalnik
> Sent: Thursday, October 05, 2006 12:03 PM
> To: pgsql-admin@postgresql.org
> Subject: [ADMIN] pg_dump/pg_restore problem
>
> 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
>