This document describes how to implement automatic job execution in TencentDB for PostgreSQL through the pgAgent feature. We recommend you use the pg_cron extension to schedule jobs.
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:
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 databases, tables, and other objects, so you need to install it first.
Log in to the TencentDB for PostgreSQL instance and create your business database.
Run the following statement in the database where you need to enable the pgAgent feature and the
You must also create pgAgent in the
psql > create extension pgagent; CREATE EXTENSION
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.
After pgAgent is installed, run the following statement to start the job scheduler:
- Use the command based on the actually installed version of pgAgent. For example, if v10 is installed, the command should be
- Note that
postgresrather 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
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