How to db_dump with correct encoding

Question

I am doing a script that is able to download a db_dump of a remote postgres database.

The problem is that I am trying to get the remote database on the correct enconding and I'm not being able to do it.

the remote database has a LATIN1 enconding and when I execute the script I dumped it is UTF-8

note that I want it to keep the encoding of the remote database so if the remote db is UTF-8 i want the local one to be utf-8 too

does someone know how to accomplish this??

Answer 1

In PostgreSQL database has an encoding but also a connection to database/session has one. Server will do the necessary data conversion on-the-fly.

Command pg_dump already uses correct encoding - by default it's original database one, but you can choose different by -E option. If you use -C then it will add CREATE statement with proper encoding (plain text format).

Look at this few lines of pg_dump (-E LATIN1 -C) SQL file:

SET client_encoding = 'LATIN1';
...
CREATE DATABASE postgres WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'pl_PL.UTF-8' LC_CTYPE = 'pl_PL.UTF-8';

All you have to do is to create database with encoding you want, or use -C pg_dump option to include CREATE command in dump file. PostgreSQL psql (or pg_restore) will do the rest.