I wanted to dive into the world of postgresql so I installed both postgresql and pgadmin using docker (with the portainer web ui) and I found out that for automatic jobs you gotta install pgagent too but I haven't been able to find any information on running it with docker. I found this image https://hub.docker.com/r/chiavegatto/postgres-pgagent but portainer throws a 400 error code when trying to deploy the container which I haven't been able to figure out and I'm not quite sure of how this setup would work either. I saw it executes the following command pgagent -f hostaddr=postgres dbname=test user=postgres port=5432 at the entrypoint but what if I want to schedule jobs on multiple databases? - Has anyone been able to run pgagent with docker? - Is there any image of postgresql that comes with the agent preinstalled?

On windows (10,11 and windows server 2022). With PostgreSQL 14 and 15. Pgagent is running as a service. It runs SQL jobs and access pgpass correctly to run jobs as Local on multiple DBs. It also execute batch files correctly. **BUT if the batch files called in the step does not exists, the job fail (as expected) but the step hangs in an "r" state.** Job log: Step log: The expected behavior is that it return as status "f", result "1" and output "path to file does not exist" On another vm, with pg10, It works as intended like below: The "r" state is not an effect of PgAgent crashing. The service keeps running.(To be noted that the service crash if I run another agent that crashes whether it is SQL or Batch) **Anyone had that issue and managed to fix it ?** EDIT : The postgresql log points to the encoding. It is in french and the accents aren't handled. *2023-05-31 17:08:05.928 CEST ERREUR: séquence d'octets invalide pour l'encodage « UTF8 » : 0x82*

I am running Postgres 9.3 on Ubuntu 14.04 and I am unable to get pgAgent daemon/service to run. I have confirmed that the daemon is not running using service --status-all. When trying to run pgagent (whilst logged in as the only user in ubuntu) using pgagent hostaddr=127.0.0.1 dbname=postgres user=user1 the following messages are displayed: WARNING: Couldn't create the primary connection (attempt 10): fe_sendauth: no password supplied ERROR: Stopping pgAgent: Couldn't establish the primary connection with the database server. The lack of running pgAgent was also confirmed when trying to run a job on demand. The user 'user1' is able to log into pgAgmin and connect to the database from other computers on the local network. The user looks like this in the pg_hba.conf file: # Database administrative login by Unix domain socket local all user1 md5 A .pgpass password file also exists for this user in the home directory. What is causing the above messages and what needs to be done to get pgAgent to run scheduled tasks?

We have a Job setup on PGadmin that is being run via PgAgent in linux. However, when the Job is set as Batch. Even a simple script is getting same error error message says Any solution would be a great help, thanks

I installed **pgagent** using **EnterpriseDB**'s installer on MacOS. However, pgagent doesn't show up in pgadmin after the installation. Even when I try to manually add pgagent as an extension, I get the error: >extension "pgagent" is not available and that pgadmin "Could not open extension control file "/Applications/Postgres.app/Contents/Versions/15/share/postgresql/extension/pgagent.control": No such file or directory" Here are the installed pgagent files inside the mentioned directory, so I don't understand why it's not recognizing it.

How do I determine the version of pg_agent installed? Is it queriable with SQL just like checking PostgreSQL version / PostGIS version ?

I've been trying to make jobs running continuosly, so after previous run is done, the next one starts immediately. I made some initial success with following trigger: CREATE or replace FUNCTION continous_jobs() RETURNS trigger AS $$ BEGIN NEW.jobnextrun = clock_timestamp(); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER continous_jobs_trg BEFORE UPDATE OF jobnextrun ON pgagent.pga_job FOR EACH ROW when (NEW.jobdesc = 'CONTINOUS_JOB' ) EXECUTE PROCEDURE continous_jobs(); I set some test job, with pg_sleep(5) inside, and it worked great But after that I put there some real procedure inside, and it started to have delays (3-5 seconds). Why is that happening? I tried setting different jobnextrun values, also clock_timestamp() - '5 second'::interval, but it's still the same, delay is about couple of seconds

PgAdmin shows statistics of each run of a step of pg_agent (job). The list details the run ID, status, result, start & end & duration. The following is how it looks: How to get it by query command ?

