7 Advanced Server Supporting Components : 7.5 pgAgent
7.5 pgAgentUnless otherwise noted, the commands and paths in the following section assume that you have performed an installation with the interactive installer.pgAgent is a job scheduling agent for Advanced Server. When you schedule a pgAgent job, the scheduling agent records the job definition in a scheduling table. The pgAgent service (running in the background) monitors the scheduling table and executes jobs at their scheduled time.The Advanced Server installer creates the scheduling tables required by the pgAgent service under schema pgagent. If you are installing Advanced Server in Compatible with Oracle mode (the default), the scheduling tables are created in the edb database; if you are installing in PostgreSQL mode, the tables are created in the postgres database.You can use the Postgres Enterprise Manager Client (available from EnterpriseDB) to conveniently schedule and manage pgAgent jobs. The PEM Client also offers online documentation for pgAgent.To use the DBMS_JOB package compatible with Oracle databases from a database other than the edb database, you must install the pgAgent schema into each additional database in which you wish to schedule jobs. To install the pgAgent schema, you must:2) Invoke the pgagent.sql script.When installed with the RPM package, the pgagent.sql script is located in /usr/share/ppas95-pgagent-3.4.1. When installed with the interactive installer, the script is located in /opt/PostgresPlus/9.5AS/share/extension.If you installed Advanced Server using the interactive installer, pgAgent is installed as well. The pgagent program is installed under the bin subdirectory of the Advanced Server installation directory along with the required startup scripts, subdirectories for logging, etc.Similarly, if you installed Advanced Server using the ppas95 RPM package or the ppas95-pgagent RPM package, pgAgent is installed. The pgagent program is installed under the bin subdirectory of the Advanced Server installation directory along with the required startup scripts, subdirectories for logging, etc.As already stated, when the interactive installer is used, the pgagent schema and its scheduling tables are created in either the edb database or the postgres database.When the RPM package is used, the pgagent schema and its scheduling tables are not automatically created. The scheduling tables must be created by connecting to the desired database and executing the pgagent.sql script as previously described.When the interactive installer is used, a script named servicemanager.sh along with other supporting files is installed under the installer/pgAgent subdirectory of the Advanced Server location for providing an alternative method for managing pgAgent.The servicemanager.sh script can be used to install pgAgent, uninstall pgAgent, start the pgAgent service, stop the pgAgent service, or disable the automatic startup of pgAgent.Note: The servicemanager.sh script is only provided when the interactive installer is used, and the same functionality provided by this script can be accomplished using the methods described in Chapter 5.You must have superuser privileges to manage the pgAgent service using the servicemanager.sh script.When invoking the servicemanager.sh script, the current working directory from where the script is invoked must be /opt/PostgresPlus/9.5AS/installer/pgAgent.If for whatever reason, you need to reinstall pgAgent using the servicemanager.sh script, you must create the pgpass file in the following directory:The file should contain a set of connection properties that reflect the properties specified during the Advanced Server installation process. The format of an entry in the pgpass file is as follows:hostname specifies name of the Advanced Server host.port specifies the port on which the Advanced Server host is listening.database specifies the name of the database.username specifies the name of an Advanced Server user.password specifies the password of the database in which the scheduling table resides.After creating the pgpass file, execute the following commands as the root user to install the pgAgent service and create the startup file. The script configures the pgAgent executor to run in the background at system startup../servicemanager.sh component_name user_name install_dir service_name action_type version data_dir pg_host pg_port pg_user pg_databasecomponent_name - Specify pgAgent.user_name - Specify the Linux operating system account that runs Advanced Server. For example, this is enterprisedb if installed with the mode compatible with Oracle databases. This is postgres if installed with the mode compatible with PostgreSQL databases.install_dir - Specify the Advanced Server installation directory.service_name - Specify ppas-agent-9.5.action_type - Specify install to install pgAgent, uninstall to uninstall pgAgent, start to start up the pgAgent service, stop to stop the pgAgent service, or disable_autostart to prevent automatic startup of the pgAgent service.version - Specify 9.5.data_dir - Specify the Advanced Server data directory (for example, /opt/PostgresPlus/9.5AS/data).pg_host - Specify the name or IP address of the target database server's host.pg_port - Specify the port number of the target database server.pg_user - Specify the name of the database user listed in the pgpass file (for example, enterprisedb).pg_database - Specify the name of the target database.After installing the service, you should remove the password from the pgpass file so that the password is not available to other users.INFO: [CMD: /opt/PostgresPlus/9.5AS/bin/pgagent -l 1 -s /var/log/ppas-agent-9.5/ppas-agent-9.5.log hostaddr=127.0.0.1 port=5444 dbname=edb user=enterprisedb]INFO: [Please see service script file /var/log/ppas-agent-9.5/ppas-agent-9.5_script.log for details]INFO: [CMD: /opt/PostgresPlus/9.5AS/bin/pgagent -l 1 -s /var/log/ppas-agent-9.5/ppas-agent-9.5.log hostaddr=127.0.0.1 port=5444 dbname=edb user=enterprisedb]INFO: [Please see service script file /var/log/ppas-agent-9.5/ppas-agent-9.5_script.log for details]pgAgent also provides an alternate command line argument that allows you to install or remove the pgAgent service on a Windows system. Use the following command to install the pgAgent service:pgagent INSTALL service_name [options] connection_stringservice_name specifies the name of the pgAgent service.
specifies a user or DOMAIN\user specifies a logging verbosity. Specify a 0 to log ERROR messages, 1 to log WARNING messages, or 2 to log DEBUG messages. The default is 0connection_string specifies connection information for an Advanced Server host.%EDB_HOME%\bin\pgagent INSTALL jobscheduler -u enterprisedb -p 1safepassword hostaddr=127.0.0.1 dbname=edb user=enterprisedb
• Connecting as user enterprisedb.
• Using the password 1safepassword.You can optionally include the REMOVE keyword to remove a pgAgent service:After installing the pgAgent service on either Linux or Windows, you must start the service. Please Note: if you stop or restart the Advanced Server service, the dependent pgAgent service is also stopped; you must manually restart the pgAgent service.
7 Advanced Server Supporting Components : 7.5 pgAgent