Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 615
  • Last Modified:

Trigger to delete old data in PostgreSQL

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
Faiga Diegel
Asked:
Faiga Diegel
1 Solution
 
earth man2Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now