pg_cron Setup Guide

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

1. Install pg_cron

Ensure pg_cron is installed on your PostgreSQL 16 instance:

sudo apt-get install postgresql-16-cron

2. Create pg_cron Extension

Create the extension in the database where you want to run scheduled jobs:

CREATE EXTENSION pg_cron;

3. Grant Necessary Privileges

Ensure the user running cron jobs has sufficient privileges:

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:

sudo -i -u postgres
echo "127.0.0.1:5432:empire:architect:yourpassword" > ~/.pgpass
chmod 600 ~/.pgpass

5. Configure PostgreSQL

Edit postgresql.conf to configure pg_cron with background workers:

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

Add:

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

6. Update pg_hba.conf

Allow local connections for the user:

host    all    architect    127.0.0.1/32    scram-sha-256

7. Restart PostgreSQL

sudo systemctl restart postgresql@16-main

8. Schedule Jobs

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

9. Monitor Jobs

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