pg_cron Setup with Background Workers

Complete setup of pg_cron with background workers, including configuration settings, permission grants, and system restarts.

1. Install pg_cron

Install pg_cron on PostgreSQL 16:

sudo apt-get install postgresql-16-cron

2. Create pg_cron Extension

Create the extension in the target database:

CREATE EXTENSION pg_cron;

3. Grant Necessary Privileges

Grant privileges to the user running cron jobs (e.g., architect):

GRANT USAGE ON SCHEMA logs TO architect;
GRANT INSERT ON TABLE logs.cron TO architect;

4. Set Up .pgpass File

Create a .pgpass file for non-interactive authentication:

1. Switch to postgres user:

   sudo -i -u postgres

2. Create the file:

   echo "127.0.0.1:5432:empire:architect:yourpassword" > ~/.pgpass

3. Set permissions:

   chmod 600 ~/.pgpass

5. Configure postgresql.conf

Edit the configuration file:

sudo nano /etc/postgresql/16/main/postgresql.conf

Add these lines:

cron.use_background_workers = on
max_worker_processes = 20
cron.database_name = 'empire'

6. Update pg_hba.conf

Allow local connections for the cron user:

sudo nano /etc/postgresql/16/main/pg_hba.conf

Add:

host    all    architect    127.0.0.1/32    scram-sha-256

7. Restart PostgreSQL

sudo systemctl restart postgresql@16-main

8. Schedule Jobs

Schedule a job:

SELECT cron.schedule('logtime', '*/1 * * * *', 'INSERT INTO logs.cron (logtime) VALUES (NOW());');

9. Clean Up Old Records

Schedule cleanup of old job records:

SELECT cron.schedule('cleancronrecords', '0 12 * * *', $$DELETE FROM cron.job_run_details WHERE end_time < now() - interval '1 days'$$);

10. Monitor Jobs

Check PostgreSQL logs:

tail -f /var/log/postgresql/postgresql-16-main.log