Thread: Default maximum time replication between master and slave databases

Default maximum time replication between master and slave databases

From
Alberto Olivares
Date:
Hello,

I am running this query in the slave database: 

SELECT EXTRACT (epoch from now() - pg_last_xact_replay_timestamp());

The maximum result is 60 seconds then the value goes to 0 and start again. I would like to modify this value to maximum 30 seconds.

I have tried to change different parameters on the  postgresql.conf in both databases but I have not find the solution.

Anyone knows how to change this default value?

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
-----------------------------------------------------------------------------------------




Winner of IHS Jane's ATC Award - Enabling Technology

Re: Default maximum time replication between master and slave databases

From
Scott Ribe
Date:
On Jul 10, 2015, at 6:54 AM, Alberto Olivares <alberto.olivares@snowflakesoftware.com> wrote:
>
> SELECT EXTRACT (epoch from now() - pg_last_xact_replay_timestamp());
>
> The maximum result is 60 seconds then the value goes to 0 and start again. I would like to modify this value to
maximum30 seconds. 
>
> I have tried to change different parameters on the  postgresql.conf in both databases but I have not find the
solution.

WAL shipping or streaming?

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice







Re: Default maximum time replication between master and slave databases

From
Alberto Olivares
Date:
Streaming replication.

Alberto Olivares Colas
Technical Consultant
Snowflake Software



Winner of IHS Jane's ATC Award - Enabling Technology

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




On 10 July 2015 at 14:32, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
On Jul 10, 2015, at 6:54 AM, Alberto Olivares <alberto.olivares@snowflakesoftware.com> wrote:
>
> SELECT EXTRACT (epoch from now() - pg_last_xact_replay_timestamp());
>
> The maximum result is 60 seconds then the value goes to 0 and start again. I would like to modify this value to maximum 30 seconds.
>
> I have tried to change different parameters on the  postgresql.conf in both databases but I have not find the solution.

WAL shipping or streaming?

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice







Winner of IHS Jane's ATC Award - Enabling Technology

Re: Default maximum time replication between master and slave databases

From
Guillaume Lelarge
Date:

Hi

Le 10 juil. 2015 2:58 PM, "Alberto Olivares" <alberto.olivares@snowflakesoftware.com> a écrit :
>
> Hello,
>
> I am running this query in the slave database: 
>
> SELECT EXTRACT (epoch from now() - pg_last_xact_replay_timestamp());
>
> The maximum result is 60 seconds then the value goes to 0 and start again. I would like to modify this value to maximum 30 seconds.
>
> I have tried to change different parameters on the  postgresql.conf in both databases but I have not find the solution.
>
> Anyone knows how to change this default value?
>

pg_last_xact_replay_timestamp() gives you the timestamp of the last replayed transaction. That doesn't have anything to do with configuration. It mostly depends on the activity on the master. If you don't have any write activity on the master, you'll have bigger values. And that's normal.

--
Guillaume

Re: Default maximum time replication between master and slave databases

From
Alberto Olivares
Date:
Hi,

I don't have any write activity on the master, but still this value goes to 60 seconds and then restart constantly from 0 again . 

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 10 July 2015 at 14:56, Guillaume Lelarge <guillaume@lelarge.info> wrote:

Hi

Le 10 juil. 2015 2:58 PM, "Alberto Olivares" <alberto.olivares@snowflakesoftware.com> a écrit :
>
> Hello,
>
> I am running this query in the slave database: 
>
> SELECT EXTRACT (epoch from now() - pg_last_xact_replay_timestamp());
>
> The maximum result is 60 seconds then the value goes to 0 and start again. I would like to modify this value to maximum 30 seconds.
>
> I have tried to change different parameters on the  postgresql.conf in both databases but I have not find the solution.
>
> Anyone knows how to change this default value?
>

pg_last_xact_replay_timestamp() gives you the timestamp of the last replayed transaction. That doesn't have anything to do with configuration. It mostly depends on the activity on the master. If you don't have any write activity on the master, you'll have bigger values. And that's normal.

--
Guillaume



Winner of IHS Jane's ATC Award - Enabling Technology

Re: Default maximum time replication between master and slave databases

From
Guillaume Lelarge
Date:

Le 10 juil. 2015 4:07 PM, "Alberto Olivares" <alberto.olivares@snowflakesoftware.com> a écrit :
>
> Hi,
>
> I don't have any write activity on the master, but still this value goes to 60 seconds and then restart constantly from 0 again . 
>

Well, you have. That might be autovacuum doing some vacuum or analyze without you noticing it.

--
Guillaume

Re: Default maximum time replication between master and slave databases

From
Alberto Olivares
Date:
Thanks Guillaume,

maybe. I am trying to find the parameter that controls this value but I cannot find it. Last thing I tried was to modify the vacuum parameters on the slave but nothing changes.



Alberto Olivares Colas
Technical Consultant
Snowflake Software



Winner of IHS Jane's ATC Award - Enabling Technology

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




On 10 July 2015 at 15:11, Guillaume Lelarge <guillaume@lelarge.info> wrote:

Le 10 juil. 2015 4:07 PM, "Alberto Olivares" <alberto.olivares@snowflakesoftware.com> a écrit :
>
> Hi,
>
> I don't have any write activity on the master, but still this value goes to 60 seconds and then restart constantly from 0 again . 
>

Well, you have. That might be autovacuum doing some vacuum or analyze without you noticing it.

--
Guillaume



Winner of IHS Jane's ATC Award - Enabling Technology


On Fri, Jul 10, 2015 at 10:23 AM, Alberto Olivares <alberto.olivares@snowflakesoftware.com> wrote:
Thanks Guillaume,

maybe. I am trying to find the parameter that controls this value but I cannot find it. Last thing I tried was to modify the vacuum parameters on the slave but nothing changes.



Alberto Olivares Colas
Technical Consultant
Snowflake Software



Winner of IHS Jane's ATC Award - Enabling Technology

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




On 10 July 2015 at 15:11, Guillaume Lelarge <guillaume@lelarge.info> wrote:

Le 10 juil. 2015 4:07 PM, "Alberto Olivares" <alberto.olivares@snowflakesoftware.com> a écrit :
>
> Hi,
>
> I don't have any write activity on the master, but still this value goes to 60 seconds and then restart constantly from 0 again . 
>

Well, you have. That might be autovacuum doing some vacuum or analyze without you noticing it.

--
Guillaume



Winner of IHS Jane's ATC Award - Enabling Technology

I still don't think you're understanding how replication works along with trying to use this function to monitor slave lag. I tried to give a good summary here.

http://www.keithf4.com/monitoring_streaming_slave_lag/