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
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