Postgresql – OpenProject pg_restore – input file does not appear to be a valid archive

pg-restorepostgresql

I'm trying to restore my OpenProject's database created with the command

sudo openproject run backup

this command created the file postgresql-dump-20190722131834.pgdump which I'm trying to restore with the command

pg_restore --host=localhost --dbname=openproject postgresql-dump-20190722131834.pgdump

but it returns me the error

pg_restore: [archiver] input file does not appear to be a valid archive

I've also tried to att the --format=c option to the pg_restore command, but the error changes to

pg_restore: [archiver] did not find magic string in file header

The script which OpenProject runs to create a PostgreSQL backup is:

dump_postgresql() {
  dst="${TARGET}/postgresql-dump-${timestamp}.pgdump"
  touch "$dst" && chmod 0640 "$dst"
  pg_dump -Fc $DATABASE_URL > $dst

  echo " done" >&2
  echo "$dst"
}

I've also noticed that pg_dump prints a lot of warnings like this:

pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- ftell used

I'm trying to restore the database on a machine which runs Ubuntu 18.04 and PostgreSQL 10.

This is the result of the command head postgresql-dump-20190722131834.pgdump (I've put it in a screenshot because there are some invalid characters which I can't copy-paste):
head command results

Best Answer

I'll post an answer because it might be useful to someone else.

As the screenshot in the question displays, there is an error in the /home/openproject/.postgresqlrc file on the server on which I've run the backup.

The format of that file was not correct: I've fixed it by following the format described here and when I had run the next backup I get no errors, and I was also able to restore that backup successfully.

Related Question