Working with pgAgent

Introduction
Currently available only in aidboxdb:13.2 or later.
PgAgent provides an ability to run user-defined jobs on the Postgres database. It runs as a separate process on a system with Postgres databases.
Aidboxdb comes with PgAgent preinstalled.
By default, aidboxdb container doesn't start the PgAgent daemon. To start the daemon you need to specify PGAGENT_ENABLED variable. If the variable is specified then the daemon will be started on container startup.
PgAgent wouldn't run if Postgres instance is run as a replica (PG_ROLE is not equal to 'replica') because it may lead to unexpected behavior.
PgAgent will run on every instance of Postgres master, which may lead to unexpected behavior.
By default PgAgent process in aidboxdb will use a database specified in POSTGRES_DB environmental variable and will run as a user which is specified in POSTGRES_USER variable (postgres by default).
If you want to specify a dedicated user for PgAgent (for example to limit PgAgent privileges), you need to specify PGAGENT_USER and PGAGENT_PASSWORD variables.
If you want to use a dedicated database to store PgAgent service data, you can specify it in PGAGENT_DB variable. But you have to create the database and extension manually before starting PgAgent.
Note: if you want to use a dedicated user or database you need to create them manually before enabling PgAgent in your aidboxdb configuration.
Don't forget to create the PgAgent extension if your choose to use a dedicated database.
To create the extension run CREATE EXTENSION pgagent;
There are some more options available to configure PgAgent:
You can specify log level with PGAGENT_LOG_LEVEL and you can specify a file where PgAagent logs are written in PGAGENT_LOG_FILE_PATH.
You can see more info on configuring aidboxdb.
Step-by-step example with docker
In this example we will setup simple pgagent job with dedicated pgagent database and user.
Configuring PgAgent
First, we will need to create an initial docker-compose configuration:
docker-compose.yaml
1
services:
2
  db:
3
    container_name: aidboxdb
4
    image: healthsamurai/aidboxdb:13.2
5
    ports:
6
      - '5432:5432'
7
    environment:
8
      POSTGRES_USER: postgres
9
      POSTGRES_PASSWORD: postgres
10
      POSTGRES_DB: postgres
Copied!
Start container:
1
docker-compose up -d
Copied!
Now let's create a database, extension, test table, and user.
Connect to postgres with:
1
docker exec -it aidboxdb ps
Copied!
And run following commands:
1
CREATE DATABASE pgagent;
2
\c pgagent
3
​
4
CREATE EXTENSION pgagent;
5
​
6
CREATE TABLE test(tx timestamptz default now(), note text);
7
​
8
CREATE USER "pgagent" WITH
9
  LOGIN
10
  NOSUPERUSER
11
  INHERIT
12
  NOCREATEDB
13
  NOCREATEROLE
14
  NOREPLICATION
15
  encrypted password 'secret';
16
​
17
GRANT USAGE ON SCHEMA pgagent TO pgagent;
18
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA pgagent TO pgagent;
19
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA pgagent TO pgagent;
20
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO pgagent;
21
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO pgagent;
Copied!
After you've run commands updatedocker-compose.yaml file:
docker-compose.yaml
1
services:
2
  db:
3
    container_name: aidboxdb
4
    image: 'healthsamurai/aidboxdb:13.2'
5
    ports:
6
      - '5432:5432'
7
    environment:
8
      POSTGRES_USER: postgres
9
      POSTGRES_PASSWORD: postgres
10
      POSTGRES_DB: postgres
11
      PGAGENT_ENABLED: 'true' # Must be string
12
      PGAGENT_USER: pgagent
13
      PGAGENT_PASSWORD: secret
14
      PGAGENT_DB: pgagent
15
      PGAGENT_LOG_LEVEL: 2
Copied!
And re-create container:
1
docker-compose up -d
Copied!
Let's exec into the container and see what is going on.
1
docker exec -it aidboxdb bash
Copied!
First let's run pgrep command:
Bash
Response
1
pgrep pgagent
Copied!
1
9
Copied!
You can use ps aux command if you want to see more details on pgagent arguments:
Bash
Response
1
ps aux | grep pgagent
Copied!
1
pgagent -s /tmp/pgagent.log -l 1 host=localhost port=5432 dbname=pgagent user=pgagent password=secret
Copied!
Let's check pgagent logs:
Plain Text
Logs
1
cat /tmp/pgagent.logs
Copied!
1
WARNING: Couldn't create the primary connection [Attempt #2]
2
DEBUG: Clearing all connections
3
DEBUG: Connection stats: total - 1, free - 0, deleted - 1
4
DEBUG: Creating primary connection
5
DEBUG: Parsing connection information...
6
DEBUG: user: pgagent
7
DEBUG: password: *****
8
DEBUG: dbname: pgagent
9
DEBUG: host: localhost
10
DEBUG: port: 5432
11
DEBUG: Creating DB connection: user=pgagent password=secret  host=localhost port=5432 dbname=pgagent
12
DEBUG: Database sanity check
13
DEBUG: Clearing zombies
14
DEBUG: Checking for jobs to run
15
DEBUG: Sleeping...
16
DEBUG: Clearing inactive connections
17
DEBUG: Connection stats: total - 1, free - 0, deleted - 0
18
DEBUG: Checking for jobs to run
19
DEBUG: Sleeping...
Copied!
Don't worry if you see WARNING: Couldn't create the primary connection [Attempt #2] – postgres takes some time to start and pgagent will reconnect as soon as postgres is ready to accept connections.
Defining a job via PgAdmin
Refer to https://www.pgadmin.org/ for pgAdmin and pgAgent documentation.
Now when pgagent is up and running we can define some jobs and see if they're actually scheduled.
Create server connection
It's important that Maintenance database is the same database where pgagent data is stored.
Create pgAgent job
The job will be run every minute.
Test if jobs are running
Let's connect into postgres to see if jobs are actually running.
1
docker exec -it aidboxdb psql pgagent
Copied!
We can check scheduled jobs, last and next time their run in pgagent.pga_job table.
SQL
Response
1
--turn on extended view
2
\x
3
SELECT * FROM pgagent.pga_job;
Copied!
1
-[ RECORD 1 ]+------------------------------
2
jobid        | 1
3
jobjclid     | 1
4
jobname      | Test Job
5
jobdesc      |
6
jobhostagent |
7
jobenabled   | t
8
jobcreated   | 2021-08-06 02:05:39.111269+00
9
jobchanged   | 2021-08-06 02:05:39.111269+00
10
jobagentid   |
11
jobnextrun   | 2021-08-06 02:06:00+00
12
joblastrun   |
Copied!
Let's test if job was run in our test table:
Bash
Response
1
SELECT * FROM test;
Copied!
1
-[ RECORD 1 ]-----------------------
2
tx   | 2021-08-06 02:06:03.970728+00
3
note | test
Copied!
If everything was successfull you will see new records in the table.
Summary
pgagent is a tool to run various jobs on your postgres database.
pgagent runs only if PGAGENT_ENABLED variable is present.
You can configure pgagent using several variables described here.
If you have any questions on how to configure pgagent feel free to contact us.

Patient Encounter notification Application

Working with Extensions

Last modified 7mo ago

Copy link

Contents

Introduction

Step-by-step example with docker

Configuring PgAgent

Defining a job via PgAdmin

Summary