pgAgent is a job scheduling agent for Postgres databases, capable of running multi-step batch or shell scripts and SQL tasks on complex schedules, which may be managed using pgAdmin.
pgAgent shipped as a separate application. This blog is for users/developers who specifically want to build from a source. Most users should use pre-built packages from the PostgreSQL APT/YUM repositories, or StackBuilder.
Prerequisite to build the code:
- A C/C++ compiler, such as GCC
- CMake 3.3
- A Boost library 1.41 or higher installation
- Supported PostgreSQL installation
Follow the steps below:
- Download the source packages from https://www.pgadmin.org/download/pgagent-source-code/ and extract the code.
- Create a build directory.
- Run ccmake from the build directory. By default, ccmake will generate Unix Makefiles.
ccmake /path/to/pgagent -B /build_directory
Note: You can use the CMake GUI for Mac and Windows.
4. Press 'c' to generate a default configuration if required.
Use the ccmake interface to adjust any settings as required. When configured as needed, press 'c' to re-configure (if required) and 'g' to generate the build files and exit.
If the build type parameter is not configured by default, you must configure it; otherwise, build generation will not happen.
5. Run 'make' to build pgAgent on Mac or Unix, or open the generated project files in VC++ on Windows and build the solution in the desired configuration.
We can run pgAgent as a demon on Linux/Mac or as a service on Windows. For more details, refer to https://www.pgadmin.org/docs/pgadmin4/development/pgagent_install.html
A few command line options can be used to debug, check the logs, run in the foreground, etc.
/path/to/pgagent [options] <connect-string>
-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)>
-s <log file (messages are logged to STDOUT if not specified)>
-l <logging verbosity (ERROR=0, WARNING=1, DEBUG=2, default 0)>
Note: On windows, additional options other than those above are available to tell the service what to do.
Using pgAgent with pgAdmin
To use pgAgent from the pgAdmin browser tree, you must create the pgAgent extension in the maintenance database registered with pgAdmin.
Before creating the extension, copy the pgagent.control and pgagent-<version>.sql files from the pgagent build directory to the database’s extension directory.
Now, you can create the pgagent extension through the pgAdmin Sql Editor tool or use the Extensions GUI interface to create the extension. Make sure you do this against the maintenance database.
CREATE EXTENSION pgagent;
Ensure that pl/pgsql procedural language is installed too. For EDB PostgreSQL, it is installed by default, but for PostgreSQL, you need to create it.
CREATE LANGUAGE plpgsql;
After creating an extension, you can see the pgAgent node in the pgAdmin browser tree. From where you can create and manage the jobs.
Apart from source packages, pgAgent is also available as a StackBuilder add-on package if you use EDB's Windows, Mac and Linux Installers, as a Debian package from apt.postgresql.org, or an RPM package from yum.postgresql.org.