If you are not familiar with it already, Bucardo is a nifty multi-master replication system for PostgreSQL, written by Greg Sabino Mullane. Written in Perl, it is great if you need replication that doesn’t have the restrictions associated with PG 9’s streaming replication.
To keep your Bucardo installation clean and tidy, a few regular cron jobs are required. One of them cleans up the archived replicated data (stored in a separate database by Bucardo) once you know you are done with it.
The Bucardo page above has a recommended script using all sorts of bashing, but I wanted something a bit more pure-PostgreSQL; it also doesn’t purge more than one old table at a time. So, I whipped up the following PL/pgSQL function.
(Note that this is for Bucardo 4.4. I haven’t played with the forthcoming Bucardo 5, so I’m not sure if this is still required.)
1 CREATE OR REPLACE FUNCTION bucardo.purge_frozen_child_qs(far_back interval) 2 RETURNS SETOF TEXT AS 3 $purge_frozen_child_qs$ 4 DECLARE 5 t TEXT; 6 qt TEXT; 7 BEGIN 8 9 IF far_back IS NULL THEN 10 RAISE EXCEPTION 'Interval cannot be null.' 11 USING HINT = 'So, do not do that.'; 12 END IF; 13 14 IF (now() + far_back) > now() THEN 15 RAISE EXCEPTION 'Interval must be negative.' 16 USING HINT = 'Consider using the "ago" form of intervals.'; 17 END IF; 18 19 FOR t IN 20 SELECT tablename 21 FROM pg_tables 22 WHERE schemaname='freezer' 23 AND tablename like 'child_q_%' 24 AND (replace(tablename, 'child_q_', '')::timestamp with time zone) < now() + far_back::interval 25 ORDER BY tablename 26 LOOP 27 qt := 'freezer.' || t; 28 EXECUTE 'DROP TABLE ' || qt; 29 RETURN NEXT qt; 30 END LOOP; 31 32 DELETE FROM bucardo.q 33 WHERE (started < now() + far_back::interval 34 OR ended < now() + far_back::interval 35 OR aborted < now() + far_back::interval 36 OR cdate < now() + far_back::interval) 37 AND (ended IS NULL OR aborted IS NULL); 38 39 RETURN; 40 41 END 42 $purge_frozen_child_qs$ 43 LANGUAGE plpgsql 44 VOLATILE;
To use it, just call it repeatedly from a cron job with the appropriate argument, along the lines of:
1 SELECT * FROM bucardo.purge_frozen_child_qs('7 days ago'::interval);
It returns the names of the tables it deleted.
This particular function doesn’t need to be run more often than once a day. And it keeps your Bucardo goats nice and clean.
(A “bucardo” is a now-extinct species of goat. For why Bucardo is goat-related, ask Greg.)
Comments
Greg · 28 September 2011
Thanks, but I didn't originally write it, Jon Jensen did. But I've certainly modified the original version a bit. You are correct to ask about Bucardo 5 - the function won't be needed, as both the q and the child_q tables are no more. Although bucardo_purge_delta() still needs to be run, the MCP will spawn a process to handle that for you in Bucardo 5. Which means that you should not need any maintenance cronjobs at all.