Sql Job Schedule in Postgre

[JayakumarEMIS]

Hi @bill-ramos-rmoswi, I want to execute query for clean up table daily, for that need to sql jobs, can you able to help for implement job in postgre, when I searched internet I got to know there are mainly two extensions available in postgre pgagent and pgcron but i could not able to install it, getting below error kindly help me to fix

[staticlibs]

@JayakumarEMIS , official pgagent installer from EDB requires the EDB version of Postgres - cannot be used with WiltonDB. I suggest to try this standalone pgagent build instead - pgagent-4.2.2-test-1.

Its setup (below) is currently fully manual and quite complicated. There are ideas to implement the MSI installer and a GUI config tool for it and to ship required extension files with main WiltonDB installer. And also to add there the ability to run scheduled SQL not only over Postgres port, but also over TDS port (to be able to call T-SQL procedures).

There are 2 parts of the setup - setup pgagent tables in DB and PgAdmin GUI for them, and to setup pgagent Windows service.

Tables and PgAdmin setup:

1. copy pgagent.control and pgagent--4.2.sql to C:\Program Files\WiltonDB Software\wiltondb3.3\share\extension

2. in PgAdmin run:

CREATE EXTENSION pgagent;

This must be run connecting to WiltonDB, but NOT in wilton DB, but in postgres DB, this is needed for PgAdmin UI to work.

3. refresh the connection node in PgAdmin, pgAgent Jobs node should appear there:

pgagent Windows SCM setup:

1. unzipped pgagent-4.2.2-test-1 directory and add it to PATH system (not user) environment variable

2. open Admiistrator console and run:

pgagent.exe INSTALL pgagent_422 -l 2 "hostaddr=127.0.0.1 port=5432 dbname=postgres user=wilton password=wilton"

It will create "pgagent_422" SCM service with the description "PostgreSQL Scheduling Agent - pgagent_422"

3. change the pgagent_422 service user from Administrator to LocalSystem

4. run the service and look into Event Viewer, pgagent should spam there every 5 seconds

5. to remove the service, close it in SCM UI and run the following in Admin console:

pgagent.exe REMOVE pgagent_422

Sheduled jobs setup:

Create a pgAgent job in PgAdmin GUI.

Leave "Host Agent" field empty there, agent service will check jobs with Host Agent matches its Windows computer name or is empty. It will look for created jobs that has due date in the past. Enable queries logging to see agent queries.

I've checked only local connection SQL job type, but others should work too.

[psrinivasa2]

There are ideas to implement the MSI installer and a GUI config tool for it and to ship required extension files with main WiltonDB installer. And also to add there the ability to run scheduled SQL not only over Postgres port, but also over TDS port (to be able to call T-SQL procedures).

@staticlibs, any idea when this work will be started as you mentioned the steps seems to be complex and would be vey great if it as part of WiltonDB installer?

[staticlibs]

@psrinivasa2 , only SQL and control extension files are intended to be shipped with main WiltonDB installer. Actual pgagent installer is intended to be separate, it can be installed on a separate machine for remote DB.

Realistic timing for this is at least 1 month forward, because I am going to be unavailable for the 2 following weeks and can only begin the work after that.

Before getting into it, I wonder whether you can look into details how pgagent works (separate SCM service that polls DB every few seconds) and how authentication/password handling for pgagent is supposed to work. And also whether PgAdmin UI for scheduling is sufficient for your needs (it cannot realistically be changed). If you find that some other scheduler (like pgcron) can suite better - I can look into bringing it to WiltonDB instead.

[psrinivasa2]

Thanks @staticlibs, Who will be the alternate point of contact in your absence?

[JayakumarEMIS]

HI @staticlibs, when I executed below query in postgre db, pgagent not showing in UI but when I executed in wilton db it is showing, I am not sure why pgagent ui not showing while pointing into postgre db CREATE EXTENSION pgagent;

and also I was getting below error kindly help me to resolve it

these are the below steps I made:

