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

Posted on 2008-10-21
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?


Question by:toshi_
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
LVL 10

Expert Comment

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= dbname=notpostgres user=postgres

or this (for *nix):

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

Read through the documentation if you need more info on how to install the service:

Author Comment

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= 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.

Author Comment

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

Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part


Author Comment

ID: 22784178
LVL 10

Accepted Solution

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.

Author Comment

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

how to find out what's the problem?

thank you for reply,


Featured Post

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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: ( 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.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

622 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