[ZBX-19672] PostgreSQL 13 - template zabbix abent 2. Created: 2021 Jul 12  Updated: 2021 Dec 21  Resolved: 2021 Dec 21

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Agent2 plugin (G), Templates (T)
Affects Version/s: 5.4.1, 5.4.2
Fix Version/s: None

Type: Problem report Priority: Minor
Reporter: pavel Assignee: Vyacheslav Khaliev
Resolution: Fixed Votes: 2
Labels: PostgreSQL, TimescaleDB
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: PNG File Cache hit.png     PNG File image-2021-07-13-14-09-44-025.png     PNG File image-2021-07-13-14-10-44-528.png     PNG File image-2021-07-13-14-12-52-601.png     PNG File image-2021-07-13-14-13-18-767.png     PNG File image-2021-07-13-14-13-57-197.png     PNG File image-2021-07-13-14-17-31-316.png     PNG File image-2021-07-13-14-17-52-744.png     PNG File screenshot-1.png    
Issue Links:
Sub-task
depends on ZBX-19897 Bug in template PostgreSQL by agent2 ... Closed

 Description   

When using the template PostgreSQL by Zabbix agent 2.

There is an error in the database(slave+master) log :

ERROR: column "received_lsn" does not exist at character 36
HINT: Perhaps you meant to reference the column "pg_stat_wal_receiver.received_tli".
STATEMENT: SELECT pg_catalog.pg_wal_lsn_diff (received_lsn, pg_last_wal_replay_lsn())
FROM pg_stat_wal_receiver;

 

 Comments   
Comment by pavel [ 2021 Jul 12 ]

I am use PostgreSQL 13 (open source) + timescaledb

Comment by Aigars Kadikis [ 2021 Jul 13 ]

Thank you for reaching out. I was not capable to find any error while using PostgreSQL 13.3 and TimescaleDB 2.3.1, Zabbix agent 2 and stock template which comes along with Zabbix 5.4. Would you mind reimport the most recent 5.4 template. The last modification has been made on 17 Jun 2021.

If you still encounter an error, what is the item name having the error? Perhaps attaching a screenshot of the error message could help.

Comment by pavel [ 2021 Jul 13 ]

Error in replica:

items master:

items replica:

in psql (master):

in psql (replica):

Comment by Aigars Kadikis [ 2021 Jul 13 ]

Yeah, this is a cutting edge situation. Postgres 11 and 12 is having a column 'received_lsn':

[root@c8 ~]# PGPORT=7412 psql
psql (13.3, server 12.2)
Type "help" for help.

postgres=# \d pg_stat_wal_receiver
                      View "pg_catalog.pg_stat_wal_receiver"
        Column         |           Type           | Collation | Nullable | Default
-----------------------+--------------------------+-----------+----------+---------
 pid                   | integer                  |           |          |
 status                | text                     |           |          |
 receive_start_lsn     | pg_lsn                   |           |          |
 receive_start_tli     | integer                  |           |          |
 received_lsn          | pg_lsn                   |           |          |
 received_tli          | integer                  |           |          |
 last_msg_send_time    | timestamp with time zone |           |          |
 last_msg_receipt_time | timestamp with time zone |           |          |
 latest_end_lsn        | pg_lsn                   |           |          |
 latest_end_time       | timestamp with time zone |           |          |
 slot_name             | text                     |           |          |
 sender_host           | text                     |           |          |
 sender_port           | integer                  |           |          |
 conninfo              | text

Postgres 13 is not having column 'received_lsn':

[root@c8 ~]# PGPORT=7413 psql
psql (13.3)
Type "help" for help.

postgres=# \d pg_stat_wal_receiver
                      View "pg_catalog.pg_stat_wal_receiver"
        Column         |           Type           | Collation | Nullable | Default
-----------------------+--------------------------+-----------+----------+---------
 pid                   | integer                  |           |          |
 status                | text                     |           |          |
 receive_start_lsn     | pg_lsn                   |           |          |
 receive_start_tli     | integer                  |           |          |
 written_lsn           | pg_lsn                   |           |          |
 flushed_lsn           | pg_lsn                   |           |          |
 received_tli          | integer                  |           |          |
 last_msg_send_time    | timestamp with time zone |           |          |
 last_msg_receipt_time | timestamp with time zone |           |          |
 latest_end_lsn        | pg_lsn                   |           |          |
 latest_end_time       | timestamp with time zone |           |          |
 slot_name             | text                     |           |          |
 sender_host           | text                     |           |          |
 sender_port           | integer                  |           |          |
 conninfo              | text                     |           |          | 
Comment by Aigars Kadikis [ 2021 Jul 13 ]

PostgreSQL 11 and 12 is having a column 'received_lsn' for the table 'pg_stat_wal_receiver', but on PostgreSQL 13 we do not have it.

According to documentation the template was tested on PG 12. Need a fix for PG 13.

Comment by Aleksey Volodin [ 2021 Jul 13 ]

Also missing parameters in pgsql.cache.hit key:

zabbix_agent2 -t pgsql.cache.hit["tcp://localhost:5432"]
pgsql.cache.hit[tcp://localhost:5432] [m|ZBX_NOTSUPPORTED] [Connection failed: failed to connect to `host=localhost user=postgres database=postgres`: failed SASL auth (FATAL: password authentication failed for user "postgres" (SQLSTATE 28P01)).]

Work fine if add username and password:

zabbix_agent2 -t pgsql.cache.hit["tcp://localhost:5432","zbx_monitor","zabbix123"]
pgsql.cache.hit[tcp://localhost:5432,zbx_monitor,zabbix123][s|99.540000]
Comment by Pavel Kalian [ 2021 Sep 20 ]

The problematic code is https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/src/go/plugins/postgres/handler_replication.go?at=refs%2Fheads%2Frelease%2F5.4#97-98

It seems to me the correct version of the query compatible with Postgres 10 and newer should probably be

SELECT pg_catalog.pg_wal_lsn_diff (pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn());

It maintains the same level of compatibility as the current implementation as the already used pg_last_wal_replay_lsn() function was also renamed in Postgres 10, but could be extended all the way to 9.0 by using pg_last_xlog_receive_location() / pg_last_xlog_replay_location() in the version of the query for 9.x

References:

Comment by pavel [ 2021 Oct 21 ]

Maybe should use the view pg_stat_replication with pg_wal_lsn_diff

scripts:

SELECT
client_addr AS client, usename AS user, application_name AS name,
state, sync_state AS mode,
(pg_wal_lsn_diff(pg_current_wal_lsn(),sent_lsn) / 1024)::bigint as pending ,
(pg_wal_lsn_diff(sent_lsn,write_lsn) / 1024)::bigint as write,
(pg_wal_lsn_diff(write_lsn,flush_lsn) / 1024)::bigint as flush,
(pg_wal_lsn_diff(flush_lsn,replay_lsn) / 1024)::bigint as replay,
(pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn))::bigint / 1024 as total_lag
FROM pg_stat_replication;

https://pgpedia.info/p/pg_wal_lsn_diff.html
https://pgpedia.info/p/pg_current_wal_lsn.html
https://pgpedia.info/p/pg_stat_replication.html

Comment by Vyacheslav Khaliev [ 2021 Dec 02 ]

Resolved in ZBX-19897

Generated at Sun May 18 06:33:22 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.