public inbox for [email protected]
help / color / mirror / Atom feedFrom: Wasim Devale <[email protected]>
To: Ron Johnson <[email protected]>
Cc: pgsql-admin <[email protected]>
Cc: Pgsql-admin <[email protected]>
Subject: Re: Restoration process generates 1.2 TB of WAL files.
Date: Thu, 29 Jan 2026 13:25:14 +0530
Message-ID: <CAB5fag5v1YAbH82jTuYbo_hW6s-qzPuMrNb+kboXMWKi8PR_tg@mail.gmail.com> (raw)
In-Reply-To: <CANzqJaBXCKAKaFxpuW5cjLwJC9_UMB_OtMbGq+o3NfTiTDd3tQ@mail.gmail.com>
References: <CAB5fag4n6o0=wLj6wn=fbUsHTZyOd4cZ=_soLX3FDc+N8FQJVg@mail.gmail.com>
<CANzqJaBXCKAKaFxpuW5cjLwJC9_UMB_OtMbGq+o3NfTiTDd3tQ@mail.gmail.com>
Thank you for the information.
Wasim.
On Thu, 29 Jan, 2026, 1:03 am Ron Johnson, <[email protected]> wrote:
> On Wed, Jan 28, 2026 at 2:16 PM Wasim Devale <[email protected]> wrote:
>
>> Hi All
>>
>> I am taking directory dump from postgresql 12 and restoring it on
>> postgresql 17 this process create 1.2TB of wals files in pg_wal directory.
>> Can we reduce this WAL files generation via any configuration changes?
>>
>
> Sure. This is what I used when restoring some 3+ TB databases. (Your
> values for $SharedBuffs and $MaintMem will vary based on how much RAM
> you have and how many threads you use in pg_restore.)
>
> Setting $SharedBuffs and (especially) $MaintMem too high will cause the
> Linux oom-killer to kill one or more of the CREATE INDEX or ADD PRIMARY KEY
> statements, and you'll scratch your head looking at the pg_restore log file
> wondering what happened.
>
> I based it on:
> https://www.databasesoup.com/2014/09/settings-for-fast-pgrestore.html
>
> pg_ctl restart -wt$TimeOut -mfast \
> -o "-c hba_file=$PGDATA/pg_hba_maintmode.conf" \
> -o "-c fsync=off" \
> -o "-c log_statement=none" \
> -o "-c log_temp_files=100kB" \
> -o "-c log_checkpoints=on" \
> -o "-c log_min_duration_statement=120000" \
> -o "-c shared_buffers=${SharedBuffs}GB" \
> -o "-c maintenance_work_mem=${MaintMem}GB" \
> -o "-c synchronous_commit=off" \
> -o "-c archive_mode=off" \
> -o "-c full_page_writes=off" \
> -o "-c checkpoint_timeout=30min" \
> -o "-c max_wal_size=36GB" \
> -o "-c wal_level=minimal" \
> -o "-c max_wal_senders=0" \
> -o "-c wal_buffers=64MB" \
> -o "-c autovacuum=off" || exit $?
> pg_restore --verbose --jobs=X ... &> pg_restore.log || exit $?
> pg_ctl stop -wt9999 && pg_ctl start -wt9999
>
> These are "bad" settings if you have other databases in your instance and
> they need to keep running while you restore your DB. *I don't have that *
> *problem*, so think it's great.
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Restoration process generates 1.2 TB of WAL files.
In-Reply-To: <CAB5fag5v1YAbH82jTuYbo_hW6s-qzPuMrNb+kboXMWKi8PR_tg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox