Re: pg_last_xact_replay_timestamp() sometimes reports unlikely, very large delays

> On Mar 22, 2017, at 8:06 PM, Toby Corkindale <toby(dot)corkindale(at)strategicdata(dot)com(dot)au> wrote:
>
> My best guess for what is going on is:
> - There has been no activity for hours or days, and so the oldest replayed
> transaction on the slave is genuinely quite old.
> - Something has happened on the master that causes its
> pg_current_xlog_location() to be updated, but not in a way that is sent to the
> slave until the end of a long-running transaction.
>
>
> Could anyone suggest how to do this in a manner that avoids the problem?

Are you using streaming replication or only WAL archiving? If you are not streaming the archive command does not send the file until it is full (16MB, if I recall correctly). To address this, you can change the archive_timeout setting to ensure the WAL file is sent at some interval even if it is not full.

I use 'archive_timeout = 300' to send it every 5 minutes. If the lag is greater than 15 minutes, the alarm bells start going off.

John DeSoi, Ph.D.