How to Deal with the Case of Stuck TransactionIDs in PostgreSQL

January 23, 2023

The Story

Postgres is up and running, and things are humming along. Replication is working, vacuums are running, and there are no idle transactions in sight. You poke around your logs and make sure things are clean, but you notice a little warning:

2019-01-03 07:51:38 GMT WARNING:  oldest xmin is far in the past
2019-01-03 07:51:38 GMT HINT:  Close open transactions soon to avoid wraparound problems.

Strange. There are no open transactions. You look in pg_stat_activity once, twice, three times–nothing. What’s up with that? How long has this been happening? You go back to you logs and you throw the whole thing into grep and find:

$ cat db1.log | grep "WARNING"
2019-01-03 07:51:38 GMT WARNING:  oldest xmin is far in the past
2019-01-03 07:51:38 GMT WARNING:  oldest xmin is far in the past
2019-01-03 07:51:38 GMT WARNING:  oldest xmin is far in the past
2019-01-03 07:51:38 GMT WARNING:  oldest xmin is far in the past
2019-01-03 07:51:38 GMT WARNING:  oldest xmin is far in the past
2019-01-03 07:51:38 GMT WARNING:  oldest xmin is far in the past
2019-01-03 07:51:38 GMT WARNING:  oldest xmin is far in the past
2019-01-03 07:51:38 GMT WARNING:  oldest xmin is far in the past
2019-01-03 07:51:55 GMT WARNING:  oldest xmin is far in the past
2019-01-03 07:51:55 GMT WARNING:  oldest xmin is far in the past
...
$ cat db1.log | grep "HINT"
2019-01-03 07:51:38 GMT HINT:  Close open transactions soon to avoid wraparound problems.
2019-01-03 07:51:38 GMT HINT:  Close open transactions soon to avoid wraparound problems.
2019-01-03 07:51:38 GMT HINT:  Close open transactions soon to avoid wraparound problems.
2019-01-03 07:51:38 GMT HINT:  Close open transactions soon to avoid wraparound problems.
...

Uh oh. What’s going on? And the pressure is on–nobody wants to be stuck dealing with wraparound problems. The adrenaline rush hits, and you’re frantically looking for the silver bullet to kill off this problem. Kill. Maybe restart the database? No, you can’t do that–it’s a production server. You’ll get chewed out! You look everywhere. autovacuum_freeze_max_age, pg_class.relfrozenxid, even pg_prepared_xacts and pg_prepared_statements. Nothing. You try VACUUMing the entire database, but to no avail. And in the logs, you see the unnerving autovacuum message that 2019-01-03 07:52:48 GMT DETAIL: ### dead row versions cannot be removed yet. for several tables. You ask your colleagues, and nobody’s got answers. You take a deep breath and decide it’s time: pg_ctl restart

The database goes down, comes back up, and all the apps start connecting again. But the messages keep on coming: WARNING: oldest xmin is far in the past. You’ve got a real zombie on your hands. And it’s not a dream.

The Unturned Stone

The solution for this scenario is in a seemingly unlikely place: pg_replication_slots. So it turns out that in this scenario, someone had set up a replication slot, used it, but stopped using it. They didn’t clean up after themselves and left the replication slot lying around. We’ve seen this problem before, where the pg_xlog/ partition (a common practice for many DBAs) would with WAL files and bring down the server. In this scenario, pg_xlog/ was sufficiently sized and didn’t suffer the filling-up that most people with stray replication slots encounter. Over time, XIDs advance, and the stray replication slot is still waiting for a subscriber to send all its stashed-away data to, leading to the warning and hints as seen above.

Conclusion

Clean up after yourself. My wife tells me that all the time. I tell it to my kids as well. Do it with your database too. Vacuum, remove unused indexes, close out your transactions, and remove your unused replication slots. It’s good for your server, it’s good for the environment, and it’s good for your health. Have a Happy 2019!

Special thanks to Andres Freund for helping with the analysis and determining the solution

Share this

Relevant Blogs

Why you should use Docker Compose

h2 { text-align: left !important; } .summary{ background:#f3f7f9; padding:20px; } SUMMARY: This article explains the benefits of using Docker Compose for creating multiple container applications. It reviews the steps for...
January 24, 2023

More Blogs

Quickstart guide on using pgPool

Steps (as root user)   #!/bin/bash   # Setup YUM repository for installing EPAS as the PEM # repository rpm -Uvh   # Set YUM username/password in edb.repo export YUM_USER=
January 24, 2023

Using auth_method=hba in PgBouncer

Introduction PgBouncer is a great tool for improving database performance with connection pooling.  I've been using it for many years, since it first became available in 2007.  Since then, several...
January 23, 2023