MIME-Version: 1.0
Reply-To: csanyipal@gmail.com
From: =?UTF-8?B?Q3PDoW55aSBQw6Fs?= <csanyipal@gmail.com>
Date: Wed, 5 Feb 2025 19:05:49 +0100
Message-ID: <CAONhAotk=VP6V6zDtzqAQ+ENDhkRZt+4nNjW44U3fGT9j0O_Mg@mail.gmail.com>
Subject: Using PgAgent with SQL commands only?
To: pgsql general <pgsql-general@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAONhAotk%3DVP6V6zDtzqAQ%2BENDhkRZt%2B4nNjW44U3fGT9j0O_Mg%40mail.gmail.com>
Precedence: bulk

Hello,

I am using Debian 12 operating system and
have installed on it

postgresql 15.10 (Debian 15.10-0+deb12u1)

and

pgagent -V
PostgreSQL Scheduling Agent
Version: 4.2.2

booth installed with apt.

PgAgent is running as service:
systemctl status pgagent
=E2=97=8F pgagent.service - pgAgent for PostgreSQL
     Loaded: loaded (/etc/systemd/system/pgagent.service; enabled;
preset: enabled)
     Active: active (running) since Sun 2025-02-02 08:27:43 CET; 3 days ago
    Process: 92063 ExecStart=3D/usr/bin/pgagent -s ${LOGFILE} -l
${LOGLEVEL} host=3D${DBHOST} dbname=3D${DBNAME} user=3D${DBUSER}
port=3D${DBPORT} (code=3Dexited, status=3D0/SUCCESS)
   Main PID: 92064 (pgagent)
      Tasks: 1 (limit: 6999)
     Memory: 4.4M
        CPU: 10.913s
     CGroup: /system.slice/pgagent.service
             =E2=94=94=E2=94=8092064 /usr/bin/pgagent -s /var/log/pgagent/p=
gagent.log
-l 1 host=3Dlocalhost dbname=3Dpostgres user=3Dpgagent port=3D5432

Because I can't install and run correct PgAdmin 4 on this system
I am trying to use PgAgent with SQL commands, so far without any success.

I did the following to create a job, jobstep and schedule for that job:
(This is just a test for me to see whether I can use PgAgent with SQL comma=
nds.
This example job should dump my database every five minutes.)

For this purpose these are my SQL commands which I did run sofar:

sudo su - postgres
psql

INSERT INTO pgagent.pga_job (jobjclid, jobname, jobdesc, jobhostagent,
jobenabled, jobcreated, jobchanged, jobagentid, jobnextrun,
joblastrun)
VALUES (3, 'otpercenkent_menti_vagyonunk_kezelese_t', '=C3=96tpercenk=C3=A9=
nt
menti vagyonunk_kezelese adattelepet.', '', TRUE, DEFAULT, DEFAULT,
NULL, NULL, NULL);

INSERT INTO pgagent.pga_jobstep (jstjobid, jstname, jstdesc,
jstenabled, jstkind, jstcode, jstconnstr, jstdbname, jstonerror,
jscnextrun)
VALUES ((SELECT jobid FROM pgagent.pga_job WHERE jobname =3D
'otpercenkent_menti_vagyonunk_kezelese_t'),
'menti_vagyonunk_kezelese_t', 'Menti vagyonunk_kezelese adattelepet.',
TRUE,
 'b', '/home/pali/Dokumentumok/AdatTelepeim/PgAgent/PgAgent_Hasznalata/pgag=
ent_pg_dump_vagyonunk_kezelese.sh',DEFAULT
, '', DEFAULT, NULL);

INSERT INTO pgagent.pga_schedule (jscjobid, jscname, jscdesc,
jscenabled, jscstart, jscend, jscminutes, jschours, jscweekdays,
jscmonthdays, jscmonths)
VALUES ((SELECT jobid FROM pgagent.pga_job WHERE jobname =3D
'otpercenkent_menti_vagyonunk_kezelese_t'),'menti_vagyonunk_kezelese_t',
'Menti vagyonunk_kezelese adattelepet.', TRUE, now(), NULL,
-- jscminutes [60]
'{f,f,f,f,f,t,f,f,f,f,t,f,f,f,f,t,f,f,f,f,t,f,f,f,f,t,f,f,f,f,t,f,f,f,f,t,f=
,f,f,f,t,f,f,f,f,t,f,f,f,f,t,f,f,f,f,t,f,f,f,f}',
-- jschours [24]
'{t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t}',
-- jscweekdays [7]
'{t,t,t,t,t,t,t}',
-- jscmonthdays [32]
'{t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t}',
-- jscmonths [12]
'{t,t,t,t,t,t,t,t,t,t,t,t}');

So this way I have created a pga_job, a pga_jobstep and a pga_schedule
but this job won't run every five minutes.

What am I missing here?

Any suggestions will be appreciated!

--=20
Best, Paul Chany