Question about different behaviour in pg_wal when archive_mode is set on or off

From: Arpad Kiss A <arpad(dot)a(dot)kiss(at)est(dot)tech>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Question about different behaviour in pg_wal when archive_mode is set on or off
Date: 2025-06-26 11:59:13
Message-ID: AS8P189MB148022F6C262DC2C83CDFE97A97AA@AS8P189MB1480.EURP189.PROD.OUTLOOK.COM
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I'm trying to understand the relation between 'archive_mode', pg_wal directory, 'max_wal_size' and failover.

I'm running the kubernetes example from patroni (patroni 4.0.6)
When 'archive_mode: off', and I issue a couple of 'patronictl failover' commands I observe the following files pg_wal:
```
postgres(at)patronidemo-0:~/pgdata/pgroot/data/pg_wal$ ls
000000080000000000000017 00000008.history       00000009.history     0000000A.history        0000000B.history   0000000C.history
000000080000000000000018 000000090000000000000018 0000000A0000000000000018 0000000B0000000000000018      0000000C0000000000000018 archive_status
```
Our problem with this is that If I do a lot of failovers, lets say a 100, then there will be a 100 files ( from 000000080000000000000018, to 0000001C0000000000000018)
and during checkpoint it doesn't decrease, and can go way over max_wal_size.

Whereas if I set 'archive_mode: on', and 'archive_command: /bin/true'
```
postgres(at)patronidemo-0:~/pgdata/pgroot/data/pg_wal$ ls
0000000E000000000000001F       0000000E.history       0000000F.history           00000010.history        00000011.history
0000000E0000000000000020       0000000F0000000000000021 000000100000000000000022   000000110000000000000023      archive_status
0000000E0000000000000021.partial 0000000F0000000000000022 000000100000000000000023.partial 000000110000000000000024
```
In this latter case after a timeline id is incremented postgres creates a new file with an incremented seqno, and in this case the files get rotated out normally they don't go over max_wal_size

Can someone explain why the difference in behavior? Does postgres only use the seqno only to differentiate the wal files here? Why does postgres not rotate out files in the former case?

Many thanks,
Arpi

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Peter J. Holzer 2025-06-26 12:27:41 Re: password rules
Previous Message raphi 2025-06-25 15:55:12 Re: password rules