pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Zsolt Ero <zsolt.ero@gmail.com>
Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #16732: pg_dump creates broken backups
Date: Fri, 20 Nov 2020 21:45:08 -0500
Message-ID: <1505939.1605926708@sss.pgh.pa.us> (raw)
In-Reply-To: <CAKFQuwZK43BHjM-yBLDGrxdLtM5aqkS9fkSTJ=oOnxMFv8=VxA@mail.gmail.com>
References: <16732-f34c8da8c206c290@postgresql.org>
	<CAKw-smD2cRb_ZnOfm45yoH4CStLZwCbcRxUR50+A4BMWm9RVgA@mail.gmail.com>
	<1303022.1605889713@sss.pgh.pa.us>
	<CAKw-smD5B5xYEUKeZSrUNBnMwafag7Ki0W3i9riiuqqexC3exg@mail.gmail.com>
	<1501942.1605920616@sss.pgh.pa.us>
	<CAKFQuwZK43BHjM-yBLDGrxdLtM5aqkS9fkSTJ=oOnxMFv8=VxA@mail.gmail.com>

"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





view thread (15+ messages)  latest in thread

Message-ID: <1505939.1605926708@sss.pgh.pa.us>
Permalink:  ../1505939.1605926708@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/1505939.1605926708@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, zsolt.ero@gmail.com, pgsql-bugs@lists.postgresql.org
  Subject: Re: BUG #16732: pg_dump creates broken backups
  In-Reply-To: <1505939.1605926708@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox