Thread: PgAgent does not works
Hi, I want to run some jobs in postgresql database using pgAgent. I have installed PGAdmin III on my windows XP machine. I tried by scheduling small job like inserting data into a table. The job is created, I ahve scheduled to run every minute but it is not running at all. I can not see it in the statistics tab in pgAdmin as well. The job is shown in the pga_jobs table in pgagent schema. I have installed it as service and its running perfectly fine. Kindly let me know whats going wrong. _________________________________________________________________ Like puzzles? Play free games & earn great prizes. Play Clink now. http://club.live.com/clink.aspx?icid=clink_hotmailtextlink2
Vishal Arora wrote: > > Hi, > > I want to run some jobs in postgresql database using pgAgent. I have > installed PGAdmin III on my windows XP machine. > > I tried by scheduling small job like inserting data into a table. The > job is created, I ahve scheduled to run every minute but it is not > running at all. I can not see it in the statistics tab in pgAdmin as > well. The job is shown in the pga_jobs table in pgagent schema. I have > installed it as service and its running perfectly fine. > > Kindly let me know whats going wrong. > Are the pgAdmin maintenance DB, the DB containing the pgagent schema, and the DB that pgagent connects to all the same one? Regards, Dave
>From: Dave Page <dpage@postgresql.org> >To: Vishal Arora <aroravishal22@hotmail.com> >CC: pgadmin-support@postgresql.org >Subject: Re: [pgadmin-support] PgAgent does not works >Date: Sun, 24 Jun 2007 20:46:53 +0100 > >Vishal Arora wrote: >> >>Hi, >> >>I want to run some jobs in postgresql database using pgAgent. I have >>installed PGAdmin III on my windows XP machine. >> >>I tried by scheduling small job like inserting data into a table. The job >>is created, I ahve scheduled to run every minute but it is not running at >>all. I can not see it in the statistics tab in pgAdmin as well. The job is >>shown in the pga_jobs table in pgagent schema. I have installed it as >>service and its running perfectly fine. >> >>Kindly let me know whats going wrong. >> > >Are the pgAdmin maintenance DB, the DB containing the pgagent schema, and >the DB that pgagent connects to all the same one? Yes it is the postgres database as mentioned in the installation guide. >Regards, Dave > >---------------------------(end of broadcast)--------------------------- >TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly _________________________________________________________________ Picture this � share your photos and you could win big! http://www.GETREALPhotoContest.com?ocid=TXT_TAGHM&loc=us
Vishal Arora wrote: > > > >> From: Dave Page <dpage@postgresql.org> >> To: Vishal Arora <aroravishal22@hotmail.com> >> CC: pgadmin-support@postgresql.org >> Subject: Re: [pgadmin-support] PgAgent does not works >> Date: Sun, 24 Jun 2007 20:46:53 +0100 >> >> Vishal Arora wrote: >>> >>> Hi, >>> >>> I want to run some jobs in postgresql database using pgAgent. I have >>> installed PGAdmin III on my windows XP machine. >>> >>> I tried by scheduling small job like inserting data into a table. The >>> job is created, I ahve scheduled to run every minute but it is not >>> running at all. I can not see it in the statistics tab in pgAdmin as >>> well. The job is shown in the pga_jobs table in pgagent schema. I >>> have installed it as service and its running perfectly fine. >>> >>> Kindly let me know whats going wrong. >>> >> >> Are the pgAdmin maintenance DB, the DB containing the pgagent schema, >> and the DB that pgagent connects to all the same one? > > Yes it is the postgres database as mentioned in the installation guide. Do you have anything listed in the host agent field? If so, please remove it. Regards, Dave.
I have also tried that. When i create a new job I think some rows should be written to the tables in the pgagent schema. But when I am checking physically the tables there is no data. Is that so due to service of pgagent. Thanks & Best Regards M.AHRAM KHAN -----Original Message----- From: pgadmin-support-owner@postgresql.org [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of Dave Page Sent: Monday, June 25, 2007 1:17 PM To: Vishal Arora Cc: pgadmin-support@postgresql.org Subject: Re: [pgadmin-support] PgAgent does not works Vishal Arora wrote: > > > >> From: Dave Page <dpage@postgresql.org> >> To: Vishal Arora <aroravishal22@hotmail.com> >> CC: pgadmin-support@postgresql.org >> Subject: Re: [pgadmin-support] PgAgent does not works >> Date: Sun, 24 Jun 2007 20:46:53 +0100 >> >> Vishal Arora wrote: >>> >>> Hi, >>> >>> I want to run some jobs in postgresql database using pgAgent. I have >>> installed PGAdmin III on my windows XP machine. >>> >>> I tried by scheduling small job like inserting data into a table. The >>> job is created, I ahve scheduled to run every minute but it is not >>> running at all. I can not see it in the statistics tab in pgAdmin as >>> well. The job is shown in the pga_jobs table in pgagent schema. I >>> have installed it as service and its running perfectly fine. >>> >>> Kindly let me know whats going wrong. >>> >> >> Are the pgAdmin maintenance DB, the DB containing the pgagent schema, >> and the DB that pgagent connects to all the same one? > > Yes it is the postgres database as mentioned in the installation guide. Do you have anything listed in the host agent field? If so, please remove it. Regards, Dave. ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
Khan, Mahmood Ahram wrote: > I have also tried that. When i create a new job I think some rows should > be written to the tables in the pgagent schema. But when I am checking > physically the tables there is no data. Is that so due to service of > pgagent. pgagent itself doesn't have any effect on the creation of jobs. In your case, it sounds like the schema is not where you think it is, or you have a second one and you and pgAdmin do not agree on which is the right one. Check which database is your maintenance DB (look at the server properties), and then look in the pgagent schema in *that* database. Regards, Dave
Vishal Arora wrote: > There is nothing mentioned in the host agent field. I am sending the > snapshots if you want to have a look at. > - vishal Whats your pgagent connection string? Regards, Dave
>From: Dave Page <dpage@postgresql.org> >To: Vishal Arora <aroravishal22@hotmail.com> >CC: pgadmin-support@postgresql.org >Subject: Re: [pgadmin-support] PgAgent does not works >Date: Mon, 25 Jun 2007 10:50:03 +0100 > >Vishal Arora wrote: > >>There is nothing mentioned in the host agent field. I am sending the >>snapshots if you want to have a look at. >>- vishal > >Whats your pgagent connection string? pgAgent -u postgres -p XXXX hostaddr=127.0.0.1 dbname=postgres user=postgres (where XXXX is replaced with the actual postgres password.) - Vishal > >Regards, Dave _________________________________________________________________ Hotmail to go? Get your Hotmail, news, sports and much more! http://mobile.msn.com
Vishal Arora wrote: > pgAgent -u postgres -p XXXX hostaddr=127.0.0.1 dbname=postgres > user=postgres Hmm, OK - can you please add -l 2 to that to increase the log output, and send me a dump of that from eventviewer once it's run long enough to have missed a job, along with a pg_dump of your pgagent schema from the postgres database. thanks, Dave
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi Can you reinstall your services with logging enable (option -l)? C:\Program Files\pgAdmin III\pgAgent INSTALL pgAgent -l 1 -u postgres -p secret hostaddr=127.0.0.1 dbname=postgres user=postgres And check your Event Viewer. Regards, Christophe Chauvet. Khan, Mahmood Ahram a écrit : > I have also tried that. When i create a new job I think some rows should > be written to the tables in the pgagent schema. But when I am checking > physically the tables there is no data. Is that so due to service of > pgagent. > > Thanks & Best Regards > > M.AHRAM KHAN > -----Original Message----- > From: pgadmin-support-owner@postgresql.org > [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of Dave Page > Sent: Monday, June 25, 2007 1:17 PM > To: Vishal Arora > Cc: pgadmin-support@postgresql.org > Subject: Re: [pgadmin-support] PgAgent does not works > > Vishal Arora wrote: >> >> >>> From: Dave Page <dpage@postgresql.org> >>> To: Vishal Arora <aroravishal22@hotmail.com> >>> CC: pgadmin-support@postgresql.org >>> Subject: Re: [pgadmin-support] PgAgent does not works >>> Date: Sun, 24 Jun 2007 20:46:53 +0100 >>> >>> Vishal Arora wrote: >>>> Hi, >>>> >>>> I want to run some jobs in postgresql database using pgAgent. I > have >>>> installed PGAdmin III on my windows XP machine. >>>> >>>> I tried by scheduling small job like inserting data into a table. > The >>>> job is created, I ahve scheduled to run every minute but it is not >>>> running at all. I can not see it in the statistics tab in pgAdmin as > >>>> well. The job is shown in the pga_jobs table in pgagent schema. I >>>> have installed it as service and its running perfectly fine. >>>> >>>> Kindly let me know whats going wrong. >>>> >>> Are the pgAdmin maintenance DB, the DB containing the pgagent schema, > >>> and the DB that pgagent connects to all the same one? >> Yes it is the postgres database as mentioned in the installation > guide. > > Do you have anything listed in the host agent field? If so, please > remove it. > > Regards, Dave. > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > > -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.2 (MingW32) iD8DBQFGf5EOMl/S4ZhUIzERAh1BAJ44s/MwmWgE2RryzPQf/lKYopxxEwCfWdf1 R7QbXRICBYaG4DsDPGu/Vjs= =Jp3Q -----END PGP SIGNATURE-----
Vishal Arora wrote:
>
> Please find the event log and dump of pgAgent schema.
From the dump file, this is the only schedule present:
COPY pga_schedule (jscid, jscjobid, jscname, jscdesc, jscenabled, jscstart,
jscend, jscminutes, jschours, jscweekdays, jscmonthdays, jscmonths) FROM
stdin;
1 1 sch1 t 2007-06-25 14:33:01+05:30 2008-06-25
00:00:00+05:30
{f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f}
{f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f} {f,f,f,f,f,f,f}
{f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f}
{f,f,f,f,f,f,f,f,f,f,f,f}
\.
Those arrays of f's are true or false booleans in arrays of minutes,
hours, weekdays, monthsdays and months, each corresponding to the check
list boxes in the Schedule configuration dialog (see
http://www.pgadmin.org/docs/1.6/pgagent-schedules.html). Because all of
them are false in your case, there is effectively no schedule defined.
you must define at least one time element to trigger the schedule!
As for the eventlog, well, there aren't *any* pgagent entries present.
Are you sure it's installed and running as a service?
Regards, Dave.
Khan, Mahmood Ahram wrote: > Still not working > Assuming thats an error you get when starting pgagent, then I think it's fairly clear what the problem is from the log you posted: Event Type: Failure Audit Event Source: Security Event Category: Logon/Logoff Event ID: 529 Date: 6/25/2007 Time: 3:57:20 PM User: NT AUTHORITY\SYSTEM Computer: INBLR-KHANM2 Description: Logon Failure: Reason: Unknown user name or bad password ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ User Name: postgres Domain: INBLR-KHANM2 Logon Type: 5 LogonProcess: Advapi Authentication Package: Negotiate Workstation Name: INBLR-KHANM2 And am I safe to assume from the similarity of your computer hostnames that you and Vishal are working together, or at least in the same company? Regards, Dave
Vishal Arora wrote: > Hi > > I am sending the event file and dump once again. Sorry for trouble. OK, that looks a little more like I'd expect. So, the event log contains a of messages like: Couldn't create connection: fe_sendauth: no password supplied. (to get rid of the garbage before that, register pgaevent.dll using regsvr32). So you need to specify the password - see the bottom section of http://www.pgadmin.org/docs/1.6/pgagent-install.html which discusses the database password. The database dump does also specify a schedule now - from an eyeball of the arrays it looks like it's setup to run at 16:40 every Monday which ties up with the job record which has a next runtime value of 2007-06-25 16:40:00+05:30. Regards, Dave.
>From: Dave Page <dpage@postgresql.org> >To: Vishal Arora <aroravishal22@hotmail.com> >Subject: Re: [pgadmin-support] PgAgent does not works >Date: Mon, 25 Jun 2007 13:59:32 +0100 > >Vishal Arora wrote: >> >>I have tried to install the dll using RegSvr32 - it says - "... >>pgaevent.dll was loaded, but the DllRegiseterServer entry point was not >>found" - The file can not be regisetered. >> > >Sorry - my fault. That one is actually registered by pgAgent when it's >installed as a service - at least it should be. Assuming your pgAgent >service id is 'pgagent', you can manually install by creating the registry >key: > >HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Application\pgagent > >and then adding a string value called EventMessageFile, containing the full >path and filename of pgaevent.dll, and a dword value called TypesSupported >with a value of 7. > >Those entries should be created when you install pgagent as a service >though; and if it can't create them, you definitely should see an error. > These values are already in place. >>>So you need to specify the password - see the bottom section of >>>http://www.pgadmin.org/docs/1.6/pgagent-install.html which discusses the >>>database password. >> >>I already have pgpass.conf file in place. > >For you, or the postgres user account that the service runs under? You need >the latter. Also, if you're using 127.0.0.1 as the address, try localhost >instead (or vice-versa). The value there should match the value used in the >pgAgent connection string of course. > I have tried using both of these values and also the hostname as well. But nothing worked. >Regards, Dave. _________________________________________________________________ Get a preview of Live Earth, the hottest event this summer - only on MSN http://liveearth.msn.com?source=msntaglineliveearthhm
>From: "Vishal Arora" <aroravishal22@hotmail.com> >To: dpage@postgresql.org >CC: pgadmin-support@postgresql.org >Subject: Re: [pgadmin-support] PgAgent does not works >Date: Tue, 26 Jun 2007 04:17:45 +0000 > > > > >>From: Dave Page <dpage@postgresql.org> >>To: Vishal Arora <aroravishal22@hotmail.com> >>Subject: Re: [pgadmin-support] PgAgent does not works >>Date: Mon, 25 Jun 2007 13:59:32 +0100 >> >>Vishal Arora wrote: >>> >>>I have tried to install the dll using RegSvr32 - it says - "... >>>pgaevent.dll was loaded, but the DllRegiseterServer entry point was not >>>found" - The file can not be regisetered. >>> >> >>Sorry - my fault. That one is actually registered by pgAgent when it's >>installed as a service - at least it should be. Assuming your pgAgent >>service id is 'pgagent', you can manually install by creating the registry >>key: >> >>HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Application\pgagent >> >>and then adding a string value called EventMessageFile, containing the >>full path and filename of pgaevent.dll, and a dword value called >>TypesSupported with a value of 7. >> >>Those entries should be created when you install pgagent as a service >>though; and if it can't create them, you definitely should see an error. >> > >These values are already in place. > >>>>So you need to specify the password - see the bottom section of >>>>http://www.pgadmin.org/docs/1.6/pgagent-install.html which discusses the >>>>database password. >>> >>>I already have pgpass.conf file in place. >> >>For you, or the postgres user account that the service runs under? You >>need the latter. Also, if you're using 127.0.0.1 as the address, try >>localhost instead (or vice-versa). The value there should match the value >>used in the pgAgent connection string of course. >> > > >I have tried using both of these values and also the hostname as well. But >nothing worked. Thanks for all your time and support. I have managed to run the pgAgent working fine. The service was running under postgres account but pgpass.conf file was missing in the same. I created one and it worked. Thanks for your suggestions. - Vishal > >>Regards, Dave. > >_________________________________________________________________ >Get a preview of Live Earth, the hottest event this summer - only on MSN >http://liveearth.msn.com?source=msntaglineliveearthhm > > >---------------------------(end of broadcast)--------------------------- >TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate _________________________________________________________________ Hotmail to go? Get your Hotmail, news, sports and much more! http://mobile.msn.com