EDR: How to find Postgres tables to vacuum

Products

• Carbon Black EDR (formerly Cb Response)

Issue/Introduction

Find candidate tables in Postgres that need to be manually vacuumed

Environment

• EDR Server: 6.x and Higher (formerly CB Response)

Resolution

• 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 does need to be vacuumed, it will be much larger than most tables.

Additional Information

• Typically the largest table will be the sensor_registrations table
• The command to vacuum a table will be the following. Services should be stopped before performing the vacuum

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

• If not on the customer's server, the output can be piped to a file and uploaded to alliance 

  • 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;" > cb_table_size.csv; /usr/share/cb/cbpost cb_table_size.csv; rm -f cb_table_size.csv