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
Technical Consultant
Snowflake Software
Tel.: +44 (0)2380 386578
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 LinuxYou 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_lagFROM 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
