Hi
I am facing issue while restoring the database. I have taken the backup of my database using pg_dump and then create new database and try to restore it using pg_restore. I am using PostgreSQL 9.0.
I have created one small test case to reproduce the issue, attached is the sql file.
Steps to reproduce:-
- Create new database 'Test_Backup' and run the attached .sql file.
- Take backup of the above mentioned database using pg_dump
- Create new database 'Test_Restore, and try to restore using pg_restore.
As per my understanding, problem is with the pg_dump, need confirmation. After analyzing the dump file(attached with the mail) it seems that COPY sample statement comes before COPY sample_one which is wrong because test.sample.ref_id is the REFERENCES of test.sample_one(id) table and I have created one constraint on sample table which is getting failed during restore.
One thing that I found is if we rename the 'sample' table to 'sample2' and 'sample_one' table to 'sample1', bug is not reproducible. So it seems that pg_dump take the backup sequential rather resolving the dependencies.
Is there any way to solve this issue without renaming the tables? Please help.
--
Akshay Joshi
Senior Software Engineer
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
Phone: +91 20-3058-9522
Mobile: +91 976-788-8246
