discuss: Thread: pgagent howto

hello:
this is a suggestion of pgagent howto.

*first:  this is copied from the pgadmin III help*

pgAgent Installation
pgAgent runs as a daemon on *nix systems, and a service on Windows 
systems. In the majority of cases it will run on the database server 
itself - it is for this reason that pgAgent is not automatically setup 
when pgAdmin is installed. In some cases however, it may be preferable 
to run pgAgent on multiple systems, against the same database - 
individual jobs may be targetted at a particular host, or left for 
execution by any host. Locking prevents execution of the same instance 
of a job by multiple hosts.
Database setup
pgAgent stores it's configuration in the "postgres" database in your 
cluster. This database exists by default in PostgreSQL 8.1 versions, for 
earlier versions you need to create the database yourself. The database 
must have the pl/pgsql procedural language installed - PostgreSQL's 
'createlang' program can do this if required.
Once created, connect to the new database, and open the SQL tool. Select 
the File -> Open option from the menu and find the 'pgagent.sql' script 
installed with pgAdmin. The installation location for this file varies 
from operating system to operating system, however it will normally be 
found under 'C:\Program files\pgAdmin III' on Windows systems (or 
'C:\Program files\PostgreSQL\8.x\pgAdmin III' if installed with the 
PostgreSQL server installer), or '/usr/local/pgadmin3/share/pgadmin3' or 
'/usr/share/pgadmin3' on *nix systems. Once the file is loaded, click 
the 'Run' button to execute the script.
The script will create a number of tables and other objects in a schema 
called 'pgagent'.

Daemon installation on *nix
To install the pgAgent daemon on a *nix system, you will normally need 
to have root privileges to modify the system startup scripts - doing so 
is quite system specific so you should consult your system documentation 
for further information.
The program itself takes few command line options - most of which are 
only needed for debugging or specialised configurations:
Usage:
  /path/to/pgagent [options] <connect-string>
  
options:
  -f run in the foreground (do not detach from the terminal)
  -t <poll time interval in seconds (default 10)>
  -r <retry period after connection abort in seconds (>=10, default 30)>
  -l <logging verbosity (ERROR=0, WARNING=1, DEBUG=2, default 0)>

The connect string required is a standard PostgreSQL libpq connection 
string (see the PostgreSQL documentation for further details). For 
example, the following command lilne will run pgAgent against a server 
listening on the localhost, using a database called 'pgadmin', 
connecting as the user 'postgres':
/path/to/pgagent hostaddr=127.0.0.1 dbname=pgadmin user=postgres


Service installation on Windows
pgAgent is able to self-install itself as a service on Windows systems. 
The command line options available are similar to those on *nix systems, 
but include an additional parameter to tell the service what to do:
Usage:
  "C:\Program Files\pgAdmin III\pgAgent" REMOVE <serviceName>
  "C:\Program Files\pgAdmin III\pgAgent" INSTALL <serviceName> [options] 
<connect-string>
  
  options:
    -u <user or DOMAIN\user>
    -p <password>
    -d <displayname>
    -t <poll time interval in seconds (default 10)>
    -r <retry period after connection abort in seconds (>=10, default 30)>
    -l <logging verbosity (ERROR=0, WARNING=1, DEBUG=2, default 0)>

The service may be quite simply installed from the command line as follows:
"C:\Program Files\pgAdmin III\pgAgent" INSTALL pgAgent -u postgres -p 
secret hostaddr=127.0.0.1 dbname=pgadmin user=postgres

The service may then be started from the command line using net start 
pgAgent, or from the Services control panel applet. Any logging output 
or errors will be reported in the Application event log.


Security concerns
pgAgent is a very powerful tool, but does have some security 
considerations that you should be aware of:
Database password - DO NOT be tempted to include a password in the 
pgAgent connection string - on *nix systems it may be visible to all 
users in 'ps' output, and on Windows systems it will be stored in the 
registry in plain text. Instead, use a libpq ~/.pgpass file to store the 
passwords for every database that pgAgent must access. Details of this 
technique may be found in the PostgreSQL documentation.
System/database access - all jobs run by pgAgent will run with the 
security privileges of the pgAgent user. SQL steps will run as the user 
that pgAgent connects to the database as, and batch/shell scripts will 
run as the operating system user that the pgAgent service or daemon is 
running under. Because of this, it is essential to maintain control over 
the users that are able to create and modify jobs. By default, only the 
user that created the pgAgent database objects will be able to do this - 
this will normally be the PostgreSQL superuser.

