Re: Getting the list of foreign keys (for deleting data from the database)

On 08/02/2015 05:25 PM, Adrian Klaver wrote:
> On 08/02/2015 08:04 AM, Mario Splivalo wrote:
>> 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).
>
> I have an answer of sorts below.
>
> I do have some questions in the meantime though.
>
> What is the purpose of an ERP that has no history?
>
> In particular how do you do the P(lan) part without reference to the past?

I don't need that data in the 'current' database - it makes backups and
archiving harder. The customers can still access 'old' databases if they
need to check data that exists there.

>>
>> 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.
>
> My guess is for this case it will be less resource intensive to just do
> the DELETE(s), in smaller batches then a year, then to replicate the
> referential integrity in your own code.

Yup, that would work. Actually, I am using that approach on some other
databases, I have a cronjob that runs every hour that deletes all data
older than 8765 hours from the database, thus keeping only the
year-worth of data.

Unfortunately, I inherited this and I need to 'purge' old data from the
database.

Mario
--
Mario Splivalo
mario(at)splivalo(dot)hr

"I can do it quick, I can do it cheap, I can do it well. Pick any two."