1. create the extension in postgre db but pggent ui not showing in pgadmin
2. unzipped pgagent-4.2.2-test-1 directory and add it to PATH system environment variable
3. Executed below command in command prompt in admin mode
4. after that I can see scheduling agent in scm and changed logon into local account
5. but I am getting below error when started services

Kindly help to fix this,

[staticlibs]

@psrinivasa2 , there is no alternate point of contact.

[staticlibs]

@JayakumarEMIS

1. you are correct about using wilton DB for pgagent extension instead of postgres DB, with wilton DB use the following winservice registration instead:

pgagent.exe INSTALL pgagent_422 -l 2 "hostaddr=127.0.0.1 port=5432 dbname=wilton user=wilton password=wilton"

2. System cannot find the file specified can be caused by wrong PATH, service starts for me correctly if pgagent.exe and its deps are in PATH. Perhaps try adding also C:\Program Files\WiltonDB Software\wiltondb3.3\bin to PATH.

[JayakumarEMIS]

Hi @staticlibs,

1. I moved the unzipped file to C:\Program Files\WiltonDB Software\wiltondb3.3\bin path
2. I added the path C:\Program Files\WiltonDB Software\wiltondb3.3\bin\pgagent-4.2.2-test-1 in system env variable path
3. Ran the command (pgagent.exe INSTALL pgagent_422 -l 2 "hostaddr=127.0.0.1 port=5432 dbname=wilton user=wilton password=wilton") in command prompt in admin mode
4. Now I can able to start the service
5. but after started I can see below error in event viewer each 5 seconds, not sure why I am getting do you have any idea about that

[JayakumarEMIS]

Hi @staticlibs, we currently custom logic of change tracking in custom logic which is based on the trigger, we capturing the change information in our own custom table, that was done now, for next step as you know there is an option in change tracking for auto cleanup with retention period this functionality data will retain only with in particular time after it will remove automatically, so achieve the functionality we need schedule job to clean up the change information, I can see pgagent job schedule via pgagent, but we want to get via query will be fine, if pgagent not working fine for me, can you able to me to add pgcron extension to do the same

[staticlibs]

@JayakumarEMIS , can you see the polling SQL queries in DB log ("log_statement" needs to be enabled)? Also I suggest to add WiltonDB installation bin dir to PATH just in case. Placing pgagent dir inside bin dir is unnecessary.

[staticlibs]

@JayakumarEMIS , no, I won't be able to include pg_cron into WiltonDB until the next update in September. You may want to experiment with it on a plain Postgres on Linux - it should be trivial to be built and installed there unlike on Windows. Also, if you make a decision to use pg_agent or not - please let me know.

[JayakumarEMIS]

@staticlibs in pgagent most of the schedule setup done via pgagent ui, in other hand I can see pg_cron is scheduled via query manner instead of ui and seems like simple to schedule it, so I thought pg_cron would be better, if pg_cron added in wilton, so will write helper procedure and call that procedure to schedule job instead of setup via pgagent ui

[staticlibs]

@JayakumarEMIS , AFAIK PgAdmin UI just creates appropriate scheduling records in pgagent tables. It should be straightforward to create such records manually from stored procedures without the UI.

[JayakumarEMIS]

@staticlibs if we create schedule jobs in pgagent via stored procedure (without pgagent ui) it would be great, will look into it, and try to solve pgagent issue in my machine, thanks

[staticlibs]

@JayakumarEMIS , I've experimented with pgagent jobs creation in PgAdmin and found a few issues with it.

First, pgagent extension really needs to be created in postgres DB, not in wilton one. When tracing the queries made by PgAdmin, there are bits like: dbname = 'postgres' or dbname = 'edb'. To make the PgAdmin UI show that pgagent jobs - default DB name in connection config must match the DB name where pgagent extension is created.

