2

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 [18696]: [1-1] LOG:  autovacuum launcher started
2019-10-29 11:10:31 EDT [18697]: [1-1] ERROR:  MultiXactId 1074790449 does no longer exist -- apparent wraparound
2019-10-29 11:10:31 EDT [18697]: [2-1] CONTEXT:  automatic vacuum of table "mydb.public.event_audit_2019_10_01"
2019-10-29 11:10:31 EDT [18698]: [1-1] LOG:  autovacuum launcher started
2019-10-29 11:10:33 EDT [18699]: [1-1] ERROR:  MultiXactId 1074790449 does no longer exist -- apparent wraparound
2019-10-29 11:10:33 EDT [18699]: [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:

  1. An application errored because the autovacuums to prevent wraparound had not been executing. This was a significant maintenance oversight on a legacy installation.
  2. 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
  3. Efforts to use pg_dump to export the now-renamed table and VACUUM to clean it up all failed
  4. 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.
  5. The vacuums shown above also fail.
  6. There does not seem to be any change in behavior between single-user and regular mode for Postgres
  7. I have both backups as well as VM level snapshots of this database. I would prefer to avoid restoring those if practical.
  8. 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.
  9. 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
  10. 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

  11. 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 10 and get back data. I cannot do a SELECT * FROM mytable where primary_key = 1000 and 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:

  • 1
    "in the PG 10.x range but I don't know exactly which version of PG 10" - note that with Postgres 10 they changed the numbering scheme. The major version is only indicated by the first number now, so 10 is the major version and 10.5 is the minor (bugfix) version. So if you can upgrade to 10, go for the latest minor version. – a_horse_with_no_name Oct 29 at 15:41
  • 1
    This message from the mailing list might be interesting. – Laurenz Albe Oct 29 at 18:26
  • @LaurenzAlbe that was interesting and helpful! I've updated my post to reflect that I'm running the pgrowlocks query to identify the bad rows. Reading around the bad rows will be a pain in the butt but I see how its possible. – Freiheit Oct 29 at 21:27
  • There were Multixact data corruption bugs fixed in 9.4.9 and 9.4.13. There is some reason why the project recommends upgrading. – Laurenz Albe Oct 29 at 21:43
  • @LaurenzAlbe the rowlocks query also failed MirthResultsProd=# SELECT event_audit_key, event_date FROM event_audit_2019_10_01 AS m, pgrowlocks('event_audit_2019_10_01') AS p WHERE p.locked_row = m.ctid ; ERROR: MultiXactId 1074790449 does no longer exist -- apparent wraparound Do I need to run it and just get the primary key or ctid? – Freiheit Oct 30 at 13:03

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.