PGSQL DB cannot be automatically vacuumed

Products

• Carbon Black EDR (formerly Cb Response)

Issue/Introduction

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

2020-12-12 01:32:14 EST [cb-sensorservices(233097) @ ???.???.???.???(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) @ ???.???.???.???(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) @ ???.???.???.???(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>"

 

Environment

• EDR: All Supported Versions

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: How to find Postgres tables to vacuum
  • Vacuum tables:  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.