On Thu, Nov 23, 2023 at 10:55 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Ron Johnson <ronljohnsonjr@gmail.com> writes: > On Thu, Nov 23, 2023 at 3:37 AM Laurenz Albe <laurenz.albe@cybertec.at> > 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.)