Обсуждение: PG 9.1 Windows 7 64-bit pgAdmin says all pgAgent jobs fail
I started writing this after struggling and Googling for hours and not getting anywhere. I'm running EnterpriseDB 9.1.2.2 64-bit on Windows 7 64-bit. I installed pgAdmin separately, version 1.14.1. The database appears to be working fine. pgAgent was automatically installed and the service starts without error. I see the pgAgent in the Catalog list, and Jobs shows in the postgres database. I'm logged in as user postgres in the postgres database. I created the following table: CREATE TABLE datetest ( datestamp timestamp without time zone ) and populated a single row: insert into datetest values(sysdate); I then created a simple test job, of type SQL, with the following definition: update datetest set datestamp = sysdate I scheduled this to run every 5 minutes. When I started this email, the job just wouldn't run when I said Run Now, and no errors showed up in the event log. I finally remembered from somewhere that Host agent has to be empty; sure enough, I removed "localhost" from there and then my job ran :(. So, now the only remaining issue is that in PgAdmin, the Statistics tab shows all runs with a status of "Failed". But the datetest table is getting updated properly. I see no events in the Application event log. Finally, I ran pgAgent from the command line inDEBUG mode: DEBUG: Checking for jobs to run DEBUG: Creating job thread for job 2 DEBUG: Creating DB connection: user=postgres port=5432 hostaddr=127.0.0.1 password=xxxx dbname=postgres DEBUG: Allocating new connection to database postgres DEBUG: Starting job: 2 DEBUG: Sleeping... DEBUG: Creating DB connection: user=postgres port=5432 hostaddr=127.0.0.1 password=xxxx dbname=postgres dbname=postgres DEBUG: Allocating new connection to database postgres DEBUG: Executing SQL step 3 (part of job 2) DEBUG: Returning connection to database postgres DEBUG: Returning connection to database postgres DEBUG: Completed job: 2 DEBUG: Destroying job thread for job 2 DEBUG: Checking for jobs to run DEBUG: Sleeping... The run also shows a status of "Failed". Any idea why? Thanks. -- Guy Rouillier
On Wed, Feb 29, 2012 at 7:39 AM, Guy Rouillier <guyr@burntmail.com> wrote: > I started writing this after struggling and Googling for hours and not > getting anywhere. I'm running EnterpriseDB 9.1.2.2 64-bit on Windows 7 > 64-bit. I installed pgAdmin separately, version 1.14.1. The database > appears to be working fine. pgAgent was automatically installed and the > service starts without error. I see the pgAgent in the Catalog list, and > Jobs shows in the postgres database. > > I'm logged in as user postgres in the postgres database. I created the > following table: > > CREATE TABLE datetest > ( > datestamp timestamp without time zone > ) > > and populated a single row: > > insert into datetest values(sysdate); > > I then created a simple test job, of type SQL, with the following > definition: > > update datetest set datestamp = sysdate > > I scheduled this to run every 5 minutes. When I started this email, the job > just wouldn't run when I said Run Now, and no errors showed up in the event > log. I finally remembered from somewhere that Host agent has to be empty; > sure enough, I removed "localhost" from there and then my job ran :(. > > So, now the only remaining issue is that in PgAdmin, the Statistics tab > shows all runs with a status of "Failed". But the datetest table is getting > updated properly. I see no events in the Application event log. Finally, I > ran pgAgent from the command line in DEBUG mode: > > DEBUG: Checking for jobs to run > DEBUG: Creating job thread for job 2 > DEBUG: Creating DB connection: user=postgres port=5432 hostaddr=127.0.0.1 > password=xxxx dbname=postgres > DEBUG: Allocating new connection to database postgres > DEBUG: Starting job: 2 > DEBUG: Sleeping... > DEBUG: Creating DB connection: user=postgres port=5432 hostaddr=127.0.0.1 > password=xxxx dbname=postgres dbname=postgres > DEBUG: Allocating new connection to database postgres > DEBUG: Executing SQL step 3 (part of job 2) > DEBUG: Returning connection to database postgres > DEBUG: Returning connection to database postgres > DEBUG: Completed job: 2 > DEBUG: Destroying job thread for job 2 > DEBUG: Checking for jobs to run > DEBUG: Sleeping... > > The run also shows a status of "Failed". Any idea why? Thanks. The step or the job shows as failed? What's the output from the step? -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 2/29/2012 4:13 AM, Dave Page wrote: > On Wed, Feb 29, 2012 at 7:39 AM, Guy Rouillier<guyr@burntmail.com> wrote: >> So, now the only remaining issue is that in PgAdmin, the Statistics tab >> shows all runs with a status of "Failed". But the datetest table is getting >> updated properly. I see no events in the Application event log. Finally, I >> ran pgAgent from the command line in DEBUG mode: >> >> DEBUG: Checking for jobs to run >> DEBUG: Creating job thread for job 2 >> DEBUG: Creating DB connection: user=postgres port=5432 hostaddr=1270.0.1 >> password=xxxx dbname=postgres >> DEBUG: Allocating new connection to database postgres >> DEBUG: Starting job: 2 >> DEBUG: Sleeping... >> DEBUG: Creating DB connection: user=postgres port=5432 hostaddr=1270.0.1 >> password=xxxx dbname=postgres dbname=postgres >> DEBUG: Allocating new connection to database postgres >> DEBUG: Executing SQL step 3 (part of job 2) >> DEBUG: Returning connection to database postgres >> DEBUG: Returning connection to database postgres >> DEBUG: Completed job: 2 >> DEBUG: Destroying job thread for job 2 >> DEBUG: Checking for jobs to run >> DEBUG: Sleeping... >> >> The run also shows a status of "Failed". Any idea why? Thanks. > > The step or the job shows as failed? What's the output from the step? Both the step and the job are shown as failed. Thanks. -- Guy Rouillier
On Thu, Mar 1, 2012 at 4:22 AM, Guy Rouillier <guyr@burntmail.com> wrote: > On 2/29/2012 4:13 AM, Dave Page wrote: >> >> On Wed, Feb 29, 2012 at 7:39 AM, Guy Rouillier<guyr@burntmail.com> wrote: >>> >>> So, now the only remaining issue is that in PgAdmin, the Statistics tab >>> >>> shows all runs with a status of "Failed". But the datetest table is >>> getting >>> updated properly. I see no events in the Application event log. >>> Finally, I >>> ran pgAgent from the command line in DEBUG mode: >>> >>> DEBUG: Checking for jobs to run >>> DEBUG: Creating job thread for job 2 >>> DEBUG: Creating DB connection: user=postgres port=5432 hostaddr=1270.0.1 >>> >>> password=xxxx dbname=postgres >>> DEBUG: Allocating new connection to database postgres >>> DEBUG: Starting job: 2 >>> DEBUG: Sleeping... >>> DEBUG: Creating DB connection: user=postgres port=5432 hostaddr=1270.0.1 >>> >>> password=xxxx dbname=postgres dbname=postgres >>> DEBUG: Allocating new connection to database postgres >>> DEBUG: Executing SQL step 3 (part of job 2) >>> DEBUG: Returning connection to database postgres >>> DEBUG: Returning connection to database postgres >>> DEBUG: Completed job: 2 >>> DEBUG: Destroying job thread for job 2 >>> DEBUG: Checking for jobs to run >>> DEBUG: Sleeping... >>> >>> The run also shows a status of "Failed". Any idea why? Thanks. >> >> >> The step or the job shows as failed? What's the output from the step? > > > Both the step and the job are shown as failed. Thanks. And the step output? -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 3/1/2012 4:20 AM, Dave Page wrote: > On Thu, Mar 1, 2012 at 4:22 AM, Guy Rouillier<guyr@burntmail.com> wrote: >> On 2/29/2012 4:13 AM, Dave Page wrote: >>> >>> On Wed, Feb 29, 2012 at 7:39 AM, Guy Rouillier<guyr@burntmail.com> wrote: >>>> >>>> So, now the only remaining issue is that in PgAdmin, the Statistics tab >>>> >>>> shows all runs with a status of "Failed". But the datetest table is >>>> getting >>>> updated properly. I see no events in the Application event log. >>>> Finally, I >>>> ran pgAgent from the command line in DEBUG mode: >>>> >>>> DEBUG: Checking for jobs to run >>>> DEBUG: Creating job thread for job 2 >>>> DEBUG: Creating DB connection: user=postgres port=5432 hostaddr=1270.0.1 >>>> >>>> password=xxxx dbname=postgres >>>> DEBUG: Allocating new connection to database postgres >>>> DEBUG: Starting job: 2 >>>> DEBUG: Sleeping... >>>> DEBUG: Creating DB connection: user=postgres port=5432 hostaddr=1270.0.1 >>>> >>>> password=xxxx dbname=postgres dbname=postgres >>>> DEBUG: Allocating new connection to database postgres >>>> DEBUG: Executing SQL step 3 (part of job 2) >>>> DEBUG: Returning connection to database postgres >>>> DEBUG: Returning connection to database postgres >>>> DEBUG: Completed job: 2 >>>> DEBUG: Destroying job thread for job 2 >>>> DEBUG: Checking for jobs to run >>>> DEBUG: Sleeping... >>>> >>>> The run also shows a status of "Failed". Any idea why? Thanks. >>> >>> >>> The step or the job shows as failed? What's the output from the step? >> >> >> Both the step and the job are shown as failed. Thanks. > > And the step output? I think you are referring to the "Output" column in the Statistics tab. That column is completely blank for all tests. Thanks. -- Guy Rouillier
On Fri, Mar 2, 2012 at 4:02 AM, Guy Rouillier <guyr@burntmail.com> wrote: > On 3/1/2012 4:20 AM, Dave Page wrote: >> >> On Thu, Mar 1, 2012 at 4:22 AM, Guy Rouillier<guyr@burntmail.com> wrote: >>> >>> On 2/29/2012 4:13 AM, Dave Page wrote: >>>> >>>> >>>> On Wed, Feb 29, 2012 at 7:39 AM, Guy Rouillier<guyr@burntmail.com> >>>> wrote: >>>>> >>>>> >>>>> So, now the only remaining issue is that in PgAdmin, the Statistics tab >>>>> >>>>> shows all runs with a status of "Failed". But the datetest table is >>>>> getting >>>>> updated properly. I see no events in the Application event log. >>>>> Finally, I >>>>> ran pgAgent from the command line in DEBUG mode: >>>>> >>>>> DEBUG: Checking for jobs to run >>>>> DEBUG: Creating job thread for job 2 >>>>> DEBUG: Creating DB connection: user=postgres port=5432 >>>>> hostaddr=1270.0.1 >>>>> >>>>> password=xxxx dbname=postgres >>>>> DEBUG: Allocating new connection to database postgres >>>>> DEBUG: Starting job: 2 >>>>> DEBUG: Sleeping... >>>>> DEBUG: Creating DB connection: user=postgres port=5432 >>>>> hostaddr=1270.0.1 >>>>> >>>>> password=xxxx dbname=postgres dbname=postgres >>>>> DEBUG: Allocating new connection to database postgres >>>>> DEBUG: Executing SQL step 3 (part of job 2) >>>>> DEBUG: Returning connection to database postgres >>>>> DEBUG: Returning connection to database postgres >>>>> DEBUG: Completed job: 2 >>>>> DEBUG: Destroying job thread for job 2 >>>>> DEBUG: Checking for jobs to run >>>>> DEBUG: Sleeping... >>>>> >>>>> The run also shows a status of "Failed". Any idea why? Thanks. >>>> >>>> >>>> >>>> The step or the job shows as failed? What's the output from the step? >>> >>> >>> >>> Both the step and the job are shown as failed. Thanks. >> >> >> And the step output? > > > I think you are referring to the "Output" column in the Statistics tab. > That column is completely blank for all tests. Can you confirm from the server log (on the server on which the step runs), that the step does actually run or not? Assuming it does, try running pgAgent with the log level increased to WARNING to see if anything shows up there. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 3/5/2012 7:26 AM, Dave Page wrote: >>>>> The step or the job shows as failed? What's the output from the step? >>>> >>>> >>>> >>>> Both the step and the job are shown as failed. Thanks. >>> >>> >>> And the step output? >> >> >> I think you are referring to the "Output" column in the Statistics tab. >> That column is completely blank for all tests. > > Can you confirm from the server log (on the server on which the step > runs), that the step does actually run or not? > > Assuming it does, try running pgAgent with the log level increased to > WARNING to see if anything shows up there. Ok, I finally figured this out. The step does run; using the sample job I provided, the table is updated with the current date. In my original post, I provided the output of a DEBUG run; that should have had at least as much detail as WARNING, yes? At any rate, the problem was (drum roll...) I neglected to put a commit after my update statement in the job step SQL. After doing so, the job and step are both successful. So now the only thing odd is that the update actually *was* committed, even the job indicated it had failed. I say this because the updated value persisted between reboots. Thanks for persisting, Dave. -- Guy Rouillier