I'm trying to dump a database and restore it. Running on windows 10, PostgreSQL 15.

I cannot wrap my head around the following:

This command creates a file that I cannot restore through pgadmin (pg_restore: error: input file does not appear to be a valid archive):

pg_dump -U postgres --blobs -Fc lusiadas > C:\mm\db_backup\lusiadas00.sql

This command creates a file that can be used in pg_restore (through pgadmin, same as above):

pg_dump --file C:\mm\db_backup\lusiadas0.sql -U postgres -Fc --blobs lusiadas

I've tried to do the same with a clean (empty) database (the "postgres" one that comes with the server), same results, so seems like the contents of the DB are not at fault. The plain format obviously works with both notations when restored with psql, only c and t are causing the problem.

File sizes

  • I would not give a dump in the custom forma the "sql" extension - that is confusing because it is not a "SQL script". Regarding the actual question: redirecting binary output on Windows doesn't work reliably (don't know if it's a Postgres problem or Windows problem). Try to use pg_dump ... -f C:\mm\db_backup\lusiadas00.sql instead. The there should be no problem restoring it using pg_restore.
    – user1822
    Commented Dec 12, 2022 at 18:45
  • Yea, I should've used .backup for good measure here in the post (the issue was more one of curiosity necessity).
    – Mixel
    Commented Dec 12, 2022 at 21:48

1 Answer 1


When using pg_dump > file, the pg_dump program writes bytes to the standard output, and the calling shell is responsible for writing the bytes to disk, into file.

Some shells change the contents in transit, instead of just passing the bytes through. Powershell, for instance. See this still-open issue on github: Don’t parse the pipeline as text when it is directed from an EXE to another EXE or file. Keep the bytes as-is. #1908 .

Since the wrong file looks about twice the size of the other one, the shell probably encoded it to UTF-16 (2 bytes per character for most characters).

The simplest solution is to use the --file option, or if it's really needed to pipe the output to a shell, make sure to use a shell program that does not have this problem.

Your Answer

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

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