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
>