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*/ 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.kla...@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.kla...@aklaver.com > > <mailto:adrian.kla...@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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
