یادداشت کوتاه: عملیات تکرارشونده در Postgres

مدتی بود که به یه چالش برخورده بودیم، می‌خواستیم در پایان هر روز، یک عملیات آپدیت روی دیتابیس انجام بدیم. طبق بررسی‌ای که کردم، در سیستم‌های دیتابیس دیگه مثل MySQL این کار به سادگی امکان‌پذیر هست، چون می‌تونیم triggerهایی بنویسیم که در بازه‌های زمانی ثابتی اجرا بشن، اما متاسفانه در Postgres این طور نیست.

راه حل؟ از cron کمک بگیریم.

یک دیتابیس نمونه

بیاین یه دیتابیس نمونه ایجاد کنیم تا چیزی که تو ذهنمه رو روی اون انجام بدیم. با تغییر کاربر جاری به postgres دسترسی کامل به دیتابیس‌ها پیدا می‌کنم تا بتونم دیتابیس و کاربر جدید بسازم:

sudo -iu postgres

حالا یه کاربر جدید می‌سازم، بعد یه دیتابیس، و دسترسی کامل اون دیتابیس رو به اون کاربر میدم. می‌خوام یکم به چالش بکشیم خودمون رو! اول با اتصال به دیتابیس و فراخوانی شلِ postgres شروع می‌کنیم و بعد کوئری می‌زنیم:

psql

CREATE USER example WITH PASSWORD 'examplePass';

CREATE DATABASE example_db;

GRANT ALL PRIVILEGES ON DATABASE example_db TO example;

حالا بیاین یه جدول ایجاد کنیم. کاری به ارتباطات و کلیدهای خارجی ندارم. فقط شِمای جدول برام مهمه. البته قبلش بیاین به عنوان کاربری که ایجاد کردیم به دیتابیس وصل شیم. دو بار ctrl + d رو بزنین تا به شل خودتون، به عنوان کاربر عادی برگردین، و:

psql -U example example_db

حالا جدول رو بسازین:

CREATE TABLE membership ( 
    user_id BIGINT, 
    channel_id BIGINT, 
    expiration_date TIMESTAMPTZ, 
    active BOOL, 
    PRIMARY KEY (user_id, channel_id) 
);

سناریو

فرض کنین دیتای زیر رو داریم. ما تاریخ انقضای عضویت هر کاربر رو ساعت ۲۳:۵۹ شب آخرین روز اشتراک ذخیره می‌کنیم:

user_id | channel_id |      expiration_date      | active  
---------+------------+---------------------------+-------- 
       1 |          1 | 2021-04-16 23:59:00+04:30 | t 
       2 |          1 | 2021-04-17 23:59:00+04:30 | t

کاری که می‌خوایم بکنیم اینه که، هر بامداد ساعت ۰۰:۰۵ چک کنیم و افرادی که عضویتشون منقضی شده رو از حالت active در بیاریم.

راه حل

اول بیاین ببینیم کوئری که باید برای انجام این تغییر بزنیم چیه. من کوئری رو بر اساس تاریخ جاری می‌زنم، یعنی کاربرانی که تاریخ عضویتشون تا قبل از تاریخ جاری بوده رو حذف می‌کنم.

UPDATE membership        
SET active=false 
WHERE expiration_date < NOW();

چون الان که دستور رو اجرا کردم ۱۷ آوریل هست، ردیف دوم (عضویت کاربر با شناسه ۱) از حالت active خارج شد. حالا که کوئری تست شده، دوباره عضویتش رو فعال می‌کنم تا بتونم بازم تست کنم. پس فرض کنین اتفاقی نیفتاده :)

خب به نظر میاد ما باید این کوئری رو هر بامداد ساعت ۰۰:۰۵ اجرا کنیم، اما چطوری؟ این‌جا ما از ابزار cron استفاده می‌کنیم. cron به صورت یک سرویس اجرا میشه و یک‌سری کارهای تکراری رو به صورت خودکار انجام میده. قبل از اینکه این فعالیت تکراری رو برای انجام ثبت‌کنیم، بیاید ایجادش کنیم. ما هر شب ساعت ۰۰:۰۵ باید چه کاری انجام بدیم؟

اول اسکریپت آپدیت دیتابیس رو توی یک فایل ذخیره می‌کنیم، توی یه مسیر مناسب. مثلا remove-expired-memberships.sql/~ . می‌تونین همین الان هم این اسکریپت رو تست کنین:

psql -f ~/remove-expired-memberships.sql -U example example_db

ممکنه روی سیستم لوکال به خاطر سطوح دسترسی و گروهی که کاربر جاری عضوش هست، از شما رمزی پرسیده نشه، اما در محیط پروداکشن، شما باید رمز وارد کنین. ساعت دوازده شب چطور برم پای سیستم رمز وارد کنم؟ تازه وقتی cron این تسک رو اجرا می‌کنه شما اصلا امکان وارد کردن رمز رو ندارین. Postgres از یه فایل به اسم pgpass. استفاده می‌کنه که توی این فایل ما می‌تونیم این اطلاعات رو وارد کنیم. در صورتی که مثل دستور بالا، رمزی ارائه نشده باشه، رمز از این فایل خونده میشه.

پس فایل زیر رو ایجاد کنین:

touch ~/.pgpass

و داخلش این خط رو وارد کنین:

localhost:5432:example_db:example:examplePass

در نهایت، دسترسی به فایل رو تغییر بدین که هر کسی نتونه بخونه:

chmod 0600 ~/.pgpass

حالا می‌تونیم یک cron job جدید ایجاد کنیم. دستور زیر رو وارد کنین تا شروع به ویرایش فایل cron مربوط به یوزر خودتون کنین. اگر قبلا این کار رو انجام نداده باشین، از شما می‌پرسه که می‌خواین از چه ادیتوری استفاده کنین:

crontab -e

خط زیر رو در انتهای فایل وارد کنین:

05 00 * * * psql -f ~/remove-expired-memberships.sql -U example example_db

می‌بینین که قبل از دستوری که می‌خوایم اجرا کنیم، ۵ تا فیلد وارد شده. این فیلدها از چپ به راست نشون‌دهنده دقیقه، ساعت، روز ماه، ماه، و روز هفته هستن. چون حفظ‌کردنش سخته، می‌تونین از crontab.guru استفاده کنین تا حالت‌های مختلف رو خیلی راحت ایجاد کنین.

کار ما تمومه! چند تا سطر وارد کنین و فردا تست کنین که غیر فعال شده باشن، یا خیلی ساده، ساعت و دقیقه‌ای که تسک اجرا میشه رو به پنج دقیقه آینده تغییر بدین که همین الان از درستیش مطمئن بشین :)