Contents.Any UNIX-Like Platform.General Linux. Gentoo:.Debian/Ubuntu LinuxDebian based installs have a somewhat unique design that allows multiple database clusters to be managed independently. This allows running both multiple database instances as well as multiple versions more easily than other packaging schemes.Note: if you do not wish, for example, PostgreSQL or Slony to start automatically when the software is upgraded, do the following:echo 'if $1 in postgresql-9.3 slony1-2 then exit 101 else exit 0' /usr/sbin/policy-rc.dchmod +x /usr/sbin/policy-rc.d.
To use Postgre in your machine, you need to install: Postgre Database ServerA graphical tool to administer and manage the DB. PgAdmin is the most popular tool GUI Tool for Postgre How to Download and Install PostgreSQL on Windows.
pgAgent runs as a daemon on Unix systems, and a service on Windows systems.In the majority of cases it will run on the database server itself - it isfor this reason that pgAgent is not automatically setup when pgAdmin isinstalled. In some cases however, it may be preferable to run pgAgent onmultiple systems, against the same database - individual jobs may be targettedat a particular host, or left for execution by any host. Locking preventsexecution of the same instance of a job by multiple hosts.
Database setup¶
pgAgent stores its configuration in the ‘postgres’ database in your cluster. Thisdatabase exists by default in PostgreSQL 8.1 versions, for earlier versionsyou need to create the database yourself. The databasemust have the pl/pgsql procedural language installed - PostgreSQL’s‘createlang’ program can do this if required.
Connect to the “postgres” database (once created if needed), and open the SQL tool.If the server is 9.1 or later, and pgAgent 3.4.0 or later, simply paste in thisquery and click the ‘Run’ button:
This command will create a number of tables and other objects in a schema called‘pgagent’.
For earlier versions of PostgreSQL or pgAgent, select the File -> Open option from themenu and find the ‘pgagent.sql’ script installed with pgAdmin. Theinstallation location for this file varies from operating system to operatingsystem, however it will normally be found under ‘C:Program filespgAdmin III’on Windows systems (or‘C:Program filesPostgreSQL8.xpgAdmin III’ if installed with the PostgreSQL server installer),or ‘/usr/local/pgadmin3/share/pgadmin3’ or ‘/usr/share/pgadmin3’ on Unixsystems. 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 Unix¶
To install the pgAgent daemon on a Unix system, you will normally need to have rootprivileges to modify the system startup scripts - doing so is quite system specificso you should consult your system documentation for further information.
The program itself takes few command line options - most of which are onlyneeded for debugging or specialised configurations:
The connect string required is a standard PostgreSQL libpq connectionstring (see the PostgreSQL documentation on the connection stringfor further details). For example, the followingcommand lilne will run pgAgent against a server listening on the localhost,using a database called ‘pgadmin’, connecting as the 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 Unix systems, butinclude an additional parameter to tell the service what to do:
The service may be quite simply installed from the command line as follows(adjusting the path as required):
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 bereported in the Application event log. The DEBUG mode may be used to run pgAgent from acommand prompt. When run this way, log messages will output to the command window.
Security concerns¶
pgAgent is a very powerful tool, but does have some security considerationsthat you should be aware of:
Database password - DO NOT be tempted to include a password inthe pgAgent connection string - on Unix systems it may be visible to all usersin ‘ps’ output, and on Windows systems it will be stored in the registry inplain text. Instead, use a libpq ~/.pgpass file to store the passwords forevery database that pgAgent must access. Details of this technique may befound in the PostgreSQL documentation on .pgpass file.
System/database access - all jobs run by pgAgent will run with thesecurity privileges of the pgAgent user. SQL steps will run as the user thatpgAgent connects to the database as, and batch/shell scripts will run as theoperating system user that the pgAgent service or daemon is running under.Because of this, it is essential to maintain control over the users that areable to create and modify jobs. By default, only the user that created thepgAgent database objects will be able to do this - this will normally be thePostgreSQL superuser.