Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save CodyKochmann/81e502c8d6430fce9f181df9ff8918f9 to your computer and use it in GitHub Desktop.
Save CodyKochmann/81e502c8d6430fce9f181df9ff8918f9 to your computer and use it in GitHub Desktop.
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.
#!/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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment