Re: pgagent

[Gabi Draghici <gabi.draghici@xxxxxxxxx>]

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.klaver@xxxxxxxxxxx> 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@xxxxxxxxxxx
> <mailto:adrian.klaver@xxxxxxxxxxx>> 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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
>


--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx