• This monitoring uses Telegraf postgresql_extensible input plugin + requires special function in PostgreSQL database.
  • Runs on replication master – uses pg_catalog object “pg_stat_replication”.
  • For safety reason use special PostgreSQL user with simple select / execute rights. This user of course will not have direct access to pg_stat_replication – therefore you will have to create additional function.

PostgreSQL function:

CREATE OR REPLACE FUNCTION public.func_stat_replication()
RETURNS SETOF pg_stat_replication AS
' select * from pg_stat_replication; '
LANGUAGE sql VOLATILE SECURITY DEFINER
COST 100
ROWS 1000;

Grant execute right for this function to your telegraf user (presume “postgres_exporter”):

GRANT EXECUTE ON FUNCTION public.func_stat_replication() to postgres_exporter;

Telegraf configuration:

[[inputs.postgresql_extensible]]
address = "host=localhost user=postgres_exporter password=your_password sslmode=disable dbname=your_db"
[[inputs.postgresql_extensible.query]]
sqlquery="select public.hex_to_int(substr(sent_location::text, position('/' in sent_location::text)+1)) as position_sent, public.hex_to_int(substr(write_location::text, position('/' in write_location::text)+1)) as position_write, public.hex_to_int(substr(flush_location::text, position('/' in flush_location::text)+1)) as position_flush, public.hex_to_int(substr(replay_location::text, position('/' in replay_location::text)+1)) as position_replay, client_addr::text as client from public.func_stat_replication();"
withdbname=false
tagvalue="db,client"
measurement="postgresql_replication_clients"