Re: Need some help setting up pgAgent

Okay my required n00b question of the week, hopefully this'll be an easy one..

I decided to give pgAgent a shot, because there's this stored sproc
(sorry, function) I need to run nightly and I think spending hours
figuring out pgAgent would somehow be better than the 3 minutes it
would take to add a cron job that calls psql..

I'm following the directions at:
http://www.pgadmin.org/docs/1.6/pgagent-install.html

However, the heading "Daemon installation on *nix" needs me to believe
that running pgagent from the command line should install the daemon,
and then things should be happily running in the background. However,
when I run the command line as instructed, it just sits there and does
nothing. I never get returned to the bash prompt, it's as if the
daemon is just running in interactive mode until I stop the process..
Maybe I'm missing something, or I have to add that command line to
some config file. I'm not a unix guru but from my past experiences, I
can usually run daemons with "/etc/init.d/blah start" - pgAgent
doesn't seem to be installed in that manner.

Second, assuming the daemon is running in interactive mode, like..
now what? The docs have no next steps. How do I create a new job to
run my SQL function every night? From some documentation from
Postgres Plus, I was led to believe that there should be a "Jobs" tree
in pgAdmin created.. But I see nothing. Thanks!

Mike

On Sun, Oct 17, 2010 at 2:32 PM, Mike Christensen <mike(at)kitchenpc(dot)com> wrote:
> Okay my required n00b question of the week, hopefully this'll be an easy one..
>
> I decided to give pgAgent a shot, because there's this stored sproc
> (sorry, function) I need to run nightly and I think spending hours
> figuring out pgAgent would somehow be better than the 3 minutes it
> would take to add a cron job that calls psql..
>
> I'm following the directions at:
> http://www.pgadmin.org/docs/1.6/pgagent-install.html

Why such an old version? Try
http://www.pgadmin.org/docs/1.12/pgagent-install.html

> However, the heading "Daemon installation on *nix" needs me to believe
> that running pgagent from the command line should install the daemon,
> and then things should be happily running in the background.  However,
> when I run the command line as instructed, it just sits there and does
> nothing.  I never get returned to the bash prompt, it's as if the
> daemon is just running in interactive mode until I stop the process..

Did you use the -f option?

> Maybe I'm missing something, or I have to add that command line to
> some config file.  I'm not a unix guru but from my past experiences, I
> can usually run daemons with "/etc/init.d/blah start" - pgAgent
> doesn't seem to be installed in that manner.

If you built from source, it's up to you to create the startup script.
The pgAgent source tree doesn't contain OS-specific startup scripts.

> Second, assuming the daemon is running in interactive mode, like..
> now what?  The docs have no next steps.  How do I create a new job to
> run my SQL function every night?  From some documentation from
> Postgres Plus, I was led to believe that there should be a "Jobs" tree
> in pgAdmin created..  But I see nothing.  Thanks!

Did you setup the database per the part of the docs prior to that section?

The "next steps" are most certainly there - see the index page at
http://www.pgadmin.org/docs/1.12/pgagent.html

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, Oct 18, 2010 at 2:07 AM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
> On Sun, Oct 17, 2010 at 2:32 PM, Mike Christensen <mike(at)kitchenpc(dot)com> wrote:
>> Okay my required n00b question of the week, hopefully this'll be an easy one..
>>
>> I decided to give pgAgent a shot, because there's this stored sproc
>> (sorry, function) I need to run nightly and I think spending hours
>> figuring out pgAgent would somehow be better than the 3 minutes it
>> would take to add a cron job that calls psql..
>>
>> I'm following the directions at:
>> http://www.pgadmin.org/docs/1.6/pgagent-install.html
>
> Why such an old version? Try
> http://www.pgadmin.org/docs/1.12/pgagent-install.html
>
>> However, the heading "Daemon installation on *nix" needs me to believe
>> that running pgagent from the command line should install the daemon,
>> and then things should be happily running in the background.  However,
>> when I run the command line as instructed, it just sits there and does
>> nothing.  I never get returned to the bash prompt, it's as if the
>> daemon is just running in interactive mode until I stop the process..
>
> Did you use the -f option?
>
>> Maybe I'm missing something, or I have to add that command line to
>> some config file.  I'm not a unix guru but from my past experiences, I
>> can usually run daemons with "/etc/init.d/blah start" - pgAgent
>> doesn't seem to be installed in that manner.
>
> If you built from source, it's up to you to create the startup script.
> The pgAgent source tree doesn't contain OS-specific startup scripts.
>
>> Second, assuming the daemon is running in interactive mode, like..
>> now what?  The docs have no next steps.  How do I create a new job to
>> run my SQL function every night?  From some documentation from
>> Postgres Plus, I was led to believe that there should be a "Jobs" tree
>> in pgAdmin created..  But I see nothing.  Thanks!
>
> Did you setup the database per the part of the docs prior to that section?
>
> The "next steps" are most certainly there - see the index page at
> http://www.pgadmin.org/docs/1.12/pgagent.html
>
> --
> Dave Page
> Blog: http://pgsnake.blogspot.com
> Twitter: @pgsnake
>
> EnterpriseDB UK: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

