pgAgent Setup

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.

Installation

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:

1. Download the source packages from https://www.pgadmin.org/download/pgagent-source-code/ and extract the code.
2. Create a build directory.
3. 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.

 BOOST_MULTITHREADED_BUILD ON                                                                                                                                                                 
 BOOST_STATIC_BUILD OFF                                                                                                                                                                
 CMAKE_BUILD_TYPE Release                                                                                                                                                            
 CMAKE_INSTALL_PREFIX /usr/local                                                                                                                                                         
 CMAKE_OSX_ARCHITECTURES x86_64                                                                                                                                                            
 CMAKE_OSX_DEPLOYMENT_TARGET 11.2                                                                                                                                                              
 CMAKE_OSX_SYSROOT /Library/Developer/CommandLineTools/SDKs/MacOSX11.3.sdk                                                                                                            
 PG_CONFIG_PATH /usr/local/bin/pg_config                                                                                                                                           
 STATIC_BUILD OFF 

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.

Run pgAgent

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>

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)>
  -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.

Conclusion

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.