postgres replication state metrics

[hunterhuang8810]

Hi expert,

to monitor mysql replication stopped or not, mysql_exporter has metrics IO_THREADS_STATUS and SQL_THREAD_STATUS .

is there same metris in postgresql to check is the pg replication work well? thanks

[CarTerr]

I have the same problem. How can you help?

[CarTerr]

Can anyone help?

[sysadmind]

I'm not sure that I understand what is being asked.

Is the question "How do I monitor postgresql replication"? If so, I'm not sure that this project is the right place to find that information. This project is designed to export metrics from postgresql servers. Any "monitoring" beyond that, would be handled by another tool (like prometheus, alertmanager, etc) This article seems to lay out some information to look at: https://pgdash.io/blog/monitoring-postgres-replication.html

I'm not versed in postgresql replication so I can not speak to which stats may be useful to be exported by this project. There does seem to be a pg_stat table(pg_stat_replication) that may have useful metrics. Most of the columns in that table are discarded in this exporter however. I'm not sure why that was chosen to begin with, but if there are specific useful columns in that table, it would be useful to ask for those columns to be added to the exporter (or submit a PR to add them).

[CarTerr]

current metrics do not provide replication status need to see the metric: pg_replication_status -- streaming or not streaming (1/0)

in prometheus i see only:

pg_stat_replication_pg_current_wal_lsn_bytes{application_name="my_application", container="metrics", endpoint="http-metrics", instance="10.233.125.15:9187", job="blacklist-prod-postgresql-metrics", namespace="itf-services-prod", pod="blacklist-prod-postgresql-master-0", server="127.0.0.1:5432", service="blacklist-prod-postgresql-metrics", slot_name="998373"}
pg_stat_replication_pg_wal_lsn_diff{application_name="my_application", container="metrics", endpoint="http-metrics", instance="10.233.125.15:9187", job="blacklist-prod-postgresql-metrics", namespace="itf-services-prod", pod="blacklist-prod-postgresql-master-0", server="127.0.0.1:5432", service="blacklist-prod-postgresql-metrics", slot_name="998373"}

and not see status replication

[justficks]

I ran into the same problem and solved it with PG_EXPORTER_EXTEND_QUERY_PATH and a slightly hard to understand SQL query.

My steps:

1. Initially my container was described like this:

postgres-exporter:
    image: quay.io/prometheuscommunity/postgres-exporter:latest
    environment:
      DATA_SOURCE_NAME: "postgresql://user:password@serverIp:5432/postgres?sslmode=disable"
    ports:
      - 9187:9187
    restart: always

2. Then we need to add our own metric as from the example in the documentation, and for this we create a file custom-queries.yml:

pg_replication:
  query: "with q as(SELECT CASE WHEN state = 'streaming' THEN 1 ELSE 0 END as state FROM pg_stat_replication) select state from q union all select '0' where not exists(select 1 from q)"
  master: true
  metrics:
    - state:
        usage: "GAUGE"
        description: "Replication status. 1 - streaming, 0 - not streaming"

3. Next we update the postgres_exporter container with instructions to collect our metric:

postgres-exporter:
  image: quay.io/prometheuscommunity/postgres-exporter:latest
  volumes:
    - ./postgres_exporter/custom-queries.yml:/custom-queries.yml
  environment:
    DATA_SOURCE_NAME: "postgresql://user:password@serverIp:5432/postgres?sslmode=disable"
    PG_EXPORTER_EXTEND_QUERY_PATH: "/custom-queries.yml"
  ports:
    - 9187:9187
  restart: always

4. Rebuild container:

docker-compose up --build postgres_exporter

5. Go to prometheus UI and run next PromQL query:

pg_replication_state{}

as result we see:

pg_replication_state{server="serverIp:5433"} | 1
pg_replication_state{server="serverIp:5432"} | 0

[CarTerr]

Thanks @justficks It worked for me too -- https://github.com/prometheus-community/postgres_exporter/issues/535#issuecomment-1380050238