Re: pg_dump/pg_restore schema and data separately and foreign key constraints

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