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.
-[ 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
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