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!
LVL 15
Faiga DiegelSr Database EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.

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.