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() {
  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

  • 1
    How is this backup taken? Maybe that .pgdump is a dump in plain text format which is essentially a SQL script that you need to run through psql. What do you see with head postgresql-dump-20190722131834.pgdump – a_horse_with_no_name Jul 24 '19 at 10:38
  • @a_horse_with_no_name I've updated my question with the result. I've found some invalid characters in that file, but I don't know if it means that the file is corrupted or that it's not a plain-text format... – Riccardo Jul 24 '19 at 15:48

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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