Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r6C3K-00HXdj-ST for pgsql-general@arkaria.postgresql.org; Thu, 23 Nov 2023 15:55:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1r6C3J-001tCU-HF for pgsql-general@arkaria.postgresql.org; Thu, 23 Nov 2023 15:55:41 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r6C3J-001tCB-6H for pgsql-general@lists.postgresql.org; Thu, 23 Nov 2023 15:55:41 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r6C3G-0083s8-TZ for pgsql-general@lists.postgresql.org; Thu, 23 Nov 2023 15:55:40 +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 3ANFta9I2659883; Thu, 23 Nov 2023 10:55:36 -0500 From: Tom Lane To: Ron Johnson cc: "pgsql-general@lists.postgresql.org" Subject: Re: pg_restore enhancements In-reply-to: References: <362032628.6955904.1700659540303.ref@mail.yahoo.com> <362032628.6955904.1700659540303@mail.yahoo.com> <383739960.7061560.1700672154915@mail.yahoo.com> <368f73d6c7a1530d82f9c1c12a2cd0a14780b37a.camel@cybertec.at> Comments: In-reply-to Ron Johnson message dated "Thu, 23 Nov 2023 09:07:08 -0500" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <2659881.1700754936.1@sss.pgh.pa.us> Content-Transfer-Encoding: 8bit Date: Thu, 23 Nov 2023 10:55:36 -0500 Message-ID: <2659882.1700754936@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Ron Johnson writes: > On Thu, Nov 23, 2023 at 3:37 AM Laurenz Albe > wrote: >> You can avoidwriting WAL if you set "wal_level = minimal", restart >> PostgreSQL >> and restore the dump with the --single-transaction option. > Why does "--single-transaction" prevent WAL writes? I'd expect _more_ > pg_wal growth from One Ginormous Transaction. I don't recall all the details offhand, but there's some optimization concerned with not writing WAL if COPY's target table was created in the current transaction. WAL will still be made for the catalog changes, but usually the bulk of the WAL for a pg_restore run comes from loading data, and this recipe eliminates that. (Of course, you cannot use it on a replication primary.) regards, tom lane