A quick post on how to set up pgAgent on a database server running CentOS 7 in order to create some scheduled maintenance tasks to run against a postgreSQL database. Note that the server already has postgreSQL installed.

pgAgent is a job scheduling agent for Postgres databases. The installation process is briefly described here, but there are a few details missing.

Step 1 - Gather information & install pgagent

Simply running sudo yum install pgagent on CentOS won't get you very far. You need to install the PostgreSQL Yum Repository first.

Visit https://www.postgresql.org/download/linux/redhat/ to figure out what the yum command you'll need is to do this. You'll need to know the version of postgreSQL that you are running, the CentOS version and the architecture of the server. If (like me) you've inherited the server rather than setting it up yourself, you can determine the CentOS version by running the following;

$ cat /etc/centos-release

If you have to confirm the postgreSQL version, run the following query;

# SELECT version();

Once you enter values for the Select version:, Select platform: and Select architecture: drop downs, you are presented with a script to copy/paste and execute. Because the server already had postgreSQL installed, I was only interested in the first line of the script to install the PostgreSQL Yum Repository.

Entering 11 for the version, 'RedHat Enterprise, CentOS, Scientific or Oracle version 7' vor the platform and X86_64 for the architecture gave me the following as the first command (no need to run the rest of the script)

# Install the repository RPM:
$ sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Once that was run, I could then search for the possible pgagents to install

$ yum search pgagent

which yielded a number of choices but pgagent_11.x86_64 seemed the best bet.

$ sudo yum install pgagent_11.x86_64

Step 2 - Configure pgagent user

To avoid having a password in the pgagent connection string (as recommended here), set up a user that pgagent will use (pgagent should be easy to remember) that the postgres user on the database server will use to connect to the database with.

Create the .pgpass file

Switch to the postgres user

$ sudo su - postgres

create the .pgpass file

$ echo 127.0.0.1:5432:*:pgagent:{super secret password} >> ~/.pgpass

A couple of things to note - localhost and 127.0.0.1 are not interchangable - make sure to stick with 127.0.0.1 or localhost from here out. Also, replace with an actual password.

Make sure only the postgres user can read/write to the file

$ chmod 600 ~/.pgpass

Finally, make sure ownership for the file is set correctly. Do a ls -la to check ownership, or simply run

$ chown postgres:postgres ~/.pgpass

log out of the postgres user

$ exit

Set up log directory

$ sudo mkdir /var/log/pgagent
$ sudo chown -R postgres:postgres /var/log/pgagent
$ sudo chmod g+w /var/log/pgagent

Set up pgagent user in the database

The examples I found elsewhere made these next steps a bit confusing, so to simplify (hopefully) these instructions, we're going to configure a database called 'metrics_data' that is the database we're interested in running scheduled clean up tasks against. We have an admin/database owner/master user called 'metrics_admin'.

Connect to the 'metrics_data' database as 'metrics_admin'

$ psql metrics_data metrics_admin

Install the pgagent schema (Dropping this extension will remove this schema and any jobs you have created.)

# CREATE EXTENSION pgagent;

Create the pgagent user & grant access

# CREATE USER "pgagent" WITH
# LOGIN
# NOSUPERUSER
# INHERIT
# NOCREATEDB
# NOCREATEROLE
# NOREPLICATION
# encrypted password '{super secret password}';
# GRANT USAGE ON SCHEMA pgagent TO pgagent;
# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA pgagent TO pgagent;
# GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA pgagent TO pgagent;
# GRANT CONNECT ON DATABASE metrics_data TO pgagent;
# GRANT TEMP ON DATABASE metrics_data TO pgagent;

(replacing with the value used to populate the .pgpass file)

If the pgagent user is going to be executing sql against tables in the database that is being maintained (metrics_data in this case) make sure to GRANT appropriate permissions to the schema here as well. For example, the job that I eventually want to setup is to clean out old data from the values table, so in this case I'd also add

# GRANT SELECT, DELETE ON TABLE values TO pgagent;

exit out of psql for the metrics_admin user

# \q

Test pgagent user connection to the database

Test that the pgagent user is able to connect to the database.

$ psql -h 127.0.0.1 -d metrics_data -U pgagent

You should be prompted for the pgagent password, then connect to the metrics_data database

Configure pg_hba.conf

