Hi all,
We are currently running Zabbix 5.0 with PostgreSQL 12, and history and trend data partitioning.
History and trend data housekeeping has been disabled in Zabbix.
In the PostgreSQL logs, we get the following error:
2023-12-06 09:12:47 CET [3509536-5] zabbix@postgres STATEMENT: select current_setting('zbx_tmp.wal_json_res');
2023-12-06 09:17:47 CET [3516312-1] zabbix@postgres ERROR: permission denied for function pg_ls_waldir
2023-12-06 09:17:47 CET [3516312-2] zabbix@postgres CONTEXT: SQL statement "SELECT row_to_json(T) FROM (
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(),'0/00000000') AS WRITE,
count(*) FROM pg_ls_waldir() AS COUNT
) T"
PL/pgSQL function inline_code_block line 10 at SQL statement
2023-12-06 09:17:47 CET [3516312-3] zabbix@postgres STATEMENT: DO LANGUAGE plpgsql $$
DECLARE
ver integer;
res text := '{"write":0,"count":0}';
BEGIN
SELECT current_setting('server_version_num') INTO ver;
IF (SELECT NOT pg_is_in_recovery()) THEN
IF (ver >= 100000) THEN
SELECT row_to_json(T) INTO res FROM (
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(),'0/00000000') AS WRITE,
count(*) FROM pg_ls_waldir() AS COUNT
) T;
ELSE
SELECT row_to_json(T) INTO res FROM (
SELECT pg_xlog_location_diff(pg_current_xlog_location(),' 0/00000000') AS WRITE,
count(*) FROM pg_ls_dir('pg_xlog') AS COUNT
) T;
END IF;
END IF;
perform set_config('zbx_tmp.wal_json_res', res, false);
END $$;
2023-12-06 09:17:47 CET [3516312-4] zabbix@postgres ERROR: unrecognized configuration parameter "zbx_tmp.wal_json_res"
2023-12-06 09:17:47 CET [3516312-5] zabbix@postgres STATEMENT: select current_setting('zbx_tmp.wal_json_res');
This seems to be related to permissions-problem and missing/unknown configuration parameter.
We found the following article describing how to set correct permission:
Is this a known issue? Should we just perform the step according to procedure in above link to set permission?
It also seems that history and trend data is not deleted correctly from database. We find trend data at least two years old in Zabbix, while 'keep trend data' is set to 365d. Not sure if this might be related.
Please advise.
Best regards,
Arne H.
We are currently running Zabbix 5.0 with PostgreSQL 12, and history and trend data partitioning.
History and trend data housekeeping has been disabled in Zabbix.
In the PostgreSQL logs, we get the following error:
2023-12-06 09:12:47 CET [3509536-5] zabbix@postgres STATEMENT: select current_setting('zbx_tmp.wal_json_res');
2023-12-06 09:17:47 CET [3516312-1] zabbix@postgres ERROR: permission denied for function pg_ls_waldir
2023-12-06 09:17:47 CET [3516312-2] zabbix@postgres CONTEXT: SQL statement "SELECT row_to_json(T) FROM (
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(),'0/00000000') AS WRITE,
count(*) FROM pg_ls_waldir() AS COUNT
) T"
PL/pgSQL function inline_code_block line 10 at SQL statement
2023-12-06 09:17:47 CET [3516312-3] zabbix@postgres STATEMENT: DO LANGUAGE plpgsql $$
DECLARE
ver integer;
res text := '{"write":0,"count":0}';
BEGIN
SELECT current_setting('server_version_num') INTO ver;
IF (SELECT NOT pg_is_in_recovery()) THEN
IF (ver >= 100000) THEN
SELECT row_to_json(T) INTO res FROM (
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(),'0/00000000') AS WRITE,
count(*) FROM pg_ls_waldir() AS COUNT
) T;
ELSE
SELECT row_to_json(T) INTO res FROM (
SELECT pg_xlog_location_diff(pg_current_xlog_location(),' 0/00000000') AS WRITE,
count(*) FROM pg_ls_dir('pg_xlog') AS COUNT
) T;
END IF;
END IF;
perform set_config('zbx_tmp.wal_json_res', res, false);
END $$;
2023-12-06 09:17:47 CET [3516312-4] zabbix@postgres ERROR: unrecognized configuration parameter "zbx_tmp.wal_json_res"
2023-12-06 09:17:47 CET [3516312-5] zabbix@postgres STATEMENT: select current_setting('zbx_tmp.wal_json_res');
This seems to be related to permissions-problem and missing/unknown configuration parameter.
We found the following article describing how to set correct permission:
Is this a known issue? Should we just perform the step according to procedure in above link to set permission?
It also seems that history and trend data is not deleted correctly from database. We find trend data at least two years old in Zabbix, while 'keep trend data' is set to 365d. Not sure if this might be related.
Please advise.
Best regards,
Arne H.