Solved

after reinstalling postgresql and brand new install of pgAgent, i can not run my jobs, what 's the error?

Posted on 2008-10-21
6
775 Views
Last Modified: 2011-10-19
i need to workd with jobs in postgresql,
i've installed pgAgent, script, service....create user postgres to logon....start it ....i have followed tutorials.

i can see the pgAgent Catalog in my postgres database with 8 tables,
as a test i've created a function in a database ( not postgres)
i then created a job for the Database i created the function in, this job contains only one step, the function call.

question :
if my database (not postgres) contains also the Catalog pgAgent, why after creating my job, infos are in the postgres pgAgent Catalog
and not in my database(notpostgres) pgAgent Catalog? even when creating my job i tell that the function is for my database(not postgres).

is it normal?

do i have to create all functions in my postgres database and create  jobs for my postgres database?
hlep


when

0
Comment
Question by:toshi_
  • 4
  • 2
6 Comments
 
LVL 10

Expert Comment

by:ivanovn
ID: 22770252
pgagent will use catalog specified by the service installation. So if you created a pgagent catalog in different database (lets call it "notpostgres") then when installing the pgagent service you should have something like this (for windows):

"C:\Program Files\pgAdmin III\pgAgent" INSTALL pgAgent -u postgres -p secret hostaddr=127.0.0.1 dbname=notpostgres user=postgres

or this (for *nix):

/path/to/pgagent hostaddr=127.0.0.1 dbname=notpostgres user=postgres

Read through the documentation if you need more info on how to install the service: http://www.pgadmin.org/docs/1.8/pgagent-install.html
0
 

Author Comment

by:toshi_
ID: 22771855
the thing is that i had install the catalogue in a database that i backed up and restored after my  new installation.
for the new installation i pass the command as follow, so i have two databases with the catalog.

"C:\Program Files\pgAdmin III\pgAgent" INSTALL pgAgent -u postgres -p myPass hostaddr=127.0.0.1 dbname=postgres user=postgres

i have my service running on windows, log on with the same user : postgres,
in total i have two services: one for the database and one for pgAgent.

i  created a job, the postgres catalog receive the infos in the tables as you explained to check in another post.

now, why is my job not running the only step i wrote , the call of a function : level_of_day(); ?
i know my function does the jos, as i can pass "select level_of_day();" in my sql interface (pgAdmin)
and verify in my tables.

thank you to pay attention to my question ivanovn.
whatever is necessary in order to find out why the thing is not working for me i can provide it...i think...
as i set my log to the maximum info.

thank you in advance.
0
 

Author Comment

by:toshi_
ID: 22782380
this is what i have in logs,
after ask jobs  to "run now", forced in pgAdmin by a command in the contextual menu.
what is wrong with my jobs?

note: my job is create with one step, the step call a function that is declared in a database "not postgres", the job has to write in a table in the host database, the function name is write_email.

thank you for all info concerning this ...jobs


pgadmin.log
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:toshi_
ID: 22784178
help??????????????????????????????
0
 
LVL 10

Accepted Solution

by:
ivanovn earned 500 total points
ID: 22786020
I am not sure I 100% follow what you are trying to do. Are you having a job in database A that is supposed to call a function defined in database B?

I am guessing now you have your job created correctly and it's in the database A catalog? When you run it though you are not getting your database B tables populated?

If that's the case, then you just have to make sure that when you create your step, you select which database step applies to. It should be in drop down menu of databases. Also you may want to add some print outs (using RAISE NOTICE) in your function to see if it's getting executed at all or if it's failing somewhere within the function. I'm not seeing anything in your log in regards with your function running.
0
 

Author Comment

by:toshi_
ID: 22790633
hello ivanovn, thanks for reply

i have installed pgAgent as explained in pgAgent tutorial,
created the user,
run the script for having the catalog created in the principal database "postgres",
and all the necessary to have the service started on my windows machine.

now about the job, i have created a function that i know is working as i when i fire it from
my sql pgAdmin interface i see the work's done,

i created a job for the database that contain the function,
the job contains 1 step, the step is a call to the function.

my problem:
i have followed the procedure to be able to create jobs, but i can not make them work.

my job's name is write_email, if you parse the pgadmin.log with this string you'll come across
something,

question:
how to find out what's the problem?

thank you for reply,

0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now