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

----- "Vasiliy Vasin" <vasi3854(at)yandex(dot)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 data to individual files and restore them in the proper order.

Adrian Klaver
aklaver(at)comcast(dot)net