Use pgAgent to create scheduled tasks

You can use Postgresql's pgAgent to create scheduled tasks to conveniently create scheduled tasks. Currently, the online materials are relatively old, And the Postgresql version is updated faster, resulting in some online tutorials being useless. Now I want to share my experience. Postgresql version 9. X1. install pgAgent. PgAgent

You can use Postgresql's pgAgent to create scheduled tasks to conveniently create scheduled tasks. Currently, the online materials are relatively old, And the Postgresql version is updated faster, resulting in some online tutorials being useless. Now I want to share my experience. Postgresql version 9.X 1. Install pgAgent. PgAgent

Use pgAgent to create scheduled tasks

With Postgresql's pgAgent, you can easily create scheduled tasks. Currently, the online materials are relatively old, And the Postgresql version is updated faster, resulting in some online tutorials being useless. Now I want to share my experience.
The Postgresql version is 9.X.

1. Install pgAgent. PgAgent is provided by pgAdmin in earlier versions, but the new version of pgAgent must be installed using the Application Stack Builder provided by Postgresql. The specific process is as follows:








* Enter the Postgresql postgres username and password, or the user account that has the permission to operate the postgres database. Because pgAgent needs to create the corresponding table in postgres database, the user entered here must have the permission to operate the postgres database. Here, the owner of the maximum permission will be used.

* Here is the windows User Account for executing pgAgent in ghost. If you are a domain user, enter the complete domain user name and password. Because pgAgent will add a service named "PostgreSQL Scheduling Agent-pgAgent" in the windows service, and this service must be logged on before it can run, therefore, the account entered here must be the current account that widnows can log on.



Here, it indicates that the pgAgent has been successfully installed. You can go to the windows Service and check whether the pgAgent service is running:

2. Create a Job. After the pgAgent is successfully installed and the pgAgent is running properly, create a scheduled task. Postgresql has two concepts: Schedule and Step. Schedule is a timer that configures when the Job is executed. Step is the content of the execution task. By combining these two parts, you can configure when the Job will be executed. A Job can have multiple Schedule and multiple steps.
The procedure is as follows:
Create a new Job named Job1, set the Job Class to Routine Maintenance, and select Enabled.


Add Schedule to job1 and name it schedule1. Check Enabled and set the start time and end time. If the end time is not specified, the Job will run endlessly. In the Minutes section of the Times Tab, select 00 and 30. This indicates that job1 will be executed on the hour's hour (00) and half (30. That is, it is executed every 30 minutes. If you select only 30, the execution is performed every 30 minutes, for example, and. The concepts of other Hours, Week Days, Month Days, and Months are the same.

In addition, the Exceptions Tab is an exception. In this case, the Job will not be executed.
Then add the execution content for job1. Add a Step named Step 1. Select Enabled. Then select the database for which the job is to be executed. Pay attention to the Kind and On Error sections. Kind indicates whether to execute the job as an SQL statement or a batch job. In addition to running SQL, jobs can also run windows batch commands. In this example, SQL is used. In the On Error section, it indicates whether to stop (Fail) When an Error occurs during job execution, whether it is normal (Succeed) or ignored (Ignore ). In this example, Fail is used to stop running and exit when an error occurs in the execution content.

Now we define the SQL statements that job1 needs to execute. We generally recommend that you write these SQL statements in a transaction so that even if job1 fails to run, the data will not be affected.

In this step, job1 is basically created.
To monitor the running status of job1, you can view the next running time, last running time, and result of job1 on the Properties Tab of job1 through pgAdmin. You can also use the Statistics Tab to view the number of job1 operations.



Now, the introduction of how to use pgAgent to create scheduled tasks is complete. We will discuss it further in the future.
Thank you.