June 17th, 2012, 11:31 PM
Encoding snafu, please help!
We just moved our database from one server running Postgres 8.4 to a new server running 9.1. The 8.4 database had an encoding of LATIN1, and the 9.1 has UTF-8.
To move the database over, I simply piped the output from pg_dumpall on the 8.4 machine into psql on the new machine. However, I think I created big problems because I didn't run it through iconv.
Now I have a UTF-8 database with lots of badly-encoded strings.
How can I fix this? I'd like to make the database LATIN1 again, since this is the encoding used by our whole system.
June 18th, 2012, 02:17 AM
You'll have to drop and re-create the database. There is no option to change the characterset for a database.
Originally Posted by jehsom
June 18th, 2012, 08:10 AM
I understand that, that's fine, but I want to make sure that when I do that, my data comes out correctly. The problem is that when I restored the dump from the LATIN1 database, I had no client_encoding set in psql (rather it was SQL_ASCII), connected to a UTF8 database, restoring LATIN1 data, so I'm not sure how to get the data out in a way that will make sense.
Originally Posted by shammat
June 18th, 2012, 08:30 AM
You need to either make sure the client_encoding is set correctly for both tools (pg_dumpall and psql).
I think when piping the data it's quite hard to get the client encoding correct for each step. You are probably better off using pg_dumpall and psql separately (or pg_dump/pg_restore using the custom format, maybe that's more stable regarding different client encodings)