postgres_exporter unable to export pg_replication_lag metrics to promethus

[NAGARJUNA D.N]

Hi,

We are running postgres 12.x on 2 VMs (1 master and 1 replica).

We had configured postgres_exporter to ship metrics but we don't see any metrics exporter for pg_replication_lag. Kindly help.

exporter version:

postgres_exporter v0.5.1 (built with go1.11)

Startup file:

/etc/systemd/system/postgres_exporter.service

[Unit]

Description=Prometheus exporter for Postgresql

Wants=network-online.target

After=network-online.target

[Service]

User=postgres

Group=postgres

WorkingDirectory=/opt/postgres_exporter

EnvironmentFile=/opt/postgres_exporter/postgres_exporter.env

ExecStart=/usr/local/bin/postgres_exporter

Restart=always 

[Install]

WantedBy=multi-user.target

Thanks in advance. 

[NAGARJUNA D.N]

Would you please someone help?

Trying this also didn't help - https://github.com/wrouesnel/postgres_exporter/blob/master/queries.yaml

startup script:

ExecStart=/usr/local/bin/postgres_exporter --extend.query-path /opt/postgres_exporter/queries.yaml

[L.walid (PowerM)]

Hi,

Are you having issues with only the pg_replication_lag metric ?

Best regards, 
--------------------------------------------------------------
Walid Largou
Solution Architect / Service Delivery Manager
Mobile : +212 621 31 98 71
Email : l.w...@powerm.ma
320 Bd Zertouni 6th Floor, Casablanca, Morocco
--------------------------------------------------------------
https://www.powerm.ma
Power Maroc #1 IBM Business Partner – Power Maroc
This message is confidential .Its contents do not constitute a commitment by Power Maroc S.A.R.L except where provided for in a written agreement between you and Power Maroc S.A.R.L. Any authorized disclosure, use or dissemination, either whole or partial, is prohibited. If you are not the intended recipient of the message, please notify the sender immediately.

On 16 Dec 2020, at 06:43, 'NAGARJUNA D.N' via Prometheus Users <promethe...@googlegroups.com> wrote:

pg_replication_lag

[NAGARJUNA D.N]

Yes, only with the  pg_replication_lag. 

Or somehow I need to get the replica lags behind the master.

Regards,

Nagarjuna

This communication is for informational purposes only.  Any comments or statements made herein do not necessarily reflect those of Blue Pi Consulting India Private Limited, its subsidiaries and affiliates. This transmission may contain information that is privileged, confidential, legally privileged, and/or exempt from disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. Although this transmission and any attachments are believed to be free of any virus or other defect that might affect any computer system into which it is received and opened, it is the responsibility of the recipient to ensure that it is virus free and no responsibility is accepted by Blue Pi Consulting India Private Limited, its subsidiaries and affiliates, as applicable, for any loss or damage arising in any way from its use. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format. Thank you.

[L.walid (PowerM)]

Can you share the output of the select : 

SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) as lag

Best regards, 
--------------------------------------------------------------
Walid Largou
Solution Architect / Service Delivery Manager
Mobile : +212 621 31 98 71
Email : l.w...@powerm.ma
320 Bd Zertouni 6th Floor, Casablanca, Morocco
--------------------------------------------------------------
https://www.powerm.ma
Power Maroc #1 IBM Business Partner – Power Maroc
This message is confidential .Its contents do not constitute a commitment by Power Maroc S.A.R.L except where provided for in a written agreement between you and Power Maroc S.A.R.L. Any authorized disclosure, use or dissemination, either whole or partial, is prohibited. If you are not the intended recipient of the message, please notify the sender immediately.

[NAGARJUNA D.N]

On replica node:

SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) as lag                                                        ;
     lag    
-------------
 2036.505786
(1 row)

Regards,

Nagarjuna

[L.walid (PowerM)]

Did you try to change the version of the exporter, also please the following open issue on the exporter : 

https://github.com/wrouesnel/postgres_exporter/issues/385

Best regards, 
--------------------------------------------------------------
Walid Largou
Solution Architect / Service Delivery Manager
Mobile : +212 621 31 98 71
Email : l.w...@powerm.ma
320 Bd Zertouni 6th Floor, Casablanca, Morocco
--------------------------------------------------------------
https://www.powerm.ma
Power Maroc #1 IBM Business Partner – Power Maroc
This message is confidential .Its contents do not constitute a commitment by Power Maroc S.A.R.L except where provided for in a written agreement between you and Power Maroc S.A.R.L. Any authorized disclosure, use or dissemination, either whole or partial, is prohibited. If you are not the intended recipient of the message, please notify the sender immediately.

[NAGARJUNA D.N]

Yes, I upgraded to v0.8.0 but no help. 

Regards,

Nagarjuna

[L.walid (PowerM)]

