1

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 dump a plain SQL UTF8 file?

Improve this question

1 Answer 1

Reset to default
3

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.

Improve this answer
1
  • 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! Commented Mar 22, 2022 at 2:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.