Migrating from Postgres 8.4 to 9.4 in different data centers

Question

I'm trying to migrate a Postgres 8.4 database in a Brazilian machine to Amazon RDS Postgres 9.4 in North Virginia.

The database uses encoding LATIN1, tablespace pg_default, lc_collate and lc_ctype C.

• When the web application is connected to the 8.4 database, the accented words are shown normally.

União Estável

• When I connect it to the 9.4 restored database (created using the same encoding, tablespace, collate and ctype) the accented words are shown weird.

União Estável

The web application HTML pages have charset defined to iso-8559-1.

Commands used for pg_dump and restore

pg_dump --file=example --format=custom --host=exampleIP --username=exampleusername --lock-wait-timeout=5000 exampledatabase

pg_restore --dbname=exampledatabase --format=custom --host="awsRdsDns" --username=exampleusername --verbose exampleFile

I also tried dump with --encoding=LATIN1 but nothing changed

When I select those data through PgAdmin3 in both databases, the accented words are shown normally.

What am I doing wrong?

The HTML charset is set to iso-8859-1. Altering HTML charset didn't change it. The RDS client_encoding is UTF8 because the majority of databases uses UTF8, there's only 6 other databases that uses LATIN1. Is there a way I can change it only for those 6 databases, without affecting the others?

Conclusion

I found out that my problem was my web application CLIENT_ENCODING was UTF8, so my solution was to set it to LATIN1, but because my question was directed to "other problem" I decided to acept that answer.

Answer 1

You can set an encoding that is different from the cluster-wide one the following way:

CREATE DATABASE important WITH ENCODING 'LATIN1' TEMPLATE template0;

but the documentation further says:

Notice that the above commands specify copying the template0 database. When copying any other database, the encoding and locale settings cannot be changed from those of the source database, because that might result in corrupt data.

All this can be done when creating the database, there is no ALTER DATABASE syntax for changing it later.

Setting client_encoding on the server might be tempting, but that you cannot do for a single DB only, it's all or none. A slightly ugly idea could be to set this parameter to 'UTF8' upon connection, before any data is accessed.