Installing pgAgent

To prevent tables from becoming extremely large over time, some tables of the database used by Z ChatOps are partitioned by date range. You must maintain these partitions regularly. PostgreSQL does not provide task scheduler to perform certain tasks regularly by default. You can use many ways to create partitions in a specified period.

About this task

You can use the following ways to create partitions in a specified period. It is recommended that you use pgAgent together with pgAdmin 4. pgAgent is distributed independently of pgAdmin 4.

The job scheduler of your operating system. For more information, see https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/7/html/system_administrators_guide/ch-automating_system_tasks.
pgAgent. For more information, see https://www.pgadmin.org/docs/pgadmin4/latest/pgagent.html.
pg_cron. For more information, see https://github.com/citusdata/pg_cron.
pg_timetable. For more information, see https://github.com/cybertec-postgresql/pg_timetable.

This topic describes how to install and start pgAgent in your PostgreSQL container on xLinux as an example. If you want to install pgAgent via native installation package, you can follow related guide in pgAdmin 4 web site.

Procedure

Install pgAgent into PostgreSQL container.
1. Copy the sample SQL scripts to your PostgreSQL container.
  - If your Z ChatOps server is installed with native installation package, run the following command:
```
docker cp $ZCHATOPS_HOME/samples/sql your postgresql container id:/
```
  - If your Z ChatOps server is installed with container image:
    1. Copy out the SQL scripts from IBM Z ChatOps container through the following command:
      docker cp your zchatops container id:/opt/ibm/zchatops/samples/sql target host path
    2. Copy the SQL scripts to your PostgreSQL container through the following command:
      docker cp target host path/sql your postgresql container id:/
2. Log in your PostgreSQL container:
```
docker exec -it postgresql_container_id bash
```
3. Install pgAgent if it is not installed:
```
apt list --installed | grep pgagent
apt-get update && apt-get install pgagent
```
4. Install two required extensions plpgsql and pgagent using the provided SQL script if they are not installed:
```
psql -h localhost -p 5432 -d postgres -U postgres -c 'select extname from pg_extension;' 
psql -h localhost -p 5432 -d postgres -U postgres -f /sql/pgAgent/installExtension.sql
```
5. Create a log directory for pgAgent:
```
mkdir -p /var/log/pgagent 
chown postgres:0 /var/log/pgagent
```

Start pgAgent.

docker exec -it postgresql_container_id bash

Run the following command to check if the pgAgent is connected to the database:
```
ps -ef |grep pgagent
```
Note: You might need to install the ps command first:
```
apt-get update && apt-get install procps
```
su to PostgreSQL user account.
```
su postgres
```

Start pgAgent.

pgagent hostaddr=127.0.0.1 port=5432 dbname=postgres user=postgres password=your password -s /var/log/pgagent/pgagent.log

Confirm if the pgAgent is connected to the maintenance database.

psql -h localhost -d postgres -p 5432 -U postgres -c 'SELECT * FROM pgagent.pga_jobagent;'