EDR: How to Vacuum Sensor Related Postgres Tables

Products

• Carbon Black EDR (formerly Cb Response)

Issue/Introduction

Postgres tables can get filled with dead entries which cause Postgres queries to take a long time to complete.  In EDR 7.6.x and higher, maintenance cronjobs routinely run in the background to identify dead entries in Postgres and perform safe vacuums to keep Postgres running efficiently. 
However, if the following symptoms are noted it is best to check the Postgres tables for large tables and possibly run the vacuum tool outside of the maintenance window.

Symptoms:

• Odd behavior on some pages in the console. Sensor Details, Groups, Triage Alerts for example
• Sensors showing offline that are connected and sending data
• Sensor Groups not saving new settings

Environment

• EDR Server: 7.4.x and higher

Resolution

1. Stop EDR services. 

Standalone:
# sudo systemctl stop cb-enterprise
Cluster:
# sudo /usr/share/cb/cbcluster stop

2. Confirm all cb-enterprise services are down prior to vacuuming.
    *  Important Note: Only cb-pgsql should be running. Failure to check could result in corrupting the Postgres database. This is a safe procedure when only cb-pgsql is running.  

Standalone:
# ps ef | grep cb
Cluster: 
# sudo /usr/share/cb/cbcluster status

3. Only cb-pgsql should be running.   Start only the Postgres service on the Standalone or Primary server.

# /usr/share/cb/cbservice cb-pgsql start

4. Determine which tables are too large.  View a list of tables ordered from largest to smallest.

psql -p 5002 cb -c "SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_relation_size(C.oid)) AS "size" 
FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') 
ORDER BY pg_relation_size(C.oid) DESC;"

     *  Typically if a table needs to be vacuumed, it will be much larger than most tables. The command to vacuum a table is below:

psql -p 5002 -d cb -c "vacuum (full, analyze, verbose) <TABLE_NAME>;"

Examples (these two tables can be very large):
psql -p 5002 -d cb -c "vacuum (full, analyze, verbose) sensor_registrations;" 
psql -p 5002 -d cb -c "vacuum (full, analyze, verbose) sensor_queued_data_stats;"

5. When the commands from Step 4 are complete, stop Postgres.

# sudo /usr/share/cb/cbservice cb-pgsql stop

6. Restart EDR.  

Standalone:
# sudo systemctl start cb-enterprise

Cluster:
# sudo /usr/share/cb/cbcluster start

Additional Information

• Confirm all cb-enterprise services are down. Only cb-pgsql should be running. Failure to check could result in corrupting the postgres database. This is a safe procedure when only cb-pgsql is running.
• Postgres runs a vacuum as part of normal operations. If a table gets too large, the auto vacuum will time out and not complete.
• To vacuum all Postgres tables. 

  psql -p 5002 -d cb -c "vacuum (full, analyze, verbose);"