Обсуждение: SQL command in Slave Database - Monitor Replication

Поиск
Список
Период
Сортировка

SQL command in Slave Database - Monitor Replication

От
Alberto Olivares
Дата:
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
Snowflake Software



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

Re: SQL command in Slave Database - Monitor Replication

От
Matheus de Oliveira
Дата:

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

Re: SQL command in Slave Database - Monitor Replication

От
Alberto Olivares
Дата:
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

Re: SQL command in Slave Database - Monitor Replication

От
Alberto Olivares
Дата:
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

Re: SQL command in Slave Database - Monitor Replication

От
Stuart Bishop
Дата:
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/