Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

After using below the backup cmd in a db with 250+ tables:

pg_dump -h localhost -p portNum -U userName -f fileName -o dbName

I tried restoring it with the following command:

psql -h localhost -p portNum -U userName -d dbName -f fileName

While doing so many of the tables were having foreign key dependency, and hence only few tables got restored. How can I resolve the issue and restore complete database? Will turning off the foreign key constraint in postgres help me solve the issue. If so, how can I achieve this? What should I do to turn it off and turn it on after restoring?


migration rejected from Sep 28 '13 at 12:46

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as off-topic by dezso, RolandoMySQLDBA, Mark Storey-Smith, Paul White, bluefeet Sep 28 '13 at 12:46

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – dezso, RolandoMySQLDBA, bluefeet
If this question can be reworded to fit the rules in the help center, please edit the question.

PostgreSQL version? pg_dump version? psql in modern-ish versions will sort tables to restore in FK dependency order and for full (non --data-only) dumps will create the FKs and indexes after the tables are created and data are restored. – Craig Ringer Mar 26 '13 at 8:54
What version of PostgreSQL are you using? This works for me on 8+ cat fileName | psql dbName – Chris Aitchison Mar 26 '13 at 12:03
Are you restoring into a non-empty database? – Daniel Vérité Mar 26 '13 at 16:41

Normally pg_dump adds the foreign keys after restoring. My suspicion here is that you are restoring to a non-empty db. You should try creating a new db first and then restoring. If that still fails, then you should check and make sure you didn't accidently restore at some point to template1. If you did, the simplest solution is to use initdb to create a new data directory and restore there to test.

If that works you can drop template1 and recreate it from template0, but that is a somewhat complex process and you;d probably want to ask another question for that.