lesovsky/pgscv

Troubles with logical replication

Opened this issue · 6 comments

dan-aksenov commented

Describe the bug
We are using PostgreSQL logical replication (with debezium). And there are multiple logical replication processes per cluster.
This is what pg_stat_replication shows:

database=# select pid,usename,application_name,client_addr,state from pg_stat_replication ;
  pid  |       usename       |    application_name    | client_addr |   state
-------+---------------------+------------------------+-------------+-----------
 11090 | replication         | pg_slave_server       | 10.75.3.202 | streaming
 30410 | debezium_replicator | PostgreSQL JDBC Driver | 10.75.3.105 | streaming
 30411 | debezium_replicator | PostgreSQL JDBC Driver | 10.75.3.105 | streaming

pgscv does not work in such setup.
Errors looks like this:

curl -s localhost:9890/metrics
An error has occurred while serving metrics:

6 error(s) occurred:
* collected metric "postgres_replication_lag_bytes" { label:<name:"application_name" value:"PostgreSQL JDBC Driver" > label:<name:"client_addr" value:"10.75.3.105/32" > label:<name:"lag" value:"pending" > label:<name:"service_id" value:"postgres:5432" > label:<name:"state" value:"streaming" > label:<name:"user" value:"debezium_replicator" > gauge:<value:0 > } was collected before with the same name and label values
* collected metric "postgres_replication_lag_bytes" { label:<name:"application_name" value:"PostgreSQL JDBC Driver" > label:<name:"client_addr" value:"10.75.3.105/32" > label:<name:"lag" value:"write" > label:<name:"service_id" value:"postgres:5432" > label:<name:"state" value:"streaming" > label:<name:"user" value:"debezium_replicator" > gauge:<value:-4.11433728e+08 > } was collected before with the same name and label values
* collected metric "postgres_replication_lag_seconds" { label:<name:"application_name" value:"PostgreSQL JDBC Driver" > label:<name:"client_addr" value:"10.75.3.105/32" > label:<name:"lag" value:"write" > label:<name:"service_id" value:"postgres:5432" > label:<name:"state" value:"streaming" > label:<name:"user" value:"debezium_replicator" > gauge:<value:0 > } was collected before with the same name and label values
* collected metric "postgres_replication_lag_seconds" { label:<name:"application_name" value:"PostgreSQL JDBC Driver" > label:<name:"client_addr" value:"10.75.3.105/32" > label:<name:"lag" value:"flush" > label:<name:"service_id" value:"postgres:5432" > label:<name:"state" value:"streaming" > label:<name:"user" value:"debezium_replicator" > gauge:<value:0 > } was collected before with the same name and label values
* collected metric "postgres_replication_lag_seconds" { label:<name:"application_name" value:"PostgreSQL JDBC Driver" > label:<name:"client_addr" value:"10.75.3.105/32" > label:<name:"lag" value:"replay" > label:<name:"service_id" value:"postgres:5432" > label:<name:"state" value:"streaming" > label:<name:"user" value:"debezium_replicator" > gauge:<value:0 > } was collected before with the same name and label values
* collected metric "postgres_replication_lag_all_seconds" { label:<name:"application_name" value:"PostgreSQL JDBC Driver" > label:<name:"client_addr" value:"10.75.3.105/32" > label:<name:"service_id" value:"postgres:5432" > label:<name:"state" value:"streaming" > label:<name:"user" value:"debezium_replicator" > gauge:<value:0 > } was collected before with the same name and label values

And its seems to be truly that data was collected before with the same name and label values

Steps to reproduce
Use postgresql 11 with multiple logical replication walsenders.

Expected behavior
There should be no errors and exporter mast work correctly.

pgSCV startup options

services:
  "postgres:5432":
    service_type: "postgres"
    conninfo: "postgres://postgres@127.0.0.1:5432/postgres"
defaults:
    postgres_username: "pgscv"
    postgres_password: "XXX"

Errors and Logs
All are in Describe the bug section

Environment (please complete the following information):

  • Used OS (or Containers): [output of cat /etc/os-release]
