Hello,
Recently our team performed an upgrade on one of our old postgres 9.2 databases. "for science" we tried a direct upgrade from 9.2 to 9.6 on our staging environment . The initial plan was to do 9.2 -> 9.4 and 9.4 -> 9.6.
The upgrade turned out successful on staging and we decided to go with it on prod as well.
Prod starting throwing the following errors during autovacuum -> 'cannot freeze committed xmax <xid>' on 2 different tables. Running vacuum manually revealed more tables affected by the same error.
The staging database did not present any error but the process to sync prod to stage includes a sanitize script that removes sensitive information and it somehow seems to fix the issue on stage (we've done a sync from prod to stage after the upgrade)
I've been doing a lot of search about this and even tried to go through the code that throws that specific error. I tried to temporarily increase the `vacuum_freeze_min_age` setting and run a vacuum, trying to bypass the freezing process. But this was unsuccessful (still throwing the error on some tables, and not anymore on some others)
There's still a lot of unknown on my side about
- what does this error exactly implies? -> are we trying to freeze committed transactions?
-Why would that be an issue?
- How can I fix this? If it's possible
I would welcome any information that could put me in the right direction.
Cheers,
Alexandre