I have several data sources and create a C# app to connect to each one, download the data and insert into postgres. That create a Data Pool and I create a Timer Event to execute a store procedure to process that pool in batch of 800. The problem is sometimes the 800 batch give me a time out. So I want change that from C# timer to pgAgent. I have configure pgAgent before, but not sure how will work on this case. Lets say I set the pgAgent to execute process every min - check for the unprocess rows, select 100 and start the process. - but if after one min the process hasnt end it, pgAgent will start again and see the same 100 rows unprocessed. - Also the process need to be sequential, I cant start rows 100 to 200 until first 100 are done. - Finally if process end In more than one minute and still data in the pool, the process should start again and not wait for the pgAgent timer. In C# I just create a thread and if after the timer run off the process is still busy I just wait for another minute. So can I configure pgAgent to handle this scenerio?

OS: Windows 10 PostgreSQL 11.4 I Installed PgAgent with Stackbuilder setup. In Windows services; PostgreServer service is running. However, the pgAgent service is stopped. When I try to start pgAdmin service from there I get this error message:

The PostgreSQL Scheduling Agent - pgAgent service on Local Computer started and then stopped. 
Some services stop automatically if they are not in use by other services or programs.

I have the pgAgent service to log On As -->LocalSystem (Note, I have also tried to change it to an windows administrator account) When opening pgAdmin 4 I can see the pgAgent Jobs tab as well as create jobs. Acording to the description on this page ,one should create an extension as well. However, when pgAgent was installed , there wasen't copied any extension files to %\PostgreSQL\11\share\extension so I can not create the extension in pgAdmin Any advice on how I get the pgAgent service to run my jobs I have createn in pg Admin? ---------- **EDIT:** On This thread They state that editing pgpass.conf in c:/user/user/AppData/postgresql helped. I have no such folder nore file. The only postgres related folders I've found under C:\Users is C:\Users\user\AppData\Roaming\pgAdmin ---------- **EDIT2:** * I opened CMD as an admin and run cd %APPDATA% to find correct folder. * I created the pgpass.conf inside newly created folder postgresql in appdata --> C:\Users\user\AppData\Roaming\postgresql\pgpass.conf. * I added that file as a system variable PGPASSFILE (Source1 and Source2 ). That did not change anything either. Cholud I do some editting in REDEDIT? Source3 ----------

I had Postgre running just fine on my Windows 10 (64-bit). Then I tried to install pgAgent via the stackbuilder for postgres. I got some error that it was not able to create a .conf file under %\user\appdata\postgres. (Note That folder has never existed. I had my data under C:\Program Files\PostgreSQL\11\data) After that is asked me to restart my PC, so I did. Now I am not able to start pgAdmin, not able to start postgre from microsoft services. I am not either able to run the stack builder for postgres, it simply suggest that it might help to reinstall postgres. Can one stackbuilder install really corrupt my entire database? I got these messages in the logger:

