What a job scheduler can do in Postgres Pro
Scheduler tasks (scheduler) is not at all times considered a mandatory tool in the world of databases. Everything depended on the purpose and origin of the DBMS. It is absolutely impossible to imagine classic commercial DBMS (Oracle, DB2, MS SQL) without a scheduler. On the other hand, it is difficult to imagine a potential MongoDB user who refuses to choose this trendy NoSQL-DBMS due to the lack of a scheduler. (By the way, the term “task scheduler” is used in the Russian context of a DBMS to distinguish it from the query planner - query planner, but for brevity we will call it here as a planner).
PostgreSQL, being an Open Source and having absorbed the traditions of the DIY community (“do it yourself”), nowadays regularly claims to be at least a deputy commercial DBMS. From this it automatically follows that PostgreSQL simply must have a scheduler, and that this scheduler should be convenient for the database administrator and the user. And that it is desirable to reproduce the full functionality of commercial DBMS, although it would be nice to add something of your own.
The need for a scheduler is most obviously manifested when working with a base in industrial operation. To the developer, who was assigned a server to experiment with the database, the scheduler, in general, is useless: if necessary, he himself will plan all the necessary operations using OS tools (cron or at in Unix). But to the working base of his serious company will not let a gun shot. There is also an important administrative nuance, that is, no longer a nuance, but a serious, if not decisive, reason: the database administrator and the sysadmin are not just different people with different tasks. It is not excluded that they belong to different divisions of the company, and, maybe, even sit on different floors. Ideally, the database administrator maintains its viability and monitors its evolution, and the sysadmin’s area of ​​responsibility maintains the viability of the OS and network.
')
Consequently, the base administrator must have a tool to perform the necessary set of possible jobs on the server. It is not for nothing that the Oracle Scheduler materials say that “Oracle Scheduler eliminates the need to use OS-specific task schedulers (cron, at) when building a database-centric application.” That is, the admin database can do everything, especially since it is difficult to imagine an Oracle administrator who is not oriented in the OS mechanisms. He does not have to run to the sysadmin or write letters to him every time when routine operations by means of the OS are required.
Here are the scheduler requirements that are typical for commercial databases, such as Oracle, DB2, MS SQL:
Planner should be able to
The last point seems to be not obvious: there are quite a few other, regular means besides the scheduler, capable of running one-time tasks. But this is not a completely normal execution mode. For example, the detached job mode: we are talking about a task, which seems to be disconnected (temporarily or permanently) from the process that caused it. Having done the work, the disconnected process can again contact the process that started it (by sending a signal of successful or unsuccessful completion) to it, report the result to it or write the result (to a file or database table). Some DBMS schedulers are able to stop and start the DBMS itself (we did not set such a task).
You can solve the tasks in different ways: “outside” and “inside” of the DBMS itself. The most serious attempt to make a full-featured scheduler is pgAgent, distributed with pgAdmin III / IV. In the commercial version — in the EnterpriseDB distribution — it is integrated into the pgAdmin graphical user interface and can be used cross-platform.
pgAgent can:
This scheduler works as a PostgreSQL extension, but does not execute tasks “inside” the DBMS, but creating its own, “external” daemons.
There are drawbacks to this approach. Among them are important:
All tasks started by pgAgent will be executed with the rights of the user who started the agent. SQL queries will be executed with the rights of the user connected to the database. The shell scripts will be executed with the rights of the user on whose behalf the daemon (or the Windows service) pgAgent is running. Therefore, for security, you will have to control users who can create and run tasks. In addition, the password cannot be included in the connection string (connection string), since in Unix it will be visible in the output of the ps command and in the database startup script, and in Windows it will be stored in the registry as unencrypted text.
(from pgAdmin 4 1.6 documentation ).
In this solution, pgAgent polls the base server at specified intervals (since the information about the jobs is stored in the base tables): whether there are any jobs available. Therefore, if for some reason the agent does not work at the moment when the work should start, it will not start until the agent starts working.
In addition, any connection to the server consumes a pool of possible connections, the maximum number of which is determined by the configuration parameter max_connections. If an agent spawns many processes, and the administrator has not shown proper vigilance, this can be a problem.
Creating a fully integrated scheduler in the DBMS (“inside” the DBMS) eliminates these problems. And it is especially convenient for those users who are accustomed to minimalist interfaces for accessing the database, such as psql.
At the end of 2016, Postgres Professional started to create their own scheduler, which is fully integrated into the database. Now it is used by customers and is documented in detail. The scheduler was created as an extension (optional module), called pgpro_scheduler, and comes as part of the commercial version of Postgres Pro Enterprise, starting with the first version. Developer - Vladimir Ershov.
When installing it in the DBMS configuration files, you need to remember to include it in the configuration file
From the very beginning, it was decided to create pgpro_scheduler in a modern style, organic for the company - with a configuration record in JSON. This is convenient, for example, for web services creators who can integrate the scheduler into their applications. But for those who do not want to use JSON, there are functions that take parameters as ordinary variables. The scheduler comes with the distribution of the DBMS and it is cross-platform.
pgpro_scheduler does not start external daemons or services, but creates child processes in relation to the postmaster background background processes. The number of “workers” is set in the pgpro_scheduler configuration, but is limited to the overall server configuration. Actually, the most usual SQL commands come to the scheduler input, without any restrictions, so you can run functions in any available Postgres language. If several SQL queries are included in the JSON structure, they can (if you follow a certain syntax) be executed inside a single transaction:
this is equivalent to:
and if each request is in its own transaction, then:
For example, in the second command in the list, an error will occur (of course, in
When pgpro_scheduler is started, a group of background workers with its own hierarchy always starts working: one worker, in the rank of supervisor scheduler, controls workers in the rank of database managers - one for each database specified in the configuration line. Managers, in turn, control workers directly serving the tasks. The supervisor and managers are fairly easy processes, so if the scheduler serves even dozens of bases, this does not affect the overall system load. And then the workers are started in each database according to the needs in processing requests. In sum, they must fit within the limitation of the max_worker_processes DBMS. A group of teams for the instant execution of tasks uses resources differently, but more on that later.
Fig.1 Basic mode of operation pgpro_scheduler
You can create a task using JSON using the function
This object may contain the following keys, some of which may be omitted:
You can choose from the types of representation of the schedule: for those who are accustomed to cron - a line in the style of crontab, which defines the schedule of execution. But you can use the rule - then the schedule will be represented as a JSONB object (see description below). Another option: date - a set of specific dates on which the execution of commands is scheduled. They can be combined, but at least one option must be used. It will look, for example, like this:
In addition, there are still many useful parameters, which can be found in the documentation . Among them:
The schedule can be specified as a crontab-style string (cron key) or as a JSONB object (rule key). They may contain the following keys:
An assignment can also be scheduled for a specific date or set of dates. That is, in principle, the task can be one-time, although for one-time tasks you can use the special one-time job mode with other function calls.
Scheme 1. Scheduler Hierarchy Process
The
This query will be executed under any state of completion of the main transaction. You can get the transaction completion status in it from the Postgres Pro Enterprise
As can be seen even from the abbreviated description, the range of possibilities is rich. In total, there are about 40 functions that work with the pgpro_scheduler application. You can create tasks, cancel them, view their status, filter information about tasks by users and other criteria.
As mentioned, there is still an important class of tasks for the scheduler: the formation of separate, non-periodic tasks using the one-time job mechanism. If the run_after parameter is not specified, then in this mode the scheduler is able to start executing the task immediately at the moment of arrival - up to the polling time interval of the table to which the task is written. In the current implementation, the interval is fixed and equal to 1 second. Background workers start in advance and wait for jobs to appear "under steam", rather than starting as needed, as in schedule mode. Their number is defined by the schedule.max_parallel_workers parameter. The corresponding number of requests can be processed in parallel.
Fig.2 One-time job mode.
The main function that forms the task is as follows:
This function has, in accordance with its specificity, which was at the beginning, fine tuning. The max_duration parameter sets the maximum execution time. If the work is not done in the allotted time, the task is removed (by default, the execution time is unlimited). max_wait_interval does not refer to the running time, but to the waiting time to start working. If the DBMS does not find “workers” within this period of time ready to take on execution, the task is removed. An interesting parameter depends_on specifies an array of work (in one-time mode), after which you have to start this work.
A useful parameter -
At the beginning, detached jobs were mentioned. In the current version, the process that started the one-time task is dragging its existence in anticipation of the result. The overhead of the background worker’s work is small, it makes no sense to stop it. It is important that the execution or non-performance of the task will not pass without a trace, we can learn about its fate from a request to the scheduler log, available to us, and not just to the database administrator. This is not the only way to track down a transaction even if it is rolled back: Postgres Pro Enterprise has an autonomous transaction mechanism that can be used for the same purpose. But in this case, the result will be recorded in the DBMS log, and not in the “personal” log of the user running the scheduler.
If the scheduler user needs to schedule or simply run some OS commands with the rights that are available to him inside the OS, he can easily do this through the scheduler, using the programming languages ​​available to him. Suppose he decided to use untrusted Perl:
After that, you can write in the usual request such as, for example, the function:
To begin with, a simplified example of managing sections (partitions) from the scheduler. Suppose we broke the logs of visiting the site into sections by month. We do not want to store on the two-year freshness and younger sections on expensive fast drives, and dump the rest into another tablespace corresponding to other, cheaper media, while maintaining full-featured search and other operations on all the logs (with a table that is not divided into sections). impossible). We use convenient section management functions in the pg_pathman extension. The postgresql.conf file should contain the line shared_preload_libraries = 'pg_pathman, pgpro_scheduler'.
Configuring:
Baz can be several. In this case, they are listed separated by commas inside quotes.
We have just created a parent table that we will split into sections. This is a tribute to the traditional PostgreSQL syntax based on table inheritance. Now, in Postgres Pro Enterprise, you can create sections not in 2 stages (first empty parent table, then specify sections), but immediately define sections. In this case, we will use the convenient pg_pathman function, which allows you to first set the approximate number of sections. As you fill the necessary sections will be created automatically:
We set 10 initial sections, one per month, starting from 1 January. 2015. Fill them with some amount of data.
You can monitor the number of sections as follows:
Starting
Create a directory in the OS and the corresponding tablespace where the outdated logs will be stored:
And finally, the function that the scheduler will run every day:
It takes as parameters: the name of the partitioned table
To warm up, we will set a one-time task:
Once executed, the scheduler will display the task id. Its status can be viewed in the views
To make it easier to play with the scheduler and sections, you can create a function
It can also be run from the scheduler, but using the run_after parameter, which sets the delay time in seconds - so that we have time to think about whether we did the right thing:
and if wrong, you can cancel it with the function
Making sure that everything works as planned, you can put the task (now in JSON syntax) already in the schedule:
That is, every morning at five minutes to eight the planner will check if it is time to move the outdated partitions to the “cold” archive and relocate if it is time. The status at this time can be checked by the scheduler log:
Let us show how one of the typical tasks is solved, in which the execution of works on a schedule is required and one-time tasks are used.
We have a content delivery network (CDN). We are going to put banners on several websites that are part of it, which the users from advertising agencies have automatically uploaded to the directory assigned to them.
Let's create a function that initializes the loading of the banner on the server. For each server, it creates a download task, as well as a task that waits for all created downloads and sets the correct status for the banner when the downloads are completed:
And this function simulates sending a banner to the server (in fact, it’s just sleeping for a while):
This function, based on the status of uploading a banner to the server, will determine which status to put the banner:
This function will check on schedule whether there are raw banners. And, if necessary, run the banner processing:
Now let's take a look at the data. Create a list of servers:
Create a couple of banners:
And, finally, we will schedule the task of checking newly submitted banners, which should be decomposed into servers. The task will be executed every minute:
That's all, pictures will be laid out on sites, you can relax.
As Post Scriptum, we inform you that the pgpro_scheduler scheduler works not only on a separate server, but also in a multimaster cluster configuration. But this is a topic for another conversation.
And as Post Post Scriptum - in the future plans of embedding the scheduler in the graphical administration shell that is being created now.
PostgreSQL, being an Open Source and having absorbed the traditions of the DIY community (“do it yourself”), nowadays regularly claims to be at least a deputy commercial DBMS. From this it automatically follows that PostgreSQL simply must have a scheduler, and that this scheduler should be convenient for the database administrator and the user. And that it is desirable to reproduce the full functionality of commercial DBMS, although it would be nice to add something of your own.
The need for a scheduler is most obviously manifested when working with a base in industrial operation. To the developer, who was assigned a server to experiment with the database, the scheduler, in general, is useless: if necessary, he himself will plan all the necessary operations using OS tools (cron or at in Unix). But to the working base of his serious company will not let a gun shot. There is also an important administrative nuance, that is, no longer a nuance, but a serious, if not decisive, reason: the database administrator and the sysadmin are not just different people with different tasks. It is not excluded that they belong to different divisions of the company, and, maybe, even sit on different floors. Ideally, the database administrator maintains its viability and monitors its evolution, and the sysadmin’s area of ​​responsibility maintains the viability of the OS and network.
')
Consequently, the base administrator must have a tool to perform the necessary set of possible jobs on the server. It is not for nothing that the Oracle Scheduler materials say that “Oracle Scheduler eliminates the need to use OS-specific task schedulers (cron, at) when building a database-centric application.” That is, the admin database can do everything, especially since it is difficult to imagine an Oracle administrator who is not oriented in the OS mechanisms. He does not have to run to the sysadmin or write letters to him every time when routine operations by means of the OS are required.
Here are the scheduler requirements that are typical for commercial databases, such as Oracle, DB2, MS SQL:
Planner should be able to
- run the work on schedule
- supervise the execution of work, be able to shoot tasks, if necessary,
- run tasks in a limited period of time (in the window),
- build a sequence of tasks (the next one starts to run after the previous one is completed),
- be able to perform multiple queries in a single transaction,
- a task defined in one DB, run on several,
- use the (basic) features of the OS,
- notify the administrator if any tasks from the schedule have not been completed,
- perform one-time tasks.
The last point seems to be not obvious: there are quite a few other, regular means besides the scheduler, capable of running one-time tasks. But this is not a completely normal execution mode. For example, the detached job mode: we are talking about a task, which seems to be disconnected (temporarily or permanently) from the process that caused it. Having done the work, the disconnected process can again contact the process that started it (by sending a signal of successful or unsuccessful completion) to it, report the result to it or write the result (to a file or database table). Some DBMS schedulers are able to stop and start the DBMS itself (we did not set such a task).
PostgreSQL and its agent
You can solve the tasks in different ways: “outside” and “inside” of the DBMS itself. The most serious attempt to make a full-featured scheduler is pgAgent, distributed with pgAdmin III / IV. In the commercial version — in the EnterpriseDB distribution — it is integrated into the pgAdmin graphical user interface and can be used cross-platform.
pgAgent can:
- run tasks
- run a sequence of tasks consisting of SQL scripts (including on different databases) and / or shell / batch scripts,
- set non-working windows (for example, DO NOT perform some action on weekends).
This scheduler works as a PostgreSQL extension, but does not execute tasks “inside” the DBMS, but creating its own, “external” daemons.
There are drawbacks to this approach. Among them are important:
All tasks started by pgAgent will be executed with the rights of the user who started the agent. SQL queries will be executed with the rights of the user connected to the database. The shell scripts will be executed with the rights of the user on whose behalf the daemon (or the Windows service) pgAgent is running. Therefore, for security, you will have to control users who can create and run tasks. In addition, the password cannot be included in the connection string (connection string), since in Unix it will be visible in the output of the ps command and in the database startup script, and in Windows it will be stored in the registry as unencrypted text.
(from pgAdmin 4 1.6 documentation ).
In this solution, pgAgent polls the base server at specified intervals (since the information about the jobs is stored in the base tables): whether there are any jobs available. Therefore, if for some reason the agent does not work at the moment when the work should start, it will not start until the agent starts working.
In addition, any connection to the server consumes a pool of possible connections, the maximum number of which is determined by the configuration parameter max_connections. If an agent spawns many processes, and the administrator has not shown proper vigilance, this can be a problem.
Creating a fully integrated scheduler in the DBMS (“inside” the DBMS) eliminates these problems. And it is especially convenient for those users who are accustomed to minimalist interfaces for accessing the database, such as psql.
pgpro_scheduler and its schedule
At the end of 2016, Postgres Professional started to create their own scheduler, which is fully integrated into the database. Now it is used by customers and is documented in detail. The scheduler was created as an extension (optional module), called pgpro_scheduler, and comes as part of the commercial version of Postgres Pro Enterprise, starting with the first version. Developer - Vladimir Ershov.
When installing it in the DBMS configuration files, you need to remember to include it in the configuration file
shared_preload_libraries = 'pgpro_scheduler' . Having installed the extension ( CREATE EXTENSION pgpro_scheduler;) , you must enable it in a line in the configuration file (schedule.enabled = on) and let it list which databases fall under the action of the scheduler (for example schedule.database = 'database1,database2' ).From the very beginning, it was decided to create pgpro_scheduler in a modern style, organic for the company - with a configuration record in JSON. This is convenient, for example, for web services creators who can integrate the scheduler into their applications. But for those who do not want to use JSON, there are functions that take parameters as ordinary variables. The scheduler comes with the distribution of the DBMS and it is cross-platform.
pgpro_scheduler does not start external daemons or services, but creates child processes in relation to the postmaster background background processes. The number of “workers” is set in the pgpro_scheduler configuration, but is limited to the overall server configuration. Actually, the most usual SQL commands come to the scheduler input, without any restrictions, so you can run functions in any available Postgres language. If several SQL queries are included in the JSON structure, they can (if you follow a certain syntax) be executed inside a single transaction:
SELECT schedule.create_job( '{"commands": [ "SELECT 1", "SELECT 2", "SELECT 3"], "cron": "23 23 */2 * *" }' );this is equivalent to:
SELECT schedule.create_job( '{"commands": [ "SELECT 1", "SELECT 2", "SELECT 3"], "cron": "23 23 */2 * *","use_same_transaction": true}' );and if each request is in its own transaction, then:
SELECT schedule.create_job( '{"commands": [ "SELECT 1", "SELECT 2", "SELECT 3" ], "cron": "23 23 */2 * *" }' ); - that is, without the last parameter, by default.For example, in the second command in the list, an error will occur (of course, in
SELECT 2 it is unlikely to occur, but let's imagine some dumb query). In the case of execution in one transaction, all results will be rolled back, but in the scheduler's log a message will appear about the collapse of the second command. The same message will appear in the case of execution of separate transactions, but the result of the first will be saved (the third transaction will not be executed).When pgpro_scheduler is started, a group of background workers with its own hierarchy always starts working: one worker, in the rank of supervisor scheduler, controls workers in the rank of database managers - one for each database specified in the configuration line. Managers, in turn, control workers directly serving the tasks. The supervisor and managers are fairly easy processes, so if the scheduler serves even dozens of bases, this does not affect the overall system load. And then the workers are started in each database according to the needs in processing requests. In sum, they must fit within the limitation of the max_worker_processes DBMS. A group of teams for the instant execution of tasks uses resources differently, but more on that later.
Fig.1 Basic mode of operation pgpro_scheduler
pgpro_scheduler is a Postgres extension. Therefore, it is installed on a specific database. This creates several system tables in the schedule scheme; by default, they are not visible to the user. The database now knows 2 new, special data types: cron_rec and cron_job, with which it will be possible to work through SQL queries. There is a table-log, which does not duplicate the DBMS log. Information about the success or failure of the task scheduler is available only through the functions of the extension pgpro_scheduler. This is done so that one user of the scheduler does not know about the activities of another user of the scheduler. Functions enable selective viewing of the log, starting from a specific date, for example:SELECT * from schedule.get_user_log() WHERE started > now() - INTERVAL '1 day' ;You can create a task using JSON using the function
schedule.create_job(data jsonb) . The only argument to this function is a JSONB object with the job information. Examples will come next.This object may contain the following keys, some of which may be omitted:
- name - the name of the task;
- node is the name of the node (in case of work in the multimaster architecture);
- command - a set of SQL queries to be executed, specified as an array;
- run_as is the user on whose behalf the commands will be executed;
You can choose from the types of representation of the schedule: for those who are accustomed to cron - a line in the style of crontab, which defines the schedule of execution. But you can use the rule - then the schedule will be represented as a JSONB object (see description below). Another option: date - a set of specific dates on which the execution of commands is scheduled. They can be combined, but at least one option must be used. It will look, for example, like this:
"cron":"55 7 * * *" - from the example that can be seen below.In addition, there are still many useful parameters, which can be found in the documentation . Among them:
- start_date and end_date - the beginning and end of the interval in which the execution of the scheduled command is possible (maybe NULL);
- max_instances - the maximum number of job instances that can be run simultaneously. 1 by default;
- max_run_time - determines the maximum duration of the task. It is specified in the interval type format. If this field is NULL or not specified, the time is not limited. The default is NULL;
- onrollback is a SQL query that will be executed when the main transaction fails. By default, the query is not defined;
- next_time_statement - SQL query that will be executed to calculate the next task start time. It must return a value in the format timestamp with time zone;
The schedule can be specified as a crontab-style string (cron key) or as a JSONB object (rule key). They may contain the following keys:
- minutes - minutes; Array of integers in the range 0 ... 59;
- hours - hours; Array of integers in the range 0 ... 23;
- days - days of the month; array of integers in the range of 1 ... 31;
- months - months; array of integers in the range 1 ... 12;
- wdays - days of the week; Array of integers in the range 0 ... 6 (0 - Sunday);
- onstart is an integer value of 0 or 1; if this value is 1, the task will be executed only once when the scheduler is started.
An assignment can also be scheduled for a specific date or set of dates. That is, in principle, the task can be one-time, although for one-time tasks you can use the special one-time job mode with other function calls.
Scheme 1. Scheduler Hierarchy Process
The
next_time_statement field may contain a SQL query that will be executed after the main transaction to calculate the next run time. If this key is defined, the time of the first start of the task will be calculated according to the methods described above, but the next start will be scheduled at the time that this request returns. This request should return an entry containing in the first field a value of type timestamp with time zone. If the return value is of a different type or an error occurs during the execution of the query, the task is marked as failed and its further execution is canceled.This query will be executed under any state of completion of the main transaction. You can get the transaction completion status in it from the Postgres Pro Enterprise
schedule.transaction_state:- success - transaction completed successfully
- failure - the transaction failed
As can be seen even from the abbreviated description, the range of possibilities is rich. In total, there are about 40 functions that work with the pgpro_scheduler application. You can create tasks, cancel them, view their status, filter information about tasks by users and other criteria.
Once, but out of turn
As mentioned, there is still an important class of tasks for the scheduler: the formation of separate, non-periodic tasks using the one-time job mechanism. If the run_after parameter is not specified, then in this mode the scheduler is able to start executing the task immediately at the moment of arrival - up to the polling time interval of the table to which the task is written. In the current implementation, the interval is fixed and equal to 1 second. Background workers start in advance and wait for jobs to appear "under steam", rather than starting as needed, as in schedule mode. Their number is defined by the schedule.max_parallel_workers parameter. The corresponding number of requests can be processed in parallel.
Fig.2 One-time job mode.
The main function that forms the task is as follows:
schedule.submit_job(query text [options...])This function has, in accordance with its specificity, which was at the beginning, fine tuning. The max_duration parameter sets the maximum execution time. If the work is not done in the allotted time, the task is removed (by default, the execution time is unlimited). max_wait_interval does not refer to the running time, but to the waiting time to start working. If the DBMS does not find “workers” within this period of time ready to take on execution, the task is removed. An interesting parameter depends_on specifies an array of work (in one-time mode), after which you have to start this work.
A useful parameter -
resubmit_limit - sets the maximum number of restart attempts. Let's say a task runs a procedure that starts sending a message to the mail. The mail server, however, is not in a hurry to receive it, and after a timeout or even because of a lack of communication, the process ends in order to resume again immediately or after a specified time. Without restrictions in resubmit_limit attempts will continue until the bitter end.Condiments and desserts
At the beginning, detached jobs were mentioned. In the current version, the process that started the one-time task is dragging its existence in anticipation of the result. The overhead of the background worker’s work is small, it makes no sense to stop it. It is important that the execution or non-performance of the task will not pass without a trace, we can learn about its fate from a request to the scheduler log, available to us, and not just to the database administrator. This is not the only way to track down a transaction even if it is rolled back: Postgres Pro Enterprise has an autonomous transaction mechanism that can be used for the same purpose. But in this case, the result will be recorded in the DBMS log, and not in the “personal” log of the user running the scheduler.
If the scheduler user needs to schedule or simply run some OS commands with the rights that are available to him inside the OS, he can easily do this through the scheduler, using the programming languages ​​available to him. Suppose he decided to use untrusted Perl:
CREATE LANGUAGE plperlu;After that, you can write in the usual request such as, for example, the function:
DO LANGUAGE 'plperlu' $$
system ( 'cat /etc/postgresql/9.6/main/pg_hba.conf > $HOME/conf_tmp' );
$$;Life example: 1. storing irrelevant logs
To begin with, a simplified example of managing sections (partitions) from the scheduler. Suppose we broke the logs of visiting the site into sections by month. We do not want to store on the two-year freshness and younger sections on expensive fast drives, and dump the rest into another tablespace corresponding to other, cheaper media, while maintaining full-featured search and other operations on all the logs (with a table that is not divided into sections). impossible). We use convenient section management functions in the pg_pathman extension. The postgresql.conf file should contain the line shared_preload_libraries = 'pg_pathman, pgpro_scheduler'.
CREATE EXTENSION pg_pathman; CREATE EXTENSION pgpro_scheduler;Configuring:
ALTER SYSTEM SET schedule.enabled = on ;
ALTER SYSTEM SET schedule.database = 'test_db' ;Baz can be several. In this case, they are listed separated by commas inside quotes.
SELECT pg_reload_conf(); - reread configuration changes without restarting Postgres.CREATE TABLE partitioned_log (id int NOT NULL , visit timestamp NOT NULL );We have just created a parent table that we will split into sections. This is a tribute to the traditional PostgreSQL syntax based on table inheritance. Now, in Postgres Pro Enterprise, you can create sections not in 2 stages (first empty parent table, then specify sections), but immediately define sections. In this case, we will use the convenient pg_pathman function, which allows you to first set the approximate number of sections. As you fill the necessary sections will be created automatically:
SELECT create_range_partitions( 'partitioned_log' , 'visit' , '2015-01-01' :: date , '1 month' :: interval , 10);We set 10 initial sections, one per month, starting from 1 January. 2015. Fill them with some amount of data.
INSERT INTO partitioned_log SELECT i, '2015-01-01' :: date + 60*60*i*random():: int * '1 second' :: interval visit FROM generate_series(1,24*365) AS g(i);You can monitor the number of sections as follows:
SELECT count (*) FROM pathman_partition_list WHERE parent='partitioned_log':: regclass ;Starting
INSERT , “spinning up” the initial date and / or multipliers before random, make the number of sections a little more than 24 (2 years).Create a directory in the OS and the corresponding tablespace where the outdated logs will be stored:
CREATE TABLESPACE archive LOCATION '/tmp/archive' ;And finally, the function that the scheduler will run every day:
CREATE OR REPLACE FUNCTION move_oldest_to_archive (parent_name text , suffix text , tblsp_name text , months_hot int ) RETURNS int AS
$$
DECLARE
i int ;
part_rename_sql text ;
part_chtblsp_sql text ;
part_name text ;
BEGIN
i=0;
FOR part_name IN SELECT partition FROM pathman_partition_list WHERE parent=parent_name::regclass and partition:: text NOT LIKE '%' ||suffix ORDER BY range_max OFFSET months_hot LOOP
i:=i+1;
part_rename_sql:=format( 'ALTER TABLE %I RENAME to %I' , part_name, part_name|| '_' ||suffix);
part_chtblsp_sql:=format( 'ALTER TABLE %I SET TABLESPACE %I' , part_name, tblsp_name);
EXECUTE part_chtblsp_sql;
EXECUTE part_rename_sql;
RAISE NOTICE 'executed %, %' ,part_rename_sql,part_chtblsp_sql;
END LOOP ;
RETURN i;
END ;
$$ LANGUAGE plpgsql;It takes as parameters: the name of the partitioned table
(partitioned_log) , the suffix added to the name of the moved partition (archived) , the table space (archive) and the number of months - the boundary of the 1st freshness logs (24).To warm up, we will set a one-time task:
SELECT schedule.submit_job(query := $$ select move_oldest_to_archive( 'partitioned_log' , 'archived' , 'archive' , 24);$$);Once executed, the scheduler will display the task id. Its status can be viewed in the views
schedule.job_status and schedule.all_job_status . The task scheduler log assigned by the submit_job() function is not submit_job() .To make it easier to play with the scheduler and sections, you can create a function
unarchive(parent_name text , suffix text ) that rolls back changes (we do not give to save space).It can also be run from the scheduler, but using the run_after parameter, which sets the delay time in seconds - so that we have time to think about whether we did the right thing:
SELECT schedule.submit_job(query := $$ 'select unarchive('partitioned_log','archived');',run_after='10' $$);and if wrong, you can cancel it with the function
schedule.cancel_job(id) ;Making sure that everything works as planned, you can put the task (now in JSON syntax) already in the schedule:
SELECT schedule.create_job($$ {"commands":"SELECT move_oldest_to_archive('partitioned_log','archived', 'archive', 24);","cron":"55 7 * * *"} $$);That is, every morning at five minutes to eight the planner will check if it is time to move the outdated partitions to the “cold” archive and relocate if it is time. The status at this time can be checked by the scheduler log:
schedule.get_log() ;Example from life: 2. we spread banners on servers
Let us show how one of the typical tasks is solved, in which the execution of works on a schedule is required and one-time tasks are used.
We have a content delivery network (CDN). We are going to put banners on several websites that are part of it, which the users from advertising agencies have automatically uploaded to the directory assigned to them.
DROP SCHEMA IF EXISTS banners CASCADE ;
CREATE SCHEMA banners;
SET search_path TO 'banners' ;
CREATE TYPE banner_status_t AS enum ( 'submitted' , 'distributing' , 'ready' , 'error' );
CREATE TYPE cdn_dist_status_t AS enum ( 'submitted' , 'processing' , 'ready' , 'error' );
CREATE TABLE banners (
id SERIAL PRIMARY KEY ,
title text ,
file text ,
status banner_status_t DEFAULT 'submitted'
);
CREATE TABLE cdn_servers (
id SERIAL PRIMARY KEY ,
title text ,
address text ,
active boolean
);
CREATE TABLE banner_on_cdn (
banner_id int ,
server_id int ,
created timestamp with time zone DEFAULT now(),
started timestamp with time zone ,
finished timestamp with time zone ,
url text ,
error text ,
status cdn_dist_status_t DEFAULT 'submitted'
);
CREATE INDEX banner_on_cdn_banner_server_idx ON banner_on_cdn (banner_id, server_id);
CREATE INDEX banner_on_cdn_url_idx ON banner_on_cdn (url);Let's create a function that initializes the loading of the banner on the server. For each server, it creates a download task, as well as a task that waits for all created downloads and sets the correct status for the banner when the downloads are completed:
CREATE FUNCTION start_banner_upload (bid int ) RETURNS bigint AS
$BODY$
DECLARE
job_id bigint ;
r record ;
dep bigint [];
sql text ;
len int ;
BEGIN
UPDATE banners SET status = 'distributing' WHERE id = bid;
dep := '{}' :: bigint [];
FOR r IN SELECT * FROM cdn_servers WHERE active is TRUE LOOP
--
INSERT INTO banner_on_cdn (banner_id, server_id) VALUES (bid, r.id);
sql := format( 'select banners.send_banner_to_server(%s, %s)' , bid, r.id);
job_id := schedule.submit_job(
sql ,
name := format( 'send banner id = %s to server %s' , bid, r.title)
);
--
dep := array_append(dep, job_id);
END LOOP ;
len := array_length(dep, 1);
IF len = 0 THEN
UPDATE banners SET status = error WHERE id = bid;
RETURN NULL ;
END IF ;
-- , ,
-- dep
job_id = schedule.submit_job(
format( 'SELECT banners.finalize_banner(%s)' , bid),
depends_on := dep,
name := format( 'finalization of banner %s' , bid)
);
RETURN job_id;
END
$BODY$
LANGUAGE plpgsql SET search_path FROM CURRENT ;And this function simulates sending a banner to the server (in fact, it’s just sleeping for a while):
CREATE FUNCTION send_banner_to_server (bid int , sid int )
RETURNS boolean AS
$BODY$
DECLARE
banner record ;
server record ;
BEGIN
SELECT * from banners WHERE id = bid LIMIT 1 INTO banner;
SELECT * from cdn_servers WHERE id = sid LIMIT 1 INTO server;
UPDATE banner_on_cdn SET
status = 'processing' ,
started = now()
WHERE
banner_id = bid AND server_id = sid;
PERFORM pg_sleep((random()*10):: int );
UPDATE banner_on_cdn SET
url = 'http://' || server.address || '/' || banner.file,
status = 'ready' ,
finished = now()
WHERE
banner_id = bid AND server_id = sid;
RETURN TRUE ;
END ;
$BODY$
LANGUAGE plpgsql set search_path FROM CURRENT ;This function, based on the status of uploading a banner to the server, will determine which status to put the banner:
CREATE FUNCTION finalize_banner (bid int )
RETURNS boolean AS
$BODY$
DECLARE
N int ;
BEGIN
SELECT count (*) FROM banner_on_cdn WHERE banner_id = bid AND status IN ( 'submitted' , 'processing' ) INTO N;
IF N > 0 THEN --
RETURN FALSE ;
END IF ;
SELECT count (*) FROM banner_on_cdn WHERE banner_id = bid AND status IN ( 'error' ) INTO N;
IF N > 0 THEN --
UPDATE banners SET status = 'error' WHERE id = bid;
RETURN FALSE ;
END IF ;
--
UPDATE banners SET status = 'ready' WHERE id = bid;
RETURN TRUE ;
END ;
$BODY$
LANGUAGE plpgsql set search_path FROM CURRENT ;This function will check on schedule whether there are raw banners. And, if necessary, run the banner processing:
CREATE FUNCTION check_banners () RETURNS int AS
$BODY$
DECLARE
r record ;
N int ;
BEGIN
N := 0;
FOR r IN SELECT * from banners WHERE status = 'submitted' FOR UPDATE LOOP
PERFORM start_banner_upload(r.id);
N := N + 1;
END LOOP ;
RETURN N;
END ;
$BODY$
LANGUAGE plpgsql SET search_path FROM CURRENT ;Now let's take a look at the data. Create a list of servers:
INSERT INTO cdn_servers (title, address, active)
VALUES ( 'server #1' , 'cdn1.local' , true );
INSERT INTO cdn_servers (title, address, active)
VALUES ( 'server #2' , 'cdn2.local' , true );
INSERT INTO cdn_servers (title, address, active)
VALUES ( 'server #3' , 'cdn3.local' , true );
INSERT INTO cdn_servers (title, address, active)
VALUES ( 'server #4' , 'cdn4.local' , true );Create a couple of banners:
INSERT INTO banners (title, file) VALUES ( 'banner #1' , 'bbb1.jpg' );
INSERT INTO banners (title, file) VALUES ( 'banner #2' , 'bbb2.jpg' );And, finally, we will schedule the task of checking newly submitted banners, which should be decomposed into servers. The task will be executed every minute:
SELECT schedule.create_job( '* * * * *' , 'select banners.check_banners()' );
RESET search_path;That's all, pictures will be laid out on sites, you can relax.
Afterword
As Post Scriptum, we inform you that the pgpro_scheduler scheduler works not only on a separate server, but also in a multimaster cluster configuration. But this is a topic for another conversation.
And as Post Post Scriptum - in the future plans of embedding the scheduler in the graphical administration shell that is being created now.
Source: https://habr.com/ru/post/335798/
All Articles