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
Senior Software Engineer
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
Phone: +91 20-3058-9522
Mobile: +91 976-788-8246
Attachment:
TestCase.sql
Description: Binary data
Attachment:
sample_plain.backup
Description: Binary data
-- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