2019-09-10 15:33:03.300 EEST  ERROR:  could not open extension control file "C:/Program Files/PostgreSQL/11/share/extension/pgagent.control": No such file or directory
2019-09-10 15:33:03.300 EEST  STATEMENT:  CREATE EXTENSION pgagent;
2019-09-10 15:35:23.957 EEST  ERROR:  could not open extension control file "C:/Program Files/PostgreSQL/11/share/extension/pgagent.control": No such file or directory
2019-09-10 15:35:23.957 EEST  STATEMENT:  CREATE EXTENSION pgagent;
2019-09-10 15:43:44.944 EEST  ERROR:  schema "pgagent" does not exist
2019-09-10 15:43:44.944 EEST  STATEMENT:  SELECT has_schema_privilege('pgagent', 'USAGE')
2019-09-10 15:48:53.247 EEST  ERROR:  schema "pgagent" does not exist
2019-09-10 15:48:53.247 EEST  STATEMENT:  SELECT has_schema_privilege('pgagent', 'USAGE')
2019-09-10 15:51:53.369 EEST  ERROR:  schema "pgagent" does not exist
2019-09-10 15:51:53.369 EEST  STATEMENT:  SELECT has_schema_privilege('pgagent', 'USAGE')
2019-09-10 15:59:04.716 EEST  ERROR:  schema "pgagent" does not exist
2019-09-10 15:59:04.716 EEST  STATEMENT:  SELECT has_schema_privilege('pgagent', 'USAGE')
2019-09-10 16:17:15.856 EEST  LOG:  could not receive data from client: An existing connection was forcibly closed by the remote host.
2019-09-10 16:17:15.857 EEST  LOG:  could not receive data from client: An existing connection was forcibly closed by the remote host.
2019-09-10 16:17:26.569 EEST  LOG:  received fast shutdown request
2019-09-10 16:17:26.575 EEST  ERROR:  canceling statement due to user request
2019-09-10 16:17:26.576 EEST  ERROR:  canceling statement due to user request
2019-09-10 16:17:26.580 EEST  LOG:  aborting any active transactions
2019-09-10 16:17:26.580 EEST  ERROR:  canceling statement due to user request
2019-09-10 16:17:26.609 EEST  LOG:  background worker "logical replication launcher" (PID 18252) exited with exit code 1
2019-09-10 16:17:26.609 EEST  LOG:  background worker "TimescaleDB Background Worker Launcher" (PID 18188) exited with exit code 1
2019-09-10 16:17:26.610 EEST  LOG:  background worker "TimescaleDB Background Worker Scheduler" (PID 20960) exited with exit code 1
2019-09-10 16:17:26.732 EEST  LOG:  shutting down
2019-09-10 16:17:26.898 EEST  LOG:  database system is shut down

**EDIT:** When I try to execute pg_ctl start -D C:\Program Files\PostgreSQL\11\data, I get this error:

The code execution cannot proceed because LIBPQ.dll was not found.
Reinstalling the program may fix this problem.

The code execution cannot proceed because libiconv-2.dll was not
found. Reinstalling the program may fix this problem.

I do have postgres in windows path **EDIT2:** I ended up reinstalling the PostgreSQL server. Now I got it working again. The source of the original problem was probably that pgAgent was installed in a folder where already existing postgre files were

I have a PostgreSQL 11.3 Database. I installed pgAgent for creating jobs. Now I want to run my job every 10 second, but in GUI there is possibility to write with precision of minutes, not with precision of second. **Is it possible to run job created by pgAgent faster than every minute?**

I have a customer who would like to use pgagent, however, as a DBA I have security concerns with pgagent having the ability to execute shell scripts from within the Postgres instance. Can anybody tell me how I might prevent pgagent from running ad-hoc shell scripts, or more generally how I can secure pgagent so that the customer can schedule any SQL against any database they have access to but not do much else? Thanks. Bill

Im installing pgAgent using Stack Builder. In one step ask me for service account. Should I use PC, Domain or DB user? Or create a new one? I try using my DOMAIN\my.user.name also using the postgres user and return same error. I dont have the PC_Name\Administrator password > User creation failed. The password specified is incorrect or length of the user name is longer than supported

I run a postgreSQL 9.3.3 database and I want to do some automatic Tasks with pgAgent, but it doesn't run on another database than "postgres" db. What can I do to run pgAgent on another db ? thanks !

I have a problem to run pgAgent job for PostgreSQL 9.6 under ubuntu 16.04. The job is never executed. I already configured .pgpass and added: localhost:5432:*:postgres:mypass Also modified the permission for .pgpass: $ touch ~/.pgpass $ chmod 0600 ~/.pgpass After that I run pgAgent with no problem: $pgagent hostaddr=127.0.0.1 dbname=mydb user=postgres When I check the process for pgAgent I would get this result: user@gis-srv:~$ ps aux | grep pgagent user 153179 0.0 0.0 113844 9392 pts/26 S 17:15 0:00 pgagent hostaddr=127.0.0.1 dbname=postgres user=postgres user 153220 0.0 0.0 113844 9360 pts/26 S 17:16 0:00 pgagent hostaddr=127.0.0.1 dbname=mydb user=postgres user 153227 0.0 0.0 12944 936 pts/26 S+ 17:16 0:00 grep --color=auto pgagent Here is my job setting:
Job Setting:

Schedule setting:

