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

On Tuesday 12 May 2009, 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... :(

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