Issue Overview:

As per the official PostgreSQL documentation and also here Pgpedia, the pg_stat_bgwriter table has undergone changes in PostgreSQL version 17, compared to versions 16 and below. As a result, Zabbix’s PostgreSQL plugin may throw errors in the PostgreSQL log files, such as the following:

zbx_monitor@postgres LOCATION:  errorMissingColumn, parse_relation.c:3716
zbx_monitor@postgres STATEMENT:  
	  SELECT row_to_json (T)
	    FROM (
	          SELECT
	              checkpoints_timed
	            , checkpoints_req
	            , checkpoint_write_time
	            , checkpoint_sync_time
	            , buffers_checkpoint
	            , buffers_clean
	            , maxwritten_clean
	            , buffers_backend
	            , buffers_backend_fsync
	            , buffers_alloc
	          FROM pg_catalog.pg_stat_bgwriter
			  ) T ;

Temporary Fix:

To resolve this issue temporarily, you can update the Zabbix plugin. Here’s how:

1. Clone the Zabbix repository:
   Clone the Zabbix repository from the following link:
   https://git.zabbix.com/projects/AP/repos/postgresql/browse
   Make sure to select the version of the plugin that corresponds to the Zabbix agent version you are using.

2. Edit the Plugin’s handler_bgwriter.go:
   In the repository, locate and open the handler_bgwriter.go file.
   Replace the existing query with the following updated query:

query := `
WITH checkpointer AS (
SELECT
	num_requested AS checkpoints_req,
	num_timed AS checkpoints_timed,
	write_time AS checkpoint_write_time,
	sync_time AS checkpoint_sync_time,
	buffers_written AS buffers_checkpoint
FROM pg_catalog.pg_stat_checkpointer
),
bgwriter AS (
SELECT
	buffers_alloc,
	buffers_clean,
	maxwritten_clean
FROM pg_catalog.pg_stat_bgwriter
)
SELECT jsonb_object_agg(key, value) AS row_to_json
FROM (
	SELECT 'checkpoints_req' AS key, checkpoints_req::text AS value FROM checkpointer
	UNION ALL
	SELECT 'checkpoints_timed', checkpoints_timed::text FROM checkpointer
	UNION ALL
	SELECT 'checkpoint_write_time', checkpoint_write_time::text FROM checkpointer
	UNION ALL
	SELECT 'checkpoint_sync_time', checkpoint_sync_time::text FROM checkpointer
	UNION ALL
	SELECT 'buffers_checkpoint', buffers_checkpoint::text FROM checkpointer
	UNION ALL
	SELECT 'buffers_alloc', buffers_alloc::text FROM bgwriter
	UNION ALL
	SELECT 'buffers_clean', buffers_clean::text FROM bgwriter
	UNION ALL
	SELECT 'maxwritten_clean', maxwritten_clean::text FROM bgwriter
) AS combined order by 1 ;`

3. Build the Plugin:
   After making the changes, you’ll need to build the plugin using Go.
   • First, ensure that Go is installed by following the instructions here: Go Installation.
   • Once Go is set up, navigate to the plugin directory and execute the following command to build the plugin:

go build -o zabbix-agent2-plugin-postgresql

4. Use the Newly Built Plugin:
   After the build completes, you can start using the newly compiled plugin for PostgreSQL monitoring in Zabbix.

Last modified on 2025-02-11