Second, I've seen discrepancies between the queries PgAdmin generates and the constraints in the latest public extension files (needed to change this constraint to 31 and recreate the extension). This may depend on PgAdmin version. This is concerning in general, because everything on the side of pgagent service (including the SQL it sends) is easy to change if necessary - it has a small C++ codebase and no active development upstream. But changing anything in PgAdmin does not seem realistic - it is a massive and actively developed codebase, basically the only way to modify it is to send the changes to EDB and convince them to include them, that may not be feasible.

This may be less of a concern if you are going to create scheduled jobs without the PgAdmin UI at all.

[JayakumarEMIS]

@staticlibs can we use pg_cron extension, seems simple to create a schedule jobs, if pg admin depends with EDB so can we try alternate for that?

[JayakumarEMIS]

@JayakumarEMIS , can you see the polling SQL queries in DB log ("log_statement" needs to be enabled)? Also I suggest to add WiltonDB installation bin dir to PATH just in case. Placing pgagent dir inside bin dir is unnecessary.

@staticlibs regarding this issue, I have checked wilton db log statement there is no log related to pgagent I can see only event viewer it is logging each 5 seconds,

if possible can you share the snap of where you place the file and set the path in your environment variable it would be helpful so i can compare it and check

[staticlibs]

@JayakumarEMIS , unlike pgagent, that is cross-platform out of the box, pg_cron does not support Windows (can only be compiled on Linux). If you can arrange porting it to Windows - I will happily include it with WiltonDB. I don't have cycles to do such porting myself now (it's like a week of work), only if much later.

