PostgreSQL pgAgent
▌Introduction
pgAgent is a job scheduling agent for PostgreSQL, it can has multiple
steps in a job, including SQL script, batch
or shell scripts.
This
article will show how to install pgAgent inside a PostgreSQL Docker container.
▌Environment
▋Docker Engine Community 19.03.5
▋PostgreSQL Docker Official image 10/11
▌Implement
▋Install
First we have to install pgAgent in
the container,
$ docker exec -it demo-postgres bash
$ apt-get update && apt-get install pgagent
Then install the pgAgent extension.
▋Install pgAgent extension
CREATE EXTENSION pgagent
IF NOT EXISTS;
CREATE LANGUAGE plpgsql IF NOT EXISTS;
The 【pgAgent Jobs】 shows
after installing the extension,
And the new schema named “pgagent”
and tables were created.
▋(Optional) Grant permission
It is recommended to create a new
role for using pgAgent.
For example,
GRANT USAGE ON SCHEMA pgagent TO pgagent_user;
GRANT ALL ON SCHEMA pgagent TO pgagent_user;
GRANT ALL ON all tables IN SCHEMA pgagent to pgagent_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA pgagent TO pgagent_user;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA pgagent TO pgagent_user;
GRANT CONNECT ON DATABASE postgres TO pgagent_user;
▋Start pgAgent
Back to the container, use the
following command to start the agent.
Note that using "127.0.0.1" instead of "localhost" to start pgagent.
$ cd /usr/bin
$ pgagent hostaddr=127.0.0.1 dbname=postgres user=<user_name> -s pgagent_log.log
We can check if the connection
between pgAgent and database was established by,
SELECT * FROM pgagent.pga_jobagent;
▋Scheduling a job
Here is an example for scheduling a
cleaning-table job.
1.Create a new pgAgent Job
2.【General】: Name the new job and set Job class, Host agent(Optional).
3.【Steps】: We can set multiple steps to run SQL scripts or batches here.
Notice that if we wanna run SQL script, we have to enter the database
connection string, e.q.
host=localhost port=5432 dbname=postgres connect_timeout=10 user='xxxxx' password='my_pwd'
PS. We can use pgpass (The Password File)
to skip storing the password in a pgAgent job. We will talk about it later in
this article.
Navigate to Code tab, enter the SQL/Batch script.
4.【Schedules】: Set the job’s start/end time and how it repeats.
5.Save and get the
new scheduling job.
▋Logs
We can check the logs of a job from
these tables:
SELECT * FROM pgagent.pga_joblog
SELECT * FROM pgagent.pga_jobsteplog
SELECT * FROM pgagent.pga_exception
▋Use Password File to protect the pwd
To not saving password in a pgAgent
job, we will create .pgpass (The Password File) inside
the PostgreSQL container.
$ sudo su postgres
$ echo localhost:5432:*:postgres:my_pwd >> /var/lib/postgresql/data/.pgpass
$ chmod 600 /var/lib/postgresql/data/.pgpass
$ chown postgres:postgres /var/lib/postgresql/data/.pgpass
$ export PGPASSFILE='/var/lib/postgresql/data/.pgpass'
Then we will be able to remove the
password from the connection string of pgAgent job.
We can test if it works by connecting to database
without entering password:
$ psql -h localhost -U postgres <db_name>
In Windows, you can use the Password File as following,
$ cd %APPDATA%
$ mkdir Postgres
$ cd Postgres
$ echo localhost:5432:*:postgres:my_pwd >> pgpass.conf
$ SETX PGPASSFILE %APPDATA%\Postgres\pgpass.conf
|
▋Create/Drop a pgAgent job by SQL script
Here are a pgAgent job’s create/drop
sql script for reference.
/* Create pgAgent job */
DO $$
DECLARE
jid integer;
scid integer;
BEGIN
-- Creating a new job
INSERT INTO pgagent.pga_job(
jobjclid, jobname, jobdesc, jobhostagent, jobenabled
) VALUES (
1::integer, 'Routine Clean'::text, ''::text, ''::text, true
) RETURNING jobid INTO jid;
-- Steps
-- Inserting a step (jobid: NULL)
INSERT INTO pgagent.pga_jobstep (
jstjobid, jstname, jstenabled, jstkind,
jstconnstr, jstdbname, jstonerror,
jstcode, jstdesc
) VALUES (
jid, 'Clean_News'::text, true, 's'::character(1),
'host=localhost port=5432 dbname=postgres connect_timeout=10 user=''postgres'''::text, ''::name, 'f'::character(1),
'DELETE FROM public."News"'::text, ''::text
) ;
-- Schedules
-- Inserting a schedule
INSERT INTO pgagent.pga_schedule(
jscjobid, jscname, jscdesc, jscenabled,
jscstart, jscend, jscminutes, jschours, jscweekdays, jscmonthdays, jscmonths
) VALUES (
jid, 'Daily'::text, ''::text, true,
'2020-04-24 06:14:44+00'::timestamp with time zone, '2020-04-30 05:51:17+00'::timestamp with time zone,
-- Minutes
ARRAY[false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,true,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false]::boolean[],
-- Hours
ARRAY[false,false,false,false,false,false,false,false,false,false,false,true,false,false,false,false,false,false,false,false,false,false,false,false]::boolean[],
-- Week days
ARRAY[false,false,false,false,false,false,false]::boolean[],
-- Month days
ARRAY[false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false]::boolean[],
-- Months
ARRAY[false,false,false,false,false,false,false,false,false,false,false,false]::boolean[]
) RETURNING jscid INTO scid;
END
$$;
/* Delete pgAgent job */
DO $$
DECLARE
jname VARCHAR(50) :='Routine Clean';
jid INTEGER;
BEGIN
SELECT "jobid" INTO jid from pgagent."pga_job"
WHERE "jobname"=jname;
DELETE FROM pgagent."pga_schedule"
WHERE "jscjobid"=jid;
DELETE FROM pgagent.pga_jobstep
WHERE "jstjobid"=jid;
DELETE FROM pgagent."pga_job"
WHERE "jobid"=jid;
END
$$;
▌Reference
沒有留言:
張貼留言