public inbox for [email protected]help / color / mirror / Atom feed
Restoration process generates 1.2 TB of WAL files. 3+ messages / 2 participants [nested] [flat]
* Restoration process generates 1.2 TB of WAL files. @ 2026-01-28 19:15 Wasim Devale <[email protected]> 2026-01-28 19:33 ` Re: Restoration process generates 1.2 TB of WAL files. Ron Johnson <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Wasim Devale @ 2026-01-28 19:15 UTC (permalink / raw) To: pgsql-admin; Pgsql-admin <[email protected]> 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? Thanks, Wasim ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Restoration process generates 1.2 TB of WAL files. 2026-01-28 19:15 Restoration process generates 1.2 TB of WAL files. Wasim Devale <[email protected]> @ 2026-01-28 19:33 ` Ron Johnson <[email protected]> 2026-01-29 07:55 ` Re: Restoration process generates 1.2 TB of WAL files. Wasim Devale <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Ron Johnson @ 2026-01-28 19:33 UTC (permalink / raw) To: Wasim Devale <[email protected]>; +Cc: pgsql-admin; Pgsql-admin <[email protected]> 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! ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Restoration process generates 1.2 TB of WAL files. 2026-01-28 19:15 Restoration process generates 1.2 TB of WAL files. Wasim Devale <[email protected]> 2026-01-28 19:33 ` Re: Restoration process generates 1.2 TB of WAL files. Ron Johnson <[email protected]> @ 2026-01-29 07:55 ` Wasim Devale <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Wasim Devale @ 2026-01-29 07:55 UTC (permalink / raw) To: Ron Johnson <[email protected]>; +Cc: pgsql-admin; Pgsql-admin <[email protected]> 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! > ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-01-29 07:55 UTC | newest] Thread overview: 3+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-01-28 19:15 Restoration process generates 1.2 TB of WAL files. Wasim Devale <[email protected]> 2026-01-28 19:33 ` Ron Johnson <[email protected]> 2026-01-29 07:55 ` Wasim Devale <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox