I have a large, in-house built, ERP system that I need to clean up from
old/stale data.
As all the tables are FK-related I could do 'DELETE FROM' from the
top-most table (invoices, or stock documents, or whatever) to remove all
data from all the related tables, but that is, of course, extremely slow
(The datadir is around 20GB in size, and I need to remove 4/5 of the
data from the database - fiscal years 2014, 2013, 2012 and 2011 - only
2015 should remain).
Instead of doing DELETE FROM table WHERE date_created < '2015-01-01' I
was thinking of doing something like this:
SELECT foo_drop_all_constraints();
SELECT * FROM table INTO table_copy WHERE date_created >= '2015-01-01';
DROP TABLE table;
ALTER TABLE table_copy RENAME TO table;
SELECT foo_restore_all_constraints();
Of course, this is simple if I have only one table, but when there is
over 400 tables that are 'linked' with foreign keys, things get a bit
complicated.
Suppose I have a table_detail that has column table_id which is FK
pointing to table(id), I would need to do something like this:
SELECT foo_drop_all_constraints();
SELECT * FROM table INTO table_copy WHERE date_created >= '2015-01-01';
SELECT table_detail.* INTO table_detail_copy FROM table_detail JOIN
table_copy ON table_detail.table_id = table_copy.id
DROP TABLE table;
DROP TABLE table_copy
ALTER TABLE table_copy RENAME TO table;
ALTER TABLE table_detail_copy TO table_detail;
SELECT foo_restore_all_constraints();
Now, what am I asking is - is there a tool which would help me find all
the _detail tables? I know I could query pg_constraints and similar
views but before I go onto hacking into those I'm wondering if there is
something that could aid me in doing so.
Of course, if this is not the best approach I'd appreciate different
views/opinions.
Mario
--
Mario Splivalo
mario@splivalo.hr
"I can do it quick, I can do it cheap, I can do it well. Pick any two."