Обсуждение: pg_dump/pg_restore schema and data separately and foreign key constraints

I have database on production server that backups every day. Database is not big ~ 10mb.
But I added several tables that takes big capacity and I don't want to backup data from them.

So, I backup my database in two files: schema and data:
pg_dump -s -E utf-8 -f ${filename.schema} -F custom -n public -O -Z 9 -h ${connection.hostname} -U
${connection.username}${connection.database} 
pg_dump -a -T table1 -T table2-E utf-8 -f ${filename.data} -F custom -n public -O -Z 9 -h ${connection.hostname} -U
${connection.username}${connection.database} 

Then I tried to restore this backups:
pg_restore -s -d ${connection.database} -h ${connection.hostname} -U ${connection.username} -O -F custom
${schemaFileName}
pg_restore -a -d ${connection.database} -h ${connection.hostname} -U ${connection.username} -O -F custom
${dataFileName}

Schema backup restored successfully.

But then I restoring data backup I receiving errors like:
COPY failed: ERROR:  insert or update on table "sometable" violates foreign key constraint "bla-blah"

I tried -1 option for pg_restore, it not helps.

I think this is common problem, but I don't found answer in google, only questions... :(

----- "Vasiliy Vasin" <vasi3854@yandex.ru> wrote:

> I have database on production server that backups every day. Database
> is not big ~ 10mb.
> But I added several tables that takes big capacity and I don't want to
> backup data from them.
>
> So, I backup my database in two files: schema and data:
> pg_dump -s -E utf-8 -f ${filename.schema} -F custom -n public -O -Z 9
> -h ${connection.hostname} -U ${connection.username}
> ${connection.database}
> pg_dump -a -T table1 -T table2-E utf-8 -f ${filename.data} -F custom
> -n public -O -Z 9 -h ${connection.hostname} -U ${connection.username}
> ${connection.database}
>
> Then I tried to restore this backups:
> pg_restore -s -d ${connection.database} -h ${connection.hostname} -U
> ${connection.username} -O -F custom ${schemaFileName}
> pg_restore -a -d ${connection.database} -h ${connection.hostname} -U
> ${connection.username} -O -F custom ${dataFileName}
>
> Schema backup restored successfully.
>
> But then I restoring data backup I receiving errors like:
> COPY failed: ERROR:  insert or update on table "sometable" violates
> foreign key constraint "bla-blah"
>
> I tried -1 option for pg_restore, it not helps.
>
> I think this is common problem, but I don't found answer in google,
> only questions... :(
>


Sounds like an ordering problem, you are restoring the child table before the parent. Best bet is to dump the table
datato individual files and restore them in the proper order. 

Adrian Klaver
aklaver@comcast.net

On Tuesday 12 May 2009, Vasiliy Vasin <vasi3854@yandex.ru> wrote:
> I have database on production server that backups every day. Database is
> not big ~ 10mb. But I added several tables that takes big capacity and I
> don't want to backup data from them.
>
> So, I backup my database in two files: schema and data:
> pg_dump -s -E utf-8 -f ${filename.schema} -F custom -n public -O -Z 9 -h
> ${connection.hostname} -U ${connection.username} ${connection.database}
> pg_dump -a -T table1 -T table2-E utf-8 -f ${filename.data} -F custom -n
> public -O -Z 9 -h ${connection.hostname} -U ${connection.username}
> ${connection.database}
>
> Then I tried to restore this backups:
> pg_restore -s -d ${connection.database} -h ${connection.hostname} -U
> ${connection.username} -O -F custom ${schemaFileName} pg_restore -a -d
> ${connection.database} -h ${connection.hostname} -U
> ${connection.username} -O -F custom ${dataFileName}
>
> Schema backup restored successfully.
>
> But then I restoring data backup I receiving errors like:
> COPY failed: ERROR:  insert or update on table "sometable" violates
> foreign key constraint "bla-blah"
>
> I tried -1 option for pg_restore, it not helps.
>
> I think this is common problem, but I don't found answer in google, only
> questions... :(

If you restore the whole schema first, it creates foreign key constraints.
Loading data after that would have to be done in full order of
dependencies.

A normal full schema + data restore creates the constraints only after the
data is already loaded. So the restore command doesn't worry about ordering
the data for foreign key dependencies.

In short, if you are constructing a partial backup + restore plan, you'll
have to order the data yourself, or create the schema in such a way that
you can load the data before creating the foreign key constraints.
pg_restore isn't going to cut it.


--
Even a sixth-grader can figure out that you can’t borrow money to pay off
your debt

In the end, decided to make easier: make a complete dump except tables that I don't want to backup, then just dump
schemaof these tables. So I have 2 files: schema+data for all except table1 and table2, schema only for table1 and
table2.
It's a strangely that pg_restore is still no option to check the constraints after you insert all data.
Something like http://www.postgresql.org/docs/8.3/static/sql-set-constraints.html

13.05.09, 01:17, "Adrian Klaver" <aklaver@comcast.net>:

> Sounds like an ordering problem, you are restoring the child table before the parent. Best bet is to dump the table
datato individual files and restore them in the proper order. 

Vasiliy Vasin wrote:
> I have database on production server that backups every day. Database is not big ~ 10mb.
> But I added several tables that takes big capacity and I don't want to backup data from them.
>
> So, I backup my database in two files: schema and data:
> pg_dump -s -E utf-8 -f ${filename.schema} -F custom -n public -O -Z 9 -h ${connection.hostname} -U
${connection.username}${connection.database} 
> pg_dump -a -T table1 -T table2-E utf-8 -f ${filename.data} -F custom -n public -O -Z 9 -h ${connection.hostname} -U
${connection.username}${connection.database} 
>
> Then I tried to restore this backups:
> pg_restore -s -d ${connection.database} -h ${connection.hostname} -U ${connection.username} -O -F custom
${schemaFileName}
> pg_restore -a -d ${connection.database} -h ${connection.hostname} -U ${connection.username} -O -F custom
${dataFileName}
>
> Schema backup restored successfully.
>
> But then I restoring data backup I receiving errors like:
> COPY failed: ERROR:  insert or update on table "sometable" violates foreign key constraint "bla-blah"
>
> I tried -1 option for pg_restore, it not helps.
>
> I think this is common problem, but I don't found answer in google, only questions... :(
>
>
What I did is the following:

1. create table 'copy_tables' that will contain all tables that are
supposed to be in backup procedure. That table contains those tables in
that order which will prevent this errors which occur when You restore
tables in order that will make 'foreign-key dependencies'. If table A
has foreign key to table B,You should first restore table B and then
table A,so foreign-keys that are supposed to be created will have an
object to refer to.

2. make a perl/php/bash script that will read table 'copy_tables' and
make backup or restore them. Backup is from lowest to biggest ID in that
table,while restore is from biggest to lower.

pg_dump/pg_restore does not have any intelligence over foreign-key
dependencies between tables.

Sincerely

Dragan Zubac

On Tuesday 12 May 2009 2:35:10 pm Vasiliy Vasin wrote:
> In the end, decided to make easier: make a complete dump except tables that
> I don't want to backup, then just dump schema of these tables. So I have 2
> files: schema+data for all except table1 and table2, schema only for table1
> and table2. It's a strangely that pg_restore is still no option to check
> the constraints after you insert all data. Something like
> http://www.postgresql.org/docs/8.3/static/sql-set-constraints.html


It is more of a conceptual problem. Unless you really do a complete dump (which
you actually did not do above) then the schema and data become decoupled. A
less than complete restore could be any combination of data and schema from
various points in time. A partial pg_restore has no way of "knowing" what is
correct, therefore it is left for the user to decide correctness.

>
> 13.05.09, 01:17, "Adrian Klaver" <aklaver@comcast.net>:
> > Sounds like an ordering problem, you are restoring the child table before
> > the parent. Best bet is to dump the table data to individual files and
> > restore them in the proper order.



--
Adrian Klaver
aklaver@comcast.net