CODEWITHSUNDEEP
postgresql
Killercode
Killercode

Reputation: 904

How to db_dump with correct encoding

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??

Upvotes: 3

Views: 11235

Answers (3)

wes
wes

Reputation: 101

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

Upvotes: 0

kupson
kupson

Reputation: 7238

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.

Upvotes: 1

Daniel
Daniel

Reputation: 28104

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.

Upvotes: 0

Related Questions