Обсуждение: pgagent
Hi,
I have installed postgresql 12 on sles 15 for some tests. Now I'm interested in some sort of scheduler and from what I've read so far, pgagent should do the job. So I've installed pgagent 4.0. I've added a job (which I can see in pgagent.pga_job) but everytime I ran it (from pgadmin) nothing happens ! When I start the pgagent I can see "... pgagent ... connection authorized" in the main log. What else should I check ?
Thanks,
Gabi
On 11/5/20 1:07 PM, Gabi Draghici wrote: > > Hi, > > I have installed postgresql 12 on sles 15 for some tests. Now I'm > interested in some sort of scheduler and from what I've read so far, > pgagent should do the job. So I've installed pgagent 4.0. I've added a > job (which I can see in pgagent.pga_job) but everytime I ran it (from > pgadmin) nothing happens ! When I start the pgagent I can see "... > pgagent ... connection authorized" in the main log. What else should I > check ? pg_cron: https://www.citusdata.com/blog/2020/10/31/evolving-pg-cron-together/ For pgagent what is the job doing and when? Could it be it hasn't run because it has not reached it's scheduled time. > > Thanks, > Gabi > > -- Adrian Klaver adrian.klaver@aklaver.com
Hi,
1. The job it's a simple call to one stored function like that :
BEGIN
CALL other_user_name.get_function();
END;
CALL other_user_name.get_function();
END;
Problem is that despite the fact that it's long passed by the scheduled running time and I tried the "Run now" option (from pgadmin) a couple of times, there is no evidence that the job actually runned ! Nothing in pgagent log, main log or in pgagent.pga_joblog or pgagent.pga_jobsteplog.
Is there any way to trace of debug this ?
2. Yes, pg_cron also looks good and it's my second option if I can't make 1 to work.
Regards,
Gabi
On Thu, Nov 5, 2020 at 11:34 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 11/5/20 1:07 PM, Gabi Draghici wrote:
>
> Hi,
>
> I have installed postgresql 12 on sles 15 for some tests. Now I'm
> interested in some sort of scheduler and from what I've read so far,
> pgagent should do the job. So I've installed pgagent 4.0. I've added a
> job (which I can see in pgagent.pga_job) but everytime I ran it (from
> pgadmin) nothing happens ! When I start the pgagent I can see "...
> pgagent ... connection authorized" in the main log. What else should I
> check ?
pg_cron:
https://www.citusdata.com/blog/2020/10/31/evolving-pg-cron-together/
For pgagent what is the job doing and when? Could it be it hasn't run
because it has not reached it's scheduled time.
>
> Thanks,
> Gabi
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 11/6/20 4:26 AM, Gabi Draghici wrote: > Hi, > > 1. The job it's a simple call to one stored function like that : > BEGIN > CALL other_user_name.get_function(); > END; > Problem is that despite the fact that it's long passed by the scheduled > running time and I tried the "Run now" option (from pgadmin) a couple of > times, there is no evidence that the job actually runned ! Nothing in > pgagent log, main log or in pgagent.pga_joblog or pgagent.pga_jobsteplog. > Is there any way to trace of debug this ? Is the pgagent daemon running? You could crank up the the log_statement to 'all'(temporarily as this can generate a lot of logs) in postgresql.conf and reload the server. Then tail the Postgres log file when click on 'Run now' to see what happens. > > 2. Yes, pg_cron also looks good and it's my second option if I can't > make 1 to work. > > Regards, > Gabi > > > On Thu, Nov 5, 2020 at 11:34 PM Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > On 11/5/20 1:07 PM, Gabi Draghici wrote: > > > > Hi, > > > > I have installed postgresql 12 on sles 15 for some tests. Now I'm > > interested in some sort of scheduler and from what I've read so far, > > pgagent should do the job. So I've installed pgagent 4.0. I've > added a > > job (which I can see in pgagent.pga_job) but everytime I ran it > (from > > pgadmin) nothing happens ! When I start the pgagent I can see "... > > pgagent ... connection authorized" in the main log. What else > should I > > check ? > > pg_cron: > > https://www.citusdata.com/blog/2020/10/31/evolving-pg-cron-together/ > <https://www.citusdata.com/blog/2020/10/31/evolving-pg-cron-together/> > > For pgagent what is the job doing and when? Could it be it hasn't run > because it has not reached it's scheduled time. > > > > > Thanks, > > Gabi > > > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > -- Adrian Klaver adrian.klaver@aklaver.com
postgres 2093 0.0 0.3 8720088 218280 ? Ss 17:54 0:00 /usr/lib/postgresql12/bin/postgres -D /opt/postgresql/database
postgres 2315 0.0 0.0 64664 5708 pts/2 S 17:57 0:00 /usr/bin/postgresql12-pgagent hostaddr=10.1.0.4 dbname=postgres user=pgagent -s /opt/postgresql/pglog/pg_agent.log
postgres 2326 0.0 0.0 8696 820 pts/2 S+ 17:57 0:00 grep --color=auto postgresql
I've switched log_statement to 'all' and restarted the DB. All I see it's a bunch of statements like these :
2020-11-06 18:07:03.869 EET postgres pgagent [2316]LOG: statement: SELECT J.jobid FROM pgagent.pga_job J WHERE jobenabled AND jobagentid IS NULL AND jobnextrun <= now() AND (jobhostagent = '' OR jobhostagent = 'dbdocs-prd') ORDER BY jobnextrun
2020-11-06 18:07:04.466 EET postgres postgres [2104]LOG: statement: /*pga4dash*/
SELECT 'session_stats' AS chart_name, row_to_json(t) AS chart_data
FROM (SELECT
(SELECT count(*) FROM pg_stat_activity) AS "Total",
(SELECT count(*) FROM pg_stat_activity WHERE state = 'active') AS "Active",
(SELECT count(*) FROM pg_stat_activity WHERE state = 'idle') AS "Idle"
) t
UNION ALL
SELECT 'tps_stats' AS chart_name, row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(xact_commit) + sum(xact_rollback) FROM pg_stat_database) AS "Transactions",
(SELECT sum(xact_commit) FROM pg_stat_database) AS "Commits",
(SELECT sum(xact_rollback) FROM pg_stat_database) AS "Rollbacks"
) t
UNION ALL
SELECT 'ti_stats' AS chart_name, row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(tup_inserted) FROM pg_stat_database) AS "Inserts",
(SELECT sum(tup_updated) FROM pg_stat_database) AS "Updates",
(SELECT sum(tup_deleted) FROM pg_stat_database) AS "Deletes"
) t
UNION ALL
SELECT 'to_stats' AS chart_name, row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(tup_fetched) FROM pg_stat_database) AS "Fetched",
(SELECT sum(tup_returned) FROM pg_stat_database) AS "Returned"
) t
UNION ALL
SELECT 'bio_stats' AS chart_name, row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(blks_read) FROM pg_stat_database) AS "Reads",
(SELECT sum(blks_hit) FROM pg_stat_database) AS "Hits"
) t
2020-11-06 18:07:05.459 EET postgres postgres [2104]LOG: statement: /*pga4dash*/
SELECT 'session_stats' AS chart_name, row_to_json(t) AS chart_data
FROM (SELECT
(SELECT count(*) FROM pg_stat_activity) AS "Total",
(SELECT count(*) FROM pg_stat_activity WHERE state = 'active') AS "Active",
(SELECT count(*) FROM pg_stat_activity WHERE state = 'idle') AS "Idle"
) t
UNION ALL
SELECT 'tps_stats' AS chart_name, row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(xact_commit) + sum(xact_rollback) FROM pg_stat_database) AS "Transactions",
(SELECT sum(xact_commit) FROM pg_stat_database) AS "Commits",
(SELECT sum(xact_rollback) FROM pg_stat_database) AS "Rollbacks"
) t
UNION ALL
SELECT 'ti_stats' AS chart_name, row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(tup_inserted) FROM pg_stat_database) AS "Inserts",
(SELECT sum(tup_updated) FROM pg_stat_database) AS "Updates",
(SELECT sum(tup_deleted) FROM pg_stat_database) AS "Deletes"
) t
UNION ALL
SELECT 'to_stats' AS chart_name, row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(tup_fetched) FROM pg_stat_database) AS "Fetched",
(SELECT sum(tup_returned) FROM pg_stat_database) AS "Returned"
) t
UNION ALL
SELECT 'bio_stats' AS chart_name, row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(blks_read) FROM pg_stat_database) AS "Reads",
(SELECT sum(blks_hit) FROM pg_stat_database) AS "Hits"
) t
2020-11-06 18:07:04.466 EET postgres postgres [2104]LOG: statement: /*pga4dash*/
SELECT 'session_stats' AS chart_name, row_to_json(t) AS chart_data
FROM (SELECT
(SELECT count(*) FROM pg_stat_activity) AS "Total",
(SELECT count(*) FROM pg_stat_activity WHERE state = 'active') AS "Active",
(SELECT count(*) FROM pg_stat_activity WHERE state = 'idle') AS "Idle"
) t
UNION ALL
SELECT 'tps_stats' AS chart_name, row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(xact_commit) + sum(xact_rollback) FROM pg_stat_database) AS "Transactions",
(SELECT sum(xact_commit) FROM pg_stat_database) AS "Commits",
(SELECT sum(xact_rollback) FROM pg_stat_database) AS "Rollbacks"
) t
UNION ALL
SELECT 'ti_stats' AS chart_name, row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(tup_inserted) FROM pg_stat_database) AS "Inserts",
(SELECT sum(tup_updated) FROM pg_stat_database) AS "Updates",
(SELECT sum(tup_deleted) FROM pg_stat_database) AS "Deletes"
) t
UNION ALL
SELECT 'to_stats' AS chart_name, row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(tup_fetched) FROM pg_stat_database) AS "Fetched",
(SELECT sum(tup_returned) FROM pg_stat_database) AS "Returned"
) t
UNION ALL
SELECT 'bio_stats' AS chart_name, row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(blks_read) FROM pg_stat_database) AS "Reads",
(SELECT sum(blks_hit) FROM pg_stat_database) AS "Hits"
) t
2020-11-06 18:07:05.459 EET postgres postgres [2104]LOG: statement: /*pga4dash*/
SELECT 'session_stats' AS chart_name, row_to_json(t) AS chart_data
FROM (SELECT
(SELECT count(*) FROM pg_stat_activity) AS "Total",
(SELECT count(*) FROM pg_stat_activity WHERE state = 'active') AS "Active",
(SELECT count(*) FROM pg_stat_activity WHERE state = 'idle') AS "Idle"
) t
UNION ALL
SELECT 'tps_stats' AS chart_name, row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(xact_commit) + sum(xact_rollback) FROM pg_stat_database) AS "Transactions",
(SELECT sum(xact_commit) FROM pg_stat_database) AS "Commits",
(SELECT sum(xact_rollback) FROM pg_stat_database) AS "Rollbacks"
) t
UNION ALL
SELECT 'ti_stats' AS chart_name, row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(tup_inserted) FROM pg_stat_database) AS "Inserts",
(SELECT sum(tup_updated) FROM pg_stat_database) AS "Updates",
(SELECT sum(tup_deleted) FROM pg_stat_database) AS "Deletes"
) t
UNION ALL
SELECT 'to_stats' AS chart_name, row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(tup_fetched) FROM pg_stat_database) AS "Fetched",
(SELECT sum(tup_returned) FROM pg_stat_database) AS "Returned"
) t
UNION ALL
SELECT 'bio_stats' AS chart_name, row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(blks_read) FROM pg_stat_database) AS "Reads",
(SELECT sum(blks_hit) FROM pg_stat_database) AS "Hits"
) t
......
Regards,
Gabi
On Fri, Nov 6, 2020 at 5:38 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 11/6/20 4:26 AM, Gabi Draghici wrote:
> Hi,
>
> 1. The job it's a simple call to one stored function like that :
> BEGIN
> CALL other_user_name.get_function();
> END;
> Problem is that despite the fact that it's long passed by the scheduled
> running time and I tried the "Run now" option (from pgadmin) a couple of
> times, there is no evidence that the job actually runned ! Nothing in
> pgagent log, main log or in pgagent.pga_joblog or pgagent.pga_jobsteplog.
> Is there any way to trace of debug this ?
Is the pgagent daemon running?
You could crank up the the log_statement to 'all'(temporarily as this
can generate a lot of logs) in postgresql.conf and reload the server.
Then tail the Postgres log file when click on 'Run now' to see what
happens.
>
> 2. Yes, pg_cron also looks good and it's my second option if I can't
> make 1 to work.
>
> Regards,
> Gabi
>
>
> On Thu, Nov 5, 2020 at 11:34 PM Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
> On 11/5/20 1:07 PM, Gabi Draghici wrote:
> >
> > Hi,
> >
> > I have installed postgresql 12 on sles 15 for some tests. Now I'm
> > interested in some sort of scheduler and from what I've read so far,
> > pgagent should do the job. So I've installed pgagent 4.0. I've
> added a
> > job (which I can see in pgagent.pga_job) but everytime I ran it
> (from
> > pgadmin) nothing happens ! When I start the pgagent I can see "...
> > pgagent ... connection authorized" in the main log. What else
> should I
> > check ?
>
> pg_cron:
>
> https://www.citusdata.com/blog/2020/10/31/evolving-pg-cron-together/
> <https://www.citusdata.com/blog/2020/10/31/evolving-pg-cron-together/>
>
> For pgagent what is the job doing and when? Could it be it hasn't run
> because it has not reached it's scheduled time.
>
> >
> > Thanks,
> > Gabi
> >
> >
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 11/6/20 8:12 AM, Gabi Draghici wrote: > > It't not a daemon yet (I started manually) but yes, it's running : > > postgres@dbdocs:~> ps aux | grep postgresql > postgres 2093 0.0 0.3 8720088 218280 ? Ss 17:54 0:00 > /usr/lib/postgresql12/bin/postgres -D /opt/postgresql/database > postgres 2315 0.0 0.0 64664 5708 pts/2 S 17:57 0:00 > /usr/bin/postgresql12-pgagent hostaddr=10.1.0.4 dbname=postgres > user=pgagent -s /opt/postgresql/pglog/pg_agent.log > postgres 2326 0.0 0.0 8696 820 pts/2 S+ 17:57 0:00 grep > --color=auto postgresql > > I've switched log_statement to 'all' and restarted the DB. All I see > it's a bunch of statements like these : > > 2020-11-06 18:07:03.869 EET postgres pgagent [2316]LOG: statement: > SELECT J.jobid FROM pgagent.pga_job J WHERE jobenabled AND > jobagentid IS NULL AND jobnextrun <= now() AND (jobhostagent = '' > OR jobhostagent = 'dbdocs-prd') ORDER BY jobnextrun > 2020-11-06 18:07:04.466 EET postgres postgres [2104]LOG: statement: > /*pga4dash*/ The above is from when you click 'Run now'? > > Regards, > Gabi > > > > > > > -- Adrian Klaver adrian.klaver@aklaver.com
There is just one that I think it's about "Run now" :
... EET postgres postgres [2104]LOG: statement: UPDATE pgagent.pga_job SET jobnextrun=now()::timestamptz WHERE jobid=1::integer
but that's just about it !
Is there any way I can test that pgagent it's able to start a job ?
Regards,
Gabi
On Fri, Nov 6, 2020 at 6:41 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 11/6/20 8:12 AM, Gabi Draghici wrote:
>
> It't not a daemon yet (I started manually) but yes, it's running :
>
> postgres@dbdocs:~> ps aux | grep postgresql
> postgres 2093 0.0 0.3 8720088 218280 ? Ss 17:54 0:00
> /usr/lib/postgresql12/bin/postgres -D /opt/postgresql/database
> postgres 2315 0.0 0.0 64664 5708 pts/2 S 17:57 0:00
> /usr/bin/postgresql12-pgagent hostaddr=10.1.0.4 dbname=postgres
> user=pgagent -s /opt/postgresql/pglog/pg_agent.log
> postgres 2326 0.0 0.0 8696 820 pts/2 S+ 17:57 0:00 grep
> --color=auto postgresql
>
> I've switched log_statement to 'all' and restarted the DB. All I see
> it's a bunch of statements like these :
>
> 2020-11-06 18:07:03.869 EET postgres pgagent [2316]LOG: statement:
> SELECT J.jobid FROM pgagent.pga_job J WHERE jobenabled AND
> jobagentid IS NULL AND jobnextrun <= now() AND (jobhostagent = ''
> OR jobhostagent = 'dbdocs-prd') ORDER BY jobnextrun
> 2020-11-06 18:07:04.466 EET postgres postgres [2104]LOG: statement:
> /*pga4dash*/
The above is from when you click 'Run now'?
>
> Regards,
> Gabi
>
>
>
>
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
I didn't read the documentation too well ... and completed the 'Host agent' with the IP instead of the hostname !
The job it's running now.
Thank you for help !
Regards,
Gabriel
On Sat, Nov 7, 2020 at 1:29 PM Gabi Draghici <gabi.draghici@gmail.com> wrote:
I don't think that most of them are related to pgagent, since they are coming anyway (I suppose it's about pgadmin's Dashboard).There is just one that I think it's about "Run now" :... EET postgres postgres [2104]LOG: statement: UPDATE pgagent.pga_job SET jobnextrun=now()::timestamptz WHERE jobid=1::integerbut that's just about it !Is there any way I can test that pgagent it's able to start a job ?Regards,GabiOn Fri, Nov 6, 2020 at 6:41 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:On 11/6/20 8:12 AM, Gabi Draghici wrote:
>
> It't not a daemon yet (I started manually) but yes, it's running :
>
> postgres@dbdocs:~> ps aux | grep postgresql
> postgres 2093 0.0 0.3 8720088 218280 ? Ss 17:54 0:00
> /usr/lib/postgresql12/bin/postgres -D /opt/postgresql/database
> postgres 2315 0.0 0.0 64664 5708 pts/2 S 17:57 0:00
> /usr/bin/postgresql12-pgagent hostaddr=10.1.0.4 dbname=postgres
> user=pgagent -s /opt/postgresql/pglog/pg_agent.log
> postgres 2326 0.0 0.0 8696 820 pts/2 S+ 17:57 0:00 grep
> --color=auto postgresql
>
> I've switched log_statement to 'all' and restarted the DB. All I see
> it's a bunch of statements like these :
>
> 2020-11-06 18:07:03.869 EET postgres pgagent [2316]LOG: statement:
> SELECT J.jobid FROM pgagent.pga_job J WHERE jobenabled AND
> jobagentid IS NULL AND jobnextrun <= now() AND (jobhostagent = ''
> OR jobhostagent = 'dbdocs-prd') ORDER BY jobnextrun
> 2020-11-06 18:07:04.466 EET postgres postgres [2104]LOG: statement:
> /*pga4dash*/
The above is from when you click 'Run now'?
>
> Regards,
> Gabi
>
>
>
>
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com