Please share the postgres version. Please test this query on master, if it returns results, replace it on the queries.yml and test

select now()-pg_last_xact_replay_timestamp() as lag

Sent from my iPhone

On Dec 16, 2020, at 11:27 AM, NAGARJUNA D.N <naga...@bluepi.in> wrote:



[NAGARJUNA D.N]

Postgres version is 12.x.

This query is returning empty on master.

select now()-pg_last_xact_replay_timestamp() as lag
postgres-# ;
 lag
-----
 
(1 row)

Regards,

Nagarjuna

[L.walid (PowerM)]

The problem you have is that the exporter is querying your master (not the replica), I’m trying to find the right query to get the lag from the master. Normally the select I shared should have brought your results. But I’m still checking. Please share from master : 

select * from pg_stat_replication;

Best regards, 
--------------------------------------------------------------
Walid Largou
Solution Architect / Service Delivery Manager
Mobile : +212 621 31 98 71
Email : l.w...@powerm.ma
320 Bd Zertouni 6th Floor, Casablanca, Morocco
--------------------------------------------------------------
https://www.powerm.ma
Power Maroc #1 IBM Business Partner – Power Maroc
This message is confidential .Its contents do not constitute a commitment by Power Maroc S.A.R.L except where provided for in a written agreement between you and Power Maroc S.A.R.L. Any authorized disclosure, use or dissemination, either whole or partial, is prohibited. If you are not the intended recipient of the message, please notify the sender immediately.

On 16 Dec 2020, at 12:42, NAGARJUNA D.N <naga...@bluepi.in> wrote:

pg_last_xact_replay_timestamp

[NAGARJUNA D.N]

  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 |            reply_time            
-------+----------+------------+------------------+-------------+-----------------+-------------+---------------------------------+--------------+-----------+--------------+--------------+--------------+--------------+-----------+-----------+------------+---------------+------------+----------------------------------
 32408 |    25158 | replicator | node4            | 10.0.x.x  |                 |       56242 | 2020-12-14 11:57:55.04265+05:30 |              | streaming | D4B/E335D8C8 | D4B/E335D8C8 | D4B/E335D8C8 | D4B/E335D8C8 |           |           |            |             1 | sync       | 2020-12-16 17:22:55.164426+05:30
(1 row)

Regards,

Nagarjuna

This communication is for informational purposes only.  Any comments or statements made herein do not necessarily reflect those of Blue Pi Consulting India Private Limited, its subsidiaries and affiliates. This transmission may contain information that is privileged, confidential, legally privileged, and/or exempt from disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. Although this transmission and any attachments are believed to be free of any virus or other defect that might affect any computer system into which it is received and opened, it is the responsibility of the recipient to ensure that it is virus free and no responsibility is accepted by Blue Pi Consulting India Private Limited, its subsidiaries and affiliates, as applicable, for any loss or damage arising in any way from its use. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format. Thank you.

[L.walid (PowerM)]

Can you try this one : 

SELECT CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn()

 

THEN 0

 

ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())

 

END AS log_delay;

Best regards, 
--------------------------------------------------------------
Walid Largou
Solution Architect / Service Delivery Manager
Mobile : +212 621 31 98 71
Email : l.w...@powerm.ma
320 Bd Zertouni 6th Floor, Casablanca, Morocco
--------------------------------------------------------------
https://www.powerm.ma
Power Maroc #1 IBM Business Partner – Power Maroc
This message is confidential .Its contents do not constitute a commitment by Power Maroc S.A.R.L except where provided for in a written agreement between you and Power Maroc S.A.R.L. Any authorized disclosure, use or dissemination, either whole or partial, is prohibited. If you are not the intended recipient of the message, please notify the sender immediately.

On 16 Dec 2020, at 12:53, NAGARJUNA D.N <naga...@bluepi.in> wrote:

streaming

[NAGARJUNA D.N]

 log_delay
-----------

         0
(1 row)

Regards,

Nagarjuna

[L.walid (PowerM)]

Great, so your replica is synced, can you replace this on your queries.yml and test.

Best regards, 
--------------------------------------------------------------
Walid Largou
Solution Architect / Service Delivery Manager
Mobile : +212 621 31 98 71
Email : l.w...@powerm.ma
320 Bd Zertouni 6th Floor, Casablanca, Morocco
--------------------------------------------------------------
https://www.powerm.ma
Power Maroc #1 IBM Business Partner – Power Maroc
This message is confidential .Its contents do not constitute a commitment by Power Maroc S.A.R.L except where provided for in a written agreement between you and Power Maroc S.A.R.L. Any authorized disclosure, use or dissemination, either whole or partial, is prohibited. If you are not the intended recipient of the message, please notify the sender immediately.

[NAGARJUNA D.N]

Worked, thanks.

Regards,

Nagarjuna