Step Setting:
My propblen that the job is never executed and there is no error in the errors log for PostgreSQL. Please help me to find out why jobs never run. I am receving the following errors: from postgres.log: 2017-11-03 16:01:32.269 EDT postgres@postgres LOG: could not receive data from client: Connection reset by peer from pgagent.log: (attempt 9): fe_sendauth: no password supplied Fri Nov 3 16:01:32 2017 : WARNING: Couldn't create the primary connection (attempt 10): fe_sendauth: no password supplied Fri Nov 3 16:01:32 2017 : ERROR: Stopping pgAgent: Couldn't establish the primary connection with the database server. Also I modified the permission .pgpass to allow everyone to access it. (Just for testing)

I have a job and I set the schedule to run every minute. However, it always skips the next minute after finishing the process, even when the process ends in less than one minute. The problem is that data is still arriving during that minute so when the job starts, it has to process two minutes of data. I'd rather have two 15 seconds runs, than one long 30 seconds run. **How can I make pgAgent run every minute?** In other cases when I set the schedule to every 3 minutes, pgAgent skips the 4th minute and starts on the 5th.

When my windows server 2008 start, both services postgres and pgAgent start because they are automatic. But if my db is frozen and I need to restart the postgres service, the pgAgent is also stoped, but when postgres restart, pgAgent doesn't. So I have to restart pgAgent manually. Is there a way to link pgAgent, so that it starts whenever postgresql starts? PD: If someone knows a proper tag for windows services please add it, I couldn't find it.

I'm running a scheduled job using pgAgent on PostgresSQL 9.5. The duration of this job seems to grow over subsequent runs, and this slow down doesn't appear to be a direct relationship to the amount of data processed each time. The job itself is a function, which opens a cursor and records results in another table. The function always processes one hour of data, but that hour may contain more or less records depending on the time of day. My question is, is there any resource management I need to do when creating a pgAgent job? In other words, does anything persist between runs that should be cleaned up by the developer or is each instance of the job completely sandboxed?

I have a Django website with a postgresql 9.3.10 backend. The website contains dynamic user-generated content (think of it as a form of 9gag). There are some tables from which I routinely delete aged rows. The database is called dbname and I log into it via the user postgres. The following are two examples of such routine deletion: begin; DELETE FROM links_groupseen WHERE which_reply_id IN (SELECT id FROM links_reply where "submitted_on" < now() - interval '7 days'); DELETE FROM links_reply WHERE "submitted_on" < now() - interval '7 days'; commit; begin; DELETE FROM links_vote WHERE link_id IN (SELECT id FROM links_link WHERE id NOT IN (select answer_to_id from links_publicreply) AND "submitted_on" < now() - interval '1 hour'); DELETE FROM links_photoobjectsubscription WHERE which_link_id IN (SELECT id FROM links_link WHERE id NOT IN (select answer_to_id from links_publicreply) AND "submitted_on" < now() - interval '1 hour'); DELETE FROM links_link WHERE id NOT IN (select answer_to_id from links_publicreply) AND "submitted_on" < now() - interval '1 hour'; commit; There are ~10 other such operations I run. At the moment, I do everything manually - I want to automate these to run at a certain low-utlization time of day. How do I do that? Can someone give me an illustrative example? ------------------- As per my own rudimentary knowledge, I'm thinking I should first save the following shell script to /etc/cron.daily: #!/bin/sh dbname="dbname" username="postgres" psql $dbname $username << EOF begin; DELETE FROM links_groupseen WHERE which_reply_id IN (SELECT id FROM links_reply where "submitted_on" < now() - interval '7 days'); DELETE FROM links_reply WHERE "submitted_on" < now() - interval '7 days'; commit; begin; DELETE FROM links_vote WHERE link_id IN (SELECT id FROM links_link WHERE id NOT IN (select answer_to_id from links_publicreply) AND "submitted_on" < now() - interval '1 hour'); DELETE FROM links_photoobjectsubscription WHERE which_link_id IN (SELECT id FROM links_link WHERE id NOT IN (select answer_to_id from links_publicreply) AND "submitted_on" < now() - interval '1 hour'); DELETE FROM links_link WHERE id NOT IN (select answer_to_id from links_publicreply) AND "submitted_on" < now() - interval '1 hour'; commit; EOF And then add the said script to crontab like so: 30 4 * * * /etc/cron.daily/mypgscript And then service crond restart