I'm running Aurora Postgres and noticed that metric Maximum Used Transaction IDs is increasing a lot. Trying to manually vacuum the tables that's was not automatically vacuumed lead me to the following errors:

sql> VACUUM sales.hist_status
[2019-01-06 15:22:44] [58P01] ERROR: could not access status of transaction 247756185
[2019-01-06 15:22:44] Detail: CLOG segment 29 does not exist: No such file or directory.

sql> VACUUM sales.hist_items
[2019-01-06 15:22:55] [XX001] ERROR: found xmin 235941673 from before relfrozenxid 256796328

Googling these erros points me to database corruption. What should I do?

  • If you have a support with AWS I would open up a impacted level ticket. Is the database still accessible otherwise? – Aaron Jan 6 '19 at 18:02
  • These are errors that should never occur. (Maybe transaction ID wraparound - which should never occur) Sounds like Amazon did something wrong with their Aurora fork of Postgres or your particular instance. Make sure you have backups if possible and contact Amazon. If they do not help you, please let us know. If they help, let us know as well. Any idea where the many transactions come from? Is that expected db load? – Erwin Brandstetter Jan 6 '19 at 18:09
  • Hello @Aaron and @Erwin! I'll fill a support ticket on AWS to see this. This load is expected, since I'm migrating a system from Oracle to PostgreSQL and running some "stress test" on the new environment. But, as far as I know, I didn't reach the Transaction ID limit for the database. Thanks a lot!!!! – regisxp Jan 6 '19 at 18:12
  • @regisxp Did you get a useful response from AWS support? I've seen another reference to Aurora Postgres DB corruption that seems similar (high write volumes which is presumably why your max transaction ID is increasing fast): reddit.com/r/devops/comments/9aqo78/… – RichVel Feb 20 '19 at 12:52
  • Hello @RichVel, unfortunately not. Since I didn't have support activated on that account (it's a development account) they refused to investigate the case. Here is the link on AWS Forum. forums.aws.amazon.com/thread.jspa?messageID=884009& – regisxp Feb 23 '19 at 0:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.