postgresql 9.6 - cannot freeze committed xmax

From: Alexandre Garcia <alexandre(at)vmfarms(dot)com>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: postgresql 9.6 - cannot freeze committed xmax
Date: 2018-02-26 22:48:39
Message-ID: CAPYLKR6yxV4=pfW0Gwij7aPNiiPx+3ib4USVYnbuQdUtmkMaEA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Alvaro Herrera 2018-02-26 23:09:31 Re: postgresql 9.6 - cannot freeze committed xmax
Previous Message Tim Cross 2018-02-26 21:29:56 Re: Is it okay to run Postgres service account as a domain account (on Linux)?