Message-ID: <55BE3ACB.6030909@splivalo.hr>
Date: Sun, 02 Aug 2015 17:44:11 +0200
From: Mario Splivalo <mario@splivalo.hr>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Thunderbird/31.8.0
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
CC: adrian.klaver@aklaver.com
Subject: Re: Getting the list of foreign keys (for deleting data from
 the database)
References: <55BE3180.5020600@splivalo.hr> <55BE3653.8020207@aklaver.com>
In-Reply-To: <55BE3653.8020207@aklaver.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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@splivalo.hr

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


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql