WAL control functions cannot be executed during recovery

[erkki]

Hot standby servers cannot run WAL control functions so these should probably be skipped

2017-02-18 04:26:12 UTC ERROR: recovery is in progress
2017-02-18 04:26:12 UTC HINT: WAL control functions cannot be executed during recovery.
2017-02-18 04:26:12 UTC STATEMENT:
SELECT *,
pg_current_xlog_location(),
pg_xlog_location_diff(pg_current_xlog_location(), replay_location)::float
FROM pg_stat_replication

[mattbostock]

As a hacky workaround on PosgreSQL 9.2.0 and above, you can add this to your queries.yaml for standby servers:

pg_stat_replication:
  query: SELECT *, 12345.123 AS pg_current_xlog_location, 12345.123 AS pg_xlog_location_diff FROM pg_stat_replication

It'll return a garbage value 12345.123 for pg_current_xlog_location and pg_xlog_location_diff but prevent the above error.

[dan-aksenov]

ERROR:  recovery is in progress
HINT:  WAL control functions cannot be executed during recovery.
STATEMENT:
                                SELECT slot_name, database, active, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
                                FROM pg_replication_slots

I have this error again in latest postgres_exporter 0.0.1-rev (built with go1.15.8)

[sirantonyru]

As I created replication slot on replica for set up new patroni standby cluster I got an error:

2024-12-09 21:23:34.874 UTC [1309514] postgres_exporter@postgres ERROR:  recovery is in progress
2024-12-09 21:23:34.874 UTC [1309514] postgres_exporter@postgres HINT:  WAL control functions cannot be executed during recovery.
2024-12-09 21:23:34.874 UTC [1309514] postgres_exporter@postgres STATEMENT:
                                SELECT slot_name, database, active, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
                                FROM pg_replication_slots

$ postgres_exporter --version

postgres_exporter, version 0.15.0 (branch: HEAD, revision: 68c176b8833b7580bf847cecf60f8e0ad5923f9a)
  build user:       root@88f74f2c2888
  build date:       20231027-14:38:56
  go version:       go1.21.3
  platform:         linux/amd64
  tags:             unknown