MIME-Version: 1.0
References: 
 <CAB5fag4n6o0=wLj6wn=fbUsHTZyOd4cZ=_soLX3FDc+N8FQJVg@mail.gmail.com>
In-Reply-To: 
 <CAB5fag4n6o0=wLj6wn=fbUsHTZyOd4cZ=_soLX3FDc+N8FQJVg@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Wed, 28 Jan 2026 14:33:27 -0500
Message-ID: 
 <CANzqJaBXCKAKaFxpuW5cjLwJC9_UMB_OtMbGq+o3NfTiTDd3tQ@mail.gmail.com>
Subject: Re: Restoration process generates 1.2 TB of WAL files.
To: Wasim Devale <wasimd60@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>,
	Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000002b4a86064977d1e7"
Archived-At: 
 <https://www.postgresql.org/message-id/CANzqJaBXCKAKaFxpuW5cjLwJC9_UMB_OtMbGq%2Bo3NfTiTDd3tQ%40mail.gmail.com>
Precedence: bulk

--0000000000002b4a86064977d1e7
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wed, Jan 28, 2026 at 2:16=E2=80=AFPM Wasim Devale <wasimd60@gmail.com> w=
rote:

> 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=3D$PGDATA/pg_hba_maintmode.conf" \
        -o "-c fsync=3Doff" \
        -o "-c log_statement=3Dnone" \
        -o "-c log_temp_files=3D100kB" \
        -o "-c log_checkpoints=3Don" \
        -o "-c log_min_duration_statement=3D120000" \
        -o "-c shared_buffers=3D${SharedBuffs}GB" \
        -o "-c maintenance_work_mem=3D${MaintMem}GB" \
        -o "-c synchronous_commit=3Doff" \
        -o "-c archive_mode=3Doff" \
        -o "-c full_page_writes=3Doff" \
        -o "-c checkpoint_timeout=3D30min" \
        -o "-c max_wal_size=3D36GB" \
        -o "-c wal_level=3Dminimal" \
        -o "-c max_wal_senders=3D0" \
        -o "-c wal_buffers=3D64MB" \
        -o "-c autovacuum=3Doff" || exit $?
pg_restore --verbose --jobs=3DX ... &> 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.

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

--0000000000002b4a86064977d1e7
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Wed, Jan 28, 2026 at 2:16=E2=80=AFPM W=
asim Devale &lt;<a href=3D"mailto:wasimd60@gmail.com">wasimd60@gmail.com</a=
>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><blockqu=
ote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px=
 solid rgb(204,204,204);padding-left:1ex"><div dir=3D"auto">Hi All<div dir=
=3D"auto"><br></div><div dir=3D"auto">I am taking directory dump from postg=
resql 12 and restoring it on postgresql 17 this process create 1.2TB of wal=
s files in pg_wal directory. Can we reduce this WAL files generation via an=
y configuration changes?</div></div></blockquote><div><br></div><div>Sure.=
=C2=A0 This is what I used when restoring some 3+ TB databases.=C2=A0 (Your=
 values for $SharedBuffs and $MaintMem will vary based on how much RAM you=
=C2=A0have and how many threads you use in pg_restore.)</div><div><br></div=
><div>Setting $SharedBuffs and (especially) $MaintMem too high will cause t=
he Linux=C2=A0oom-killer to kill one or more of the CREATE INDEX or ADD PRI=
MARY KEY statements, and you&#39;ll scratch your head looking at the pg_res=
tore log file wondering what happened.</div><div><br></div><div>I based it =
on:</div><div><a href=3D"https://www.databasesoup.com/2014/09/settings-for-=
fast-pgrestore.html">https://www.databasesoup.com/2014/09/settings-for-fast=
-pgrestore.html</a></div><div><br></div><div><font face=3D"monospace">pg_ct=
l restart -wt$TimeOut -mfast \<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o &quot;-c h=
ba_file=3D$PGDATA/pg_hba_maintmode.conf&quot; \<br>=C2=A0 =C2=A0 =C2=A0 =C2=
=A0 -o &quot;-c fsync=3Doff&quot; \<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o &quot=
;-c log_statement=3Dnone&quot; \<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o &quot;-c=
 log_temp_files=3D100kB&quot; \<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o &quot;-c =
log_checkpoints=3Don&quot; \<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o &quot;-c log=
_min_duration_statement=3D120000&quot; \<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o =
&quot;-c shared_buffers=3D${SharedBuffs}GB&quot; \<br>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 -o &quot;-c maintenance_work_mem=3D${MaintMem}GB&quot; \<br>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 -o &quot;-c synchronous_commit=3Doff&quot; \<br>=C2=A0=
 =C2=A0 =C2=A0 =C2=A0 -o &quot;-c archive_mode=3Doff&quot; \<br>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 -o &quot;-c full_page_writes=3Doff&quot; \<br>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 -o &quot;-c checkpoint_timeout=3D30min&quot; \<br>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 -o &quot;-c max_wal_size=3D36GB&quot; \<br>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 -o &quot;-c wal_level=3Dminimal&quot; \<br>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 -o &quot;-c max_wal_senders=3D0&quot; \<br>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 -o &quot;-c wal_buffers=3D64MB&quot; \<br>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 -o &quot;-c autovacuum=3Doff&quot;</font><span style=3D"font-family:=
monospace">=C2=A0</span><span style=3D"font-family:monospace">|| exit $?</s=
pan></div><div><font face=3D"monospace">pg_restore --verbose --jobs=3DX ...=
 &amp;&gt; pg_restore.log || exit $?</font></div><div><font face=3D"monospa=
ce">pg_ctl stop -wt9999 &amp;&amp; pg_ctl start -wt9999</font></div><div><b=
r></div><div>These are &quot;bad&quot; settings if you have=C2=A0other data=
bases in your instance and they need to keep running while you restore your=
 DB.=C2=A0 <b>I don&#39;t have that </b><b>problem</b>, so think=C2=A0it=
9;s great.</div><div><br></div></div><span class=3D"gmail_signature_prefix"=
>-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">=
Death to &lt;Redacted&gt;, and butter sauce.<div>Don&#39;t boil me, I&#39;m=
 still alive.<br><div><div>&lt;Redacted&gt; lobster!</div></div></div></div=
></div></div>

--0000000000002b4a86064977d1e7--