Menu
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?
804
PostgreSQL Dump Import process can be carried out using psql command. You can use the psql utility to recover the SQL script file created by pg_dump, pg_dumpall, or any other tool that creates compatible backup files. You can run the complete script in the dump file with the psql tool.
Restoring the data from pg_dump doesn't overwrite the data but it appends the data to the original database. New!
sql , which uses the plain or SQL format, the pg_dump command does not store any file anywhere. It just sends the output to STDOUT , which is usually your screen, and it's done.
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>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With