Akshay Joshi wrote:
> 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:-
>
> 1. Create new database 'Test_Backup' and run the attached .sql
file.
> 2. Take backup of the above mentioned database using pg_dump
> 3. 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.
I can reproduce the error with 9.1.
pg_dump does not resolve dependencies, it avoids problems by adding
constraints
after inserting the data.
It seems that this is not done for CHECK constraints, however - they are
added
when the table is defined.
I think that this is a bug.
Yours,
Laurenz Albe
