Open hunterhuang8810 opened 3 years ago
I have the same problem. How can you help?
Can anyone help?
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).
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
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:
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
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"
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
docker-compose up --build postgres_exporter
pg_replication_state{}
as result we see:
pg_replication_state{server="serverIp:5433"} | 1
pg_replication_state{server="serverIp:5432"} | 0
Thanks @justficks It worked for me too -- https://github.com/prometheus-community/postgres_exporter/issues/535#issuecomment-1380050238
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