NAME="Ubuntu"
VERSION="18.04.5 LTS (Bionic Beaver)"
  • pgSCV Version [output of pgscv --version]
    pgscv 0.7.5 d52f09925a44a67493bd8c598a42c7455e182d5e-release
  • PostgreSQL Version [output of psql -c 'select version()']
PostgreSQL 11.8 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit
patroni 2.0.2
  • Do PostgreSQL and pgSCV are running on the same host?
    Yes

Additional context
None

m-shalenko commented

Hello there, guys!

We have the same problem:

cannot read data: unexpected status code returned when scraping "http://postgres-02.********:9890/metrics": 500; expecting 200; response body: "An error has occurred while serving metrics:\n\n9 error(s) occurred:\n* collected metric \"postgres_replication_lag_bytes\" { label:<name:\"application_name\" value:\"PostgreSQL JDBC Driver\" > label:<name:\"client_addr\" value:\"10.*****.132/32\" > label:<name:\"lag\" value:\"pending\" > label:<name:\"service_id\" value:\"postgres\" > label:<name:\"state\" value:\"streaming\" > label:<name:\"user\" value:\"debezium\" > gauge:<value:0 > } was collected before with the same name and label values\n* collected metric \"postgres_replication_lag_bytes\" { label:<name:\"application_name\" value:\"PostgreSQL JDBC Driver\" > label:<name:\"client_addr\" value:\"10.******.132/32\" > label:<name:\"lag\" value:\"write\" > label:<name:\"service_id\" value:\"postgres\" > label:<name:\"state\" value:\"streaming\" > label:<name:\"user\" value:\"debezium\" > gauge:<value:4.38588e+06 > } was collected before with the same name and label values\n* collected metric \"postgres_replication_lag_bytes\" { label:<name:\"application_name\" value:\"PostgreSQL JDBC Driver\" > label:<name:\"client_addr\" value:\"10.11.3.132/32\" > label:<name:\"lag\" value:\"flush\" > label:<name:\"service_id\" value:\"postgres\" > label:<name:\"state\" value:\"streaming\" > label:<name:\"user\" value:\"debezium\" > gauge:<value:2.3684544e+07 > } was collected before with the same name and label values\n* collected metric \"postgres_replication_lag_bytes\" { label:<name:\"application_name\" value:\"PostgreSQL JDBC Driver\" > label:<name:\"client_addr\" value:\"10.******.132/32\" > label:<name:\"lag\" value:\"replay\" > label:<name:\"service_id\" value:\"postgres\" > label:<name:\"state\" value:\"streaming\" > label:<name:\"user\" value:\"debezium\" > gauge:<value:0 > } was collected before with the same name and label values\n* collected metric \"postgres_replication_lag_seconds\" { label:<name:\"application_name\" value:\"PostgreSQL JDBC Driver\" > label:<name:\"client_addr\" value:\"10.******.132/32\" > label:<name:\"lag\" value:\"write\" > label:<name:\"service_id\" value:\"postgres\" > label:<name:\"state\" value:\"streaming\" > label:<name:\"user\" value:\"debezium\" > gauge:<value:0 > } was collected before with the same name and label values\n* collected metric \"postgres_replication_lag_seconds\" { label:<name:\"application_name\" value:\"PostgreSQL JDBC Driver\" > label:<name:\"client_addr\" value:\"10.*****.132/32\" > label:<name:\"lag\" value:\"flush\" > label:<name:\"service_id\" value:\"postgres\" > label:<name:\"state\" value:\"streaming\" > label:<name:\"user\" value:\"debezium\" > gauge:<value:0 > } was collected before with the same name and label values\n* collected metric \"postgres_replication_lag_seconds\" { label:<name:\"application_name\" value:\"PostgreSQL JDBC Driver\" > label:<name:\"client_addr\" value:\"10.******.132/32\" > label:<name:\"lag\" value:\"replay\" > label:<name:\"service_id\" value:\"postgres\" > label:<name:\"state\" value:\"streaming\" > label:<name:\"user\" value:\"debezium\" > gauge:<value:0 > } was collected before with the same name and label values\n* collected metric \"postgres_replication_lag_all_bytes\" { label:<name:\"application_name\" value:\"PostgreSQL JDBC Driver\" > label:<name:\"client_addr\" value:\"10.******.132/32\" > label:<name:\"service_id\" value:\"postgres\" > label:<name:\"state\" value:\"streaming\" > label:<name:\"user\" value:\"debezium\" > gauge:<value:2.8070424e+07 > } was collected before with the same name and label values\n* collected metric \"postgres_replication_lag_all_seconds\" { label:<name:\"application_name\" value:\"PostgreSQL JDBC Driver\" > label:<name:\"client_addr\" value:\"10.******.132/32\" > label:<name:\"service_id\" value:\"postgres\" > label:<name:\"state\" value:\"streaming\" > label:<name:\"user\" value:\"debezium\" > gauge:<value:0 > } was collected before with the same name and label values\n"

