Re: BUG #16732: pg_dump creates broken backups

I didn't state why I'm doing all this: basically all I'd like to do is
exclude a table when restoring data.
The only way I managed to do this was to grep -v the list file before data
only restore.

On 2020. Nov 21., Sat at 3:45, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)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
>