Add pg_cron as an add-on?

[jjkoehorst]

First of all the image works great and I can finally start working on improving my SQL / timescale skills.

For reasons (e.g. remove sun.sun information older than 1 day) I was thinking of developing a cron job that runs every day / week or month and then removes the data older than 1 day related to certain entities. I would like to record it but it does not need to remain there forever (at the moment).

So one of the options is to use as far as I can tell is pg_cron however this is not available in pgadmin.

I added the module but not sure if something needs to be added to the image

OK: 24047 distinct packages available
(1/1) Installing postgresql-pg_cron (1.6.2-r0)
Executing postgresql-pg_cron-1.6.2-r0.post-install
*
* Add the following line to postgresql.conf and restart PostgreSQL:
*   shared_preload_libraries = 'pg_cron'
*
Executing postgresql-common-1.2-r1.trigger
OK: 947 MiB in 192 packages
[16:13:39] INFO: done
s6-rc: info: service init-user successfully started
s6-rc: info: service init-addon: starting
[16:13:39] INFO: done
[16:13:39] INFO: Tuning resources..
Using postgresql.conf at this path:
/data/postgres/postgresql.conf

Writing backup to:
max_connections = 25
/tmp/timescaledb_tune.backup202409081613

Recommendations based on 512.00 MB of available memory and 4 CPUs for PostgreSQL 16
timescaledb.last_tuned = '2024-09-08T16:13:40+02:00'
timescaledb.last_tuned_version = '0.15.0'
Saving changes to: /data/postgres/postgresql.conf
[16:13:40] INFO: done
[16:13:40] INFO: Applying max connections..
[16:13:40] INFO: done
s6-rc: info: service init-addon successfully started
s6-rc: info: service postgres: starting
s6-rc: info: service postgres successfully started
s6-rc: info: service pgagent: starting
s6-rc: info: service pgagent successfully started
s6-rc: info: service legacy-services: starting
[16:13:40] INFO: Starting PostgreSQL..
[16:13:40] INFO: done
s6-rc: info: service legacy-services successfully started
2024-09-08 14:13:40.418 UTC [806] LOG:  starting PostgreSQL 16.3 on aarch64-alpine-linux-musl, compiled by gcc (Alpine 13.2.1_git20240309) 13.2.1 20240309, 64-bit
2024-09-08 14:13:40.418 UTC [806] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2024-09-08 14:13:40.418 UTC [806] LOG:  listening on IPv6 address "::", port 5432
2024-09-08 14:13:40.425 UTC [806] LOG:  listening on Unix socket "/run/postgresql/.s.PGSQL.5432"
2024-09-08 14:13:40.438 UTC [816] LOG:  database system was shut down at 2024-09-08 14:13:27 UTC
2024-09-08 14:13:40.469 UTC [806] LOG:  database system is ready to accept connections
2024-09-08 14:13:40.481 UTC [819] LOG:  TimescaleDB background worker launcher connected to shared catalogs
[16:13:41] INFO: Starting PgAgent..
[16:13:41] INFO: Create database if not exist: 'homeassistant'
[16:13:41] INFO: done
[16:13:41] INFO: done
[16:13:41] NOTICE: TimescaleDb is running!
2024-09-08 14:13:58.122 UTC [866] ERROR:  unrecognized configuration parameter "cron.database_name"
2024-09-08 14:13:58.122 UTC [866] CONTEXT:  PL/pgSQL function inline_code_block line 3 at IF
2024-09-08 14:13:58.122 UTC [866] STATEMENT:  CREATE EXTENSION IF NOT EXISTS pg_cron;

2024-09-08 14:14:15.243 UTC [871] ERROR:  unrecognized configuration parameter "cron.database_name"
2024-09-08 14:14:15.243 UTC [871] CONTEXT:  PL/pgSQL function inline_code_block line 3 at IF
2024-09-08 14:14:15.243 UTC [871] STATEMENT:  CREATE EXTENSION IF NOT EXISTS pg_cron;

2024-09-08 14:18:40.537 UTC [814] LOG:  checkpoint starting: time
2024-09-08 14:19:00.955 UTC [814] LOG:  checkpoint complete: wrote 204 buffers (1.2%); 0 WAL file(s) added, 0 removed, 0 recycled; write=20.358 s, sync=0.010 s, total=20.419 s; sync files=36, longest=0.003 s, average=0.001 s; distance=1221 kB, estimate=1221 kB; lsn=5/9CB36120, redo lsn=5/9CAFE0D8
2024-09-08 14:23:41.043 UTC [814] LOG:  checkpoint starting: time
2024-09-08 14:23:55.054 UTC [814] LOG:  checkpoint complete: wrote 140 buffers (0.9%); 0 WAL file(s) added, 0 removed, 0 recycled; write=13.988 s, sync=0.006 s, total=14.011 s; sync files=16, longest=0.004 s, average=0.001 s; distance=922 kB, estimate=1191 kB; lsn=5/9CBFE248, redo lsn=5/9CBE49E0

[expaso]

pg_Cron indeed is not standard in this image.. but....

The Timecale Addon does come with pg_Agent preinstalled.

Together with the pgAdmin addon (or just pgAdmin installed on your workstation) you can create and schedule autonomous jobs like backups or any other scripts or even binaries.

See also it's documentation: https://www.pgadmin.org/docs/pgadmin4/development/pgagent_jobs.html

Once the jobs are setup using pgAdmin, they will just run independently:

You can add the pgAgent extension to the standard postgres database. The extension is already available:

Does this suit your needs?

If not, I could help you with getting pg_cron running.

[jjkoehorst]

Thanks a lot should be able to sort it out when I am at home.