pg_dump/restore encoding woes - Mailing list pgsql-hackers

pg_dump and pg_restore don't behave very nicely when the client and 
server encodings don't match. Below are three issues that arise from 
that. All the examples below use a console with a UTF-8 locale, and the 
'latin1db' database uses ISO-8859-1 as the database encoding. In that 
database, there is a single table called "pöö".

1. pg_dump verbose output
-------------------------

$ pg_dump -d latin1db -Fc -v -f a.backup

...
pg_dump: finding the columns and types of table "p��"
...

When client encoding is not specified explicitly with the -E option, or 
PGCLIENTENCODING env variable, the dump is created in the server encoding.

Alexander Law reported this bug about a year ago, see bug #6742: 
http://www.postgresql.org/message-id/E1SrOVd-00028F-Sz@wrigleys.postgresql.org.

Now, you can say that it's the user's fault for not specifying 
client_encoding correctly, but see #2.

2. pg_dump -t option doesn't work if client_encoding is not set
---------------------------------------------------------------

$ ./pg_dump -d latin1db -Fc -t pöö -f a.backup
pg_dump: No matching tables were found

$ ./pg_dump -d latin1db -Fc -t pöö -f a.backup -E utf-8
(success)

The table name given in the argument is passed to the server without 
translation, so client_encoding needs to be set or the server will not 
interpret the table name correctly.

Like #1, this is a user-error - he needs to set client_encoding 
correctly. Other client programs like vacuumdb have the same problem. 
But we could do better. psql sets client_encoding automatically 
(client_encoding='auto') based on the locale. Why don't we do the same 
in all the client programs?

However, pg_dump is special, because client encoding affects not only 
the encoding used to speak to the server, but it also determines how the 
resulting dump is encoded. If you have a UTF-8 server, and a LATIN1 
console, there is no way to get a UTF-8 encoded dump of a single table 
which has non-ASCII characters in its name. There is a good reason to 
want to dump in the server encoding regardless of the encoding of the 
client: that avoids the costly encoding conversion during the dump, and 
very likely another conversion back on restore. (as a convenience, it 
would be nice if you could specify "-E server" to mean "same as server 
encoding")

The pg_dump -E option just sets client_encoding, but I think it would be 
better for -E to only set the encoding used in the dump, and 
PGCLIENTENCODING env variable (if set) was used to determine the 
encoding of the command-line arguments. Opinions?

3. pg_restore -t option doesn't work if dump is in different encoding
---------------------------------------------------------------------

$ pg_dump -d latin1db -Fc -f a.backup
$ ./pg_restore -t pöö a.backup
(restores nothing)

pg_restore doesn't convert encodings when it matches the table name 
given with -t option with the table names in the dump. Hence in above 
example, where the dump is in LATIN1 encoding and the console uses a 
UTF-8 locale, the table name is not matched even though there is a table 
with that name in the dump.

Unfortunately I don't see any easy solution to this third issue :-(. We 
don't have any infrastructure to do encoding conversions in the client. 
I guess we could use iconv(3) if it's available, but I'm a bit reluctant 
to start using that, given that we've managed to do with out client-side 
conversions this far. Or we could do the conversion in the server using 
"convert_from()", but that only works if pg_restore is connected to a 
database. Perhaps it's best to just throw a warning in if -t is used and 
the dump's encoding doesn't match the current locale.

- Heikki