Thanks for the info! First, I have no idea what version of pgAgent it
is. Where does it say? I installed it using apt-get..

apt-cache search pgagent
pgagent - job scheduler for PostgreSQL

It doesn't say the version, but that's the only match.. Maybe there's
a bin file or something better?

I ran it using:

/usr/bin/pgagent hostaddr=127.0.0.1 dbname=KitchenPC user=root

and it immediately returned to the bash prompt, and it appears to be running:

ps | grep pgagent
4134 pts/0 00:00:00 pgagent

I ran the .sql file that it came with, and it created a new catalog
called pgAgent.

However, I'm still not sure what to do next. According to the
instructions, pgAdmin should show a node called "Jobs". I don't see
that. I'm using pgAdmin for OS/X and connecting to the server from
remote over SSL. Thanks!

Mike

On Mon, Oct 18, 2010 at 2:21 AM, Mike Christensen <mike(at)kitchenpc(dot)com> wrote:
> On Mon, Oct 18, 2010 at 2:07 AM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>> On Sun, Oct 17, 2010 at 2:32 PM, Mike Christensen <mike(at)kitchenpc(dot)com> wrote:
>>> Okay my required n00b question of the week, hopefully this'll be an easy one..
>>>
>>> I decided to give pgAgent a shot, because there's this stored sproc
>>> (sorry, function) I need to run nightly and I think spending hours
>>> figuring out pgAgent would somehow be better than the 3 minutes it
>>> would take to add a cron job that calls psql..
>>>
>>> I'm following the directions at:
>>> http://www.pgadmin.org/docs/1.6/pgagent-install.html
>>
>> Why such an old version? Try
>> http://www.pgadmin.org/docs/1.12/pgagent-install.html
>>
>>> However, the heading "Daemon installation on *nix" needs me to believe
>>> that running pgagent from the command line should install the daemon,
>>> and then things should be happily running in the background.  However,
>>> when I run the command line as instructed, it just sits there and does
>>> nothing.  I never get returned to the bash prompt, it's as if the
>>> daemon is just running in interactive mode until I stop the process..
>>
>> Did you use the -f option?
>>
>>> Maybe I'm missing something, or I have to add that command line to
>>> some config file.  I'm not a unix guru but from my past experiences, I
>>> can usually run daemons with "/etc/init.d/blah start" - pgAgent
>>> doesn't seem to be installed in that manner.
>>
>> If you built from source, it's up to you to create the startup script.
>> The pgAgent source tree doesn't contain OS-specific startup scripts.
>>
>>> Second, assuming the daemon is running in interactive mode, like..
>>> now what?  The docs have no next steps.  How do I create a new job to
>>> run my SQL function every night?  From some documentation from
>>> Postgres Plus, I was led to believe that there should be a "Jobs" tree
>>> in pgAdmin created..  But I see nothing.  Thanks!
>>
>> Did you setup the database per the part of the docs prior to that section?
>>
>> The "next steps" are most certainly there - see the index page at
>> http://www.pgadmin.org/docs/1.12/pgagent.html
>>
>> --
>> Dave Page
>> Blog: http://pgsnake.blogspot.com
>> Twitter: @pgsnake
>>
>> EnterpriseDB UK: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>
> Thanks for the info!  First, I have no idea what version of pgAgent it
> is.  Where does it say?  I installed it using apt-get..
>
> apt-cache search pgagent
> pgagent - job scheduler for PostgreSQL
>
> It doesn't say the version, but that's the only match..  Maybe there's
> a bin file or something better?
>
> I ran it using:
>
> /usr/bin/pgagent hostaddr=127.0.0.1 dbname=KitchenPC user=root
>
> and it immediately returned to the bash prompt, and it appears to be running:
>
> ps | grep pgagent
>  4134 pts/0    00:00:00 pgagent
>
> I ran the .sql file that it came with, and it created a new catalog
> called pgAgent.
>
> However, I'm still not sure what to do next.  According to the
> instructions, pgAdmin should show a node called "Jobs".  I don't see
> that.  I'm using pgAdmin for OS/X and connecting to the server from
> remote over SSL.  Thanks!
>
> Mike
>

