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??
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.
I just ran into this problem and could not find a clean solution. Google led me here. I wound up manually editing the file resulting from pg_dump to add the encoding to its CREATE statement.
Like this:
sed -i "s/CREATE DATABASE dbname WITH TEMPLATE = template0 OWNER = dbuser/CREATE DATABASE dbname WITH TEMPLATE = template0 OWNER = dbuser ENCODING = desired encoding/" data.sql
From the documentation of pg_restore:
-C, --create Zieldatenbank erzeugen
This will autocreate the target database from your dump. However, I don't know if the encoding is always correct, or just the default database encoding defined for the server you restore into.
