Re: SQL command in Slave Database - Monitor Replication - Mailing list pgsql-admin

From Alberto Olivares
Subject Re: SQL command in Slave Database - Monitor Replication
Date
Msg-id CAGdoAzgHpF5s9V8puGr8Mrvae85zhFAZqGKUDkJN2cQWhkfc1Q@mail.gmail.com
Whole thread Raw
In response to Re: SQL command in Slave Database - Monitor Replication  (Alberto Olivares <alberto.olivares@snowflakesoftware.com>)
List pgsql-admin
Hi all,

I found the SQL command that gave me what I expected:

SELECT EXTRACT (epoch from now() - pg_last_xact_replay_timestamp());  

I can check with this when the latest replication happened. 

Thanks.

Regards,
Alberto.

Alberto Olivares Colas
Technical Consultant
Snowflake Software



Winner of IHS Jane's ATC Award - Enabling Technology

Registered in England & Wales. Registered Number: 4294244
-----------------------------------------------------------------------------------------




On 6 July 2015 at 09:35, Alberto Olivares <alberto.olivares@snowflakesoftware.com> wrote:
Hi Matheus,


Thanks for your answer.  I do not have access to the primary database. So, I cannot run a SQL in there.

I need to run the command in the Slave database that tells me whether the replication is still working or not.

Regards,
Alberto.




Alberto Olivares Colas
Technical Consultant
Snowflake Software



Winner of IHS Jane's ATC Award - Enabling Technology

Registered in England & Wales. Registered Number: 4294244
-----------------------------------------------------------------------------------------




On 4 July 2015 at 15:37, Matheus de Oliveira <matioli.matheus@gmail.com> wrote:

On Fri, Jul 3, 2015 at 12:03 PM, Alberto Olivares <alberto.olivares@snowflakesoftware.com> wrote:
Is there any SQL command that I can run on the slave database to check if the replication is still working or not? I know that you can check the postgresql.conf but I want to do it directly on the SQL panel.

Database:  Postgresql 9.1 on Linux

You can query pg_stat_replication (since 9.1+) on the primary server, it will return one row for each secondary connected.

If you were on 9.2+ you could even use pg_xlog_location_diff to get the lag in bytes of data sent to the standby:

    SELECT
        application_name,

       
client_addr,

       
pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_location(), sent_location)) AS sent_lag
    FROM pg_stat_replication;

You can use write_location and replay_location similarly.

On 9.1 you'll have to do this math by yourself if you want or create your own pg_xlog_location_diff.

OBS: pg_stat_replication will only show standby connected through streaming replication, log shipping cannot be verified this way.

Best regards,
--
Matheus de Oliveira




Winner of IHS Jane's ATC Award - Enabling Technology

pgsql-admin by date:

Previous
From: David Osborne
Date:
Subject: Re: Running two postgresql servers in same Ubuntu machine
Next
From: Stuart Bishop
Date:
Subject: Re: SQL command in Slave Database - Monitor Replication