Re: pg_restore enhancements

Thanks for the explanation.

On Thu, Nov 23, 2023 at 10:55 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Ron Johnson <ronljohnsonjr(at)gmail(dot)com> writes:
> > On Thu, Nov 23, 2023 at 3:37 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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
>