Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kgItp-0002Vu-Ej for pgsql-bugs@arkaria.postgresql.org; Sat, 21 Nov 2020 02:45:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kgItm-0008IV-BO for pgsql-bugs@arkaria.postgresql.org; Sat, 21 Nov 2020 02:45:14 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kgItm-0008IN-5E for pgsql-bugs@lists.postgresql.org; Sat, 21 Nov 2020 02:45:14 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kgItj-0004zh-Nm for pgsql-bugs@lists.postgresql.org; Sat, 21 Nov 2020 02:45:13 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 0AL2j8Wj1505940; Fri, 20 Nov 2020 21:45:08 -0500 From: Tom Lane To: "David G. Johnston" cc: Zsolt Ero , PostgreSQL mailing lists Subject: Re: BUG #16732: pg_dump creates broken backups In-reply-to: References: <16732-f34c8da8c206c290@postgresql.org> <1303022.1605889713@sss.pgh.pa.us> <1501942.1605920616@sss.pgh.pa.us> Comments: In-reply-to "David G. Johnston" message dated "Fri, 20 Nov 2020 18:20:15 -0700" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1505938.1605926708.1@sss.pgh.pa.us> Date: Fri, 20 Nov 2020 21:45:08 -0500 Message-ID: <1505939.1605926708@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk "David G. Johnston" 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