Postgres job scheduling as unprivileged user
Maybe it’s just me. Maybe everyone else has the ability to understand everything from poor documentation. But I can’t. And so, when I wanted to install pgAgent as an unprivileged user, I ran into some trouble.
Some backstory here:
Many dbms’s offer a built in task scheduler, for maintenance, or automation. Postgres does not have any sort of scheduler. According to Postgres documentation:
“It is the database administrator’s responsibility to set up appropriate scripts, and to check that they execute successfully… PostgreSQL is low-maintenance compared to some other database management systems.”
Their suggestion is to manage automated tasks with a simple crontab. While this is fine for some use cases, many times we want something built in. For example, with DBaaS in a cloud, where the end user has no access to the database server, the user cannot use a crontab. Another problem is that crontab is centralized, relying on one server which will run all of the jobs, while jobs created with a built in scheduler can survive any particular node’s failure, because they are part of the database cluster, with replication and backups.
Thankfully, in order to achieve this functionality, we have postgres’s support of extensions, two of which offer job scheduling in the database. Both options are good, but work differently, both in how they are installed and how they are used. They are:
pg_cron is the simpler tool, in terms of usage and installation. However, it is less powerful. pgAgent, while more complex to set up and use, allows for much finer control. Another important difference is that pg_cron is CLI only, while pgAgent is GUI only. For a brief comparison of the two, see the following table:
https://gist.github.com/k-caps/27f9e4f3504a974ee14bb8f8762f68ef
Overall, for simple recurring tasks such as running a maintenance script, pg_cron is better, while for a complex, logic heavy routine such as DB tuning with multiple procedures, pgAgent is better suited.
The problem is that pg_cron requires a database restart, which is naturally undesirable in production, while configuring pgAgent to run without superuser access to manage jobs is not clear from the documentation. (At least, it wasn’t to me.) So, this post aims to simplify the steps needed to install pgAgent on a production system where the end user has no superuser access.
Bash and Ansible scripts to run the installation can be found on my github, here. In short, (assuming that the end user has access to a role with a database) Install pgAgent on the user’s database and NOT on “postgres”. Then when you go to pgAdmin to connect, set as your “maintance DB” the same database that you installed the pgagent extension on.
pg_cron, being simpler, simply requires changing the hba file and the postgresql.conf, before restarting postgres itself. Steps can be found here.
I hope this helps someone, and please let me know if there are other ways to do this!