In response to Michael Fuhr <mike@xxxxxxxx>: [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.
