PostgreSQL pg_dump -E encoding option not working

Question

I have a UTF8 database qdb and I want to back it up to a plain file using the same UTF8 encoding. I am using pg_dump as I don't have pgAdmin working now. I however can't get pg_dump to output a UTF8-encoded plain SQL dump file, as this is not working:

C:\Program Files\PostgreSQL12円\bin> .\pg_dump.exe -U postgres -F p -O -c -C -E UTF8 qdb > d:\qdb.sql

The output dump file is encoded as UCS-2 LE BOM nonetheless, which makes some German accented letters display incorrectly. I am using Windows 10 and PostgreSQL 12.4. How can we make pg_dump actually dump a plain SQL UTF8 file?

Answer 1

Forcing a BOM and UTF-16 encoding for redirected output can be done somehow by Windows shells (personally I've experienced this with Powershell and Windows 10). See e.g. Resolve UTF-8, UTF-16, ASCII inconsistencies for some context.

In the case of pg_dump, using the -f option to specifiy the output file instead of a shell redirection seems like a good idea against that sort of problem. From the manual:

-f file
--file=file

Send output to the specified file. This parameter can be omitted for file based output formats, in which case the standard output is used.

• Fr Jeremy Krieg: I just hit this same issue and it looks like shell translation was the culprit, thank you so much for this post. As an added bonus, using -f rather than shell redirection reduced the dump time for my database from around 90s to 10s!