Re: pg_restore enhancements - Mailing list pgsql-general

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.)

            regards, tom lane