*second: init script written by me*
file: /etc/init.d/pgagent
#!/bin/sh
# Copyright (c) 2006 Fernando Paredes Garcia Cusco-Peru.
# All rights reserved.
#
# Author: Fernando Paredes Garcia
# Please send feedback to ####@####.####
#
#
#    This program is free software; you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation; either version 2 of the License, or
#    (at your option) any later version.
#
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#
#    You should have received a copy of the GNU General Public License
#    along with this program; if not, write to the Free Software
#    Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
#
# System startup script for PgAgent
#
#
#
### BEGIN INIT INFO
# Provides:       pgagent
# Required-Start: $network $remote_fs
# Required-Stop:
# Default-Start:  3 5
# Default-Stop:
# Description:    Start the PgAdmin daemon
### END INIT INFO


pga_path=/usr/bin/
pga_filename=pgagent
conn_string=hostaddr=192.168.1.7 user=postgres password=1234 
dbname=postgres
pga_logfile=/var/log/pgagent.log
pga_pidfile=/var/run/$pga_filename.pid

case "$1" in
  start)
    echo 'PgAgent starting...'
    #I can't understand why stdout sents nothing to log file
    $pga_path$pga_filename -l2 -f $conn_string > $pga_logfile &
    #i'm sure there is a safer method to do it
    echo $! > $pga_pidfile
    echo 'Done!'
  ;;
  stop)
    #a dangerous kill, how can this script answer for running tasks?
    kill `cat $pga_pidfile`
    rm -f $pga_pidfile  
    echo 'PgAgent stopped!'
  ;;
  restart)
    kill `cat $pga_pidfile`
    rm -f $pga_pidfile
    echo 'PgAgent stopped!'
    echo 'PgAgent starting...'
    $pga_path$pga_filename -l2 -f $conn_string > $pga_logfile &
    echo $! > $pga_pidfile
    echo 'Done!'
esac


*third: example*

before:
 - be sure you have installed postgresql >= 8.0
 - be sure you are using pgadmin III >= 1.3
 - be sure you create the database named: postgres
 - be sure you run the pgagent.sql script in the database named: postgres
 - be sure you specify "postgres" in the  "Mainteance DB" field
 
pre-requisites:
 a test function with its table. execute this scripts INTO YOUR TARGET 
DATABASE (NOT DATABASE postgres)

CREATE OR REPLACE FUNCTION fun_table01()
  RETURNS bool AS
$BODY$ 
begin
  insert into table01
  select random(),now();
  if found then
    return true;
  end if;
  return false;
end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

CREATE TABLE table01
(
  col01 text,
  col02 text
)
WITHOUT OIDS;

 


testing:
 job creation
 1. right click Jobs, click New Job
 2. fill name field. ie: test1
 3. click button OK
schedule creation
 1. right click schedule(into new job), click new schedule
 2. fill name field. ie: programation
 3. change the end date field, i suggest one day after start date
 4. click button OK
step creation
 1. right click step (into new job), click new job
 2. fill name field. ie: execution
 3. IMPORTANT: select the TARGET DATABASE
 4. go to definition tab.
 5. write: select fun_table01();
 6. click button OK

wait at least 3 minutes and check the table you create before.

THATS ALL!
 
-------------------


I know it needs a ldp documentation formatting, language etc. I'm sorry 
i'm natural language speaker.

Good bless you all!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Hello Fernando,

This will need some work.  For instance, for inclusion in the HOWTO list,
you need a scope, explaining what this is about.  I have no idea, after
quickly running through this document, what this agent is actually doing.
Maybe that is because databases are not my cup of tea, but just imagine
that you see this doc in the list: you will need to attract the readers
who need to know about this infom not those who are using pgagent already
- - those readers probably also know how to configure and run it.

And then some more text, of course, and all the surrounding HOWTO screens,
see http://tldp.org/authors/template/Sample-HOWTO.xml for an example.

Tille.

- --
Machtelt Garrels                ####@####.####
Review Coordinator    	 	http://www.tldp.org/authors/

My Penguin, my freedom.         http://tille.xalasys.com



On Mon, 13 Feb 2006, Fernando Paredes Garcia wrote:

