Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vlMsG-00ASJl-0Z for pgsql-admin@arkaria.postgresql.org; Thu, 29 Jan 2026 07:55:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vlMsD-007BaN-33 for pgsql-admin@arkaria.postgresql.org; Thu, 29 Jan 2026 07:55:30 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vlMsD-007Ba5-1S for pgsql-admin@lists.postgresql.org; Thu, 29 Jan 2026 07:55:29 +0000 Received: from mail-ej1-x62e.google.com ([2a00:1450:4864:20::62e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vlMsA-002skg-32 for pgsql-admin@postgresql.org; Thu, 29 Jan 2026 07:55:28 +0000 Received: by mail-ej1-x62e.google.com with SMTP id a640c23a62f3a-b8871718b00so120499066b.3 for ; Wed, 28 Jan 2026 23:55:27 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1769673326; cv=none; d=google.com; s=arc-20240605; b=IxOEdYgYJEKXjUBLGETbXzOsdd1DUSxoBp2BvFrOxXUuvV1QZLXCdEA7I5tNW5uGPb UX/FCBLw+Sqn0KZNliebyG/eYKRm9z3UJ3OLPRWTyQSBzCYKGVn0COzuybDXwutQtzL/ HC/r23ZUIdeBfEZzZ2flM0cfqh8jMs+ZNddqZV2zTwLXLfLm37giqRSKshT5qLWb+70O yzinZYnYVLARxA4jCt66IQanVj+8YjogYnGws5bOUnCJUK/4AEgqqaxAOS5zLRGGXqdr XPBFFmrPoE5U7FjnaY1qogpgvVFXf+78e/TMTDNr0AHEFskS2Lmwr5KjbnG1eptr1Yqp gSgQ== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=QVNG+CTGmfIuAjqtUhosViEdLXUG+g05US+VpNIedGk=; fh=Cp/8gkOUDRKkHDs1biMT6lS7CbQTj2ckMKAI0IIAPPE=; b=EzR9FraLhvZDemFKPSzS8QvWgfy/zfCR4ZQL3tVWGKfRHk7lhyU0VPg1UKvmb1zOvq agFLo0WvZl0ERWlCC2B9/p9LUM4DWmXI9qA0d2uM+5bbXadJHdo0Bbr30K3OiBCgXp/i nzDxSOl9VrMCLb9x2SALkz6tJGPVxW+QLtTEc877cWC5RjqMzqeWj2YpZS0P8IJ3Cwy1 FF4OiWUn436doFIoy3qbPx13WX1zjmM5mSqHz2yiAJeDyem3mCRv36auzE7CFIln355v BtG3/eO8p5Q0gwaTI1oSZ1QoF3eSK64GOxnkZv2cVbj+JEqzax64o4lDwszaHW6AddH3 x4mw==; darn=postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1769673326; x=1770278126; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=QVNG+CTGmfIuAjqtUhosViEdLXUG+g05US+VpNIedGk=; b=CIAEevCDVpYTDi6UkUReKKL/jf2q00psCj8MjJGCvztrePCZsOxJGhTZZH6LMb0eUX 8UiPlj6YCiSWFP9siQaXmKI0JbPrHzckCue6MJeW0sCObLTAtxnPpkE4X39PTrijzDXp ETMSUOyp+xXYKjO9sPad0I8jwhS8hKCdi9xqto18NTk/cus9LH65XpzRDd42BaDBGw8k KkVfJUdoPxFcTQN7OUvEz7NcXA1yRkLNyQ40q0e6GtI7evhbGDkhufakjCyXSl36L/EA t613WysmCtk/sZWjkP58evTbtdGl3t7iok+p+RCr53mFjotsQwUGU+j5XKde5VrUcM3a MgPg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1769673326; x=1770278126; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=QVNG+CTGmfIuAjqtUhosViEdLXUG+g05US+VpNIedGk=; b=SlbkiitJB7d953Y8VyLwUatdf42iyInLN35tNWQ6bAL/IJpi6iYimZ933EqlCNxaC6 4rrHxwFNDO1YIGdOP2oY33G9Ahl5qSbJp0ebANK2Lix/abKvw24w8uA5Bn2anVifkvLt LvpOjBHLeG/9xaU9qXGYfwLVp4CScGAb/aU7VukErJON7eFw8/sbezgXy7UD+xxMw1H7 DJk7mwkyG/DMtz1+4CAL1GwPy3IJIjeyC43ebrmQ7LMxKBlDHZOOCudqGHHBx+B6yINq pc6fHs9SwAZcu5x/xFVwv3ZpeTyvJ8HqXj3hn7AR1mxu66Pjlm2DsgyXU651drsMdYYD Jmiw== X-Gm-Message-State: AOJu0YyUKlkGBmXKccZKZ9JmZRtNyo1gPmJnfn+tjWMD9Q6nAkDJIjym 014nEcwY0X/fIODVU8oelv4db1AuKqhUEnocJxZDEcvNVzQPMV3KLfgKNJsoDQjsZ7GZNYJ/pEc TZyAUwYE9MZSEWw0UHszmHNr2feXmTy8= X-Gm-Gg: AZuq6aLmJxIfhF/QDYY29pOLCl9aChEr5S5xVsK6ndQrCJgHLgw4SZ/3OmAzcOFsdWh iezhMNMP+5eRaqN2QyTOYqqbZc3cGZJ3mZtFyPG7p4XbK8y5P5QyjQlAxbS0t3xLr97+VUYe40p WWQ+zVjA9EP3L2ABHA1ygiFruFWvJWgEs2/yKfwGo2MRNCyyEo3h+JpA3WiWqx68fIAggiK22ZX 7SAsipG8RrycjKVMogtO0GPqatnwvGVc4ZVGjEo0oqmxZP6xiD+cJ7T8VQzF619cUXkwrzcsQ== X-Received: by 2002:a17:907:1c27:b0:b88:6542:86a0 with SMTP id a640c23a62f3a-b8dab39a855mr608627966b.54.1769673325818; Wed, 28 Jan 2026 23:55:25 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Wasim Devale Date: Thu, 29 Jan 2026 13:25:14 +0530 X-Gm-Features: AZwV_QgDpSYzG7Pk3o-eHNi269ZOcD--kENXPZkjnRvIqtDV1hI3LPN6-OlvTjc Message-ID: Subject: Re: Restoration process generates 1.2 TB of WAL files. To: Ron Johnson Cc: pgsql-admin , Pgsql-admin Content-Type: multipart/alternative; boundary="000000000000f4388f0649822dff" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000f4388f0649822dff Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thank you for the information. Wasim. On Thu, 29 Jan, 2026, 1:03=E2=80=AFam Ron Johnson, wrote: > On Wed, Jan 28, 2026 at 2:16=E2=80=AFPM Wasim Devale = 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 director= y. >> 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 K= EY > statements, and you'll scratch your head looking at the pg_restore log fi= le > 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. > > -- > Death to , and butter sauce. > Don't boil me, I'm still alive. > lobster! > --000000000000f4388f0649822dff Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thank you for the information.

Wasim.

On Thu, 29 Jan, 2026, = 1:03=E2=80=AFam Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
On Wed, Jan 28, 2= 026 at 2:16=E2=80=AFPM Wasim Devale <wasimd60@gmail.com> wrote:
Hi All

I am taking directory dump from postgresql 12 and restoring it on postgres= ql 17 this process create 1.2TB of wals files in pg_wal directory. Can we r= educe this WAL files generation via any configuration changes?
<= /blockquote>

Sure.=C2=A0 This is what I used when restor= ing 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.)

Setting $SharedBuffs and (especia= lly) $MaintMem too high will cause the Linux=C2=A0oom-killer to kill one or= more of the CREATE INDEX or ADD PRIMARY KEY statements, and you'll scr= atch your head looking at the pg_restore log file wondering what happened.<= /div>

I based it on:

pg_ctl = restart -wt$TimeOut -mfast \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c hba= _file=3D$PGDATA/pg_hba_maintmode.conf" \
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 -o "-c fsync=3Doff" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "= ;-c log_statement=3Dnone" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c= log_temp_files=3D100kB" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c = log_checkpoints=3Don" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o "-c log= _min_duration_statement=3D120000" \
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -o = "-c shared_buffers=3D${SharedBuffs}GB" \
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 -o "-c maintenance_work_mem=3D${MaintMem}GB" \
=C2=A0 = =C2=A0 =C2=A0 =C2=A0 -o "-c synchronous_commit=3Doff" \
=C2=A0= =C2=A0 =C2=A0 =C2=A0 -o "-c archive_mode=3Doff" \
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 -o "-c full_page_writes=3Doff" \
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 -o "-c checkpoint_timeout=3D30min" \
=C2=A0 = =C2=A0 =C2=A0 =C2=A0 -o "-c max_wal_size=3D36GB" \
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 -o "-c wal_level=3Dminimal" \
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 -o "-c max_wal_senders=3D0" \
=C2=A0 =C2=A0 =C2= =A0 =C2=A0 -o "-c wal_buffers=3D64MB" \
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 -o "-c autovacuum=3Doff"=C2=A0|| 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=C2=A0other data= bases in your instance and they need to keep running while you restore your= DB.=C2=A0 I don't have that problem, so think=C2=A0it= 9;s great.

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