Import postgresql database with Latin encoding

Question

I'm going to upgrade from postgresql 9 to postgresql 11, the encoding used is Latin. I've exported the database using this command pg_dumpall | gzip > /tmp/db.sql.gz I extracted the exported database

SET default_transaction_read_only = off;
SET client_encoding = 'LATIN1';
SET standard_conforming_strings = on;
CREATE DATABASE db WITH TEMPLATE = template0 OWNER = postgres;

Then I impoted the database using this command

psql -d postgres -f ./db.sql

The database is imported but when I run l+, I've noticed that the encoding UTF8 is used on all databases.

Why the encoding is changed from Latin to UTF8 ? and How can I fix this error ?

Answer 1

Because you created the database with that encoding. Since your CREATE DATABASE statement does not explicitly specify the encoding, you will get a database with the standard encoding of the cluster, which obviously was created with UTF8 encoding (you probably did not specify that explicitly, so initdb took it from your environment).

To get a LATIN1 database, you would have to

/* you should also specify the correct LC_COLLATE and LC_CTYPE */
CREATE DATABASE db WITH ENCODING LATIN1 TEMPLATE = template0 OWNER = postgres; 

But UTF-8 actually much better; leave it like that. The database encoding should always be UTF8. The reason for that is that you can store any character in a UTF8 database. Even if your current application only uses LATIN1, are you sure what uses the database may have 10 years from now?