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
Re: pgagent
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 ? 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 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.kla...@aklaver.com >
Re: pgagent
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/2S17: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/2S+ 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 jobenabledAND jobagentid IS NULLAND 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 .. Regards, Gabi On Fri, Nov 6, 2020 at 5:38 PM Adrian Klaver 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 log
Re: pgagent
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::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 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/2S17: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/2S+ 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 jobenabledAND > > jobagentid IS NULLAND 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.kla...@aklaver.com >
Re: pgagent
My bad ! 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 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::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 > 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/2S17: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/2S+ 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 jobenabledAND >> > jobagentid IS NULLAND 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.kla...@aklaver.com >> >