m-shalenko commented

Maybe there is a way to disable these metrics without affecting others until an update is released?

lesovsky commented

Hi,
show the content of pg_stat_replication view.

dan-aksenov commented

here:

my_db=# select * from pg_stat_replication ;
 pid  | usesysid |       usename       |    application_name    | client_addr | client_hostname | client_port |         backend_start         | backend_xmin |   state   |   sent_lsn   |  write_lsn   |  flush_lsn   |  replay_lsn  |    write_lag    |    flush_lag    |   replay_lag    | sync_priority | sync_state
------+----------+---------------------+------------------------+-------------+-----------------+-------------+-------------------------------+--------------+-----------+--------------+--------------+--------------+--------------+-----------------+-----------------+-----------------+---------------+------------
 5531 |  3645854 | debezium_replicator | PostgreSQL JDBC Driver | 10.77.5.107 |                 |       44746 | 2022-05-10 14:31:07.873206+03 |              | streaming | 501/73936338 | 501/73922D50 | 501/73892370 | 501/73892370 | 00:00:01.307215 | 00:01:11.981775 | 00:01:11.981775 |             0 | async
 5336 |    16384 | replication         | xx13-xxxxx-db202       | 10.77.5.202 |                 |       52734 | 2022-05-10 14:30:57.332764+03 |              | streaming | 501/73936338 | 501/73936338 | 501/73936338 | 501/73936338 | 00:00:00.000175 | 00:00:00.001996 | 00:00:00.00209  |             0 | async
 8389 |  3645854 | debezium_replicator | PostgreSQL JDBC Driver | 10.77.5.107 |                 |       58564 | 2022-05-11 10:24:41.676769+03 |              | catchup   | 501/1C194C90 | 4FE/C3F1FBC8 |              |              |                 |                 |                 |             0 | async
 5435 |  3645854 | debezium_replicator | PostgreSQL JDBC Driver | 10.77.5.107 |                 |       44456 | 2022-05-10 14:31:05.861099+03 |              | streaming | 501/73936338 | 501/73920828 |              |              |                 |                 |                 |             0 | async
 3616 |  3645854 | debezium_replicator | PostgreSQL JDBC Driver | 10.77.5.107 |                 |       39746 | 2022-05-11 00:10:17.071161+03 |              | streaming | 501/73936338 | 501/739229E8 | 501/7388AB58 | 501/7388AB58 | 00:00:05.96013  | 00:01:16.642303 | 00:01:16.642303 |             0 | async
 3661 |  3645854 | debezium_replicator | PostgreSQL JDBC Driver | 10.77.5.107 |                 |       40702 | 2022-05-11 00:10:32.522202+03 |              | streaming | 501/73936338 | 501/73927948 | 501/738972D0 | 501/738972D0 | 00:00:03.599762 | 00:01:04.287593 | 00:01:04.287593 |             0 | async
 7586 |  3645854 | debezium_replicator | PostgreSQL JDBC Driver | 10.77.5.107 |                 |       54664 | 2022-05-10 14:41:19.176089+03 |              | streaming | 501/73936338 | 501/73927B30 |              |              |                 |                 |                 |             0 | async
 7588 |  3645854 | debezium_replicator | PostgreSQL JDBC Driver | 10.77.5.107 |                 |       54668 | 2022-05-10 14:41:19.177411+03 |              | streaming | 501/73936338 | 501/73927948 |              |              |                 |                 |                 |             0 | async
(8 rows)
glushakov commented

same problem

PCbIX commented

Same issue version 0.7.5 or 0.8.0b6 with pg12