`fly postgres connect < ./backup.sql` produces errors

Questions / Help
1

I wrote a little side-car app that backups my postgres database: GitHub - nobt-io/docker-postgres-restic-backup: A sidecar docker container for backing up a PostgreSQL database with restic..

This works like a charm. Yesterday I had to make use of those backups and wanted to restore the database like this:

fly postgres connect --app db  < ./backup.sql

Unfortunately, that didn’t work. Towards the end, postgres reported several syntax errors.

Instead of feeding the script via stdin, I chunked it up and submitted the script piece-wise to an established psql connection (also via fly postgres connect). This worked fine and did not produce any syntax errors.

This makes me think that whatever tunneling fly postgres connect does messes up the stdin stream that ends up being fed to the database. Most parts of the backup script are sensitive to newlines for example, i.e. postgres uses COPY from stdin to repopulate the tables.

I am currently testing whether using --inserts results in the same error.

2

I’ve tested it with --inserts now and it works without problems but as documented in pg_dump, this is much slower so it may not be an option for people with big databases but it works for my needs.