How to setup pgagent to multiple database in pgadmin on CentOS

1. Install pgagent on CentOS

First, Install PostgreSQL yum (if not already) repository by running these command.

You may need to replace x86_64 with your CentOS architecture and postgresql version (13) with whatever you have installed, in the commands given below.

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# yum --enablerepo=pgdg13 install postgresql13-contrib

sudo yum install pgagent_13.x86_64

You can check postgresql version with postgresql --version command. Install the same version of pgagent as of postgresql.

You need to create a user to use pgagent (if not created automatically) called pgagent due to security concerns.

We will run as the postgres user on the server and as the pgagent on the database.

Install postgresql contrib package (if not already)

To install the postgresql13-contrib package which contains additional tool required, you need to do the following steps:

1. Download the package to your CentOS system using wget or curl. For example:

wget https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-7-aarch64/postgresql13-contrib-13.11-1PGDG.rhel7.aarch64.rpm

2. Install the package using yum or rpm. For example:

sudo yum install postgresql13-contrib-13.11-1PGDG.rhel7.aarch64.rpm
# or
sudo rpm -ivh postgresql13-contrib-13.11-1PGDG.rhel7.aarch64.rpm

2. Setup pgagent password and logging directory

After pgagent is installed, we set up pgagent authentication and logging, which would help us in upcoming steps.

Create .pgpass file for pgagent (required)

We are required to use password authentication. For this, we will create a .pgpass file.

First, change user to postgres.

sudo su - postgres

Create password file with HOST:PORT:*:pgagent:PASSWORD in $HOME (/var/lib/postgresql).

echo localhost:5432:*:pgagent:securepassword >> ~/.pgpass

Set appropriate permissions to make it accessible by the user postgres only.

chmod 600 ~/.pgpass
chown postgres:postgres /var/lib/postgresql/.pgpass

Setup Logging directory

We create a directory where pgagent logs are saved, /var/log/pgagent with postgres as owner and write permission to postgres group.

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

3. Setup pgagent extension and permission to postgres (maintenance) DB

Now, You need to add pgagent extension to the database, then create pgagent user with some usage privileges.

Connect to postgres server and run the queries given below:

Make sure you use the same password specified in .pgpass the file.

CREATE EXTENSION pgagent;

// verify pgagent installation with \dx

CREATE USER "pgagent" WITH
  LOGIN
  NOSUPERUSER
  INHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
  encrypted password 'securepassword';

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;

Perfect! This will create the pgagent schema and provide necessary privileges. Exit the database shell.

Dropping this extension will remove this schema and any jobs you have created.

Test pgagent connections

Test connection of user pgagent to database with this command:

psql -h localhost -p 5432 -d postgres -U pgagent

Replace postgres with maintenance DB. It should connect without any error. Exit the database shell.

You can view connection logs with tail -f /var/log/postgresql/postgresql-10-main.log command.

4. Start pgagent process

Now, let's check if pgagent could connect to the database.

We run pgagent command as user postgres and use the below options:

• -l 2: Log level 2 (medium)
• host=localhost: Host/IP of server running postgres (make sure it is reachable)
• port=5432: Port of pgagent server (default 5432)
• user=pgagent: User for connecting the database (pgagent)
• dbname=postgres: Maintenance DB (crucial)

sudo su - postgres
/usr/bin/pgagent -f -l 2 host=localhost port=5432 user=pgagent dbname=postgres

If it outputs nothing, that means we are good. You can review logs for errors.

Important that you use pgagent as user and your maintenance database as dbname.

Verify in Pgadmin

At this point, pgagent jobs object should be visible in Pgadmin. Maybe create a Job and check if the result is s or f.

If not, Go to display setting and mark tick on pgagent jobs option and refresh/restart pgadmin.

If still not visible. You may be not granting correct permissions from psql.

5. Setup pgagent Service/Unit

Now, we are creating a Systemd Unit which automatically connects to the database.

You need to create a config file in /etc/pgagent directory for the connection configuration like Database name, user, host, port, log file, etc. The same which worked in previous step.

For example, save the below configuration to /etc/pgagent/pgagent_13.conf file.

#/etc/pgagent_13.conf
DBNAME=postgres
DBUSER=pgagent
DBHOST=localhost
DBPORT=5432
# ERROR=0, WARNING=1, DEBUG=2
LOGLEVEL=1
LOGFILE="/var/log/pgagent/pgagent.log"

Loading pgagent with Systemd

Here is configuration to manage pgagent unit based on the CentOS pgagent.service file found at /usr/lib/systemd/system/pgagent.service location.

[Unit]
Description=pgagent for PostgreSQL
After=syslog.target
After=network.target

[Service]
Type=forking

User=postgres
Group=postgres

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

# Where to send early-startup messages from the server (before the logging
# options of pgagent.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog

# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000

ExecStart=/usr/bin/pgagent -s ${LOGFILE}  -l ${LOGLEVEL} host=${DBHOST} dbname=${DBNAME} user=${DBUSER} port=${DBPORT}
KillMode=mixed
KillSignal=SIGINT

Restart=on-failure

# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300

[Install]
WantedBy=multi-user.target

Start pgagent Service

Finally, we can reload the unit files in systemd and start pgagent service.

sudo systemctl daemon-reload
sudo systemctl start pgagent
sudo systemctl enable pgagent

Enable autorotation of logs

To enable autorotation of logs, put the configuration given below in /etc/logrotate.d/pgagent file.

/var/log/pgagent/*.log {
       weekly
       rotate 10
       copytruncate
       delaycompress
       compress
       notifempty
       missingok
       su root root
}

Test a log rotation with the following command:

logrotate -f /etc/logrotate.d/pgagent

7. Setup pgagent with pgadmin

Go to login/group roles settings and authorize pgagent user according to use case.

Go to pgadmin and right click to create a new job.

Now, select all permission you need for tasks you will be automating.