Solved

Trigger to delete old data in PostgreSQL

Posted on 2013-06-04
1
549 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now