Alternatively, can you do scheduling from inside the app (using some C# cron-like lib) or use Windows Scheduler?

[staticlibs]

@JayakumarEMIS , I will try pgagent setup in a clean env and will write the details.

[bill-ramos-rmoswi]

@staticlibs Well stated. It's very important to consider extensions that have active development! There is a reason that this extension is not available on RDS or Aurora PostgreSQL and Alex nailed it!

[staticlibs]

@JayakumarEMIS , set it up in a completely clean environment:

1. PgAdmin version 7 must be used (I've user 7.8), it seems that they have updated pgagent handling in PgAdmin 8 but did not update the public pgagent sources on github, so this functionality is broken in PgAdmin 8.

2. extension needs to be created in postgres DB, this DB also needs to be a "maintenance DB" for PgAdmin connection (also use dbname=postgres when creating the service):

3. test table to check that scheduled jobs work:

create table tab1 (col1 int, col2 timestamptz)

4. created a scheduled job to run every minute with the following query:

insert into tab1 (col1, col2) values (42, current_timestamp)

5. pgagent dir needs to be added to PATH:

6. machine needs to be rebooted so the PATH is re-read by Windows SCM (don't know if it is possible to refresh it without reboot), after that polling runs correctly for me (without "no such file" error) and scheduled job actually runs

[JayakumarEMIS]

Hi @staticlibs, I was busy with other work and currently check the pgagent job again, I am still not able to pgagent job on my machine, I did below steps from your above guidelines,

1. I have installed pgadmin version 7
2. copy pgagent.control and pgagent--4.2.sql to C:\Program Files\WiltonDB Software\wiltondb3.3\share\extension and create the extension in postgre database, and created successfully
3. configure the unzip path in system variables
4. pgagent.exe INSTALL pgagent_422 -l 2 "hostaddr=127.0.0.1 port=5432 dbname=postgres user=wilton password=wilton" run this command in command prompt
5. I changed to log on as is local system and start the service
6. I created one scheduled as you mentioned in above script create table tab1 (col1 int, col2 timestamptz) insert into tab1 (col1, col2) values (42, current_timestamp) this script will run every one minute i scheduled
7. but still my job is not running and is not inserted the data every one minute

I am not sure why is not running, and I can see every 5 seconds this log is triggering in event viewer is there any way to see exact error log why the job is not running, and also the pgagent installation setup now quite complicated, can you make into msi installer with wilton db

thanks

[staticlibs]

@JayakumarEMIS

Please try to reboot the machine after changing the PATH. I've got the same behaviour like on the last screenshot when old PATH was read by the pgagent process started by SCM.

Yes, installer is possible, but I first suggest to check that pgagent functionality is suitable for your needs.

[JayakumarEMIS]

HI @staticlibs, I have tried restart my system after change the path in system environment variable, still the job is not running I am getting the same issue "could not find the path error message" in event viewer every 5 seconds,

if suppose the service is running properly, i will get this same message or any other success message will get in event viewer for every 5 seconds, why because need to understand the job is not taken because of the service not configured properly or something I missed when creating job using pgagent in pg admin, and also can we see any exact error logs which will help to fix my issue

[staticlibs]

@JayakumarEMIS

Can you please share the contents of all 4 Event Log messages that are logged every 5 seconds (for example: 19:07:20 on your screenshot). And also check the SQL queries sent by pgagent in DB server log - are there any such queries logged there (with log_statement = all)? And also share the output of the following query in postgres DB:

select * from pgagent.pga_schedule

I cannot reproduce the exact problem locally, when service runs but "The system cannot find the file specified" is in event viewer, though I believe I've seen such message in the past. I suppose this message is related to service registration details, because when it works correctly - the message is similar (see screenshot below), but cannot be sure without local example.

[JayakumarEMIS]

Hi @staticlibs, when I create the pgagent extension in postgre db i am not able to see in pgagent ui in pgadmin, it is only showing when I created extension in wilton db, do you have any idea about it? but I have created the service use below command pgagent.exe INSTALL pgagent_422 -l 2 "hostaddr=127.0.0.1 port=5432 dbname=postgres user=wilton password=wilton"

and another thing when I remove extension from wilton db the service got stopped

might be this will be issue?

[staticlibs]

@JayakumarEMIS

For scheduler UI to be shown in PgAdmin - "Maintenance database" in PgAdmin connection settings must be set to postgres too:

I suggest to drop the pgagent extension in all DBs and then to re-create it from scratch in postgres DB under correct PgAdmin connection.

[JayakumarEMIS]

Hi @staticlibs, I have changed maintenance database is postgre and created extension in postgre and dropped the extensions from wilton, and I recreated the service setup from beginning but still the job not running for me I have added all four information logs below from event viewer

and also result snap for the below query from postgre db: select * from pgagent.pga_schedule

[staticlibs]

@JayakumarEMIS

Except for the "System cannot find the file specified" message (that is highly likely related to event source registration), your setup looks correct to me. I assume the scheduled tasks should run correctly now. Can you see polling queries in DB server log? "Checking for jobs to run" message is written to event log every time the service polling the DB for pending jobs.

[JayakumarEMIS]

Hi @staticlibs, sorry I could not able to see any polling query in my db log, for your reference I am sharing my log files to you, can you kindly help me to find any related to sql jobs query, and also in this same log file I can see previous date log as well I am not sure why those old logs are placed inside the today log files

postgresql-Wed.log

[staticlibs]

@JayakumarEMIS

Please enable SQL queries logging setting log_statement='all' server parameter (reference) with wdb_config. Then you will be able to check whether polling queries from service are sent to DB or not.

[JayakumarEMIS]

Hi @staticlibs, pg agent jobs are running fine in my machine I will look into further exploration and check it will satisfy my requirements, if everything is fine please added the extension into wilton db itself, thanks for the support

[staticlibs]

@JayakumarEMIS

pg agent jobs are running fine in my machine

That's great to know! I wonder whether you've actually got to the bottom of the "The system cannot find the file specified" problem (in case someone else will have the same problem in future), or it just stopped occurring (like it was in my local env)?

I will look into further exploration and check it will satisfy my requirements, if everything is fine please added the extension into wilton db itself, thanks for the support

I've filed #56 to track this. There is a number of points to clarify there (GUI tool for initial config, create/remove service from installer or from a GUI tool, support for scheduled T-SQL queries over TDS etc) - I will write the details there once they are more clear.