To enhance the restore speed for PostgreSQL databases, the
pg_restore command running the most time-consumping steps concurrently by providing the number of jobs with
Unfortunately the parallel restore can fail when restoring foreign keys due to not yet restored tables:
pg_restore: error: could not execute query: ERROR: there is no unique constraint matching given keys for referenced table "page" Command was: ALTER TABLE ONLY pagecontent ADD CONSTRAINT "$1" FOREIGN KEY (page_id) REFERENCES page(id);
Luckily the restore can be split into 3 phases:
# restore data definitions not handled in post-data (e.g. create tables, views) $ pg_restore -d database -U user -h localhost -W database.dump --section=pre-data # restore table data, large-object contents, and sequence values $ pg_restore -d database -U user -h localhost -j 6 -W database.dump --section=data # restore indexes, triggers, rules, and constraints other than validated check constraints $ pg_restore -d database -U user -h localhost -W database.dump --section=post-data
I did run the last command without concurrency.