PostgreSQL pg_cron extension

PostgreSQL pg_cron: 1/2 Cronとは

Cron

cronとは、多くのUNIX系OSで標準的に利用される常駐プログラム（デーモン）の一種で、
利用者の設定したスケジュールに従って指定されたプログラムを定期的に起動してくれるもの。

---

Cron schedule expressions

 ┌───────────── min (0 - 59)
 │ ┌────────────── hour (0 - 23)
 │ │ ┌─────────────── day of month (1 - 31)
 │ │ │ ┌──────────────── month (1 - 12)
 │ │ │ │ ┌───────────────── day of week (0 - 6) (0 to 6 are Sunday to
 │ │ │ │ │                  Saturday, or use names; 7 is also Sunday)
 │ │ │ │ │
 │ │ │ │ │
 * * * * *

---

PostgreSQL pg_cron: 2/2: PostgreSQL pg_cron エクステンション

PostgreSQL pg_cron エクステンションを使用すると、PostgreSQL データベース内でメンテナンスコマンドのスケジュールを組むことができます。

---

インストール方法

• Step1: OSにエクステンションインストール

# Install on Red Hat, CentOS, Fedora, Amazon Linux
sudo yum install -y pg_cron_12

# Install on Debian, Ubuntu
sudo apt-get -y install postgresql-12-cron

---

• Step2: PostgreSQLのshared_preload_librariesに追加します

# add to postgresql.conf

# required to load pg_cron background worker on start-up
shared_preload_libraries = 'pg_cron'

---

• Option: データベース変更したい場合、データベース設定します。　設定しないとデフォルトpostgres

# add to postgresql.conf

# optionally, specify the database in which the pg_cron background worker should run (defaults to postgres) 
cron.database_name = 'my_db'

---

• Step3: PostgreSQL再起動して、エクステンションを追加します。

-- run as superuser:
CREATE EXTENSION pg_cron;

---

Option: AWS RDS PostgreSQL にエクステンション追加

---

よく使う目的

• 定期的にデータ更新・削除の処理

SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);

• 定期的にVacuum実行

SELECT cron.schedule('30 3 * * 6', 'VACUUM');

---

Cronのコマンド実行の確認

• 設定したCron一覧

select * from cron.job

• 実行詳細

select * from cron.job_run_details;

• 失敗したもの

select * from cron.job_run_details where status = 'failed';

• Cronを削除

SELECT cron.unschedule(42);

---

デメリット

データベース内のデータ・ロジックしかできません

• 例1: 古い画像のレコード削除処理はpg_cron使えますが、画像自体の削除は別の処理を使わないといけません。
• 例2: コマンド実行したら、他のサビースに通知などはできません
• 解決方法
  • 他のサビース連携の時に: pgsql-http 使えます。
  • pg_cron使わず、バッチ仕組みを使います。

---

以上です。ありがとうございました。