EDR: PGSQL DB cannot be automatically vacuumed

[#1]

Environment

• EDR: All Supported Versions

Symptoms

• Errors found in /var/log/cb/pgsql/postgresql-<datetime>.log:

2020-12-12 01:32:14 EST [cb-sensorservices(233097) @ 10.220.40.8(60380)] WARNING: database "cb" must be vacuumed within 10989778 transactions

• After a period of time of the error above occurring, the error below can occur.  This is extremely rare, but if this does occur, please contact support for remediation:

2020-12-13 00:00:52 EST [cb-sensorservices(233092) @ 10.220.46.15(49520)] ERROR: database is not accepting commands to avoid wraparound data loss in database "cb"
2020-12-13 00:00:52 EST [cb-sensorservices(233092) @ 10.220.46.15(49520)] HINT: Stop the postmaster and vacuum that database in single-user mode.

• These errors were found to be an indication of what caused the vacuum to fail due to table corruption:

2020-11-28 00:04:26 EST [(259701) @ ] ERROR: uncommitted xmin 4252667839 from before xid cutoff 1830463911 needs to be frozen
2020-11-28 00:04:26 EST [(259701) @ ] CONTEXT: automatic vacuum of table "cb.public.<table_name>"

 

Cause

• PGSQL table corruption or an item stopping automatic vacuum.

Resolution

• If dealing with the first set of WARNINGS:
  • Find the appropriate tables to vacuum: EDR: How to find Postgres tables to vacuum
  • Vacuum tables:  EDR: How to vacuum sensor related Postgres tables
• If dealing with the second set of errors, please contact support for remediation.
  • At this point, the database can no longer could accept updates or deletes to the tables. Even a vacuum cannot complete if ran automatically or manually. This error can cause the system to essentially go down for any new updates or changes to the tables.
  • We have only seen this issue once and it was caused by a non-CB table being added to the CB product database, then becoming corrupted.
    • Creating additional tables within the CB database is not advised as we cannot properly manage user created tables within our code.