Bài toán quen thuộc: Ai sẽ dọn dẹp database cho mình?
Mình từng gặp tình huống này nhiều lần: database PostgreSQL chạy ngon lành, nhưng sau vài tháng, bảng audit_logs phình lên vài chục GB vì không ai dọn. Hoặc cái MATERIALIZED VIEW tổng hợp doanh thu theo ngày cứ phải refresh thủ công mỗi sáng, vì quên thì báo cáo sai số.
Giải pháp lúc đó là viết một script .sh, bỏ vào crontab của Linux. Thoạt đầu tưởng ổn. Nhưng rồi mớ phức tạp bắt đầu nảy sinh: password database phải lưu trong file .pgpass hoặc biến môi trường, script nằm rải rác trên server, log thì coi ở đâu? Khi move sang server mới, setup lại cron hay quên. Còn nếu có nhiều database trên nhiều server thì thôi, tự lo nhau đi.
PostgreSQL có một thứ mà mình hay quay lại mỗi khi gặp bài toán kiểu này: extension ecosystem đủ rộng để gần như không cần với tay ra ngoài xử lý. Và pg_cron chính là extension giải quyết cái mớ script lộn xộn đó.
Tại sao cron hệ thống + psql lại gây đau đầu
Nhìn bề ngoài thì script cron truyền thống có vẻ đơn giản:
# /etc/cron.d/cleanup-db
0 2 * * * postgres psql -U myuser -d mydb -c "DELETE FROM logs WHERE created_at < NOW() - INTERVAL '30 days';"Nhưng khi đi vào production thực tế, nó đẻ ra hàng loạt vấn đề:
- Credential management: Password phải được lưu đâu đó — file
.pgpass, biến môi trường, hoặc dùng peer authentication. Mỗi cách đều có trade-off về bảo mật hoặc tính linh hoạt. - Khó track lịch sử: Job chạy thành công hay thất bại? Muốn biết phải xem system log, grep từng dòng. Không có cái nhìn tập trung.
- Logic bị phân tán: Database schema nằm trong PostgreSQL, nhưng job schedule lại nằm trên OS. Khi handover cho người khác hoặc khi disaster recovery, dễ bỏ sót.
- Không portable: Dump database và restore sang server khác — các job không theo. Phải setup cron lại từ đầu.
Các lựa chọn thay thế
pgAgent
pgAgent là job scheduler đi kèm với pgAdmin. Nó mạnh hơn cron, có UI quản lý, hỗ trợ multi-step job, retry logic. Nhưng đổi lại phải cài pgAgent daemon riêng trên server, cần pgAdmin để quản lý, setup khá cồng kềnh. Cho một cái job xóa log chạy mỗi đêm — dựng cả một daemon riêng là quá tay.
pg_cron — Extension tích hợp thẳng vào PostgreSQL
pg_cron là extension được phát triển bởi Citus Data (hiện là Microsoft). Nó chạy ngay bên trong PostgreSQL process, sử dụng cú pháp cron quen thuộc, và lưu job schedule trực tiếp trong database. Không cần daemon ngoài, không cần pgAdmin, không cần file script trên OS.
Điểm hay nhất: khi backup database bằng pg_dump, toàn bộ metadata job của pg_cron cũng được lưu theo. Migrate server, restore xong là chạy được ngay — không cần nhớ setup lại cron từ đầu.
Cài đặt và cấu hình pg_cron
Cài đặt trên Ubuntu/Debian
# Thêm repo PostgreSQL nếu chưa có
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
sudo apt update
sudo apt install postgresql-16-cron # Thay 16 bằng version PostgreSQL bạn đang dùngCài trên Amazon RDS / Aurora PostgreSQL
Tin vui là pg_cron có sẵn trên RDS PostgreSQL và Aurora PostgreSQL, chỉ cần enable qua Parameter Group:
# Trong RDS Parameter Group, thêm:
shared_preload_libraries = pg_cron
cron.database_name = postgres # Database chứa metadata của pg_cronCấu hình postgresql.conf
Với self-hosted PostgreSQL, mở file postgresql.conf và thêm:
shared_preload_libraries = 'pg_cron'
cron.database_name = 'postgres'Sau đó restart PostgreSQL:
sudo systemctl restart postgresqlEnable extension trong database
-- Kết nối vào database 'postgres' (hoặc database bạn đã cấu hình trong cron.database_name)
CREATE EXTENSION pg_cron;
-- Grant quyền cho user cần schedule job (không phải superuser)
GRANT USAGE ON SCHEMA cron TO myuser;Sử dụng pg_cron thực tế
Cú pháp cơ bản
SELECT cron.schedule(
'job-name', -- Tên job (unique)
'0 2 * * *', -- Cron expression (phút giờ ngày tháng thứ)
$$SQL query here$$ -- Câu lệnh SQL cần chạy
);Cron expression dùng đúng cú pháp chuẩn Unix cron. Một vài ví dụ hay dùng:
'0 2 * * *'— chạy lúc 2:00 AM mỗi ngày'*/15 * * * *'— chạy mỗi 15 phút'0 0 * * 0'— chạy lúc 0:00 AM mỗi Chủ Nhật'0 9 1 * *'— chạy lúc 9:00 AM ngày 1 hàng tháng
Ví dụ 1: Xóa log cũ hơn 30 ngày
SELECT cron.schedule(
'delete-old-logs',
'0 3 * * *',
$$DELETE FROM audit_logs WHERE created_at < NOW() - INTERVAL '30 days'$$
);Ví dụ 2: Refresh Materialized View mỗi giờ
SELECT cron.schedule(
'refresh-daily-revenue',
'0 * * * *',
$$REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_revenue$$
);
-- Lưu ý: CONCURRENTLY yêu cầu materialized view phải có UNIQUE INDEXVí dụ 3: Gọi stored procedure từ database khác
Mặc định pg_cron chạy job trên database đã cấu hình trong cron.database_name. Nhưng từ pg_cron v1.4+, bạn có thể chỉ định database target:
SELECT cron.schedule_in_database(
'cleanup-app-db',
'0 4 * * *',
$$CALL cleanup_expired_sessions()$$,
'myapp_production' -- Tên database target
);Ví dụ 4: Vacuum và analyze định kỳ cho bảng lớn
SELECT cron.schedule(
'vacuum-orders-table',
'30 1 * * 6', -- 1:30 AM mỗi Thứ Bảy
$$VACUUM ANALYZE orders$$
);Quản lý và monitor jobs
Xem danh sách jobs đang active
SELECT jobid, jobname, schedule, command, active, database
FROM cron.job
ORDER BY jobid;Xem lịch sử chạy — đây là killer feature
SELECT
jrd.jobid,
j.jobname,
jrd.start_time,
jrd.end_time,
jrd.return_message,
jrd.status
FROM cron.job_run_details jrd
JOIN cron.job j ON j.jobid = jrd.jobid
ORDER BY jrd.start_time DESC
LIMIT 20;Cột status sẽ là succeeded hoặc failed. Cột return_message chứa số rows affected hoặc error message nếu job crash. Không cần grep log nữa.
Disable tạm thời một job
-- Disable
UPDATE cron.job SET active = false WHERE jobname = 'delete-old-logs';
-- Enable lại
UPDATE cron.job SET active = true WHERE jobname = 'delete-old-logs';Xóa job
SELECT cron.unschedule('delete-old-logs');
-- Hoặc theo jobid:
SELECT cron.unschedule(1);Dọn lịch sử job cũ (tránh bảng job_run_details phình to)
Bài học rút ra sau vài tháng chạy production: bảng cron.job_run_details tích lũy nhanh hơn bạn nghĩ. Chỉ cần 5 job chạy mỗi giờ là sau 90 ngày đã có hơn 10,000 records — chưa kể job nào chạy mỗi 15 phút. Giải pháp? Dùng chính pg_cron để tự dọn nó:
SELECT cron.schedule(
'cleanup-job-history',
'0 0 * * 0', -- Mỗi Chủ Nhật lúc 0:00
$$DELETE FROM cron.job_run_details WHERE end_time < NOW() - INTERVAL '7 days'$$
);Tips & Best practices từ thực chiến
1. Đặt tên job có ý nghĩa. Tên job phải unique và mô tả rõ nó làm gì. job-1 hay cleanup là tên tệ. delete-audit-logs-30d hay refresh-mv-daily-revenue là tên tốt — nhìn vào biết ngay.
2. Cẩn thận timezone. pg_cron chạy theo timezone của PostgreSQL server, mặc định thường là UTC. Muốn job chạy lúc 2 AM theo giờ Việt Nam (UTC+7), set là 19:00 UTC:
-- 2:00 AM ICT (UTC+7) = 19:00 UTC ngày hôm trước
SELECT cron.schedule('nightly-cleanup', '0 19 * * *', $$...$$);
-- Hoặc check timezone của PostgreSQL:
SHOW timezone;3. Dùng transaction khi cần atomicity. Job gồm nhiều bước cần rollback khi thất bại? Wrap vào stored procedure với exception handling — đừng nhét chuỗi SQL thẳng vào pg_cron.
4. Monitor job failures thường xuyên. Dán query này vào script monitoring hoặc Grafana alert:
-- Tìm job failed trong 24h qua
SELECT jobname, start_time, return_message
FROM cron.job_run_details jrd
JOIN cron.job j ON j.jobid = jrd.jobid
WHERE status = 'failed'
AND start_time > NOW() - INTERVAL '24 hours';5. Không lạm dụng pg_cron cho heavy ETL. pg_cron phù hợp với maintenance tasks gọn nhẹ: cleanup, refresh view, update statistics. ETL phức tạp xử lý hàng triệu rows, nhiều transformation — đó là việc của Airflow hay dbt, không phải pg_cron.
6. Test job bằng cách gọi trực tiếp trước khi schedule. Đừng schedule câu SQL chưa test. Chạy thủ công trong psql trước, confirm kết quả đúng, rồi mới bỏ vào pg_cron. Đừng để pg_cron làm vật thí nghiệm.
Sau khi bỏ cron scripts sang pg_cron, việc quản lý database job nhàn hơn hẳn. Không còn ssh vào server để check cron, không còn grep syslog tìm lỗi. Mọi thứ query được ngay trong psql, backup cùng pg_dump — khi dựng server mới, restore database là xong.
