The following bug has been logged on the website:
Bug reference: 16757
Logged by: Kris M.
Email address: mesiu84@yahoo.com
PostgreSQL version: 13.1
Operating system: Ubuntu 18.04
Description:
We switched recently from version 11 to latest 13.1 and are still using the
same postgresql.conf file. We noticed that our wal files are no longer
created every 15 minutes like it was in version 11.
We have following settings:
archive_timeout = 900
checkpoint_timeout = 5min (default)
but wal files are created right after DB startup, and then at random time
(longer then 15 min).
We checked also following configuration:
archive_timeout = 900
checkpoint_timeout = 60
checkpoint_warning = 30
log_statement = all
log_checkpoints = on
After that change we see that wal files are created every 15 minutes, but in
logs you can see that checkpoints are being executed every 5 minutes !!!
(not 60sec as in postgresql.conf)
Dec 1 11:30:43 postgresql-host postgres[571]: [11-1] %LOG: checkpoint
starting: time
Dec 1 11:35:43 postgresql-host postgres[571]: [13-1] %LOG: checkpoint
starting: time
Dec 1 11:40:43 postgresql-host postgres[571]: [15-1] %LOG: checkpoint
starting: time
Dec 1 11:45:44 postgresql-host postgres[571]: [17-1] %LOG: checkpoint
starting: time
Dec 1 11:50:44 postgresql-host postgres[571]: [19-1] %LOG: checkpoint
starting: time
Dec 1 11:55:44 postgresql-host postgres[571]: [21-1] %LOG: checkpoint
starting: time
Dec 1 12:00:44 postgresql-host postgres[571]: [23-1] %LOG: checkpoint
starting: time
Dec 1 12:05:44 postgresql-host postgres[571]: [25-1] %LOG: checkpoint
starting: time
Dec 1 12:10:44 postgresql-host postgres[571]: [27-1] %LOG: checkpoint
starting: time
db=# show checkpoint_timeout;
checkpoint_timeout
--------------------
1min
(1 row)
db=# show archive_timeout;
archive_timeout
-----------------
15min
(1 row)
There are then 2 problems:
1. archive_timeout doesn't force wal file creation when set to 15min and
checkpoint_timeout set to 5 min
2. when setting checkpoint_timeout to 1min it's still being executed every 5
min