I have a database which has a single table for which MultiXact wraparound has already occurred. Neither manual vacuums nor automatic vacuums are able to fix this condition.
Manual vacuums fail almost immediately:
SET vacuum_freeze_table_age = 0; VACUUM FREEZE VERBOSE event_audit_2019_10_01 ; INFO: vacuuming "public.event_audit_2019_10_01" ERROR: MultiXactId 1074790449 does no longer exist -- apparent wraparound SET vacuum_multixact_freeze_table_age = 0; SET VACUUM FREEZE VERBOSE event_audit_2019_10_01 ; INFO: vacuuming "public.event_audit_2019_10_01" ERROR: MultiXactId 1074790449 does no longer exist -- apparent wraparound
The automatic vacuum also fails almost immediately, though it is trying to run:
2019-10-29 11:10:29 EDT : [1-1] LOG: autovacuum launcher started 2019-10-29 11:10:31 EDT : [1-1] ERROR: MultiXactId 1074790449 does no longer exist -- apparent wraparound 2019-10-29 11:10:31 EDT : [2-1] CONTEXT: automatic vacuum of table "mydb.public.event_audit_2019_10_01" 2019-10-29 11:10:31 EDT : [1-1] LOG: autovacuum launcher started 2019-10-29 11:10:33 EDT : [1-1] ERROR: MultiXactId 1074790449 does no longer exist -- apparent wraparound 2019-10-29 11:10:33 EDT : [2-1] CONTEXT: automatic vacuum of table "mydb.public.event_audit_2019_10_01"
My goal is to be able to read and query this table. It is old audit data that needs to be retained but need not be inserted to or updated from ever again. The obvious path to that seems to be to fix the wraparound but any solution which lets me extract the table will be fine.
How can I fix this wraparound condition after it has already happened? I've read plenty on preventing it but none of the solutions I've tried so far have corrected it after the fact.
The case history of this problem is:
- An application errored because the autovacuums to prevent wraparound had not been executing. This was a significant maintenance oversight on a legacy installation.
- The immediate solution to this was to rename the table, drop the indexes on the table, then to recreate the original table and indexes in a fresh empty state to allow the application to run
- Efforts to use
pg_dumpto export the now-renamed table and
VACUUMto clean it up all failed
- An upgrade was run to get from PG 9.4.5 to PG 9.6.8 assuming that later PG versions have better protections for this (see prior question about this upgrade. This is an odd legacy application. I can upgrade from 9.6.8 to something in the PG 10.x range but I don't know exactly which minor version of PG 10 has been published for this "appliance"-style system.
- The vacuums shown above also fail.
- There does not seem to be any change in behavior between single-user and regular mode for Postgres
- I have both backups as well as VM level snapshots of this database. I would prefer to avoid restoring those if practical.
- While I read more on this issue I am running a vacuum on the entire DB with
vacuumdb --all --freeze --jobs=10 --echo --verbose. This will take some time as the DB is 6TB in total.
- I stopped the vacuum and tried Laurenz suggestion from comments and I am running
SELECT * FROM event_audit_2019_10_01 AS m, pgrowlocks('event_audit_2019_10_01') AS p WHERE p.locked_row = m.ctid ;- this also failed with the MultiXactId does no longer exist error
I am working out queries to SELECT rows by primary key and dump those to a file. The primary key range will have to be ranged in a way to find and avoid the bad rows.
INSERT INTO rebuilt_event_audit_2019_10_01 (event_audit_key, event_id, event_date, event_audit_type_key, event_data, event_reason_key, client_principal, network_access_point_id, participant_object_id, syslog_retry, person_key) ( SELECT event_audit_key, event_id, event_date, event_audit_type_key, event_data, event_reason_key, client_principal, network_access_point_id, participant_object_id, syslog_retry, person_key FROM event_audit_2019_10_01 old --note primary key wraparound also exists, this is an older legacy DB I inherited with some problems -- -2038528237 was derived from a report run before the mutlixact wraparound happened and is known to have had data written to the table with that PK WHERE old.event_audit_key <= (-2038528237 - 1000000) --minus 1,000,000 to avoid the bad range AND old.event_audit_key > (-2038528237 - 2000000) --minus 2,000,000 to get us a million rows );
This still failed with the MultiXact error
I am still working on this problem. I am attempting to follow the advice from the comments. That thread states :
So we copied all but the defect rows into a new table, dropped the old table, and renamed the new one - quite an effort for finally deleting two rows :-)
That process is failing for me. I can do a
SELECT * FROM mytable LIMIT 10and get back data. I cannot do a
SELECT * FROM mytable where primary_key = 1000and get data, even if that PK is returned by the first query. I think this is because the solution requires a patch. Is that interpretation correct?
The trick was patching postgres for case (b) in a way that it would just print a WARNING and the row ID for these defect rows and continue.
I have done my best to read and get educated on this problem. My main references so far have been:
- https://www.postgresql.org/docs/9.4/sql-vacuum.html - The PG manual
- https://medium.com/in-the-weeds/postgresql-multixactid-error-in-vacuum-106af8fbf022 - Shows a similar issue. Querying for the row(s) with the bad xmax fails for me with the same wraparound error
- http://www.databasesoup.com/2012/09/freezing-your-tuples-off-part-1.html - A good 3 part series explaining how this transaction id management works and the relevant settings
- https://www.cybertec-postgresql.com/en/whats-in-an-xmax/ - a good background on what xmax really is.