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
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 6 July 2015 at 09:35, Alberto Olivares <alberto.olivares@xxxxxxxxxxxxxxxxxxxxx> 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 ConsultantSnowflake SoftwareTel.: +44 (0)2380 386578Winner of IHS Jane's ATC Award - Enabling TechnologyRegistered 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
