issues with encoding and pg_dump/restore between windows and linux

Question

I have a dmp file I have created using pg_dump encoded in WIN1252. What I would like to do is import it using pg_restore to a database on a Linux machine. The problem is that on the windows machine postgreSQL recognizes the encoding WIN1252 but not the CP1252 encoding (they both are identical just different names for the same standard). On the Linux machine, postgreSQL recognizes CP1252 but not WIN1252. So when I try and do a pg_restore I get an error because WIN1252 is not recognized as a proper encoding. Also, if I try to do a pg_dump on windows and specify the encoding as CP1252, I get a similar error. I would like to stick with this encoding format so doing a pg_dump in utf8 or something is not something I would like to do. Any help would be appreciated!

EDIT: Actually, upon further investigation, postgreSQL does not understand cp1252. The iconv linux command used to convert a text file from one encoding to another understands cp1252 and not win1252 and thats where I got it from. On the Linux machine, I still cannot set the client encoding to WIN1252. This is the error message:

FATAL: conversion between WIN1252 and LATIN1 is not supported

The variable client_encoding was originally set to UTF8 on the Linux machines so I dont know where LATIN1 is coming from. Unless WIN1252 is not supported so postgres tries to convert it to latin1 because that is the closest thing to it and fails at achieving that due to some character miss-match?

Answer 1

I suppose the dump file you have is encoded in WIN1252 and the target database on Linux has LATIN1 as the server encoding. That's not going to work, as the error message says.

I suggest you reinitialize the target database using UTF8.

Alternatively, create the dump in UTF8 using the pg_dump -E option.

• Rafs: The command pg_dump -E UTF8 -U postgres -F p -O -c -C db > D:\db.sql is still creating a dump file in UCS-2 LE BOM encoding, although the database's encoding itself is UTF8.. what can be the cause?
• Fr Jeremy Krieg: @RTD I had the same issue - in my case, I think it was the shell redirection doing charset translation. Using the -f option to write the output directly to a file fixed the problem.
• Rafs: I confirm - the redirection was the problem. Thanks.

Answer 2

I'm late to the party, but I think that the accepted answer doesn't tell the whole story.

If you're using the syntax

pg_dump ... > out.sql

, then it's actually the shell that's writing the file to disk, not pg_dump. Sometimes the shell in Windows will "helpfully" translate it into another charset as it writes the file. I found that cmd.exe on Windows 11 was writing my output to some form of UTF-16 (not sure if it was LE or BE) instead of the UTF-8 that I had specified with -E. As a result the file was double the size I was expecting!

To be really safe, it's probably best to use the -E and the -f option to specify the filename:

pg_dump -E UTF-8 -f out.sql

That way, the shell redirection (and possible charset translation) is bypassed, and pg_dump writes directly to the output file and hence is in complete control of the encoding process.

As a bonus, the shell's charset translation is also an extra performance overhead, so writing directly to the file might be slightly faster too.

• user1337: This is actually the best answer and should be marked as the right one.

Answer 3

The solution is to use the "iconv" utility. cat dump.sql | iconv | psql. This usually cleans up your encoding.

See: http://www.documentroot.com/2013/12/utf8-encoding-and-postgres-dump.html

• Fr Jeremy Krieg: This approach may work most of the time but it is a hack. The problem with charset-related hacks is that they work until they don't... and it's hard to predict when that will be. Years of experience fixing charset issues has taught me it's better to fix the root problem than rely on hacks like these. Please see my answer above for the root fix in this case.