column "buffers_checkpoint" does not exist at postgres_bgwriter

[jmlich]

The PostgreSQL log shows the following error message:

2025-01-14T06:30:09+01:00 worker postgres [145586] local0 warning: [8] < postgres [unknown] [local] > ERROR:  column "buffers_checkpoint" does not exist at character 8
< postgres [unknown] [local] > STATEMENT:  SELECT buffers_checkpoint,buffers_clean,buffers_backend,buffers_alloc FROM pg_stat_bgwriter

It seems that the columns buffers_checkpoint and buffers_backend were removed in PostgreSQL 17.

testdb=# \d pg_stat_bgwriter 
                      View "pg_catalog.pg_stat_bgwriter"
      Column      |           Type           | Collation | Nullable | Default 
------------------+--------------------------+-----------+----------+---------
 buffers_clean    | bigint                   |           |          | 
 maxwritten_clean | bigint                   |           |          | 
 buffers_alloc    | bigint                   |           |          | 
 stats_reset      | timestamp with time zone |           |          | 

testdb=# SELECT version();
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 17.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-2), 64-bit
(1 row)

It appears that a condition should be added in:

munin/plugins/node.d/postgres_bgwriter

Lines 73 to 74 in 480273f

	basequery =>
	"SELECT buffers_checkpoint,buffers_clean,buffers_backend,buffers_alloc FROM pg_stat_bgwriter",

A possible fix might look like this:

    basequery => [
        "SELECT buffers_checkpoint,buffers_clean,buffers_backend,buffers_alloc FROM pg_stat_bgwriter",
        [ 17,
            "SELECT buffers_clean,buffers_alloc FROM pg_stat_bgwriter",
        ],
    ],

I haven't checked if there is another column that contains the same information.

[tofurky]

Just ran into the same thing after updating from postgres 16 -> 17 today. Started reading https://pganalyze.com/blog/5mins-postgres-17-pg-stat-checkpointer-bgwriter to see if the fields this plugin reports still makes sense.

[tofurky]

One other thing I'll mention: this comes back to these three different kinds of ways of how dirtied pages actually get to disk. You'll notice that there's actually two columns that are no longer in either pg_stat_bgwriter or pg_stat_checkpointer. Those are those buffers_backend and buffers_backend_fsync columns. Those two are now removed from pg_stat_bgwriter, and instead you can find that information in pg_stat_io.

Sounds like perhaps a JOIN could be used?