During recent vCloud Director cell upgrades and database migrations it was noticed the size of DB increased drastically and the amount of time needed to migrate a DB from SQL Server database to PostgreSQL database specially during creation and data migration of TABLE “audit_event”
Normally I let this process run unattended until it completes minutes later but after seeing DB migration was still running I decided to check what was going on
Once DB was fully migrated to PostgreSQL I run queries to check number of events and dates in the table. Having multiple solutions integrated with vCloud Director and user sessions puts some load on the “audit_event” table
vCloud Director 10.0 no longer keeps audit events indefinitely in the database. The default number of days is 45 and the maximum is 60. vCloud Director 10.0 maintains in the database the audit events collected from environments prior to version 10.0. You can export the audit event information in CSV format by using the cell-management-tool export-audit-events.
Before doing any DB maintenance take a backup of your DB
Running queries using PGAdmin
- Open PGAdmin and connect to PostgreSQL server
- Right click your database and select “Query Tool”
- Execute queries like below examples to get total count of events, date range and delete events in desired timeframe
# Count of events in audit_event
SELECT count(*) from audit_event;
# Date of events in audit_event
SELECT * from audit_event order by event_time;
# Delete events in selected range
DELETE from audit_event WHERE event_time < '2020-02-01 06:00:00.000';That is all you need to keep your DB audit events table with healthy size