Simple job scheduler for PostgreSQL like the Agentjob for Microsoft SQL08.01.25

After trying pgAgent I tried pg_cron and find pg_cron is more straight forward and easy to maintain.
It will create a database called cron with 2 tables, 
* job
* job_run_details (log)

pg_cron is a transparent and clear cron-based job scheduler for PostgreSQL (10 or higher) that runs inside the database as an extension. It uses the same syntax as regular cron, but it allows you to schedule PostgreSQL commands directly from the database. You can also use '[1-59] seconds' to schedule a job based on an interval.

## Install pg_cron in debian
```
sudo apt-get -y install postgresql-17-cron
```

## add /etc/postgres/17/main/postgresql.conf
```
shared_preload_libraries = 'pg_cron'
cron.database_name = 'postgres'
log_timezone = 'Asia/Bangkok'
```

## add a .pgpass file to the postgres home folder /var/lib/postgresql
```
localhost:5435:foodb:foouser:foopass

```

##After restarting PostgreSQL, you can create the pg_cron functions and metadata tables using CREATE EXTENSION pg_cron.
```
CREATE EXTENSION pg_cron;
```
## View your jobs
```
select * from cron.job;
```

## View the logs from a job
```
SELECT * FROM cron.job_run_details WHERE jobid = 1;

```

https://github.com/citusdata/pg_cron?ysclid=m5n82e3veo778136600

Anonymous

Simple job scheduler for PostgreSQL like the Agentjob for Microsoft SQL08.01.25

Information Epoch 1737543096