This script contains the steps needed to shrink a massive table on postgres when disks did not have enough room to run a VACUUM FULL after major deletions.

shrink-postgres-table-disk-usage-with-full-disks.sh

#!/bin/bash
# This script contains the steps needed to shrink a massive table on postgres
# when disks did not have enough room to run a VACUUM FULL after major deletions.
# by: Cody Kochmann

# NOTE - These commands were run manually and will need situation specific
#        options added if they were ever to be reused. This gist was only to
#        capture the gist of the steps needed.

# set a date to clear logs to
earliest_date_to_preserve='2024-01-01'

# delete every entry before my desired minimum date (any vacuuming or reindexing right here will still read and preserve all of the empty records on disk)
psql --dbname Syslog "delete from systemevents where receivedat<'${earliest_date_to_preserve}';"

# build the backup of the systemevents table which yielded roughly 1:20 compression ratio for the whole table
pg_backup <options> systemevents > systemevents.bak.sql                            

# empty every record in the systemevents table and allow a full vacuum to fully refresh indexes and table layouts in Syslog db
psql --dbname Syslog 'delete from systemevents; vacuum full;'

# restore the backup to the empty table
pg_restore <options> systemevents.bak.sql