Re: VACUUM: Nonremovable rows due to wal sender process

[Steve Nixon <inverasln@xxxxxxxxx>]

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@xxxxxxxx> 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