Thread: pg_restore failes - invalid byte sequence for encoding "UNICODE": 0xa3

Hi all,

I encountered a somewhat big problem in Postgres which is effectively keeping me
from restoring any previous and future database dumps.

We are running FC4 with the latest updates. "SELECT version();" shows
"PostgreSQL 8.0.8 on i386-redhat-linux-gnu, compiled by GCC
i386-redhat-linux-gcc (GCC) 4.0.2 20051125 (Red Hat 4.0.2-8)".

Up until now it was easy to just take a previous dump of the live server's
database, restore it on the test server, restore it on my development machine
or any other operation. I never had any problems.

But today, when trying to restore yesterdays backup for some test data, I get
something like this (if I truncate the mentioned table before backing up, it
happens in other tables as well):

pg_restore: ERROR:  invalid byte sequence for encoding "UNICODE": 0xa3
CONTEXT:  COPY aw_statements, line 19: "2234    2006-01-11 16:15:30    2006-01-11
16:15:30.810975    2001    2006/0002234    171    \N    0    <?xml version="1.0" e..."
pg_restore: [archiver (db)] error returned by PQendcopy: ERROR:  invalid byte
sequence for encoding "UNICODE": 0xa3
CONTEXT:  COPY aw_statements, line 19: "2234    2006-01-11 16:15:30    2006-01-11
16:15:30.810975    2001    2006/0002234    171    \N    0    <?xml version="1.0" e..."

As you can see, the data it complains about was inserted in January. I backed up
and restored since then at least a hundred times without a problem. Is it
possible, that the recent update to Postgres 8.0.8 on FC4 breaks this? Despite
the statement in the manuals, that minor revisions do not need a pg_dumpall?

Even though that doesn't effect our current live server, I'm worried about
eventual data loss on the live server without a possibility to restore from
last night's backup.

Restoring to my development machine running Windows and Postgres 8.0.3 doesn't
seem to be affected. And let me assure you that source and target database are
always created with an encoding of UNICODE.

Any help is much appreciated.

Best,
Oliver Fuerst

Quoting Agent M <agentm@themactionfaction.com>:
> Hi- you have an encoding problem. What is the string encoding of your
> old database? Another option is that your backup became corrupted.

As I wrote in my initial posting: source and target encoding is UNICODE. I can
restore mentioned backups without a problem on a Windows host. And every backup
I take is not restore-able on it's original host...

Best,
Oliver

ofuerst@tsak.net writes:
> As I wrote in my initial posting: source and target encoding is UNICODE. I can
> restore mentioned backups without a problem on a Windows host. And every backup
> I take is not restore-able on it's original host...

Indeed, 8.0.8 is stricter about encoding than prior versions, and 0xA3
is *not* a valid UTF8 character.  Perhaps your data is really Latin1 or
some such?

            regards, tom lane