Go Premium for a chance to win a PS4. Enter to Win


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_
  • 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: http://www.pgadmin.org/docs/1.8/pgagent-install.html

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.


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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

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: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
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.
Screencast - Getting to Know the Pipeline
Suggested Courses

782 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