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 <s...@zsrv.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 >
