Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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

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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.


Author Comment

ID: 22784178
LVL 10

Accepted Solution

ivanovn earned 1500 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

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…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
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 will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : All lightning effects with instructions : http://www.mediaf…

721 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