Error on restore a dockerized PostgreSQL database

Question

I have a dockerized Postgres db. I have successfully executed

docker exec -t your-db-container pg_dumpall -c -U postgres > dump_`date +%d-%m-%Y"_"%H_%M_%S`.sql

and generated .sql file. But when I'm trying to restore with the following script:

cat my_dump.sql | docker exec -i your-db-container psql --username="myusername" mydb

I get:

ERROR: syntax error at or near "pg_dumpall"

LINE 1: pg_dumpall: error: could not connect to database "template1"...

What am I missing?

Answer 1

The dump file contains an error message from the backup procedure instead of containing a database dump.

docker exec combines the standard output and the standard error. Its output cannot be trusted for a backup file.

Aside from solving the root problem that pg_dumpall in the container cannot connect to template1, you want a more sophisticated dump procedure that cannot create this situation where a shell error message ends up where SQL statements should be.