pg_wal lifecycle

From: Luca Ferrari <fluca1978(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: pg_wal lifecycle
Date: 2021-07-13 07:07:09
Message-ID: CAKoxK+4L069ZjNAtJ9Vwio6wRVSw0Cpp50+SJ=QdRskogGcrfA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,
I'd like to see if I get it right about pg_wal: it grows up to pretty
much max_wal_size, at which point a checkpoint is triggered.
If the server triggers a timed checkpoint before the pg_wal is at
max_wal_size, the system recycles the wals thus keeping the pg_wal
size lower than max_wal_size.
Is this correct? In particular, what is in simple words, the
discrimination between recycling a segment and creating a new one?

% sudo du -hs $PGDATA/pg_wal/
273M /postgres/13/data/pg_wal/
% psql -U postgres -c "show max_wal_size;" -c "show checkpoint_timeout;"
max_wal_size
--------------
1GB
(1 riga)

checkpoint_timeout
--------------------
5min
(1 riga)

and from the last log checkpoint entry:

2021-07-13 02:48:10.492 EDT [2424] LOG: checkpoint starting: time
2021-07-13 02:50:44.515 EDT [2424] LOG: checkpoint complete: wrote
9170 buffers (28.0%); 0 WAL file(s) added, 0 removed, 13 recycled;
write=148.878 s, sync=1.889 s, total=154.023 s; sync files=42,
longest=0.109 s, average=0.045 s; distance=217376 kB, estimate=217376
kB

Thanks,
Luca

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Peter Eisentraut 2021-07-13 13:37:04 Re: libicu global support
Previous Message Tom Lane 2021-07-12 21:21:07 Re: How to debug a connection that's "active" but hanging?