Thread: Facing error while restoring the database

On 27 March 2012 11:33, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:
> 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.

What is the error?

It looks like you are trying to use pg_restore on a plain text dump.
In that case, use psql to restore instead.

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

On 27 March 2012 15:12, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:
> On Tue, Mar 27, 2012 at 6:15 PM, Alban Hertroys <haramrae@gmail.com> wrote:
>>
>> On 27 March 2012 11:33, Akshay Joshi <akshay.joshi@enterprisedb.com>
>> wrote:
>    pg_restore: restoring data for table "sample"
>    pg_restore: [archiver (db)] Error while PROCESSING TOC:
>    pg_restore: [archiver (db)] Error from TOC entry 1800; 0 54031 TABLE DATA
> sample postgres
>    pg_restore: [archiver (db)] COPY failed for table "sample": ERROR:  new
> row for relation "sample" violates check constraint
> "check_params_array_size"
>    CONTEXT:  COPY sample, line 1: "1 2 {}"
>    pg_restore: restoring data for table "sample_one"
>
>   As per the above log, it is clear that restoring of sample table start
> before sample_one and due to that my "check_params_array_size" constraint
> gets failed.

Aha, it's a check constraint! Those don't cover multiple tables, so
the order in which tables with check constraints get restored should
not matter.

In your case it apparently does matter, which seems to indicate that
you did something to circumvent that limitation and are now running
into problems because of that.

What's the definition of that constraint?

Perhaps a foreign key constraint would server you? Adding one should
at least make sure your database gets dumped in the correct order.
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

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

"Albe Laurenz" <laurenz.albe@wien.gv.at> writes:
> 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.

It is not a bug; it is an unsafe and unsupported use of CHECK
constraints.

Using a CHECK to enforce a cross-row constraint is fundamentally broken,
because there is no way for the database to know that the constraint
might be violated after the *other* row is modified.  In the example
at hand, a change in sample_one.param_names could leave the constraint
unsatisfied for some rows in sample, but the database wouldn't detect
that.

I think the right fix here would be to redesign the table schema so that
the required cross-table constraint could be expressed as a foreign key.
We don't have enough context to guess at what a better design would
look like, though.

            regards, tom lane

On Tuesday, March 27, 2012 08:20:23 PM Akshay Joshi wrote:
>   In my case I won't allow anyone to insert/modify the rows of sample_one
> table. I have already inserted some rows in sample_one table where I
>   want one constraint is number of array elements of sample_one.param_names
> and sample.params must be same. That's why I have created
>   CHECK constraint in sample table. User can insert, modify and delete the
> rows of sample table, so I don't want any mismatch in the number of
>   array elements of sample_one.param_names and sample.params table.
>

Well you have a couple of options.

1) Redesign to use a trigger instead of a check constraint.

2) During restore, you can use a custom table of contents extracted from the
dump, remove the constraint from that, do your restore, and then create the
constraint manually afterwards. This requires that you use the custom dump
format and pg_restore, of course.