EDR: How to find Postgres tables to vacuum
search cancel

EDR: How to find Postgres tables to vacuum

book

Article ID: 285296

calendar_today

Updated On:

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