Re: [GENERAL] Need some help setting up pgAgent
On Mon, Oct 18, 2010 at 3:37 AM, Dave Page wrote: > On Mon, Oct 18, 2010 at 11:34 AM, Mike Christensen wrote: >> On Mon, Oct 18, 2010 at 3:07 AM, Dave Page wrote: >>> On Mon, Oct 18, 2010 at 10:46 AM, Mike Christensen >>> 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 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! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need some help setting up pgAgent
On Mon, Oct 18, 2010 at 3:07 AM, Dave Page wrote: > On Mon, Oct 18, 2010 at 10:46 AM, Mike Christensen 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need some help setting up pgAgent
On Mon, Oct 18, 2010 at 11:34 AM, Mike Christensen wrote: > On Mon, Oct 18, 2010 at 3:07 AM, Dave Page wrote: >> On Mon, Oct 18, 2010 at 10:46 AM, Mike Christensen >> 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 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need some help setting up pgAgent
On Mon, Oct 18, 2010 at 10:46 AM, Mike Christensen 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need some help setting up pgAgent
On Mon, Oct 18, 2010 at 2:43 AM, Dave Page wrote: > On Mon, Oct 18, 2010 at 10:41 AM, Mike Christensen wrote: >> On Mon, Oct 18, 2010 at 2:34 AM, Dave Page wrote: >>> On Mon, Oct 18, 2010 at 10:31 AM, Mike Christensen >>> 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need some help setting up pgAgent
On Mon, Oct 18, 2010 at 10:41 AM, Mike Christensen wrote: > On Mon, Oct 18, 2010 at 2:34 AM, Dave Page wrote: >> On Mon, Oct 18, 2010 at 10:31 AM, Mike Christensen >> 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need some help setting up pgAgent
On Mon, Oct 18, 2010 at 2:34 AM, Dave Page wrote: > On Mon, Oct 18, 2010 at 10:31 AM, Mike Christensen 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need some help setting up pgAgent
On Mon, Oct 18, 2010 at 10:31 AM, Mike Christensen 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need some help setting up pgAgent
On Mon, Oct 18, 2010 at 2:21 AM, Mike Christensen wrote: > On Mon, Oct 18, 2010 at 2:07 AM, Dave Page wrote: >> On Sun, Oct 17, 2010 at 2:32 PM, Mike Christensen 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need some help setting up pgAgent
On Mon, Oct 18, 2010 at 2:07 AM, Dave Page wrote: > On Sun, Oct 17, 2010 at 2:32 PM, Mike Christensen 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/000: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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need some help setting up pgAgent
On Sun, Oct 17, 2010 at 2:32 PM, Mike Christensen 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general