PostgreSQL pg_cron 拡張機能によるメンテナンスのスケジューリング - Amazon Aurora

PostgreSQL pg_cron 拡張機能によるメンテナンスのスケジューリング

PostgreSQL pg_cron 拡張機能を使用すると、PostgreSQL データベース内でメンテナンスコマンドのスケジュールを組むことができます。詳細については、pg_cron ドキュメントの What is pg_cron? を参照してください。

pg_cron 拡張機能は、Aurora PostgreSQL のエンジンバージョン 12.6 以降でサポートされています。

pg_cron 拡張機能の有効化

pg_cron 拡張機能を次のように有効にします。

  1. PostgreSQL DB インスタンスに関連付けられているパラメータグループを変更して、shared_preload_libraries パラメータ値に pg_cron を追加します。この変更を有効にするには、PostgreSQL DB インスタンスを再起動する必要があります。詳細については、「DB パラメータグループのパラメータの変更」を参照してください。

  2. PostgreSQL DB インスタンスが再起動したら、rds_superuser のアクセス許可を持つアカウントを使用して以下のコマンドを実行します。

    CREATE EXTENSION pg_cron;
  3. デフォルト設定を使用するか、PostgreSQL DB インスタンス内の他のデータベースで実行するようにジョブをスケジュールします。pg_cron スケジューラは、postgres という名前のデフォルトの PostgreSQL データベースに設定されます。pg_cron オブジェクトはこの postgres データベースに作成され、すべてのスケジューリングアクションがこのデータベースで実行されます。

    PostgreSQL DB インスタンス内の他のデータベースで実行するようにジョブをスケジュールするには、postgres 以外のデータベースでの cron ジョブのスケジューリング の例を参照してください。

pg_cron へのアクセス許可の付与

rds_superuser ロールとして、pg_cron 拡張機能を作成し、他のユーザーにこの機能へのアクセス許可を付与できます。他のユーザーがジョブをスケジュールできるようにするには、cron スキーマ内のオブジェクトへのアクセス許可を付与します。

重要

cron スキーマへのアクセス許可の付与は慎重に行うことをお勧めします。

他のユーザーに cron スキーマへのアクセス許可を付与するには、以下のコマンドを実行します。

postgres=> GRANT USAGE ON SCHEMA cron TO other-user;

このアクセス許可により、other-user が cron スキーマにアクセスして cron ジョブをスケジュールおよびスケジュール解除できるようになります。ただし、cron ジョブを正常に実行するには、ユーザーが cron ジョブ内のオブジェクトにアクセスするためのアクセス許可も必要です。ユーザーに権限がない場合、ジョブは失敗し、次のようなエラーが postgresql.log に表示されます。この例では、ユーザーに pgbench_accounts テーブルへのアクセス許可が与えられていません。

2020-12-08 16:41:00 UTC::@:[30647]:ERROR: permission denied for table pgbench_accounts 2020-12-08 16:41:00 UTC::@:[30647]:STATEMENT: update pgbench_accounts set abalance = abalance + 1 2020-12-08 16:41:00 UTC::@:[27071]:LOG: background worker "pg_cron" (PID 30647) exited with exit code 1

cron.job_run_details テーブル内の他のメッセージは、次のように表示されます。

postgres=> select jobid, username, status, return_message, start_time from cron.job_run_details where status = 'failed'; jobid | username | status | return_message | start_time -------+------------+--------+-----------------------------------------------------+------------------------------- 143 | unprivuser | failed | ERROR: permission denied for table pgbench_accounts | 2020-12-08 16:41:00.036268+00 143 | unprivuser | failed | ERROR: permission denied for table pgbench_accounts | 2020-12-08 16:40:00.050844+00 143 | unprivuser | failed | ERROR: permission denied for table pgbench_accounts | 2020-12-08 16:42:00.175644+00 143 | unprivuser | failed | ERROR: permission denied for table pgbench_accounts | 2020-12-08 16:43:00.069174+00 143 | unprivuser | failed | ERROR: permission denied for table pgbench_accounts | 2020-12-08 16:44:00.059466+00 (5 rows)

詳細については、「pg_cron テーブル」を参照してください。

pg_cron ジョブのスケジューリング

このセクションでは、管理タスクを実行するための pg_cron ジョブのスケジューリングについて説明します。

注記

pg_cronジョブを作成する際には、max_worker_processes の値は、常に cron.max_running_jobs の値よりも大きくなるように指定します。バックグラウンドのワーカープロセスを使い切ると、pg_cron ジョブは失敗します。pg_cron のジョブのデフォルト数は 5 です。詳細については「pg_cron パラメータ」を参照してください。。

テーブルのバキューム処理

Autovacuum は、ほとんどの場合、バキュームのメンテナンスを実行します。ただし、特定のテーブルのバキューム処理を、選択した特定の時点にスケジュールしたい、というケースも考えられます。

以下は、cron.schedule 関数を使用して、毎日 22:00 (GMT) に特定のテーブルで VACUUM FREEZE を使用するようにジョブをセットアップする例です。

SELECT cron.schedule('manual vacuum', '0 22 * * *', 'VACUUM FREEZE pgbench_accounts'); schedule ---------- 1 (1 row)

上記の例を実行した後、次のように cron.job_run_details テーブル内の履歴を確認できます。

postgres=> select * from cron.job_run_details; jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time -------+-------+---------+----------+----------+----------------------------------------+-----------+----------------+-------------------------------+------------------------------- 1 | 1 | 3395 | postgres | adminuser| vacuum freeze pgbench_accounts | succeeded | VACUUM | 2020-12-04 21:10:00.050386+00 | 2020-12-04 21:10:00.072028+00 (1 row)

以下の例では、ジョブが失敗した理由を調べるために、cron.job_run_details テーブルの履歴を表示しています。

postgres=> select * from cron.job_run_details where status = 'failed'; jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time -------+-------+---------+----------+----------+---------------------------------------+--------+--------------------------------------------------+-------------------------------+------------------------------- 5 | 4 | 30339 | postgres | adminuser| vacuum freeze pgbench_account | failed | ERROR: relation "pgbench_account" does not exist | 2020-12-04 21:48:00.015145+00 | 2020-12-04 21:48:00.029567+00 (1 row)

詳細については、「pg_cron テーブル」を参照してください。

pg_cron の履歴テーブルの除去

cron.job_run_details テーブルには、時間の経過とともに非常に大きくなる可能性がある cron ジョブの履歴が含まれています。そのため、このテーブルをクリアにするジョブをスケジュールすることをお勧めします。例えば、トラブルシューティングの目的では、1 週間分のエントリを保持するだけで十分です。

次の例では、cron.schedule 関数を使用して、cron.job_run_details テーブルをクリアにするよう、毎日午前 0 時に実行されるジョブをスケジュールします。このジョブは過去7日間しか残せません。rds_superuser アカウントを使用して、以下のようなジョブをスケジュールできます。

SELECT cron.schedule('0 0 * * *', $$DELETE FROM cron.job_run_details WHERE end_time < now() – interval '7 days'$$);

詳細については、「pg_cron テーブル」を参照してください。

pg_cron の履歴のログ記録を無効にする

cron.job_run_details テーブルへの書き込みを完全に無効にするには、PostgreSQL DB インスタンスに関連付けられているパラメータグループを変更し、cron.log_run パラメータを off に設定します。これを行うと、pg_cron 拡張機能が対象のテーブルに書き込むことはなくなり、エラーは postgresql.log ファイルのみに記録されるようになります。詳細については、「DB パラメータグループのパラメータの変更」を参照してください。

cron.log_run パラメータの値を確認するには、次のコマンドを使用します。

postgres=> SHOW cron.log_run;

詳細については、「pg_cron パラメータ」を参照してください。

postgres 以外のデータベースでの cron ジョブのスケジューリング

pg_cron のメタデータはすべて、postgres という名前の PostgreSQL のデフォルトのデータベースに保持されます。メンテナンスの cron ジョブの実行にはバックグラウンドワーカーが使用されるため、PostgreSQL DB インスタンス内の任意のデータベースでジョブのスケジューリングが可能です。

  1. cron データベースで、cron.schedule を使用して通常どおりにジョブをスケジュールします。

    postgres=> SELECT cron.schedule('database1 manual vacuum', '29 03 * * *', 'vacuum freeze test_table');
  2. 作成したジョブのデータベース列を、rds_superuser ロールを持つユーザーとして更新し、そのジョブを PostgreSQL DB インスタンス内の別のデータベースで実行できるようにします。

    postgres=> UPDATE cron.job SET database = 'database1' WHERE jobid = 106;
  3. cron.job テーブルのクエリを実行して確認します。

    postgres=> select * from cron.job; jobid | schedule | command | nodename | nodeport | database | username | active | jobname -------+-------------+----------------------------------------+-----------+----------+-----------+-----------+--------+------------------------- 106 | 29 03 * * * | vacuum freeze test_table | localhost | 8192 | database1 | adminuser | t | database1 manual vacuum 1 | 59 23 * * * | vacuum freeze pgbench_accounts | localhost | 8192 | postgres | adminuser | t | manual vacuum (2 rows)
注記

状況によっては、別のデータベースで実行する cron ジョブを追加することがあります。このような場合、ジョブは、正しいデータベース列を更新する前に、デフォルトのデータベース (postgres) で実行しようとする可能性があります。ユーザー名に権限がある場合、デフォルトのデータベースでジョブが正常に実行されます。

pg_cron リファレンス

pg_cron 拡張機能では、次のパラメータ、関数、およびテーブルを使用できます。詳細については、pg_cron ドキュメントの What is pg_cron? を参照してください。

pg_cron パラメータ

pg_cron 拡張機能の動作を制御するパラメータの一覧を次に示します。

Parameter 説明

cron.database_name

pg_cron メタデータが保持されるデータベース。

cron.host

PostgresSQL に接続するためのホスト名。この値は変更できません。

cron.log_run

job_run_details テーブルで実行されたすべてのジョブをログに記録します。有効な値は on または off です。

詳細については、「pg_cron テーブル」を参照してください。

cron.log_statement

実行する前に、すべての cron ステートメントを記録します。有効な値は on または off です。

cron.max_running_jobs

同時に実行できるジョブの最大数。

cron.use_background_workers

クライアントセッションの代わりにバックグラウンドワーカーを使用します。この値は変更できません。

次の SQL コマンドを使用して、これらのパラメータとその値を表示します。

postgres=> SELECT name, setting, short_desc FROM pg_settings WHERE name LIKE 'cron.%' ORDER BY name;

cron.schedule() 関数

この関数は、cron ジョブをスケジュールします。このジョブは、デフォルトの postgres データベースで最初にスケジュールされます。この関数は、ジョブ識別子を表す bigint の値を返します。PostgreSQL DB インスタンス内の他のデータベースで実行するようにジョブをスケジュールするには、postgres 以外のデータベースでの cron ジョブのスケジューリング の例を参照してください。

この関数には、2 つの構文形式があります。

構文
cron.schedule (job_name, schedule, command ); cron.schedule (schedule, command );
パラメータ
Parameter 説明
job_name

cron ジョブの名前。

schedule

cron ジョブのスケジュールを示すテキスト。形式は標準の cron 形式です。

command 実行するコマンドのテキスト。
postgres=> SELECT cron.schedule ('test','0 10 * * *', 'VACUUM pgbench_history'); schedule ---------- 145 (1 row) postgres=> SELECT cron.schedule ('0 15 * * *', 'VACUUM pgbench_accounts'); schedule ---------- 146 (1 row)

cron.unschedule() 関数

この関数は、cron ジョブを削除します。job_name または job_id を渡すことができます。ポリシーにより、ユーザーがジョブのスケジュールを削除する所有者であることが確認されます。この関数は、成功または失敗を示すブール値を返します。

関数の構文形式は以下のとおりです。

構文
cron.unschedule (job_id); cron.unschedule (job_name);
パラメータ
Parameter 説明
job_id

cron ジョブがスケジュールされたときに cron.schedule 関数から返されたジョブ識別子。

job_name

cron.schedule 関数でスケジュールされた cron ジョブの名前。

postgres=> select cron.unschedule(108); unschedule ------------ t (1 row) postgres=> select cron.unschedule('test'); unschedule ------------ t (1 row)

pg_cron テーブル

以下のテーブルは、cron ジョブのスケジューリングのためと、そのジョブがどのように完了したかを記録するために使用されます。

説明
cron.job

スケジュールされた各ジョブに関するメタデータが含まれます。このテーブルとのほとんどのやり取りは、cron.schedule 関数および cron.unschedule 関数を使用して行う必要があります。

注記

更新または挿入の権限をこのテーブルに直接与えることはお勧めしません。そうすることで、ユーザーは username 列を更新し、rds-superuser として実行できるようになります。

cron.job_run_details

ここには、過去にスケジュールされ実行されたジョブに関する履歴の情報が含まれます。これは、実行されたジョブのステータス、返されたメッセージ、および開始と終了の時間を調査する場合に便利です。

注記

このテーブルが無期限に増えないようにするには、定期的に削除してください。例については、「pg_cron の履歴テーブルの除去」を参照してください。