PostgreSQL Checkpoint Discrepancy-CodePudding

There is problem about that I don' t understand in a database. Our configuration is like following:

archive_mode = on
archive_timeout = 900
checkpoint_timeout = 60min
checkpoint_completion_target = 0.9
max_wal_size = 4GB

We do not hit max_wal_size limit. Our average is 60 0.9 = 54 minutes which makes sense.

postgres=# SELECT
total_checkpoints,
seconds_since_start / total_checkpoints / 60 AS minutes_between_checkpoints
FROM
(SELECT
EXTRACT(EPOCH FROM (now() - pg_postmaster_start_time())) AS seconds_since_start,
(checkpoints_timed checkpoints_req) AS total_checkpoints
FROM pg_stat_bgwriter
) AS sub;
-[ RECORD 1 ]--------------- ------------
total_checkpoints           | 240
minutes_between_checkpoints | 54.63359986

Yet, yesterday I checked the latest checkpoit at 13.19:

postgres=# SELECT * FROM pg_control_checkpoint();
-[ RECORD 1 ]-------- -------------------------
checkpoint_lsn       | 862/D67582F0
prior_lsn            | 862/7EBA9A80
redo_lsn             | 862/87008050
redo_wal_file        | 000000030000086200000087
timeline_id          | 3
prev_timeline_id     | 3
full_page_writes     | t
next_xid             | 0:1484144344
next_oid             | 8611735
next_multixact_id    | 151786
next_multi_offset    | 305073
oldest_xid           | 1284151498
oldest_xid_dbid      | 1905285
oldest_active_xid    | 1484144342
oldest_multi_xid     | 1
oldest_multi_dbid    | 1905305
oldest_commit_ts_xid | 0
newest_commit_ts_xid | 0
checkpoint_time      | 2022-09-21 12:19:17 02

So, after latest checkpoint it passed more than 60 minutes, it should have taken another checkpoint. Archive mode is enabled and 15 minutes but it does not take checkpoint. Only possbile explanation is not generating any WAL according to the official document, but we generated lots of WAL, this is very active database(not as much as to fullfil 4 GB WAL). What do I miss?

Thanks!

CodePudding user response：

That seems perfectly fine.

With your settings, PostgreSQL will run a checkpoint every hour and time it to take around 54 minutes. So 90% of the time you have some checkpoint activity, and 10% nothing. Of course this timing is not 100% accurate, so don't worry about a minute up or down.

If you want to observe this behavior in more detail, set log_checkpoints = on. Then you will get a log message whenever a checkpoint starts and whenever it completes. Leave this setting on, this is useful information for debugging database problems.