In rare cases we have seen PostgreSQL (Pg) instances attached to SaltStack Enterprise consume an inordinate amount of disk space for no clear reason. If the normal automatic database trim routines built in to SSE are running regularly this situation can be caused by Pg's standard automatic vacuum process not being aggressive enough when releasing deleted row space back to the OS. To force a more aggressive cleanup follow these steps.
- Login to the Pg instance as the Pg user that owns your SSE database. In this example we will assume that user is named
psql --user salt raas_<your raas customer_id>
- Verify there is a reasonable amount of disk space available. The amount of free space should be equal to or greater than the amount of space consumed by the largest table in any of Pg's databases. The following query should show the amount of space consumed by every table in the database.
SELECT table_schema, table_name, pg_size_pretty(total_bytes) AS total , pg_size_pretty(table_bytes) AS TABLE FROM ( SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM ( SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME , c.reltuples AS row_estimate , pg_total_relation_size(c.oid) AS total_bytes , pg_indexes_size(c.oid) AS index_bytes , pg_total_relation_size(reltoastrelid) AS toast_bytes FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE relkind = 'r' ) a ) a order by total_bytes desc;
- If you have sufficient space, run the following command at the psql prompt:
When the psql prompt returns you should see that more disk space is available.
- You may see warnings like these
WARNING: skipping "pg_authid" --- only superuser can vacuum it WARNING: skipping "pg_database" --- only superuser can vacuum it WARNING: skipping "pg_db_role_setting" --- only superuser can vacuum it
These indicate that this user doesn't have Pg superuser access and thus cannot
vacuum all tables. They may be safely ignored, or you may login to Pg as a user with superuser privileges and re-
- If you do not appear to have sufficient space you can run a full vacuum on individual smaller tables until enough space is freed up to vacuum the larger tables. For example:
vacuum full jobs;