Thread: pgagent jobs never start
I have seen other post that describe similar trouble. Dec 8th "pgAgent problems" Last suggestion on at this referenced post was to use "net start pgAgent" Had already done that prior to my quest to find the answer to this trouble. I have a clean install of Postgres 8.1.2 WinXP sp2 Of course I disobeyed all the security warnings during the installation and am confident that the accounts running the services are dbo and / or sufficiently privileged to accomplish my goal. All I am trying to accomplish is a simple insert into a test table Insert into "public".testagent values (default, 'hogan') I schedule the job to run every min of every day...just trying to see this utility work... Everything appears to work as per the instructions and help files...however the job just never starts... No history / no stats... Where else can I turn for help if this is not the right place to ask.
> -----Original Message----- > From: pgadmin-support-owner@postgresql.org > [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of > Hogan, James F. Jr. > Sent: 13 January 2006 18:23 > To: pgadmin-support@postgresql.org > Subject: [pgadmin-support] pgagent jobs never start > > I schedule the job to run every min of every day...just trying to see > this utility work... > > Everything appears to work as per the instructions and help > files...however the job just never starts... Do you have anything in the Host Agent field for the job? If so, recreate the job with it left blank and see if it starts OK. Failing that, please dump the pgagent scheme from your master connection database and send it over. Regards, Dave
> -----Original Message----- > From: Hogan, James F. Jr. [mailto:JHogan@seton.org] > Sent: 16 January 2006 17:41 > To: Dave Page > Subject: RE: [pgadmin-support] pgagent jobs never start > > I did in fact have two separate databases one named > "postgres" the other > named "hogan" > > I was hopeful that you were on to my trouble...However.... > > I have deleted the "hogan" database and created a schema > "hogan" within > the database "posgtres" > > Created a new table within the "hogan" schema > > "hogan".testagent > > Validated that account 'svc_pgsql' has sufficient rights. > > Both server and agent services run under 'svc_pgsql' > > Deleted old job and created new to run every minute > > Insert into "hogan".testagent Values (default, 'hogan') > > Restarted both Windows Services...first the Posgtres Service then the > PGAgent Service > > The New Job still fails to start... > > > > Attached result of > > Pg_dump -f c:\pgagent2.sql -U svc_pgsql postgres > > After changes noted above. OK, loaded that into my master connection DB, installed pgAgent, started it up and away it went inserting rows into the test table. So, I suspect the agent service is failing to connect to your database for some reason. Is there anything obvious in the event log? If not can you remove and add the service again with the -l 2 option please? That will cause a bunch of debug output to be logged to the Application event log, and should include some hints as to what's going on. Regards, Dave.
> -----Original Message----- > From: Hogan, James F. Jr. [mailto:JHogan@seton.org] > Sent: 17 January 2006 15:37 > To: Dave Page > Subject: RE: [pgadmin-support] pgagent jobs never start > > Wow that was a world of help adding the -l 2 switch... Great, glad to hear it's working.) > I will post a Blurb about this to my original post and also submit a > Document update Request to include this "password=" > > Looks like they indented to as I notice that the string example they > provided doesn't have the closing double quote. Not quite :-) The quotes are around the path to the pgAgent executable, not the options. If you read the next section of the docs, you'll see why the password= bit is excluded from the example: Database password - DO NOT be tempted to include a password in the pgAgent connection string - on *nix systems it may be visible to all users in 'ps' output, and on Windows systems it will be stored in the registry in plain text. Instead, use a libpq ~/.pgpass file to store the passwords for every database that pgAgent must access. Details of this technique may be found in the PostgreSQL documentation (http://www.pgadmin.org/docs/1.4/pg/libpq-pgpass.html). (Now available at http://www.pgadmin.org/docs/1.4/pgagent-install.html on the new website that just went live :-) ). The pgpass file should be configured for the Windows user account that the service runs as with appropriate connection info for the databases/postgresql user & password. > Again > > THANK YOU You're welcome. Regards, Dave.
Thanks to the assistance of Mr. Page my troubles have been solved Adding -l 2 switch the event log showed that there was issue with the pgAgent INSTALL command. I was omitting at the end of the command "password=secrete" After REMOVE and INSTALL with the corrected string All is Well "pgAgent INSTALL -l 2 -u svc_pgsql -p secrete hostaddr=127.0.0.1 dbname=Postgres user=svc_pgsql password=secrete" p.s. don't forget "net start pgAgent" Thanks Dave -----Original Message----- From: Hogan, James F. Jr. Sent: Tuesday, January 17, 2006 9:37 AM To: 'Dave Page' Subject: RE: [pgadmin-support] pgagent jobs never start Wow that was a world of help adding the -l 2 switch... I had some trouble with the "INSTALL" string syntax... Odd thing is I repeated it twice... The missing link was "password=" This is not shown in the documentation I am looking at. Documentation: ************************************** Usage: "C:\Program Files\pgAdmin III\pgAgent" REMOVE <serviceName> "C:\Program Files\pgAdmin III\pgAgent" INSTALL <serviceName>[options] <connect-string> options: -u <user or DOMAIN\user> -p <password> -d <displayname> -t <poll time interval in seconds(default 10)> -r <retry period after connection abort in seconds (>=10, default 30)> -l <logging verbosity (ERROR=0, WARNING=1, DEBUG=2, default 0)> The service may be quite simply installed from the command line as follows: "C:\Program Files\pgAdmin III\pgAgent" INSTALL pgAgent -u postgres -p secret hostaddr=127.0.0.1 dbname=pgadmin user=Postgres *************************************************** HERE IS WHAT I HAD PgAgent INSTALL pgAgent -u svc-pgsql -p secrete hostaddr=127.0.0.1 dbname=Postgres user=svc_pgsql I then added to the end on a wim "password=secrete" And I am up and running Thank you for all your help I hope I am able to help you or someone else out in return some day. I will post a Blurb about this to my original post and also submit a Document update Request to include this "password=" Looks like they indented to as I notice that the string example they provided doesn't have the closing double quote. Again THANK YOU Jim -----Original Message----- From: Dave Page [mailto:dpage@vale-housing.co.uk] Sent: Tuesday, January 17, 2006 4:36 AM To: Hogan, James F. Jr. Cc: pgadmin-support@postgresql.org Subject: RE: [pgadmin-support] pgagent jobs never start > -----Original Message----- > From: Hogan, James F. Jr. [mailto:JHogan@seton.org] > Sent: 16 January 2006 17:41 > To: Dave Page > Subject: RE: [pgadmin-support] pgagent jobs never start > > I did in fact have two separate databases one named > "postgres" the other > named "hogan" > > I was hopeful that you were on to my trouble...However.... > > I have deleted the "hogan" database and created a schema > "hogan" within > the database "posgtres" > > Created a new table within the "hogan" schema > > "hogan".testagent > > Validated that account 'svc_pgsql' has sufficient rights. > > Both server and agent services run under 'svc_pgsql' > > Deleted old job and created new to run every minute > > Insert into "hogan".testagent Values (default, 'hogan') > > Restarted both Windows Services...first the Posgtres Service then the > PGAgent Service > > The New Job still fails to start... > > > > Attached result of > > Pg_dump -f c:\pgagent2.sql -U svc_pgsql postgres > > After changes noted above. OK, loaded that into my master connection DB, installed pgAgent, started it up and away it went inserting rows into the test table. So, I suspect the agent service is failing to connect to your database for some reason. Is there anything obvious in the event log? If not can you remove and add the service again with the -l 2 option please? That will cause a bunch of debug output to be logged to the Application event log, and should include some hints as to what's going on. Regards, Dave.