From: | Mario Splivalo <mario(at)splivalo(dot)hr> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Cc: | adrian(dot)klaver(at)aklaver(dot)com |
Subject: | Re: Getting the list of foreign keys (for deleting data from the database) |
Date: | 2015-08-02 15:44:11 |
Message-ID: | 55BE3ACB.6030909@splivalo.hr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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."
From | Date | Subject | |
---|---|---|---|
Next Message | Mario Splivalo | 2015-08-02 15:48:28 | Re: Re: Getting the list of foreign keys (for deleting data from the database) |
Previous Message | Adrian Klaver | 2015-08-02 15:25:07 | Re: Getting the list of foreign keys (for deleting data from the database) |