systemcr Posted December 31, 2019 Posted December 31, 2019 Hello everyone, I need to perform some daily "maintenance" tasks on my PostgreSQL database that include mainly deleting old data and backuping some tables. I've read that PostgreSQL does not include any built-in feature to schedule these kind of tasks, similar to what can be achieved on Microsoft SQL Server through the use of jobs. However, I've read there's an extension called pg_cron [1] that can be used to achieve this in PostgreSQL. Is it possible to use it within the PostgreSQL databases we're provided on Johnny? Thanks in advance! [1] https://github.com/citusdata/pg_cron
wolstech Posted December 31, 2019 Posted December 31, 2019 Would a regular cron and a PHP script or regular program with code to carry out the task work? The SQL Server on Lily doesn't support scheduling either (the free version of SQL Server lacks this function). The servers do support cron (2 per day), and Lily supports scheduled tasks (currently no limit as long as the task is reasonable and doesn't hog the server).
systemcr Posted December 31, 2019 Author Posted December 31, 2019 Hi Wolstech, Yes, I've read about cron jobs but I was thinking in more in some alternative "self-contained" in the database. However, it seems a good approach to me what you say about using scheduled tasks on Lily or schedule cron jobs in any other server. About scheduling tasks on Lily, has anyone done this before? So that I can search in the forum first prior asking for explanation on how to do that.
wolstech Posted December 31, 2019 Posted December 31, 2019 Lily has no user configuration for much of anything. I probably will have to create it for you once you upload the files. Just let me know the command to run and the schedule at which it should be run and I can schedule it. If your software will use pg though, you might just want to make a script that runs on Johnny. That way it's not dependent on both servers to work.
systemcr Posted January 1, 2020 Author Posted January 1, 2020 Hi Wolstech, I'd prefer to be one to schedule the maintenance scheduled tasks, because if something would fail, I'd like to fix it by myself as soon as possible to minimize my customer's impact. Can this also be escalated to Krydos? So that I can take into consideration his opinion about this issue as well.
wolstech Posted January 1, 2020 Posted January 1, 2020 Escalating. Do note that if you use a scheduled task on Lily, you can fix it yourself by simply editing the file it points to. You just won't be able to change the schedule yourself. 1
Krydos Posted January 3, 2020 Posted January 3, 2020 My opinion is to just use cron and a scripting language, such as php, to execute your postgresql queries.
systemcr Posted January 3, 2020 Author Posted January 3, 2020 Hi, I will then configure two cron jobs from cPanel. Instead of using php, could I use psql to connect to the database like this Stackoverflow says? https://dba.stackexchange.com/a/157992 If so, I would like to know how and where I can place the .sql file to be given to the psql command. Thanks!
Recommended Posts