replication lag despite corrective config - Mailing list pgsql-general

From Wyatt Alt
Subject replication lag despite corrective config
Date
Msg-id CAGem3qCCP5c7JZUMApteK7TXT2yMzVi_Yy2nsZ5Pn=R30LrDvw@mail.gmail.com
Whole thread Raw
Responses Re: replication lag despite corrective config
Re: replication lag despite corrective config
List pgsql-general
I've been struggling to eliminate replication lag on a Postgres 9.6.6 instance on Amazon RDS. I believe the lag is caused by early cleanup conflicts from vacuums on the master, because I can reliably resolve it by killing long-running queries on the standby. I most recently saw ten hours of lag on Saturday and addressed it this way.

The standby is running with
hot_standby_feedback = on
max_standby_streaming_delay = 5min
max_standby_archive_delay = 30s

I am not using replication slots on the primary due to reported negative interactions with pg_repack on large tables.

My rationale for the first two settings is that hot_standby_feedback should address my issues almost all the time, but that max_standby_streaming_delay would sometimes be necessary as a fallback, for instance in cases of a transient connection loss between the standby and primary. I believe these settings are mostly working, because lag is less frequent than it was when I configured them.

My questions are,
* Am I overlooking anything in my configuration?
* What would explain lag caused by query conflicts given the max_standby_streaming_delay setting? Shouldn't those queries be getting killed?
* Is there any particular diagnostic info I should be collecting on the next occurrence, to help me figure out the cause? Note that as I'm on RDS, I don't have direct access to the datadir -- just psql.

Thanks for any advice!
Wyatt

pgsql-general by date:

Previous
From: Merlin Moncure
Date:
Subject: Re: plpgsql and intarray extension; int[] - int[] operator does notexist ?
Next
From: Wyatt Alt
Date:
Subject: Re: replication lag despite corrective config