agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Mario Splivalo <mario@splivalo.hr>
To: pgsql-sql@postgresql.org
Cc: adrian.klaver@aklaver.com
Subject: Re: Getting the list of foreign keys (for deleting data from the database)
Date: Sun, 02 Aug 2015 17:44:11 +0200
Message-ID: <55BE3ACB.6030909@splivalo.hr> (raw)
In-Reply-To: <55BE3653.8020207@aklaver.com>
References: <55BE3180.5020600@splivalo.hr>
	<55BE3653.8020207@aklaver.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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@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

view thread (5+ messages)  latest in thread

Message-ID: <55BE3ACB.6030909@splivalo.hr>
Permalink:  ../55BE3ACB.6030909@splivalo.hr/
Also on:    postgresql.org/message-id/55BE3ACB.6030909@splivalo.hr

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: mario@splivalo.hr, adrian.klaver@aklaver.com
  Subject: Re: Getting the list of foreign keys (for deleting data from the database)
  In-Reply-To: <55BE3ACB.6030909@splivalo.hr>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox