Re: What causes streaming replication delay?

On Wed, Aug 31, 2016 at 11:44 AM, Dianne Skoll <dfs(at)roaringpenguin(dot)com>
wrote:

> Hi,
>
> I have a problem that's driving me insane. I have two PostgreSQL 9.1
> installations (yes, I know... very old.) One is configured as a master
> and the other is a streaming-replication hot-standby that supports
> read-only queries.
>
> Every now and then, although the standby claims to be streaming
> transactions
> if you look with "ps", it seems to stall and the replication delay grows
> by one second for each second of elapsed time.
>
> If I restart the hot-standby, it consumes WAL files and very quickly
> catches up. And then a few minutes later, it happens again.
>
> So, what are the possible causes of this behaviour? There are no
> long transactions on the standby, nor on the master. And how can
> I find out exactly what query is causing the problem, assuming
> it's a query?
>
> The relevant settings in the hot-standby postgresql.conf are:
>
> hot_standby = on
> max_standby_archive_delay = 10s
> max_standby_streaming_delay = 10s
> hot_standby_feedback = on
>
> and on the master:
>
> wal_level = hot_standby
> archive_mode = on
> archive_command = (a command to send WAL files to the standby)
> max_wal_senders = 8
>
> and all other parameters are at their defaults.
>
> Regards,
>
> Dianne.
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

All depends on how you're monitoring your slave lag. Check this blog post
and if you're seeing actual byte lag, then you could have cause for concern.

https://www.keithf4.com/monitoring_streaming_slave_lag/

Keith