Re: Unable to restore dump due to client encoding issues -- or, when is SQL_ASCII really UTF8

In response to Michael Fuhr <mike(at)fuhr(dot)org>:

[snip]

> > The beginning of the dump file I am restoring has the following:
> > --
> > -- PostgreSQL database dump
> > --
> >
> > SET client_encoding = 'SQL_ASCII';
> > [...]
> >
> > But when I try to pull the dump in with psql, I get the following errors:
> > ERROR: invalid byte sequence for encoding "UTF8": 0xa0
> > HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".
> >
> > Connecting to the database and issuing "show client_encoding" shows that
> > the database is indeed set to SQL_ASCII.
>
> client_encoding doesn't show the database encoding, it shows the
> client encoding; execute "show server_encoding" to see the database
> encoding. You can also use "psql -l" or "\l" from within psql to
> see all databases and their encodings. The error suggests that the
> database encoding is UTF8.

Ahh ... see, I knew there'd be something obvious I was missing, but all
the google searches I tried were focused on client encoding, so I wasn't
getting this answer.

The database was, indeed, UTF8, which is the default on newer Postgres.
I find it odd that I can't alter the encoding after database creation,
but in this case it wasn't a big deal, as it's a lab system and I can
just back up a few steps and start over.

Good to learn this change now, as opposed to during the next production
deployment :)

> > Now ... I'm expecting the server to accept any byte sequence, since we're
> > using SQL_ANSII, but that is (obviously) not the case. Am I missing
> > something obvious here? Grepping the entire dump file shows absolutely
> > no references to UTF8 ... so why is the server trying to validate the
> > byte string as UTF8?
>
> Probably because the database is UTF8 (see above). Either create
> the database as SQL_ASCII (see createdb's -E option) or change the
> client_encoding setting in the dump to whatever the encoding really
> is (probably LATIN1 or WIN1252 for Western European languages).

Yup. Thanks for the quick response!

--
Bill Moran
Collaborative Fusion Inc.