After restoring dump in postgres, some primary keys are missing
After dumping the database with pg_dump:
pg_dump --verbose --host=<host> --username=<user> -W --encoding=UTF-8 -j 10 --file=dump_bak --format=d --dbname=<database>
and try reimport 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 some of them have been restored, but not all.
Any ideas?
+3
source to share
1 answer
It looks like Postgres does a little bit of a problem regarding existing connections. We found a workaround:
Reset it with:
pg_dump --verbose --host=<dbhost> --username=<username> --encoding=UTF-8 --file=<dumpfile> --format=d --jobs=10 --dbname=<dbname>
Rebuild 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>
0
source to share