Sorry, it looks like it defaulted to the wrong DB. I created the
schema in the "postgres" database and now I see a Jobs node..

One last question - How do I configure pgAgent to start automatically?

Mike

On Mon, Oct 18, 2010 at 10:31 AM, Mike Christensen <mike(at)kitchenpc(dot)com> wrote:
> Sorry, it looks like it defaulted to the wrong DB.  I created the
> schema in the "postgres" database and now I see a Jobs node..

:-)

> One last question - How do I configure pgAgent to start automatically?

You'll need to put together a startup script for your OS, if the
debian/ubuntu packager hasn't done so already. I'd suggest copying one
from /etc/init.d and tweaking it as required.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, Oct 18, 2010 at 2:34 AM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
> On Mon, Oct 18, 2010 at 10:31 AM, Mike Christensen <mike(at)kitchenpc(dot)com> wrote:
>> Sorry, it looks like it defaulted to the wrong DB.  I created the
>> schema in the "postgres" database and now I see a Jobs node..
>
> :-)
>
>> One last question - How do I configure pgAgent to start automatically?
>
> You'll need to put together a startup script for your OS, if the
> debian/ubuntu packager hasn't done so already. I'd suggest copying one
> from /etc/init.d and tweaking it as required.

This is a bit outside my comfort zone, do you have a startup script
you can share with me? I can copy it into /etc/init.d - Thanks!

Mike

On Mon, Oct 18, 2010 at 10:41 AM, Mike Christensen <mike(at)kitchenpc(dot)com> wrote:
> On Mon, Oct 18, 2010 at 2:34 AM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>> On Mon, Oct 18, 2010 at 10:31 AM, Mike Christensen <mike(at)kitchenpc(dot)com> wrote:
>>> Sorry, it looks like it defaulted to the wrong DB.  I created the
>>> schema in the "postgres" database and now I see a Jobs node..
>>
>> :-)
>>
>>> One last question - How do I configure pgAgent to start automatically?
>>
>> You'll need to put together a startup script for your OS, if the
>> debian/ubuntu packager hasn't done so already. I'd suggest copying one
>> from /etc/init.d and tweaking it as required.
>
> This is a bit outside my comfort zone, do you have a startup script
> you can share with me?  I can copy it into /etc/init.d - Thanks!

No I don't (I use a Mac - and never start pgagent automatically
anyway). Can you copy the PostgreSQL script and make appropriate
changes?

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, Oct 18, 2010 at 2:43 AM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
> On Mon, Oct 18, 2010 at 10:41 AM, Mike Christensen <mike(at)kitchenpc(dot)com> wrote:
>> On Mon, Oct 18, 2010 at 2:34 AM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>>> On Mon, Oct 18, 2010 at 10:31 AM, Mike Christensen <mike(at)kitchenpc(dot)com> wrote:
>>>> Sorry, it looks like it defaulted to the wrong DB.  I created the
>>>> schema in the "postgres" database and now I see a Jobs node..
>>>
>>> :-)
>>>
>>>> One last question - How do I configure pgAgent to start automatically?
>>>
>>> You'll need to put together a startup script for your OS, if the
>>> debian/ubuntu packager hasn't done so already. I'd suggest copying one
>>> from /etc/init.d and tweaking it as required.
>>
>> This is a bit outside my comfort zone, do you have a startup script
>> you can share with me?  I can copy it into /etc/init.d - Thanks!
>
> No I don't (I use a Mac - and never start pgagent automatically
> anyway). Can you copy the PostgreSQL script and make appropriate
> changes?

Okay I found one that I can use..

One question.. Should the connection string in the script have the
password for "root" hard coded in it? Or will it use a password from
~/.pgpass automatically? If so, what user account will it find the
.pgpass file under? Thanks!

Mike

