Skip to content

📚 PGagent enabled

🔍 Prepare：

## OS and Software environment：

Environment name Version
OS Rocky Linux 9.x
DB PostgreSQL 16.X

### 1. Install pgagent_16 package

http_proxy="http://10.55.13.139:8080"
export http_proxy
https_proxy="http://10.55.13.139:8080"
export https_proxy

cat << EOF > /etc/yum.repos.d/pgdg_rocky-16.repo
[pgdg16]
name=PostgreSQL 16 for RHEL/CentOS
baseurl=https://download.postgresql.org/pub/repos/yum/16/redhat/rhel-\$releasever-\$basearch
enabled=1
gpgcheck=0
EOF

yum clean all
yum makecache
yum list |grep pgagent
yum install -y pgagent_16.x86_64
chown -R postgres. /usr/pgsql-16

alt text

### 2. Create pgagent extension on postgres DB

su - postgres
psql -p 5435

\dx
CREATE EXTENSION pgagent;
\dx

alt text

### 3. to make sure pgAgent jobs can run the setup jobs. You need to start pgagent service on server. Grant privilege to user

su - postgres
psql -p 5435

grant all on schema pgagent to user;
grant all on all tables in schema pgagent to user;
grant all on all sequences in schema pgagent to user;
grant all on all functions in schema pgagent to user;

### 4. Check IPv4 connections 

vi /pgdata/JARVAN/pg_hba.conf
# IPv4 local connections:
  host    all             all             10.37.39.223/32         trust

alt text

pg_ctl reload -D /pgdata/{DB_NAME}

### 5. Setting pgagent

  • 5.1 Set pgagent conf
vi /etc/pgagent/pgagent_16.conf
# pgagent_16_conf
DBNAME=postgres
DBUSER=postgres
DBHOST=10.37.39.223
DBPORT=5435
# if can not connection ,use log debug
LOGFILE=/var/log/pgagent_16.log

alt text

If you have multi Cluster , you shou create copy pgagent_16.conf and service （example pgagent_16_SID.conf）

  • 5.2 start pgagent_16.service
vi /usr/lib/systemd/system/pgagent_16.service
#check and modify the row host => hostaddr
EnvironmentFile=/etc/pgagent/pgagent_16.conf
ExecStart=/usr/bin/pgagent_16 -s ${LOGFILE} hostaddr=${DBHOST} dbname=${DBNAME} user=${DBUSER} port=${DBPORT}

If you change pgagent_16.conf name , you shou modify in pgagent_16.service

systemctl enable pgagent_16.service
systemctl start pgagent_16.service
systemctl status pgagent_16.service
  • 5.3 Check running

alt text

alt text

psql -p 5435

SELECT * FROM pgagent.pga_jobagent;

alt text

Pgagent is running on server successfully

### 6. How to use Pgagent (by postgres account, like oracle sys or system)

  • 6.1 use postgres to login db

alt text

Ex. setup a inserting job to update HQRPAD1(DB)/dennis(schema)/aaa(table) every 2 minutes.

  • 6.2 create job on pgadmin.
CREATE TABLE dennis.aaa
(
  a integer,
  b integer
);
  • 6.3 Right click, pgAgent Jobs->Create->pgAgent Job…

alt text

  • 6.4 type the name

alt text

alt text

When you see question mark, Click ?, you will get the detailed tutorials from web help.

alt text

After you click ?, it will pop up new window as below.

  • 6.5 you will see below. There are Schedules and Steps under job test123

alt text

  • 6.6 Right click Steps ->Create->Job Step…

alt text

  • 6.7 type name for Step and choose database that your job step will run

alt text

  • 6.8 switch to code tag. Type the sql you wan to run and click Save
INSERT INTO dennis.aaa VALUES('3','333');

alt text

  • 6.9 switch to SQL tag. It will show the step detail. We don’t need to do any things about this tag. Just click Save

alt text

  • 6.10 Right click Schedules ->Create->Schedule…

alt text

  • 6.11 type name for Schedule and choose the start time and end time

alt text

  • 6.12 switch to repeat tag. In our case, we want every 2 mins to run job.

alt text

A.Click 00; then click 02,04,…

alt text

B.After you finished, it will like this.

alt text

  • 6.13 switch to exception. In this tag, you can set up the time that you don’t want the job run

alt text

  • 6.14 switch to SQL tag. It will show the schedule detail. We don’t need to do any things about this tag. Just click Save

alt text

  • 6.15 After we finish setup, we can run the jobs manually. ,Right click test123->Run now

alt text

  • 6.16 check the job result

Click test123 switch to top Statistics tag

alt text

Job status r=running,s=successful,f=failed,i=no step needs to run,d=abort

  • 6.17 check the HQRPAD1(DB)/dennis(schema)/aaa(table) result

Data inserted successfully.

alt text

### 7. How to use Pgagent (by non postgres account)

we plan to grant user: gary with pgagent authority.

  • 7.1 check the HQRPAD1(DB)/dennis(schema)/aaa(table) result

alt text

  • 7.2 Create Extension and Refresh
create extension pgagent;

alt text

alt text

  • 7.3 Grant privilege to Gary
grant all on schema pgagent to gary;
grant all on all tables in schema pgagent to gary;
grant all on all functions in schema pgagent to gary;
grant connect on database "IHLABD1" to gary;
grant all on all sequences in schema pgagent to gary;
grant all on all functions in schema pgagent to gary;

alt text

  • 7.4 to make sure pgAgent jobs can run the setup jobs. You need to start pgagent service on server.
su - postgres
pgagent_16 host=IP port=5435 dbname=IHLABD1 user=gary password=gary -s ./pgagent_gary.log &

If DB restart, you must execute this command again !!

  • 7.5 Leave postgres session and use gary to login DB

alt text

  • 7.6 Create table
CREATE TABLE gary.aaa
(
    a integer,
    b integer
);
  • 7.7 follow 6.3 to 6.16 create job

And 6.8 change to

INSERT INTO gary.aaa VALUES('3','333');

Note After PGagent extension installed successfully, no mater pgagent service is running or not. All the related pgagent tables and schema existed. And you can create job as well. The only thing is creating job cannot run.

pgagen’s related tables on postgres/Catalogs/pgAgent job scheduler pgagent.pga_exception: record exception job message pgagent.pga_job: job information. Like start time and end time. pgagent.pga_jobagent:pgAgent configuration. Server’s pgagent and starting time. pgagent.pga_jobclass: pgAgent configuration. Define job type pgagent.pga_joblog: job running job. Include start time and execution time pgagent.pga_jobstep: each job setp pgagent.pga_jobsteplog: eacho job step log. Include the starting time and execution time of each job pgagent.pga_schedule:job schedule information

alt text

### 8. if you want to revoke the user: dennis with pgagent authority,You need to run below sql to revoke pgagent schema from dennis;

revoke all on schema pgagent from dennis;
revoke all on all tables in schema pgagent from dennis;
revoke all on all functions in schema pgagent from dennis;
revoke connect on database postgres from dennis;
revoke all on all sequences in schema pgagent from dennis;
revoke all on all functions in schema pgagent from dennis;

Before

alt text

After

alt text