Re: Replication failed after stalling

Joe Van Dyk wrote:
> If I run "COPY (select * from complicate_view) to stdout" on the standby, I've noticed that sometimes
> halts replication updates to the slave.
>
> For example, that's happening right now and "now() - pg_last_xact_replay_timestamp()" is 22 minutes.
> There's many transactions per second being committed on the master. Once that query is canceled, the
> slave catches up immediately.

You have hot_standby_feedback = on, right?

In that case that is expected behaviour.
Some change on the master conflicted with the query on the standby,
perhaps with a tuple cleaned up after a HOT update. Replication will
stall until the query is done.

If that is unacceptable to you and you would rather have queries
canceled on the standby if they take too long, set hot_standby_feedback
back to off and increase vacuum_defer_cleanup_age.

But it is not possible to have a predictable maximum replication lag
and arbitrarily long running queries on the standby at the same time.

Yours,
Laurenz Albe