8

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?

Improve this question

3 Answers 3

Reset to default
6

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.

Improve this answer
3
  • 1
    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?
    – RTD
    Dec 8, 2020 at 15:58
  • 1
    @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.
    – Fr Jeremy Krieg
    Aug 12, 2022 at 8:45
  • 1
    I confirm - the redirection was the problem. Thanks.
    – RTD
    Aug 12, 2022 at 14:10
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 format. I found that cmd.exe on Windows 11 was writing my output to some form of UTF16 (not sure if it was LE or BE). 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, pg_dump writes directly to the file and the shell redirection (and possible charset translation) is bypassed.

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

Improve this answer
1
  • This is actually the best answer and should be marked as the right one.
    – user1337
    Nov 17, 2022 at 16:47
0

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.