The postgreSQL host-based authentication configuration file pg_hba.conf file needs to be updated to allow the pgagent user access. This file resides in the data directory (in a default installation that is something like /var/lib/pgsql/xx.x/data but if the data directory has been relocated, the path will be different - search for pg_hba.conf and you'll find it)

If you do not do this step, attempts to test the pgagent below will fail to connect

Sudo edit data/pg_hba.conf to add the following lines

# local pgagent connection
local   metrics_data      pgagent                                 password

NOTE: Refer to https://www.postgresql.org/docs/current/auth-pg-hba-conf.html for the structure of the entries for a local connection. Also note password in this case is the word password - do not replace with the pgagents password.

Restart the postgres service. To ensure you use the right service name, run the following to list all the services;

$ sudo systemctl -at service

Locate the postgres service to restart (in my case its postgresql-11.service) then run the following to restart it;

$ sudo systemctl restart postgresql-11.service

Immediately check to make sure that the postgres service has restarted correctly;

$ sudo systemctl status postgresql-11.service

If the service failed to restart, re-check the changes to pg_hba.conf

Test pgAgent

To test the postgres user running pgAgent with the pgagent user to connect to the metrics_data database

$ sudo su - postgres
$ /usr/bin/pgagent_11 -f -l 2 host=127.0.0.1 port=5432 user=pgagent dbname=metrics_data

IF you get errors such as;

WARNING: Failed to create primary connection: FATAL: no pg_hba.conf entry for host "::1", user "pgagent", database "metrics_data", SSL off

First, check that you've used the same host as that entered above in the .pgpass file. Note that localhost and 127.0.0.1 are treated as different hosts.

If you've confirmed that you've used localhost or 127.0.0.1 consistently, make sure that you've updated the pg_hba.conf file above.

A successful test should look something like;

DEBUG: Creating primary connection
DEBUG: Parsing connection information...
DEBUG: user: pgagent
DEBUG: dbname: metrics_data
DEBUG: host: 127.0.0.1
DEBUG: port: 5432
DEBUG: Creating DB connection: user=pgagent  host=127.0.0.1 port=5432 dbname=metrics_data
DEBUG: Database sanity check
DEBUG: Clearing zombies
DEBUG: Checking for jobs to run
DEBUG: Sleeping...
DEBUG: Clearing inactive connections
DEBUG: Connection stats: total - 1, free - 0, deleted - 0
DEBUG: Checking for jobs to run
DEBUG: Sleeping...

Step 3 - Configure pgAgent start on reboot

Check where the pgAgent service file is expecting to find the pgAgent configuration file by looking for the path specified for the EnvironmentFile setting in the pgagent.service file. The location and name of the pgagent service file will change depending on the version of pgagent installed, but in my case when I installed pgagent_11.x86_64, the path & filename are;

$ sudo cat /usr/lib/systemd/system/pgagent_11.service

Within the pgagent_11.service file is the following;

# Location of the configuration file
EnvironmentFile=/etc/pgagent/pgagent_11.conf

Edit the file /etc/pgagent/pgagent_11.conf (it should already exist)

$ sudo vi /etc/pgagent/pgagent_11.conf

Set the following values

DBNAME=metrics_data
DBUSER=pgagent
DBHOST=127.0.0.1
DBPORT=5432
LOGFILE=/var/log/pgagent/pgagent_11.log

Update pgagent_11.service user and group

By default, the pgagent_11.service is configured to run as the user/group of pgagent. Because we've configured the psotgres user with the .pgpass file and access to the logging directory, we need to override the User and Group setting in /usr/lib/systemd/system/pgagent_11.service. Instead of editing this file directly (and loosing these chagnes next time pgagent is updated), create an override file to apply our changes on top of /usr/lib/systemd/system/pgagent_11.service. To do this;

$ sudo systemctl edit pgagent_11.service This will create an override file at /etc/systemd/system/pgagent_11.service.d/override.conf and open it in the default text editor.

Add the following text and save the file. Be sure to include the lines where the values are set to empty values first (this clears the previous values)

[Service]
User=
User=postgres
Group=
Group=postgres

Start the pgagent service (note that if you installed a different version of pgagent, the _11 suffix will differ)

$ sudo systemctl daemon-reload
$ sudo systemctl disable pgagent_11
$ sudo systemctl enable pgagent_11
$ sudo systemctl start pgagent_11

Check the status of the pgagent service to make sure it is active (running).

$ sudo systemctl status pgagent_11

You will also note that the override config file has been applied

From here, you can now set up a pgAgent Job using pgAdmin - https://www.pgadmin.org/docs/pgadmin4/latest/pgagent_jobs.html

References

Installing pgAgent https://www.pgadmin.org/docs/pgadmin4/latest/pgagent_install.html

Install pgAgent on Postgres 10 (Debian Linux) https://gist.github.com/peterneave/83cefce2a081add244ad7dc1c53bc0c3

The .pgpass password file https://www.postgresql.org/docs/current/libpq-pgpass.html

The pg_hba.conf File https://www.postgresql.org/docs/current/auth-pg-hba-conf.html

Creating a pgAgent Job https://www.pgadmin.org/docs/pgadmin4/4.27/pgagent_jobs.html

Modifying existing systemd services https://docs.fedoraproject.org/en-US/quick-docs/understanding-and-administering-systemd/index.html#modifying-existing-systemd-services