Re: BUG #16732: pg_dump creates broken backups

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> Before doing that, have you positively confirmed that map_id=112664 exists
> on the maps table in the live database?

Nah, there's no reason to think there's any corruption problem.  After
digging in the code a bit, I confirmed my theory that the problem is
the --data-only flag.  The way Zsolt is doing this, pg_restore is
making no attempt at all to order the tables in a way that respects
the FK relationships.  There is code for that, but it's on the pg_dump
side, and it's only executed if you said --data-only during the *dump*
operation:

 * getTableDataFKConstraints -
 *      add dump-order dependencies reflecting foreign key constraints
 *
 * This code is executed only in a data-only dump --- in schema+data dumps
 * we handle foreign key issues by not creating the FK constraints until
 * after the data is loaded.  In a data-only dump, however, we want to
 * order the table data objects in such a way that a table's referenced
 * tables are restored first.  (In the presence of circular references or
 * self-references this may be impossible; we'll detect and complain about
 * that during the dependency sorting step.)

This is not terribly friendly (and certainly not documented at the
user level).  I wonder how hard it would be to improve matters.
Offhand, it seems like it might be possible to run the same code
in pg_restore, when it's told to do a data-only restore; however
pg_restore does not do dependency sorting, so I'm not sure if it
could figure out how to cope with circular FK dependencies.

In the meantime, the options are (1) don't do it like that, or
(2) use --disable-triggers and just trust that the FK constraints
are satisfied.  Or maybe (3) drop the FK constraints and then
recreate/revalidate them after the data restore.

            regards, tom lane