Is there a limit regarding the number of files that are stored in the pg_wal directory?

Question

I am trying to find an explanation regarding the actual number of files that are being stored in my pg_wal directory.

I am using postgres v13 on a large DB with a lots of updates, inserts and deletes so I am expecting to have quite a lot of files in the pg_wal directory.

Based on what I have understood so far the number of files on the pg_wal directory depends on the checkpoint_timeout and the max_wal_size value. So which ever one hits first then the pg_wal directory is being rotated

I am experiencing though a weird behavior in my environment where I see constantly 7 pg_wal segments files. That means that I have 7*16MB = 112MB of files constantly.

Then I was looking at the files' timestamp to find out the oldest and the earliest and the below example shows that their timestamp is more than 5 mins. Oldest is 19:54 and newest is 20:05

-rw-------  1 postgres postgres 16777216 Nov 10 20:04 000000010000E76A00000097
-rw-------  1 postgres postgres 16777216 Nov 10 20:05 000000010000E76A00000098
-rw-------  1 postgres postgres 16777216 Nov 10 20:10 000000010000E76A00000099
-rw-------  1 postgres postgres 16777216 Nov 10 19:54 000000010000E76A0000009A
-rw-------  1 postgres postgres 16777216 Nov 10 19:56 000000010000E76A0000009B
-rw-------  1 postgres postgres 16777216 Nov 10 20:02 000000010000E76A0000009C
-rw-------  1 postgres postgres 16777216 Nov 10 20:03 000000010000E76A0000009D
-rw-------  1 postgres postgres 16777216 Nov 10 20:01 000000010000E76A0000009E

Trying to figure out why I am constantly having 7 pg_wal segment files. My conf values are

checkpoint_timeout = 5min
max_wal_size = 2GB

I would count out that on that specific time there were 112MB of data that were changed because there are multiple scripts running on various moments and the number of files has never changed to something more or less.

I am investigating the way to create a replica and stubbed upon these values and trying to understand more things.

Tried to identify if the issue existed only on specific times of the day but that wasn't the case. Actually wherever I checked the pg_wal directory I would see 7 files.

The things that varied was the time between the oldest and the newest file. Sometimes it could be 10 minutes and other times it could be 30 or more (but this would depend on the actual data that are being written or changed in the DB)

Answer 1

You don't have to worry about the number of WAL segments in pg_wal if you plan to build a standby server. All you have to do is to configure PostgreSQL so that it retains enough WAL for the standby server. There are three ways to do that:

1. Set wal_keep_size on the primary, so that it always retains a certain amount of old WAL.

2. Set archive_mode = on and configure archive_command so that PostgreSQL creates a WAL archive. Then set restore_command on the standby so that it can recover from the archive. You can use archive_cleanup_command on the standby to delete archived WAL segments that are no longer needed.

3. Use a replication slot that makes the primary retain just the right amount of WAL.

I recommend options 2 or 3.