On Wed, 2023-11-08 at 09:09 -0500, Craig Milhiser wrote: > Our max xid are over 500m and climbing. Auto vacuuming and manual vacuuming are returning > > autovacuuming to prevent wraparound. > WARNING: oldest xmin is far in the past > HINT: Close open transactions soon to avoid wraparound problems. > You might also need to commit or roll back old prepared transactions, or drop stale replication slots. > > The autovacuum previously kicked in at 200m and reduced the maximum xid. Now there is no reduction. > > We have a writer (master) node, 3 readers (slaves) and a hot-standby. We have an ETL vendor hooked > up to replicate. All of the replications are working. There is current data in each. We have checked > our replication slots. Everything is in use and working. > > From transactions, we have terminated every writer node PID since before this climb has started. > Almost every client has been restarted. > > We are handling 100+ insert/updates per second. Most transactions complete in ~2 seconds. Though some > may take 1 minute. There are no long running idle in transaction activities. > > We have been scouring the internet reading many helpful pages and trying their advice but we cannot > find a problem. > > We have restored a daily snapshot in another cluster. After vacuuming the max xid dropped to 50m. > No client was connected. Implying that a restart should fix the issue. > > I am searching for any hints. We have not found a smoking gun to focus on. This is a 24x7 operation > and would rather not have to restart the server. > > We are on v13.8, AWS RDS. > > Is there a way to find which row or table or lock or some open transaction is preventing the vacuuming > from lowering xid? Any hints on where to look? If that is not a bug introduced by Amazon, and you are certain that there are no long-running transactions and stale replication slots, there are two more possibilities: 1. a prepared transaction (check pg_prepared_xacts) 2. a long-running query on a standby, and "hot_standby_feedback = on" Yours, Laurenz Albe
