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 1kg9H5-0003La-UX for pgsql-bugs@arkaria.postgresql.org; Fri, 20 Nov 2020 16:28:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kg9H4-0008Rq-S8 for pgsql-bugs@arkaria.postgresql.org; Fri, 20 Nov 2020 16:28:38 +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 1kg9H4-0008Rj-MF for pgsql-bugs@lists.postgresql.org; Fri, 20 Nov 2020 16:28:38 +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 1kg9H2-0006Nz-Ir for pgsql-bugs@lists.postgresql.org; Fri, 20 Nov 2020 16:28:38 +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 0AKGSXWR1303023; Fri, 20 Nov 2020 11:28:33 -0500 From: Tom Lane To: Zsolt Ero cc: pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16732: pg_dump creates broken backups In-reply-to: References: <16732-f34c8da8c206c290@postgresql.org> Comments: In-reply-to Zsolt Ero message dated "Thu, 19 Nov 2020 18:21:19 -0800" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1303021.1605889713.1@sss.pgh.pa.us> Date: Fri, 20 Nov 2020 11:28:33 -0500 Message-ID: <1303022.1605889713@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Zsolt Ero writes: > Sorry this is not so general, I over-simplified the example. The key is > --use-list (with possibly --jobs) > What I've double checked is that the following breaks it consistently: > pg_restore --schema-only --dbname=app --format=custom --single-transaction > $BACKUP_FILE > pg_restore --list $BACKUP_FILE > dump_list.full > pg_restore --data-only --dbname=app --format=custom > --use-list=dump_list.full --jobs=4 $BACKUP_FILE I suspect actually the problem has more to do with having split the restore into --schema-only and --data-only steps. That forces pg_restore to create the FK constraints before it's loaded the data, and in certain cases such as circular FK relationships, there will be no safe restore order for the data. However, that's all just speculation since you haven't shown us a reproducible case. regards, tom lane