Scheduled Jobs in Postgres.

We are migrating a web application from oracle 10g to postgres.
The problem we are facing is how to create scheduled jobs in postgres as there are jobs in oracle.
Similar jobs is to be created in postgres.Is there any method of doing so?Please suggest or provide an alternate solution.

It is really urgent so do help.

Regards...

Amrita_M wrote:We are migrating a web application from oracle 10g to postgres.
The problem we are facing is how to create scheduled jobs in postgres as there are jobs in oracle.
Similar jobs is to be created in postgres.Is there any method of doing so?Please suggest or provide an alternate solution.

It is really urgent so do help.

Regards...

Take a look at pgAgent:

http://www.pgadmin.org/download/pgagent.php
http://www.pgadmin.org/docs/1.8/pgagent.html

It means that jobs can be created in Postgres also but my pgAdmin console is not showing the jobs under Tablespaces.How to incorporate pgAgent in the pgAdmin?
Can these jobs be properly scheduled and a function or porcedure can be called from these jobs?

Amrita_M wrote:It means that jobs can be created in Postgres also but my pgAdmin console is not showing the jobs under Tablespaces.How to incorporate pgAgent in the pgAdmin?

Did you follow the installation instructions to run the pgagent.sql file? Note that pgAdmin will only add the node once it's treeview is refreshed - right-click the appropriate database node to do that.

Amrita_M wrote:
Can these jobs be properly scheduled and a function or porcedure can be called from these jobs?

Depends what you mean by 'properly scheduled'. The point of the scheduler is to run the jobs at the required time - and yes, you can call functions from job steps.

Thanks for the info.
Now I have created a job after installing pgAgent.But now the problem is that the job is not getting executed at the scheduled time.Actually I have added the details in properties but the Statistics is not showing anything.

Can you tell me how to create the job.I want the job to execute a function in every 2-3 minutes.

Regards,
Amrita

Amrita_M wrote:Thanks for the info.
Now I have created a job after installing pgAgent.But now the problem is that the job is not getting executed at the scheduled time.Actually I have added the details in properties but the Statistics is not showing anything.

Can you tell me how to create the job.I want the job to execute a function in every 2-3 minutes.

Regards,
Amrita

Did you put anything in the Host Agent field on the Job dialogue? If so, try removing it.

No i have not filled that field.

Amrita_M wrote:No i have not filled that field.

OK - thats the most common problem

What platform are you running on? If you restart pgAgent with '-l 2' (without the quotes) on the command line, that should give you lots of debugging info. On Windows, it'll go to the event log, or on *nix, it'll go to stdout. You can redirect to a logfile in that case with '-s log.txt'

I am working with J2EE as front end and windows.I am making the job directly in the pgAdmin III console,where to check the logs then.When i am clicking on statistics in jobs or schedules or steps then it is giving "no statistics are available for the current selection.
That means may be the jobs are created but it is not running......
How to check it??
Plz do tell...

Amrita_M wrote:I am working with J2EE as front end and windows.I am making the job directly in the pgAdmin III console,where to check the logs then.When i am clicking on statistics in jobs or schedules or steps then it is giving "no statistics are available for the current selection.
That means may be the jobs are created but it is not running......
How to check it??
Plz do tell...

The messages will go to the windows event log as I mentioned in my previous message. If you need to, set the debug level to 2 by removing the service and reinstalling it with -l 2 on the command line.

The pgAgent service is now showing in the windows services but when i am trying to start the service it is giving error:1069-The service did not start due to logon failure.
Please suggest a possible solution for this.

Amrita_M wrote:The pgAgent service is now showing in the windows services but when i am trying to start the service it is giving error:1069-The service did not start due to logon failure.
Please suggest a possible solution for this.

Open the properties for the service, and enter the correct password for the username under which it will run.

The username is \postgres and password is also the same .
I have run this command
pgAgent INSTALL pgAgent -l 2 -u postgres -p XXXX hostaddr=localhost dbname=postgres user=postgres password=postgres

on the prompt and the service was created in the windows automatically,now when i am trying to start the service it is giving the error.

Amrita_M wrote:The username is \postgres and password is also the same .
I have run this command
pgAgent INSTALL pgAgent -l 2 -u postgres -p XXXX hostaddr=localhost dbname=postgres user=postgres password=postgres

on the prompt and the service was created in the windows automatically,now when i am trying to start the service it is giving the error.

Windows is complaining about the password that you've written XXXX for in the command above. You need to ensure you get that right (if there are any space in it, quote it).

Please can you tell the exact syntax for it.
We are using the following syntax:

pgAgent INSTALL pgAgent -l 2 -u postgres -p postgres hostaddr=localhost dbname=postgres user=postgres password=postgres

Please tell what does -u and -p signify?

Amrita_M wrote:Please can you tell the exact syntax for it.
We are using the following syntax:

pgAgent INSTALL pgAgent -l 2 -u postgres -p postgres hostaddr=localhost dbname=postgres user=postgres password=postgres

Please tell what does -u and -p signify?

The syntax looks correct. -u and -p are the options for the Windows username and password that the service will run as - this is documented at http://www.pgadmin.org/docs/1.8/pgagent.html.

The error you get when you try to start the service tells us that the value you pass in the -p option ('postgres') is not the correct password for the postgres windows user account (note that it is a separate password from the database one - the password= option - the two may be different).

If you need to change the windows password, you can do so by running the Computer Management tool found in the Administrative Tools folder, and modfying the user account there. If you do that, you will also need to update the password on the PostgreSQL service.

Hi
I am still facing problem with starting of the application.It is giving Error:1069.The service could not start due to logon failure.

Please advice.

If you need to change the windows password, you can do so by running the Computer Management tool found in the Administrative Tools folder, and modfying the user account there. If you do that, you will also need to update the password on the PostgreSQL service.

How to update the password on the postgresql?
Do i have to install it again?

Amrita_M wrote:If you need to change the windows password, you can do so by running the Computer Management tool found in the Administrative Tools folder, and modfying the user account there. If you do that, you will also need to update the password on the PostgreSQL service.

How to update the password on the postgresql?
Do i have to install it again?

To update the PostgreSQL service password, open the Services control panel applet, find the PostgreSQL server, right-click it and select properties. You can then change the password from there

I have tried chaning the password by going to service.msc and right clicking on PostgreSQL Database Server 8.2,selecting properties and in log on changing the password but now when I am starting the database service the error is coming here also.Now i am unable to connect to database and don't have the dump also..I am stuck...Please help

Amrita_M wrote:I have tried chaning the password by going to service.msc and right clicking on PostgreSQL Database Server 8.2,selecting properties and in log on changing the password but now when I am starting the database service the error is coming here also.Now i am unable to connect to database and don't have the dump also..I am stuck...Please help

You must be mistyping the password. Try resetting it in Computer Management again, and then re-enter it for the PostgreSQL and pgAgent services.

ok..let me try again...now i have chosen the option of local computer and it started but when i am trying to start the pg agent it is giving an error 1053:The service did not respond to the start or control request in a timely fashion

Amrita_M wrote:ok..let me try again...now i have chosen the option of local computer and it started but when i am trying to start the pg agent it is giving an error 1053:The service did not respond to the start or control request in a timely fashion

Have you looked in the event log? Are there any pgAgent events logged?

I could not find any log.I tried to do everything instructed but still its not working.It seems there is no way out for creating the scheduled jobs.

Amrita_M wrote:I could not find any log.I tried to do everything instructed but still its not working.It seems there is no way out for creating the scheduled jobs.

The must be at least a message saying the service failed to start - the Windows service control manager will log that for *any* service that fails to start properly. Where are you looking exactly?