After restoring dump in postgres some primary keys are missing

Question

After dumping a database using pg_dump like so:

pg_dump --verbose --host=<host> --username=<user> -W --encoding=UTF-8 -j 10 --file=dump_bak  --format=d --dbname=<database>

and trying to reimport it with:

pg_restore -d <database> --host=<host> -n public --username=<user> -W --exit-on-error --format=d -j 10 --verbose dump_bak

…we are missing some of our Primary Keys. It looks like a few have been restored, but not all.

Any ideas?

Answer 1

It seems that Postgres is a bit buggy there concerning existing connections. We have found a workaround:

Dump it with:

pg_dump --verbose --host=<dbhost> --username=<username> --encoding=UTF-8 --file=<dumpfile> --format=d --jobs=10 --dbname=<dbname>

Restore it with:

export PGPASSWORD="<pwd>"

#prevent new connections, kill existing connections
sudo -u postgres /usr/bin/psql -q -c "update pg_database set datallowconn = 'false' where datname = '<dbname>'; SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '<dbname>';"

#kick db
sudo -u postgres dropdb <dbname>

#recreate it
sudo -u postgres createdb <dbname>

#allow connections again
sudo -u postgres /usr/bin/psql -q -c "update pg_database set datallowconn = 'true' where datname = '<dbname>';"

#import data from dump
pg_restore --verbose -d <dbname> --host=$1 --username=<username> -j 4 <dumplocation>