On Mon, Oct 18, 2010 at 10:46 AM, Mike Christensen <mike(at)kitchenpc(dot)com> wrote:
> Okay I found one that I can use..
>
> One question..  Should the connection string in the script have the
> password for "root" hard coded in it?  Or will it use a password from
> ~/.pgpass automatically?  If so, what user account will it find the
> .pgpass file under?  Thanks!

Have the script start pgagent under the postgres account eg;

su - postgres -c 'p/path/to/pgadmin....'

Then it should be able to use postgres' pgpass file. Don't put the
password in the connection string!

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, Oct 18, 2010 at 3:07 AM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
> On Mon, Oct 18, 2010 at 10:46 AM, Mike Christensen <mike(at)kitchenpc(dot)com> wrote:
>> Okay I found one that I can use..
>>
>> One question..  Should the connection string in the script have the
>> password for "root" hard coded in it?  Or will it use a password from
>> ~/.pgpass automatically?  If so, what user account will it find the
>> .pgpass file under?  Thanks!
>
> Have the script start pgagent under the postgres account eg;
>
> su - postgres -c 'p/path/to/pgadmin....'
>
> Then it should be able to use postgres' pgpass file. Don't put the
> password in the connection string!

Ok, that worked.. I can at least start and stop it now, and it
remains running when I'm logged off..

So does anything in /etc/init.d get automatically run when the server boots?

Mike

On Mon, Oct 18, 2010 at 11:34 AM, Mike Christensen <mike(at)kitchenpc(dot)com> wrote:
> On Mon, Oct 18, 2010 at 3:07 AM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>> On Mon, Oct 18, 2010 at 10:46 AM, Mike Christensen <mike(at)kitchenpc(dot)com> wrote:
>>> Okay I found one that I can use..
>>>
>>> One question..  Should the connection string in the script have the
>>> password for "root" hard coded in it?  Or will it use a password from
>>> ~/.pgpass automatically?  If so, what user account will it find the
>>> .pgpass file under?  Thanks!
>>
>> Have the script start pgagent under the postgres account eg;
>>
>> su - postgres -c 'p/path/to/pgadmin....'
>>
>> Then it should be able to use postgres' pgpass file. Don't put the
>> password in the connection string!
>
> Ok, that worked..  I can at least start and stop it now, and it
> remains running when I'm logged off..
>
> So does anything in /etc/init.d get automatically run when the server boots?

No, you have to enable it. On redhat based distros, you'd do something
like "chkconfig <servicename> on". On Debian based distros, I believe
you use the update-rc.d command.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, Oct 18, 2010 at 3:37 AM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
> On Mon, Oct 18, 2010 at 11:34 AM, Mike Christensen <mike(at)kitchenpc(dot)com> wrote:
>> On Mon, Oct 18, 2010 at 3:07 AM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>>> On Mon, Oct 18, 2010 at 10:46 AM, Mike Christensen <mike(at)kitchenpc(dot)com> wrote:
>>>> Okay I found one that I can use..
>>>>
>>>> One question..  Should the connection string in the script have the
>>>> password for "root" hard coded in it?  Or will it use a password from
>>>> ~/.pgpass automatically?  If so, what user account will it find the
>>>> .pgpass file under?  Thanks!
>>>
>>> Have the script start pgagent under the postgres account eg;
>>>
>>> su - postgres -c 'p/path/to/pgadmin....'
>>>
>>> Then it should be able to use postgres' pgpass file. Don't put the
>>> password in the connection string!
>>
>> Ok, that worked..  I can at least start and stop it now, and it
>> remains running when I'm logged off..
>>
>> So does anything in /etc/init.d get automatically run when the server boots?
>
> No, you have to enable it. On redhat based distros, you'd do something
> like "chkconfig <servicename> on". On Debian based distros, I believe
> you use the update-rc.d command.

Well, I guess that worked:

etc/init.d# update-rc.d pgagent defaults
Adding system startup for /etc/init.d/pgagent ...
/etc/rc0.d/K20pgagent -> ../init.d/pgagent
/etc/rc1.d/K20pgagent -> ../init.d/pgagent
/etc/rc6.d/K20pgagent -> ../init.d/pgagent
/etc/rc2.d/S20pgagent -> ../init.d/pgagent
/etc/rc3.d/S20pgagent -> ../init.d/pgagent
/etc/rc4.d/S20pgagent -> ../init.d/pgagent
/etc/rc5.d/S20pgagent -> ../init.d/pgagent

Thanks!