PostgreSQL Database file system space is filling up rapidly

Products

• APP PERF MANAGEMENT
• CA Application Performance Management Agent (APM / Wily / Introscope)
• CUSTOMER EXPERIENCE MANAGER
• INTROSCOPE

Issue/Introduction

Postgres Database file system is at 96% usage currently, need help to delete any older tables, data or archive. Two of the biggest tables are at_stories and at_evidences in APMDB. Each one is several GB. Would like to delete data from these tables.

Cause

This issue was due to the customer not managing alerts and errors from the application.  We found the customer had Alerts that the Normal Metrics where higher that the thresholds of the alert so these alert added to the database and also they where seeing many errors from the application and not addressing the application.

We did do a few things to help reduce this problem and that is what this article is hoping to address.

Environment

APM 10.5

Resolution

1. In the IntroscopeEnterpriseManager.properties files we recommended to set the following parameters
introscope.apm.data.agingTime=1 DAY  
introscope.apm.alert.preserving.time=30 days  
introscope.apm.data.preserving.time=30 days  ---> This by default is 365 days.  
Reference: Team Center Map Configuration
2. We used these sql statements establish a baseline and know which table to focus on.

SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;

This will return the 20 largets tables in your database. This will take a long time to run please be patient

SELECT relname AS "relation", pg_size_pretty (pg_total_relation_size (C .oid)) AS "total_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C .relnamespace)  WHERE nspname NOT IN ('pg_catalog','information_schema')

3. If there is no enough disk space on the db server to run a full VACUUM (need space for temp tables), there may be two options to work around this:

1. Backup and Restore the database. Restoring the databases, tables, indexes will free up space and defragment
2. Point the temp tables to a different disk, for eg:

CREATE TABLESPACE tempspace LOCATION '/path/to/new/folder';

Reference: https://dba.stackexchange.com/questions/17057/i-need-to-run-vacuum-full-with-no-available-disk-space