Buildbot database cleanup script for postgresql

cleanup-db-postgres.sql

BEGIN;

-- Delete all sourcestamps without any changes.
DELETE FROM sourcestamps WHERE id NOT IN (SELECT sourcestampid FROM changes);

-- Modify the foreign key constraint on changes.parent_changeids.
-- This is mandatory to allow deleting old changes without deleting all
-- "child" changes in cascade.
ALTER TABLE ONLY changes DROP CONSTRAINT changes_parent_changeids_fkey;

ALTER TABLE ONLY changes ADD CONSTRAINT changes_parent_changeids_fkey
FOREIGN KEY (parent_changeids) REFERENCES changes(changeid) ON DELETE SET NULL;

-- Delete soucestamps older than 30 days.
-- The starting point is not "now" but the most recent sourcestamp.
DELETE FROM sourcestamps
WHERE created_at < (SELECT max(created_at) - (30 * 24 * 60 * 60) FROM sourcestamps);

-- Delete buildsets that have no link to a sourcestamp.
DELETE FROM buildsets
WHERE id NOT IN (SELECT buildsetid FROM buildset_sourcestamps);

-- Delete builders that are not referenced by any build.
DELETE FROM builders
WHERE id NOT IN (SELECT builderid FROM builder_masters)
AND id NOT IN (SELECT builderid FROM builds);

COMMIT;