Jak zaplanować zadania cykliczne w bazie (pg_cron)

PostgreSQL

Jak zaplanować zadania cykliczne w bazie (pg_cron)

W skrócie

Wcześniej czy później każdy administrator PostgreSQL potrzebuje "coś, co odpali się samo": nocne VACUUM na wybranej tabeli, odświeżenie zmaterializowanego widoku raz na godzinę, kasowanie starych logów albo przeliczenie raportu o świcie. Sam PostgreSQL nie ma wbudowanego harmonogramu - i tu wchodzi rozszerzenie pg_cron, które robi dokładnie to: planuje i uruchamia zadania SQL wewnątrz bazy, składnią znaną z uniksowego crona.

Jak to wygląda w praktyce

Typowy scenariusz: masz zmaterializowany widok z podsumowaniem sprzedaży i chcesz go odświeżać co godzinę. Do tej pory robiłeś to systemowym cronem, który wołał psql -c "REFRESH MATERIALIZED VIEW ...". Działa, ale wpis żyje poza bazą - przy przenoszeniu klastra na nowy serwer łatwo o nim zapomnieć, a hasło do bazy ląduje w pliku crona albo w .pgpass. Chciałbyś, żeby harmonogram był częścią bazy: widoczny w tabeli, objęty backupem definicji, przenośny razem z resztą konfiguracji. Bez pg_cron nie masz gdzie takiego wpisu umieścić - SELECT po pg_cron zwraca błąd, że nie ma takiego schematu, a próba użycia cron.schedule kończy się komunikatem, że funkcja nie istnieje.

Dlaczego tak się dzieje

PostgreSQL z założenia nie zawiera schedulera. Twórcy zostawili to systemowi operacyjnemu (cron, systemd timer, Zadania harmonogramu w Windows), bo baza nie powinna sama pilnować zegara. pg_cron dokłada tę warstwę: uruchamia w tle proces (background worker), który co minutę sprawdza tabelę cron.job i odpala zadania, których czas nadszedł. Ponieważ jest to background worker ładowany przy starcie serwera, rozszerzenie musi być wpisane do shared_preload_libraries - a ta lista jest czytana tylko przy starcie, więc jej zmiana zawsze wymaga pełnego restartu klastra, nie samego przeładowania. To najczęstsza przyczyna "zainstalowałem, a nie działa": ktoś zrobił CREATE EXTENSION, ale nie dopisał biblioteki do preload i nie zrestartował serwera.

Jak to rozwiązać krok po kroku

1. Zainstaluj pakiet rozszerzenia na serwerze. Na rodzinie Debian/Ubuntu z repozytorium PGDG dla PostgreSQL 16 będzie to apt install postgresql-16-cron (numer dopasuj do swojej wersji). Bez pakietu systemowego samo CREATE EXTENSION nie zadziała, bo brakuje pliku biblioteki.
2. Dopisz bibliotekę do shared_preload_libraries w postgresql.conf, na przykład shared_preload_libraries = 'pg_cron'. Jeśli masz tam już inne biblioteki, dołóż pg_cron po przecinku, nie nadpisuj listy.
3. Wskaż bazę, w której pg_cron ma trzymać metadane. Domyślnie jest to postgres; jeśli chcesz inną, ustaw cron.database_name = 'twoja_baza' w tym samym pliku.
4. Zrestartuj klaster (a nie tylko przeładuj konfigurację), bo lista preload wczytywana jest wyłącznie przy starcie. Na systemd: systemctl restart postgresql.
5. Utwórz rozszerzenie w bazie metadanych: CREATE EXTENSION pg_cron; jako superużytkownik. Pojawi się schemat cron z tabelą job i funkcjami sterującymi.
6. Zaplanuj zadanie funkcją cron.schedule. Odświeżanie widoku co godzinę o pełnej godzinie: SELECT cron.schedule('odswiez-raport', '0 * * * *', 'REFRESH MATERIALIZED VIEW raport_sprzedazy');. Pierwszy argument to nazwa zadania, drugi to harmonogram w składni crona, trzeci to polecenie SQL.
7. Aby zadanie działało na innej bazie niż ta z metadanymi, użyj wariantu cron.schedule_in_database('nazwa', '*/5 * * * *', 'SQL', 'docelowa_baza'). Zadanie usuwasz przez cron.unschedule('odswiez-raport').

Jak sprawdzić, że zadziałało

Najpierw upewnij się, że biblioteka faktycznie się załadowała: SHOW shared_preload_libraries; powinno wypisać pg_cron. Listę zaplanowanych zadań zobaczysz zapytaniem SELECT jobid, schedule, command, active FROM cron.job;. Najważniejszy dowód, że zadania realnie się wykonują, znajdziesz w historii uruchomień: SELECT jobid, status, return_message, start_time, end_time FROM cron.job_run_details ORDER BY start_time DESC LIMIT 10;. Kolumna status pokaże succeeded przy udanych przebiegach, a return_message wyjaśni ewentualny błąd (na przykład brak uprawnień do widoku). Jeśli tabela historii jest pusta mimo minięcia zaplanowanej godziny, wróć do kroku z restartem - to znak, że background worker nie wystartował.

Wróć do listy: 100 najczęstszych pytań i problemów z PostgreSQL

Sprawdź szkolenie: Administracja, replikacja i tuning baz danych PostgreSQL

To szkolenie może być dofinansowane z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5

Sprawdź szkolenie: Zaawansowana administracja PostgreSQL (HA, DR, monitoring, skalowanie)

To szkolenie może być dofinansowane z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5