Thread: SQL command in Slave Database - Monitor Replication
Hi all,
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
Thanks for your answers.
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
-----------------------------------------------------------------------------------------
Winner of IHS Jane's ATC Award - Enabling Technology
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
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
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@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 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
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@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 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@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 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
On 6 July 2015 at 15: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. "SELECT pg_last_xact_replay_timestamp()" gives you the time of the last write. If it is not increasing, replication is broken or the primary is idle. If it is NULL, the database is a primary or a secondary just starting up. I think most of our are monitoring that lag time is in a certain range - "SELECT (now() - pg_last_xact_replay_timestamp()) < '5 minutes'::interval" -- Stuart Bishop <stuart@stuartbishop.net> http://www.stuartbishop.net/