pg_cron wiki

To schedule database related job we use the postgres extension pg_cron. pg_cron is automatically installed/added to the postgres image extension library, but needs to be manually added to the database in which the job should be scheduled, which requires these two steps:

1. Specifying database name in postgres dockerfile: CMD ["postgres", \ "-c", "shared_preload_libraries=pg_cron", \ "-c", "cron.database_name=sensors"]
2. Adding CREATE EXTENSION IF NOT EXISTS pg_cron; to database init_sql file.

Once pg_cron extension has been added to database a function to schedule needs to be created. There are many ways to do this, but procedural SQL is recommended. This function will contain the actual SQL logic.

To schedule the job, use the metod cron.schedule, and pass the job name (not the function name, this is the name the scheduled job will get), a cron schedule expression (check Crontab), and the function name:

CREATE OR REPLACE FUNCTION delete_everything() RETURNS void AS $$
    BEGIN
        DELETE FROM very_important_table;
    END;
$$ LANGUAGE plpgsql;

SELECT cron.schedule(
    'very_good_job',
    '* * * * *',  -- This will run every minute, every day
    $$
        SELECT delete_everything();
    $$
);

To confirm that the job was successfully scheduled, enter the postgres container and the relevant database, then enter the command select * from cron.job;.

More advanced scheduled tasks (WIP, no advanced tasks have been found yet)

We should be able to use ASP.NET built-in scheduling, requiring no additional dependencies. This approach is more in line with how our project is structured and would allow our scheduling logic to be more understandable and more easily documented. It would also allow us to more easily integrate user/role-specific tasks which need to be scheduled.