Need some help setting up pgAgent

On Sun, Oct 17, 2010 at 2:32 PM, Mike Christensen <mike@kitchenpc.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@pgadmin.org> wrote:

On Sun, Oct 17, 2010 at 2:32 PM, Mike Christensen <mike@kitchenpc.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@kitchenpc.com> wrote:

On Mon, Oct 18, 2010 at 2:07 AM, Dave Page <dpage@pgadmin.org> wrote:

On Sun, Oct 17, 2010 at 2:32 PM, Mike Christensen <mike@kitchenpc.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@kitchenpc.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@pgadmin.org> wrote:

On Mon, Oct 18, 2010 at 10:31 AM, Mike Christensen <mike@kitchenpc.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@kitchenpc.com> wrote:

On Mon, Oct 18, 2010 at 2:34 AM, Dave Page <dpage@pgadmin.org> wrote:

On Mon, Oct 18, 2010 at 10:31 AM, Mike Christensen <mike@kitchenpc.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@pgadmin.org> wrote:

On Mon, Oct 18, 2010 at 10:41 AM, Mike Christensen <mike@kitchenpc.com> wrote:

On Mon, Oct 18, 2010 at 2:34 AM, Dave Page <dpage@pgadmin.org> wrote:

On Mon, Oct 18, 2010 at 10:31 AM, Mike Christensen <mike@kitchenpc.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@kitchenpc.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@pgadmin.org> wrote:

On Mon, Oct 18, 2010 at 10:46 AM, Mike Christensen <mike@kitchenpc.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@kitchenpc.com> wrote:

On Mon, Oct 18, 2010 at 3:07 AM, Dave Page <dpage@pgadmin.org> wrote:

On Mon, Oct 18, 2010 at 10:46 AM, Mike Christensen <mike@kitchenpc.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@pgadmin.org> wrote:

On Mon, Oct 18, 2010 at 11:34 AM, Mike Christensen <mike@kitchenpc.com> wrote:

On Mon, Oct 18, 2010 at 3:07 AM, Dave Page <dpage@pgadmin.org> wrote:

On Mon, Oct 18, 2010 at 10:46 AM, Mike Christensen <mike@kitchenpc.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!