Using pgagent with docker?

Question

I wanted to dive into the world of postgresql so I installed both postgresql and pgadmin using docker (with the portainer web ui) and I found out that for automatic jobs you gotta install pgagent too but I haven't been able to find any information on running it with docker.

I found this image https://hub.docker.com/r/chiavegatto/postgres-pgagent but portainer throws a 400 error code when trying to deploy the container which I haven't been able to figure out and I'm not quite sure of how this setup would work either.

I saw it executes the following command pgagent -f hostaddr=postgres dbname=test user=postgres port=5432 at the entrypoint but what if I want to schedule jobs on multiple databases?

• Has anyone been able to run pgagent with docker?
• Is there any image of postgresql that comes with the agent preinstalled?

Answer 1

I would suggest creating one from scratch.

Install pgagent inside the postgres container:

docker exec -it <postgresql_container> bash
apt-get update && apt-get install pgagent

Install the pgAgent extension:

pgsql -h hostname -U username -d db_name -f create_ext_pgagent.sql

create_ext_pgagent.sql:

CREATE EXTENSION pgagent IF NOT EXISTS;
CREATE LANGUAGE plpgsql IF NOT EXISTS;

Start pgagent:

pgagent hostaddr=localhost dbname=postgres user=<user_name> -s pgagent_log.log

Confirm that pgagent is connected to the database:

SELECT * FROM pgagent.pga_jobagent;

Setup pgpass: Note: your postgresql can differ depending on the distribution, Fedora family is: /var/lib/pgsql/<version_number>/data

sudo su postgres
echo localhost:5432:*:postgres:my_pwd >> /var/lib/postgresql/data/.pgpass
chmod 600 /var/lib/postgresql/data/.pgpass
chown postgres:postgres /var/lib/postgresql/data/.pgpass
export PGPASSFILE='/var/lib/postgresql/data/.pgpass'

Sources: [1] https://karatejb.blogspot.com/2020/04/postgresql-pgagent-scheduling-agent.html