Request a Demo
Home   >   mailing lists

Re: Restoration process generates 1.2 TB of WAL files. - Mailing list pgsql-admin

From Ron Johnson
Subject Re: Restoration process generates 1.2 TB of WAL files.
Date
Msg-id CANzqJaBXCKAKaFxpuW5cjLwJC9_UMB_OtMbGq+o3NfTiTDd3tQ@mail.gmail.com
Whole thread Raw
In response to Restoration process generates 1.2 TB of WAL files.  (Wasim Devale <wasimd60@gmail.com>)
List pgsql-admin
Tree view
On Wed, Jan 28, 2026 at 2:16 PM Wasim Devale <wasimd60@gmail.com> 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:

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!

pgsql-admin by date:

Previous
From: Wasim Devale
Date:
Subject: Restoration process generates 1.2 TB of WAL files.
Next
From: Tim Herren
Date:
Subject: Maximum amount of pg_locks