Re: VACUUM: Nonremovable rows due to wal sender process

Thank you for the quick reply. You are correct that hot_standby_feedback is
indeed on. I'm trying to find out why at the moment because we are not
using the replication for any queries that would need that turned on. I was
just made aware of that after posting my question, and I am looking to get
permission to turn it off. I have access to the primary and the streaming
replication, but I do not have access to the replication being done by this
"Attunity" product. Our parent company is managing that.

The AUTOVACUUM appears to have stopped working sometime around NOV 22. If I
look on the replication server I have access to, one of the
pg_stat_activity entries are older than today. Based on that, I suspect
that the culprit long running transaction may be on the corporate
replicated database that I do not have direct access to.

select pid, backend_xmin, backend_start, backend_type from pg_stat_activity;

-[ RECORD 1 ]-+------------------------------
pid | 63111452
backend_xmin | 661716178
backend_start | 2022-01-04 15:52:42.269666-05
backend_type | client backend
-[ RECORD 2 ]-+------------------------------
pid | 46400004
backend_xmin |
backend_start | 2022-01-04 11:10:28.939006-05
backend_type | startup
-[ RECORD 3 ]-+------------------------------
pid | 46270090
backend_xmin |
backend_start | 2022-01-04 11:10:28.979557-05
backend_type | background writer
-[ RECORD 4 ]-+------------------------------
pid | 918684
backend_xmin |
backend_start | 2022-01-04 11:10:28.978996-05
backend_type | checkpointer
-[ RECORD 5 ]-+------------------------------
pid | 34079622
backend_xmin |
backend_start | 2022-01-04 11:10:29.172959-05
backend_type | walreceiver

Thanks again. At least it helped me figure out where I should be looking.

Steve Nixon

On Tue, 4 Jan 2022 at 15:17, Sergei Kornilov <sk(at)zsrv(dot)org> wrote:

> Hello
> This is exactly the reason why you need to track the age of the oldest
> transaction on the primary itself and on every replica that has
> hot_standby_feedback = on. By default hot_standby_feedback is disabled.
>
> > Is there anything I can do short of shutting down and restarting the
> primary (production system) that would allow the backend_xmin to move
> forward?
>
> You need to investigate this replica. Not a primary database. What
> transactions are in progress? Is it reasonable? Is hot_standby_feedback
> really needed here and is it reasonable to pay for its impact across the
> entire cluster?
> In my practice, hot_standby_feedback = on is only needed on replicas
> intended for fast OLTP queries. And where any long requests are prohibited.
>
> regards, Sergei
>