pg_dump output formats

Question

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.

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.