tencent cloud

This document describes how to implement automatic job execution in TencentDB for PostgreSQL through the pgAgent feature.

Overview

If your business needs to perform specified actions in the database at scheduled times, such as clearing redundant data, updating materialized views, performing VACUUM FULL, and executing DML, PostgreSQL can help implement with the following features:

• The crontab feature of Linux
• The pgAgent feature of pgAdmin

pgAgent is an extension in the pgAdmin tool imported in pgAdmin III v1.4. It is mainly used as a PostgreSQL job scheduling agent and capable of running multi-step batch or shell scripts and SQL jobs on complex schedules.
It should be noted that pgAgent requires the support of certain database tables and objects, so you need to install it first.

Directions

Configuring pgAgent

1. Log in to the TencentDB for PostgreSQL instance and create your business database.

2. Run the following statement in the database where you need to enable the pgAgent feature and the postgres database:

   Note：

   You must also create pgAgent in the postgres database.

   psql > create extension pgagent;
   CREATE EXTENSION
   

3. After the configuration is completed, you need to start the job scheduler through the pgAgent tool.
   Log in to the CVM instance (we recommend you put the CVM and TencentDB for PostgreSQL instances in the same VPC). Choose the pgAgent version according to the actual database version. This document uses v11.8 as an example to install pgagent_11 available here.

4. After pgAgent is installed, run the following statement to start the job scheduler:

   Note：

   • Use the command based on the actually installed version of pgAgent. For example, if v10 is installed, the command should be pgagent_10.
   • Note that dbname must be postgres rather than the name of the database that needs to run the scheduler; otherwise, the job configuration items will not be displayed on the pgAdmin page.
   • When the connection is executed, if the error "ERROR: Unsupported schema version" is reported, submit a ticket for assistance.

   pgagent_11 hostaddr=IP dbname=postgres user=username port=port password=password
   

5. After successful execution, there is no echo, but you can use the following command to check whether the process is started successfully:

   Run this statement, and if there is a `pgagent` process, it has been started successfully.
   # ps -ef |grep pgagent
   root      158553       1  0 Oct30 ?        00:00:15 pgagent_11 hostaddr=IP dbname=postgres user=username port=port password=password
   

Configuring pgAgent Jobs through pgAdmin

1. Log in to the TencentDB for PostgreSQL console, click an instance ID in the instance list to enter the instance details page, and enable the public network access.
2. Open pgAdmin 4 and access your TencentDB for PostgreSQL instance at the public network access address. At this time, you can see pgAgent Jobs on the page.
   
3. On the pgAdmin page, right-click and select pgAgent Jobs > Create > Create Jobs to create a scheduled job.
4. On the General page, configure the basic job information.
   
5. Enter the Steps tab and configure the job that needs to be executed at the scheduled time. To do so, click + in the top-right corner to add a step, name it, and then configure the SQL statement to be executed on the Code tab.
   
6. Enter the Schedules page and configure the scheduling information for job execution:
7. On the General tab below, configure the effective time of the job.
   
8. On the Repeat tab below, configure a crontab-style schedule.
   
9. After configuring the execution time, you can also configure the time when the job should not be executed on the Exceptions tab.
10. Click Save and this job will be automatically executed based on the configuration.