VmWare vCenter script to reduce /storage/seat size
root@Joker [ ~ ]# /opt/vmware/vpostgres/current/bin/psql -d VCDB -U postgres
psql.bin (9.6.16 (VMware Postgres 9.6.16.0-15334897 release))
Type “help” for help.
VCDB=# CREATE OR REPLACE FUNCTION event_full_cleanup_p ()
VCDB-# returns void as $$
VCDB$# DECLARE
VCDB$# event_partition VARCHAR (200);
VCDB$# BEGIN
VCDB$# TRUNCATE TABLE VPX_EVENT_PARTITION_LOOKUP;
VCDB$# –Truncate partitions
VCDB$# FOR part IN 1..92
VCDB$# LOOP
VCDB$# event_partition = ‘TRUNCATE TABLE VPX_EVENT_’ || CAST(part AS TEXT) || ‘ CASCADE ‘;
VCDB$# EXECUTE event_partition;
VCDB$# event_partition = ‘TRUNCATE TABLE VPX_EVENT_ARG_’ || CAST(part AS TEXT) || ‘ CASCADE ‘;
VCDB$# EXECUTE event_partition;
VCDB$# event_partition = ‘ANALYZE VPX_EVENT_’ || CAST(part AS TEXT);
VCDB$# EXECUTE event_partition;
VCDB$# event_partition = ‘ANALYZE VPX_EVENT_ARG_’ || CAST(part AS TEXT);
VCDB$# EXECUTE event_partition;
VCDB$# END LOOP;
VCDB$# TRUNCATE TABLE VPX_ENTITY_LAST_EVENT;
VCDB$# END
VCDB$# $$language plpgsql;
CREATE FUNCTION
VCDB=# select event_full_cleanup_p();
event_full_cleanup_p
————————————–
(1 row)
VCDB=# \q
This obv comes with no warantee or guarantee
it reduced our /storage/seat size from
/dev/mapper/seat_vg-seat 9.8G 8.7G 513M 95% /storage/seat
to
/dev/mapper/seat_vg-seat 9.8G 2.5G 6.8G 27% /storage/seat
the /storage/seat directory gets filled rather quickly if you run vCenter 6.7u3 unpatched for any length of time.