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\PostgreSQL\12\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 dumps a plain SQL UTF8 file?
Best Answer
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: