PostgreSQLで時間指定タスクを実装する4つの方法

PostgreSQLはOracle、MySQL、Microsoft SQL Severのようなタスクスケジューリングを内蔵していませんので、この記事ではPostgreSQLデータベースに時間指定タスクを実装する4つの方法を紹介します。そこで、本記事ではPostgreSQLのデータベースで時間指定タスクを実装する4つの方法を紹介します。

オペレーティングシステムのタイムドタスク

Linuxの時間指定タスク（crontab）またはWindowsのタスクスケジューラは、時間指定タスクを実装する伝統的な方法を提供してくれます。crontabを例にとると、以下のコマンドでタスクのリストを編集することができます。

crontab -e

次に、開いているファイルに、次のような形式でデータの行を追加します。

#Minutes Hours Day of the month Month Day of the week Command
#(0-59) (0-23) (1-31) (1-12) (0-7 [7 or 0 == Sunday])
<minute> <hour> <day of month> <month> <day of week> <command>

これらのフィールドのうち最初の5つは、コマンドが実行された時刻を示し、アスタリスク（*）を使用してすべての時刻を一致させることができます。たとえば、次のように設定します。 <月 をアスタリスクにすると、そのコマンドは毎月実行されることになります。

例えば、データベースの論理バックアップ操作を毎日0時に実行することを示すには、次のように入力します。

0 0 * * * * pg_dump --no-password -U user db_name > backup.sql

セキュリティ上の理由から、パスワードを直接入力する代わりに、パスワードを.pgpassファイルに追加し、そのファイルのパーミッションを現在のユーザにのみ見えるように設定する必要があります。

chmod 600 .pgpass

pgAgent

pgAgentはPostgreSQLデータベース用のタスクスケジューリングエージェントで、複雑なスケジューリングプランに基づいて、多段階のバッチプロセス、シェルスクリプト、SQLコマンドを実行することが可能です。Unix/Linuxシステムでは、pgAgentはバックグラウンドプロセスとして、Windowsシステムでは、pgAgentはサービスとして実行されます。

pgAgentのインストール

PgAdmin 4管理ツールはpgAgentの機能と統合されていますが、両方とも別々にインストールする必要があります。公式サイトの ダウンロード インストール手順と注意事項は 公式サイトドキュメント . インストールが完了すると、PgAdmin 4の左側のナビゲーション・ツリーに "pgAgent Jobs" ノードが表示されるようになります。

時間指定タスクの作成

pgAgent Jobs"ノードを右クリックして、"Create" > "pgAgent Job"を選択して新しい定時制ジョブを作成します。

General"ページでは、タスクの名前など基本的な情報を入力することができます。Quot;Steps"ページでは、実行するスクリプトやSQL文など、複数のステップを設定することができます。タスク実行のタイムスケジュールを定義するには、"Schedules" 画面を使用します。SQL"画面では、タスクの作成または変更に使用されるステートメントが表示されます。

Save"ボタンをクリックして設定を保存し、ジョブを作成すると、"pgAgent Job"ノードの下に作成したジョブが表示されます。

pg_cron

pg_cron pg_cronはバックグラウンドワーカープロセスとして動作し、cronライクな編集構文を使用し、データベース内で直接定時タスクを実行することができます。例えば、以下のようになります。

-- every Saturday at 3:30am (GMT) Delete history
SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);
 schedule
----------
 42

-- Execute cleanup jobs every day at 10:00am (GMT)
SELECT cron.schedule('nightly-vacuum', '0 10 * * *', 'VACUUM');
 schedule
----------
 43

-- Modify the cleanup job to 3:00am (GMT)
SELECT cron.schedule('nightly-vacuum', '0 3 * * *', 'VACUUM');
 schedule
----------
 43

-- Stop the scheduled task
SELECT cron.unschedule('nightly-vacuum' );
 unschedule 
------------
 t
(1 row)

SELECT cron.unschedule(42);
 unschedule
------------
  t

pg_cronのインストール

pg_cronは現在Linuxオペレーティングシステムのみをサポートしています。Red Hat、CentOS、Fedoraの場合、以下のコマンドで(PostgreSQL 12)をインストールできます。

sudo yum install -y pg_cron_12

Debian、Ubuntuの場合、以下のコマンドでインストールできます（PostgreSQL 12）。

sudo apt-get -y install postgresql-12-cron

または、ソースコードを使ってコンパイルし、インストールすることもできます。

git clone https://github.com/citusdata/pg_cron.git
cd pg_cron
# Ensure pg_config is in your path, e.g.
export PATH=/usr/pgsql-12/bin:$PATH
make && sudo PATH=$PATH make install

pg_cronの設定

PostgreSQLの起動時にpg_cronバックグラウンドワーカープロセスを実行するために、postgresql.confファイルのshared_preload_libraries設定エントリにpg_cronを追加する必要があります。デフォルトでは、pg_cronバックグラウンドプロセスは、必要なメタデータを取得するためにpostgresデータベースを使用します。しかし、cron.database_name構成パラメータを使用して、これを設定することもできます。

shared_preload_libraries = 'pg_cron'
cron.database_name = 'postgres'

PostgreSQLを再起動します。

sudo service postgresql-12 restart

次に、以下のコマンドでpg_cron関数とメタデータ関連テーブルを作成します。

-- Use superuser to run the following command
CREATE EXTENSION pg_cron;

-- optional action to grant access to other users
GRANT USAGE ON SCHEMA cron TO username;

pg_timetable

pg_timetable は、CYBERTECが開発したPostgreSQLジョブスケジューラで、柔軟な設定方法と多くの高度な機能を提供します。これには、複数のタスクからなるタスクチェーン、SQLコマンドと実行ファイルのサポート、組み込みタスク（電子メールの送信など）、完全にデータベースベースの設定とログ機能、cronスタイルのスケジュール管理、同時実行からの保護、などが含まれます。

pg_timetableのインストール

まず、公式リリースバイナリを使ってインストールします。 pg_timetable をインストールするためのプログラム 現在、Windows、Linux、macOSの各OSをサポートしています。

または、公式のDockerイメージは ダウンロードはこちら .

masterブランチの最新タグが最新版で、コマンドラインを使って以下のように実行します。

docker run --rm \
 cybertecpostgresql/pg_timetable:latest \
 -h 10.0.0.3 -p 54321 -c worker001

以下のように環境変数を指定します。

docker run --rm \
 -e PGTT_PGHOST=10.0.0.3 \
 -e PGTT_PGPORT=54321 \
 cybertecpostgresql/pg_timetable:latest \
 -c worker001

また、ソースコードを使って、コンパイルとインストールを行うこともできます。まず、Go言語環境をダウンロードしてインストールし、go getコマンドを使用してpg_timetableのソースコードをコピーしてください。

$ env GIT_TERMINAL_PROMPT=1 go get github.com/cybertec-postgresql/pg_timetableUsername for 'https://github.com': <Github Username>
Password for 'https://[email protected]': <Github Password>

pg_timetableを実行します。

$ cd ~/go/src/github.com/cybertec-postgresql/pg_timetable$ go run main.go --dbname=dbname --clientname=worker001 --user=scheduler --password=strongpwd

また、以下のようにバイナリにコンパイルして実行することも可能です。

$ go build
$ . /pg_timetable --dbname=dbname --clientname=worker001 --user=scheduler --password=strongpwd

プロジェクト内のすべてのテストを実行したい場合は、以下のコマンドを実行します。

$ cd ~/go/src/github.com/cybertec-postgresql/pg_timetable$ go get github.com/stretchr/testify$ go test . /...

また、postgresのdockerイメージを使用してテストを実行することができます。/...

$ RUN_DOCKER=true go test . /...

pg_timetableを使用する

pg_timetableはPostgreSQLサーバから独立して動作し、クライアントプロセスと同等です。インストールが完了したら、以下のコマンドを実行してpg_timetableプログラムを実行してください。

# . /pg_timetable

Application Options:
 -c, --clientname= Unique name for application instance
 -v, --verbose Show verbose debug information [$PGTT_VERBOSE]
 -h, --host= PG config DB host (default: localhost) [$PGTT_PGHOST]
 -p, --port= PG config DB port (default: 5432) [$PGTT_PGPORT]
 -d, --dbname= PG config DB dbname (default: timetable) [$PGTT_PGDATABASE]
 -u, --user= PG config DB user (default: scheduler) [$PGTT_PGUSER]
 -f, --file= SQL script file to execute during startup
 --password= PG config DB password (default: somestrong) [$PGTT_PGPASSWORD]
 --sslmode=[disable|require] What SSL priority use for connection (default: disable)
 --pgurl= PG config DB url [$PGTT_URL]
 --init Initialize database schema and exit. can be used with --upgrade
 --upgrade Upgrade database to the latest version
 --no-program-tasks Disable executing of PROGRAM tasks [$PGTT_NOPROGRAMTASKS]

のソースコード サンプル ディレクトリには、参考となる例が多数用意されています。次のコマンドは、8月の00:05に"MyJob"を実行するタイムドタスクを作成します。

SELECT timetable.job_add('MyJob', 'SELECT public.my_func()' , NULL, 'SQL', '5 0 * 8 *', live := TRUE);

次のコマンドは、0:00から20:00まで2時間おきに23分間で"MyJob"タスクを実行します。

SELECT timetable.job_add('MyJob', 'SELECT public.my_func()' , NULL, 'SQL', '23 0-20/2 * * * *', live := TRUE);

pg_timetableスケジュールタスクの完全な構成は、3つのフェーズで構成されています。

• 最初のステージでは、base_task を設定し、実行する操作を定義します。これには、SQL文、外部プロシージャ、組み込み操作が含まれます。
• 第2段階はtask_chainの設定に使用され、順次実行される基本タスクの集合を定義する。
• 第3段階は、chain_execution_configの設定に使用します。chain_execution_configは、タスク・チェインの実行計画を定義しています。

また、基本タスクに制御パラメータを渡すために、タスクチェイン内の各タスクに実行パラメータを付随させることができる。詳細な設定方法や設定例については、公式ホームページを参照することができます。

概要

この記事では、PostgreSQLデータベースに定時タスクを実装する4つの方法、OS定時タスク、pgAgentエージェント、pg_cronプラグイン、pg_timetableツールについて説明します。

PostgreSQLで時間指定タスクを実装する4つの方法については、この記事ですべて説明します。PostgreSQLのタイムドタスクについては、過去の記事を検索するか、引き続き以下の関連記事をご覧ください。