Solved

Trigger to delete old data in PostgreSQL

Posted on 2013-06-04
1
571 Views
Last Modified: 2013-08-07
I am trying to figure out the best way to schedule a data deletion on our production database.

First option I can see is by a trigger. I am not a PostgreSQL developer, but since I need to maintain our growing database, how can I create trigger to delete say data older than X days or retain only X number of transactions/events? I'm looking to see a parameterize trigger for us to define X number of days or X number of transactions.

The draw back I see using the trigger is that it is event driven on a CRUD statements. Meaning during insert, delete, or update, trigger will be fired off (that is how as far as I do understand a general trigger is in any of database engine, unless otherwise PostgreSQL has a different way of implementing this). Why do I say this is a draw back? --> Coz I want to trigger the deletion on during off peak hours.  

Or is there any other brilliant idea/implementation aside from trigger? I did try the scheduling a job thru pgAdmin but the draw back is I can't script the job (like how we usually do it on MS SQL) to create and deploy it. We have almost a thousand machines to deploy this (using our deployment script/technology), so creating a pgAdmin job will be a pain in the butt for us if scripting the job is not possible.

Our environment is Windows OS with pgAdmin 9.1. The implementation I am thinking of should consider when Windows OS is swapped with Linux/Unix environment in furure, so I am not leaning towards Windows scheduler job to execute batch script, etc.


Thanks!
0
Comment
Question by:faiga16
1 Comment
 
LVL 22

Accepted Solution

by:
earth man2 earned 500 total points
ID: 39224033
Your best bet is to use a stored pl/sql function to do the data deletion. Any complicated SQL goes into that function.  Then all you need to do is a simple SQL command "select do_data_delete_101();" using the psql.exe command line utility via the "at" windows command see "at /?"  At allows you to specify the remote computer.

You also have the at command in unix ...

More advanced methods would involve data partitioning, which would minimise the time that the database is in a state where you can't get a lock on that data.
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Postgresql error 57014 5 465
Trouble migrating PostgreSQL on another server 2 390
dependency tree of tables in PostgreSQL 4 1,162
calculate change in each day in postgresql 3 341
Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question