Re: SQL command in Slave Database - Monitor Replication

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx> wrote:

On Fri, Jul 3, 2015 at 12:03 PM, Alberto Olivares <alberto.olivares@xxxxxxxxxxxxxxxxxxxxx> 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
Read more

[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux