[jehsom]

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.

Thanks!

[shammat]

Originally Posted by jehsom

I'd like to make the database LATIN1 again, since this is the encoding used by our whole system

You'll have to drop and re-create the database. There is no option to change the characterset for a database.

[jehsom]

Originally Posted by shammat

You'll have to drop and re-create the database. There is no option to change the characterset for a database.

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.

[shammat]

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)