PostgreSQL 12 High Availability Cookbook - Third Edition

How to do it...

Assume that we have a PostgreSQL server named pgha1. Follow these steps on the server to enable more enhanced metrics:

1. Create or modify a file named pgha1.conf in /etc/telegraf/telegraf.d.
2. Start with the header section for the extensible metrics:

[[inputs.postgresql_extensible]] 
  address = "host=pgha1 user=perf_mon dbname=postgres" 
  outputaddress = "pgha1" 
  max_lifetime = "0s" 
  databases = ["pgbench"] 

3. Continue by defining a query to monitor replication slot lag:

[[inputs.postgresql_extensible.query]] sqlquery=""" SELECT slot_name, pg_wal_lsn_diff( pg_current_wal_insert_lsn(), restart_lsn )::BIGINT as restart_lsn_lag, pg_wal_lsn_diff( pg_current_wal_insert_lsn(), confirmed_flush_lsn )::BIGINT as confirmed_flush_lag FROM pg_replication_slots ...