Drone database periodical maintenance

Drone Support
1

What would be the advised way of performing regular database maintenance every once in a while? i.e: removing very old builds, removing legacy deployments which are not longer used, etc?

2

Hi there, Here is the sample query that might help

-- delete old logs
DELETE FROM logs WHERE log_job_id IN (
  SELECT proc_id FROM procs WHERE proc_build_id IN (
    SELECT build_id
    FROM repos, builds
    WHERE repo_id = build_repo_id 
    AND build_number < repo_counter - 20
  )
);

-- delete old procs
DELETE FROM procs WHERE proc_build_id IN (
  SELECT build_id
  FROM repos, builds
  WHERE repo_id = build_repo_id 
  AND build_number < repo_counter - 20
);

-- delete old builds
DELETE FROM builds WHERE build_id IN (
  SELECT build_id
  FROM repos, builds
  WHERE repo_id = build_repo_id 
  AND build_number < repo_counter - 20
);

Also check an issue already reported earlier with possible solutions: https://github.com/drone/drone/issues/2440

2 Likes
3

Thanks so much for the sample and also for the link! All I needed. :slight_smile: