PostgreSQL pg_cron エクステンションによるメンテナンスのスケジューリング

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

pg_cron エクステンションは、Aurora PostgreSQL エンジンのバージョン 12.6 以降のバージョンでサポートされています。

pg_cron の使用の詳細については、「RDS for PostgreSQL または Aurora PostgreSQL 互換エディションのデータベースで pg_cron を使用してジョブをスケジュールする」を参照してください

pg_cron 拡張機能のセットアップ

次のように pg_cron 拡張機能をセットアップします。

1. shared_preload_libraries パラメータ値に pg_cron を追加して、PostgreSQL DB インスタンスに関連付けられているカスタムパラメータグループを変更します。

   静的パラメータグループの変更を反映するために PostgreSQL DB インスタンスを再起動します。パラメータグループを使用する方法の詳細については、 Amazon Aurora PostgreSQL のパラメータ を参照してください。

2. PostgreSQL DB インスタンスが再起動したら、rds_superuser の許可を持つアカウントを使用して以下のコマンドを実行します。例えば、Aurora PostgreSQL DB クラスターの作成時にデフォルト設定を使用した場合は、ユーザー postgres として接続し拡張機能を作成します。

   CREATE EXTENSION pg_cron;

   pg_cron スケジューラは、postgres という名前のデフォルトの PostgreSQL データベースに設定されます。pg_cron オブジェクトはこの postgres データベースに作成され、すべてのスケジューリングアクションがこのデータベースで実行されます。

3. デフォルト設定を使用することも、ジョブをスケジュールして、PostgreSQL DB インスタンス内の他のデータベースで実行させることもできます。PostgreSQL DB インスタンス内の他のデータベースでジョブをスケジュールするには、デフォルトのデータベース以外のデータベースでの cron ジョブのスケジューリング の例を参照してください。

データベースユーザーに pg_cron を使用する権限を付与する

pg_cron 拡張機能をインストールするには、rds_superuser 権限が必要です。ただし、pg_cron の使用権限は (rds_superuser グループ/ロールのメンバーによって) 他のデータベースユーザーに付与して、各ユーザーが自分のジョブをスケジュールできるようにすることができます。本番環境での運用が改善される場合にのみ、cron スキーマへのアクセス許可を付与することをお勧めします。

cron スキーマでデータベースユーザー権限を付与するには、以下のコマンドを実行します。

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

これにより、アクセス権限のあるオブジェクトの cron ジョブをスケジュールするための cron スキーマへの db-user アクセス許可が付与されます。データベースユーザーに権限がない場合、以下に示すように、エラーメッセージを postgresql.log ファイルに投稿した後にジョブは失敗します。

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

つまり、cron スキーマへの権限を付与されているデータベースユーザーに、スケジュールを設定する予定のオブジェクト (テーブル、スキーマなど) に対する権限も付与されていることを確認します。

この cron ジョブの詳細と、その成功または失敗も cron.job_run_details テーブルにキャプチャされます。詳細については、「ジョブのスケジュール設定とステータス取得用のテーブル 」を参照してください。

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

次のセクションでは、pg_cron ジョブを使用してさまざまな管理タスクをスケジュールする方法について説明します。

注記

pg_cron ジョブの作成時は、max_worker_processes 設定が cron.max_running_jobs の数より大きいことを確認します。バックグラウンドのワーカープロセスを使い切ると、pg_cron ジョブは失敗します。pg_cron ジョブのデフォルト数は 5 です。詳しくは、「pg_cron 拡張機能の管理用パラメータ」を参照してください。

トピック

• テーブルのバキューム処理
• pg_cron の履歴テーブルの除去
• エラーのログを postgresql.log ファイルにのみ記録する
• デフォルトのデータベース以外のデータベースでの 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 の履歴テーブルの除去

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'$$);

(詳しくは、「ジョブのスケジュール設定とステータス取得用のテーブル 」を参照してください。)

エラーのログを postgresql.log ファイルにのみ記録する

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

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

postgres=> SHOW cron.log_run;

詳細については、「pg_cron 拡張機能の管理用パラメータ」を参照してください。

デフォルトのデータベース以外のデータベースでの 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 拡張機能の管理用パラメータ
• 関数リファレンス: cron.schedule
• 関数リファレンス: cron.unschedule
• ジョブのスケジュール設定とステータス取得用のテーブル

pg_cron 拡張機能の管理用パラメータ

pg_cron エクステンションの動作を制御するパラメータの一覧を次に示します。

Parameter	説明
cron.database_name	pg_cron メタデータが保持されるデータベース。
cron.host	PostgresSQL に接続するためのホスト名。この値は変更できません。
cron.log_run	job_run_details テーブルで実行されるすべてのジョブをログに記録します。有効な値は on または off です。詳細については、「ジョブのスケジュール設定とステータス取得用のテーブル 」を参照してください。
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 インスタンス内の他のデータベースで実行するようにジョブをスケジュールするには、デフォルトのデータベース以外のデータベースでの 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)

ジョブのスケジュール設定とステータス取得用のテーブル

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

表	説明
cron.job	スケジュールされた各ジョブに関するメタデータが含まれます。このテーブルとのほとんどのやり取りは、cron.schedule 関数および cron.unschedule 関数を使用して行う必要があります。 重要 更新または挿入の権限をこのテーブルに直接与えないようにお勧めします。そうすることで、ユーザーは username 列を更新し、rds-superuser として実行できるようになります。
cron.job_run_details	ここには、過去にスケジュールされ実行されたジョブに関する履歴の情報が含まれます。これは、実行されたジョブのステータス、返されたメッセージ、およびスタートと終了の時間を調査する場合に便利です。 注記 このテーブルが無期限に増えないようにするには、定期的に削除してください。例については、「pg_cron の履歴テーブルの除去」を参照してください。