Collector postgres/replication does not provide replication metrics while running on PostgreSQL 9.6.8 #86
Description
Describe the bug
Collector postgres/replication does not provide replication metrics while running on PostgreSQL 9.6.8
Steps to reproduce
Error appears in PostgreSQL 9.6.8
Expected behavior
Expected replication metrics
pgSCV startup options
Default config
Errors and Logs
sudo journalctl -fu pgscv
pgscv[6917]: {"level":"error","service":"pgscv","time":"2025-01-27T16:10:32+03:00","message":"postgres/replication collector failed; query 'SELECT pid, coalesce(host(client_addr), '127.0.0.1') AS client_addr, coalesce(client_port, '0') AS client_port, usename AS user, application_name, state, pg_current_xlog_location() - sent_location AS pending_lag_bytes, sent_location - write_location AS write_lag_bytes, write_location - flush_location AS flush_lag_bytes, flush_location - replay_location AS replay_lag_bytes, pg_current_xlog_location() - replay_location AS total_lag_bytes, NULL AS write_lag_seconds, NULL AS flush_lag_seconds, NULL AS replay_lag_seconds, NULL AS total_lag_seconds FROM pg_stat_replication', unsupported data type OID: 705"}
Environment (please complete the following information):
- Used OS (or Containers):
CentOS Linux release 7.4.1708 - pgSCV version
pgscv v0.9.7 6293eef905bc8bc01837d15fee666c5959697bbe-HEAD - pgSCV file info
-rwxr-xr-x 1 root root 21591124 Jan 15 11:49 /usr/local/bin/pgscv - PostgreSQL version
PostgreSQL 9.6.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit - Do PostgreSQL and pgSCV are running on the same host? Yes
Additional context
It needs typecasting NULL to NULL::numeric in file /internal/collector/postgres_replication.go:
const (
// Query for Postgres version 9.6 and older.
postgresReplicationQuery96 = "SELECT pid, coalesce(host(client_addr), '127.0.0.1') AS client_addr, usename AS user, application_name, state, " +
"pg_current_xlog_location() - sent_location AS pending_lag_bytes, " +
"sent_location - write_location AS write_lag_bytes, " +
"write_location - flush_location AS flush_lag_bytes, " +
"flush_location - replay_location AS replay_lag_bytes, " +
"pg_current_xlog_location() - replay_location AS total_lag_bytes, " +
"NULL::numeric AS write_lag_seconds, NULL::numeric AS flush_lag_seconds, NULL::numeric AS replay_lag_seconds, NULL::numeric AS total_lag_seconds " +
"FROM pg_stat_replication"
these changes fix the problem.