Re: SQL command in Slave Database - Monitor Replication

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

Tel.: +44 (0)2380 386578

Email: alberto(dot)olivares(at)snowflakesoftware(dot)com
Website: www.snowflakesoftware.com
Twitter: @sflakesoftware <http://www,twitter(dot)com(at)sflakesoftware/>
Follow us on LinkedIn <http://www.linkedin.com/company/snowflake-software>

*Winner of IHS Jane's ATC Award - Enabling Technology*
*Read more <http://www.snowflakesoftware.com/2015/03/janes-award/>*

Registered in England & Wales. Registered Number: 4294244
-----------------------------------------------------------------------------------------

On 6 July 2015 at 09:35, Alberto Olivares <
alberto(dot)olivares(at)snowflakesoftware(dot)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
>
> Tel.: +44 (0)2380 386578
>
> Email: alberto(dot)olivares(at)snowflakesoftware(dot)com
> Website: www.snowflakesoftware.com
> Twitter: @sflakesoftware <http://www,twitter(dot)com(at)sflakesoftware/>
> Follow us on LinkedIn <http://www.linkedin.com/company/snowflake-software>
>
> *Winner of IHS Jane's ATC Award - Enabling Technology*
> *Read more <http://www.snowflakesoftware.com/2015/03/janes-award/>*
>
> Registered in England & Wales. Registered Number: 4294244
>
> -----------------------------------------------------------------------------------------
>
>
>
>
> On 4 July 2015 at 15:37, Matheus de Oliveira <matioli(dot)matheus(at)gmail(dot)com>
> wrote:
>
>>
>> On Fri, Jul 3, 2015 at 12:03 PM, Alberto Olivares <
>> alberto(dot)olivares(at)snowflakesoftware(dot)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*
*Read more <http://www.snowflakesoftware.com/2015/03/janes-award/>*