> hello:
> this is a suggestion of pgagent howto.
>
> *first:  this is copied from the pgadmin III help*
>
> pgAgent Installation
> pgAgent runs as a daemon on *nix systems, and a service on Windows
> systems. In the majority of cases it will run on the database server
> itself - it is for this reason that pgAgent is not automatically setup
> when pgAdmin is installed. In some cases however, it may be preferable
> to run pgAgent on multiple systems, against the same database -
> individual jobs may be targetted at a particular host, or left for
> execution by any host. Locking prevents execution of the same instance
> of a job by multiple hosts.
> Database setup
> pgAgent stores it's configuration in the "postgres" database in your
> cluster. This database exists by default in PostgreSQL 8.1 versions, for
> earlier versions you need to create the database yourself. The database
> must have the pl/pgsql procedural language installed - PostgreSQL's
> 'createlang' program can do this if required.
> Once created, connect to the new database, and open the SQL tool. Select
> the File -> Open option from the menu and find the 'pgagent.sql' script
> installed with pgAdmin. The installation location for this file varies
> from operating system to operating system, however it will normally be
> found under 'C:\Program files\pgAdmin III' on Windows systems (or
> 'C:\Program files\PostgreSQL\8.x\pgAdmin III' if installed with the
> PostgreSQL server installer), or '/usr/local/pgadmin3/share/pgadmin3' or
> '/usr/share/pgadmin3' on *nix systems. Once the file is loaded, click
> the 'Run' button to execute the script.
> The script will create a number of tables and other objects in a schema
> called 'pgagent'.
>
> Daemon installation on *nix
> To install the pgAgent daemon on a *nix system, you will normally need
> to have root privileges to modify the system startup scripts - doing so
> is quite system specific so you should consult your system documentation
> for further information.
> The program itself takes few command line options - most of which are
> only needed for debugging or specialised configurations:
> Usage:
>   /path/to/pgagent [options] <connect-string>
>
> options:
>   -f run in the foreground (do not detach from the terminal)
>   -t <poll time interval in seconds (default 10)>
>   -r <retry period after connection abort in seconds (>=10, default 30)>
>   -l <logging verbosity (ERROR=0, WARNING=1, DEBUG=2, default 0)>
>
> The connect string required is a standard PostgreSQL libpq connection
> string (see the PostgreSQL documentation for further details). For
> example, the following command lilne will run pgAgent against a server
> listening on the localhost, using a database called 'pgadmin',
> connecting as the user 'postgres':
> /path/to/pgagent hostaddr=127.0.0.1 dbname=pgadmin user=postgres
>
>
> Service installation on Windows
> pgAgent is able to self-install itself as a service on Windows systems.
> The command line options available are similar to those on *nix systems,
> but include an additional parameter to tell the service what to do:
> Usage:
>   "C:\Program Files\pgAdmin III\pgAgent" REMOVE <serviceName>
>   "C:\Program Files\pgAdmin III\pgAgent" INSTALL <serviceName> [options]
> <connect-string>
>
>   options:
>     -u <user or DOMAIN\user>
>     -p <password>
>     -d <displayname>
>     -t <poll time interval in seconds (default 10)>
>     -r <retry period after connection abort in seconds (>=10, default 30)>
>     -l <logging verbosity (ERROR=0, WARNING=1, DEBUG=2, default 0)>
>
> The service may be quite simply installed from the command line as follows:
> "C:\Program Files\pgAdmin III\pgAgent" INSTALL pgAgent -u postgres -p
> secret hostaddr=127.0.0.1 dbname=pgadmin user=postgres
>
> The service may then be started from the command line using net start
> pgAgent, or from the Services control panel applet. Any logging output
> or errors will be reported in the Application event log.
>
>
> Security concerns
> pgAgent is a very powerful tool, but does have some security
> considerations that you should be aware of:
> Database password - DO NOT be tempted to include a password in the
> pgAgent connection string - on *nix systems it may be visible to all
> users in 'ps' output, and on Windows systems it will be stored in the
> registry in plain text. Instead, use a libpq ~/.pgpass file to store the
> passwords for every database that pgAgent must access. Details of this
> technique may be found in the PostgreSQL documentation.
> System/database access - all jobs run by pgAgent will run with the
> security privileges of the pgAgent user. SQL steps will run as the user
> that pgAgent connects to the database as, and batch/shell scripts will
> run as the operating system user that the pgAgent service or daemon is
> running under. Because of this, it is essential to maintain control over
> the users that are able to create and modify jobs. By default, only the
> user that created the pgAgent database objects will be able to do this -
> this will normally be the PostgreSQL superuser.
>
> *second: init script written by me*
> file: /etc/init.d/pgagent
> #!/bin/sh
> # Copyright (c) 2006 Fernando Paredes Garcia Cusco-Peru.
> # All rights reserved.
> #
> # Author: Fernando Paredes Garcia
> # Please send feedback to ####@####.####
> #
> #
> #    This program is free software; you can redistribute it and/or modify
> #    it under the terms of the GNU General Public License as published by
> #    the Free Software Foundation; either version 2 of the License, or
> #    (at your option) any later version.
> #
> #    This program is distributed in the hope that it will be useful,
> #    but WITHOUT ANY WARRANTY; without even the implied warranty of
> #    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
> #    GNU General Public License for more details.
> #
> #    You should have received a copy of the GNU General Public License
> #    along with this program; if not, write to the Free Software
> #    Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
> #
> # System startup script for PgAgent
> #
> #
> #
> ### BEGIN INIT INFO
> # Provides:       pgagent
> # Required-Start: $network $remote_fs
> # Required-Stop:
> # Default-Start:  3 5
> # Default-Stop:
> # Description:    Start the PgAdmin daemon
> ### END INIT INFO
>
>
> pga_path=/usr/bin/
> pga_filename=pgagent
> conn_string=hostaddr=192.168.1.7 user=postgres password=1234
> dbname=postgres
> pga_logfile=/var/log/pgagent.log
> pga_pidfile=/var/run/$pga_filename.pid
>
> case "$1" in
>   start)
>     echo 'PgAgent starting...'
>     #I can't understand why stdout sents nothing to log file
>     $pga_path$pga_filename -l2 -f $conn_string > $pga_logfile &
>     #i'm sure there is a safer method to do it
>     echo $! > $pga_pidfile
>     echo 'Done!'
>   ;;
>   stop)
>     #a dangerous kill, how can this script answer for running tasks?
>     kill `cat $pga_pidfile`
>     rm -f $pga_pidfile
>     echo 'PgAgent stopped!'
>   ;;
>   restart)
>     kill `cat $pga_pidfile`
>     rm -f $pga_pidfile
>     echo 'PgAgent stopped!'
>     echo 'PgAgent starting...'
>     $pga_path$pga_filename -l2 -f $conn_string > $pga_logfile &
>     echo $! > $pga_pidfile
>     echo 'Done!'
> esac
>
>
> *third: example*
>
> before:
>  - be sure you have installed postgresql >= 8.0
>  - be sure you are using pgadmin III >= 1.3
>  - be sure you create the database named: postgres
>  - be sure you run the pgagent.sql script in the database named: postgres
>  - be sure you specify "postgres" in the  "Mainteance DB" field
>
> pre-requisites:
>  a test function with its table. execute this scripts INTO YOUR TARGET
> DATABASE (NOT DATABASE postgres)
>
> CREATE OR REPLACE FUNCTION fun_table01()
>   RETURNS bool AS
> $BODY$
> begin
>   insert into table01
>   select random(),now();
>   if found then
>     return true;
>   end if;
>   return false;
> end;
> $BODY$
>   LANGUAGE 'plpgsql' VOLATILE;
>
> CREATE TABLE table01
> (
>   col01 text,
>   col02 text
> )
> WITHOUT OIDS;
>
>
>
>
> testing:
>  job creation
>  1. right click Jobs, click New Job
>  2. fill name field. ie: test1
>  3. click button OK
> schedule creation
>  1. right click schedule(into new job), click new schedule
>  2. fill name field. ie: programation
>  3. change the end date field, i suggest one day after start date
>  4. click button OK
> step creation
>  1. right click step (into new job), click new job
>  2. fill name field. ie: execution
>  3. IMPORTANT: select the TARGET DATABASE
>  4. go to definition tab.
>  5. write: select fun_table01();
>  6. click button OK
>
> wait at least 3 minutes and check the table you create before.
>
> THATS ALL!
>
> -------------------
>
>
> I know it needs a ldp documentation formatting, language etc. I'm sorry
> i'm natural language speaker.
>
> Good bless you all!
>
>

- --
My Penguin, my freedom.		http://tille.xalasys.com
Books:				http://writers.fultus.com/garrels
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFD+ZxGsIIUbMXbBA8RAhGhAJ9jw37K8RQuLoDZRNYikJKyhPvnXACgjGiS
0xx0LtxZD2ahiqGnk5xsTlQ=
=bmPl
-----END PGP SIGNATURE-----