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@xxxxxxxxxxxxxx > [mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] On Behalf Of > Benjamin Krajmalnik > Sent: Thursday, October 05, 2006 12:03 PM > To: pgsql-admin@xxxxxxxxxxxxxx > 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 >
