PG_CRON - czyli cykliczne uruchamianie zadań w PostgreSQL

Zaczynając swoją przygodę z Postgresem, często jedną z pierwszych przeszkód, z którą się stykamy, jest problem planowania cyklicznych zadań w bazie danych. Postgres domyślnie nie posiada takiej funkcjonalności (jak na przykład DBMS_JOB w Oracle), a jedyną możliwością jest zaplanowanie zadań w cronie systemowym. Istnieje jednak rozszerzenie pg_cron, dostępne w oficjalnych repozytoriach PGDG oraz do pobrania i własnej kompilacji ze strony projektu na github (https://github.com/citusdata/pg_cron), umożliwiające zaplanowanie powtarzalnych zadań bezpośrednio w Postgresie z wykorzystaniem składni znanej ze wspomnianego wcześniej unixowego crona.

Instalacja i konfiguracja

Instalacja zależnie od systemu, z którego korzystamy, odbywa się za pomocą yum na systemach z rodziny RedHat lub apt na pochodnych Debiana.

## RHEL, CentOS, Fedora, Rocky

1) wget https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

2) sudo yum install pgdg-redhat-repo-latest.noarch.rpm

3) sudo yum install -y pg_cron_15



## Debian, Ubuntu

1) sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

2) wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | \

sudo apt-key add -

3) sudo apt-get update

4) sudo apt-get -y install postgresql-15-cron

Inną możliwością jest pobranie projektu bezpośrednio z githuba i własnoręczna kompilacja.

git clone https://github.com/citusdata/pg_cron.git

cd pg_cron

# upewnij się, że zmienna PATH zawiera ścieżkę do pg_config

# przykładowo: /usr/lib/postgresql/15/bin dla ubuntu, albo /usr/pgsql-15/bin dla redhata

export PATH=/usr/pgsql-15/bin:$PATH

make && sudo PATH=$PATH make install

Następnie dodajemy ‘pg_cron’ do parametru shared_preload_libraries w pliku "postgresql.conf", albo przez ALTER SYSTEM i restartujemy Postgresa, aby zmiany zostały wczytane podczas startu Postgresa.

#Sprawdź czy nie masz już ustawionych jakichś bibliotek w "shared_preload_libraries"

show shared_preload_libraries;




#Zmiana parametru w postgresql.conf lub przez ALTER SYSTEM:

alter system set shared_preload_libraries='pg_cron';  #można po przecinku: 'pg_prewarm,pg_cron'




# na Ubuntu

/usr/lib/postgresql/15/bin/pg_ctl -D /katalog/pg/data restart -m fast



# na RHEL

/usr/pgsql-15/bin/pg_ctl -D /katalog/pg/data restart -m fast



# ścieżkę do katalogu z danymi znajdziemy za pomocą 

postgres=# show data_directory;

       data_directory          

-----------------------------

 /var/lib/postgresql/15/main

(1 row)

Rozszerzenie tworzymy zawsze tylko w jednej bazie, którą później wykorzystujemy jako pojedynczy punkt do zarządzania wszystkimi zadaniami. Jeżeli planujemy stworzenie rozszerzenia w bazie innej niż domyślna Postgres, powinniśmy dodać do pliku konfiguracyjnego nowy parametr cron.database_name, na przykład:

shared_preload_libraries = 'pg_cron'

cron.database_name = 'apka'

Po ponownym uruchomieniu możemy dodać rozszerzenie do jednej wybranej bazy, w której będziemy planować zadania. Opcjonalnie możemy również nadać uprawnienia dla schematu “cron” użytkownikom, którzy powinni mieć możliwość planowania zadań.

postgres@vagrant:~$ psql -c "create extension pg_cron;" apka

CREATE EXTENSION

postgres@vagrant:~$ psql -c "grant usage on schema cron to planer;" apka

GRANT

Jak skorzystamy z domyślnych wartości i stworzymy rozszerzenie oraz zaplanujemy zadania jako użytkownicy Postgres w bazie Postgres.

postgres@vagrant:~$ psql -c "create extension pg_cron;"

CREATE EXTENSION

Sposoby wykonywania zadań w pg_cron

Domyślnie pg_cron otwiera lokalnie połączenia do bazy, aby wykonywać zaplanowane zadania, dlatego musimy zadbać o odpowiednią konfigurację pg_hba.conf orac .pgpass, jeżeli logowanie wymaga hasła. Korzystając z użytkownika Postgres oraz bazy Postgres, wystarczy zastosować wpis dla połączeń lokalnych za pomocą socketa z metodą autentykacji “peer”.

local - postgres      postgres - peer

Jeżeli chcielibyśmy korzystać z innego użytkownika, lub z hasła, musimy odpowiednio zmodyfikować oba pliki konfiguracyjne.

Drugim sposobem wykorzystania zadań jest korzystanie z procesów tła do wykonywania zadań, zamiast nawiązywania lokalnych połączeń do bazy. Aby pg_cron przygotować do działania w ten sposób, musimy zmienić dwa parametry, oba wymagają restartu Postgresa, aby zmiany weszły w życie:

cron.use_background_workers = on

max_worker_processes = 20

cron.use_background_workers aby pg_cron korzystał z procesów tła oraz za pomocą parametru max_worker_processes określić ile zadań będzie mogło się wykonywać jednocześnie, domyślnie liczba procesów ograniczona jest do 8.

Warto też pamiętać, że pg_cron wykonuje zadania tylko na instancji głównej klastra. Rozszerzenie może być zainstalowane i skonfigurowane na instancjach standby, jednak zadania nie będą wykonywane na serwerach standby do momentu awansowania ich na instancję primary i otwarciu na zapis.

Planowanie zadań

Zadania możemy zaplanować za pomocą funkcji cron.schedule(), która przyjmuje dwie lub trzy wartości. W pierwszej wersji- harmonogram oraz polecenie do wykonania, w drugiej- nazwę zadania, harmonogram oraz polecenie. Harmonogram ustalamy za pomocą składni znanej z systemowego crona.

 ┌───────────── minuty (0 - 59)

 │ ┌────────────── godziny (0 - 23)

 │ │ ┌─────────────── dzień miesiąca (1 - 31)

 │ │ │ ┌──────────────── miesiąc (1 - 12)

 │ │ │ │ ┌───────────────── dzień tygodnia (0 - 7) (0 - 6, niedziela do soboty, 7 to

 │ │ │ │ │                            również niedziela)

 * * * * *

Przykładowe zaplanowanie zadania

SELECT - cron.schedule('select','* * * * *', 'SELECT 1');

Wszystkie zaplanowane zadania możemy znaleźć w tabeli cron.job.
Jeżeli korzystamy z większej liczby użytkowników do planowania zadań, każdy z nich może zobaczyć tylko swoje zadania. Ograniczenie to nie dotyczy użytkownika Postgres, który jest superuserem i może zobaczyć wszystkie zadania. Należy również pamiętać, że użytkownik, który jest “właścicielem” zadania, powinien mieć uprawnienia do obiektów, na których będzie pracował.

postgres=# set role=test;

SET                                                                 ^

postgres=> SELECT - cron.schedule('select','* * * * *', 'SELECT 1');

 schedule 

----------

        1

(1 row)



postgres=> select * from cron.job;

 jobid | schedule - | command - | nodename - | nodeport | database | username | active | jobname 

-------+-----------+----------+-----------+----------+----------+----------+--------+---------

     1 | * * * * * | SELECT 1 | localhost |      5432 | postgres | test      | t        | select

(1 row)



postgres=> set role=planer;

SET

postgres=> select * from cron.job;

 jobid | schedule | command | nodename | nodeport | database | username | active | jobname 

-------+----------+---------+----------+----------+----------+----------+--------+---------

(0 rows)



postgres=> set role=postgres;

SET

postgres=# select * from cron.job;

 jobid | schedule - | command - | nodename - | nodeport | database | username | active | jobname 

-------+-----------+----------+-----------+----------+----------+----------+--------+---------

     1 | * * * * * | SELECT 1 | localhost |      5432 | postgres | test      | t        | select

(1 row)

Wszystkie zadania, które tworzymy za pomocą funkcji cron.schedule(), są domyślnie tworzone w bazie Postgres, chyba że ustawiliśmy inaczej w parametrze cron.database_name oraz w innej bazie stworzyliśmy rozszerzenie. Chcąc tworzyć zadania dla innych baz, powinniśmy skorzystać z funkcji cron.schedule_in_database(). Przyjmuje ona wartości: nazwa zadania, harmonogram, polecenie, nazwa bazy, nazwa użytkownika oraz informację, czy zadanie powinno być aktywne. Jeżeli pominiemy ostatni parametr, domyślnie utworzy się jako aktywny.

postgres=# SELECT cron.schedule_in_database('zadanie w innej bazie','0 6 * * *','SELECT 1', 'app_db','planer','f');

 schedule_in_database 

----------------------

                    2

(1 row)



postgres=# select * from cron.job;

 jobid | schedule - | command - | nodename - | nodeport | database | username | active |          jobname          

-------+-----------+----------+-----------+----------+----------+----------+--------+-----------------------

     1 | * * * * * | SELECT 1 | localhost |      5432 | postgres | test      | t        | select



     2 | 0 6 * * * | SELECT 1 | localhost |      5432 | app_db - | planer - | f        | zadanie w innej bazie



(2 rows)

Tak stworzone zadanie nie będzie się wykonywało do momentu zmiany flagi “active” na “t” za pomocą funkcji cron.alter_job() przyjmującej wartości: jobid, schedule, command, database, username, active. Jedynie pierwsza wartość jest obowiązkowa, pozostałe wartości możemy zmienić, aby zmodyfikować zadanie lub podać dla nich wartość NULL, aby zachować oryginalne wartości.

postgres=# select cron.alter_job(2,null,null,null,null,'t');

 alter_job 

-----------



(1 row)



postgres=# select * from cron.job;

 jobid | schedule - | command - | nodename - | nodeport | database | username | active |          jobname          

-------+-----------+----------+-----------+----------+----------+----------+--------+-----------------------

     1 | * * * * * | SELECT 1 | localhost |      5432 | postgres | test      | t        | select

     2 | 0 6 * * * | SELECT 1 | localhost |      5432 | app_db - | planer - | t        | zadanie w innej bazie



(3 rows)

Zadania, które są już niepotrzebne, można usunąć za pomocą funkcji cron.unschedule(), która przyjmuje jako parametr jobid lub jobname.

select cron.unschedule('zadanie w innej bazie');

select cron.unschedule(2);

Oba powyższe wywołania mają ten sam efekt, spowodują usunięcie drugiego zadania w bazie “app_db”. Jeżeli mielibyśmy więcej niż jedno zadanie z taką samą nazwą, zostanie usunięte tylko jedno, z najniższym jobid.

Historię wykonanych zadań możemy przejrzeć w tabeli cron.job_run_details.

postgres=# select * from cron.job_run_details ; 

 jobid | runid | job_pid | database | username | command - | status | - return_message - | start_time | end_time 

-------+-------+---------+----------+----------+----------+--------+-------------------+------------+----------

     1 |     27 |           | postgres | test      | SELECT 1 | failed | connection failed |               | 

     1 |      1 |           | postgres | test      | SELECT 1 | failed | connection failed |               | 

Bardziej szczegółowe informacje odnośnie rezultatu zadania, lub jego błędu, znajdziemy w postgresowym pliku loga. Przykładowo:

postgres@vagrant:/var/log/postgresql$ tail -f postgresql-15-main.log

2023-05-11 19:37:00.007 UTC [35712] LOG: - cron job 1 connection failed

2023-05-11 19:38:00.065 UTC [35712] LOG: - cron job 1 starting: SELECT 1

2023-05-11 19:38:00.070 UTC [45866] test@postgres FATAL: - password authentication failed for user "test"

2023-05-11 19:38:00.070 UTC [45866] test@postgres DETAIL: - User "test" has no password assigned.

        Connection matched pg_hba.conf line 97: "host     all                all                127.0.0.1/32               scram-sha-256"

W powyższym przykładzie widzimy, że pierwsze wykonania zadania nie powiodły się, ponieważ konfiguracja pg_hba.conf wymagała od użytkownika “test” hasła przy logowaniu. Po nadaniu mu hasła oraz stworzeniu pliku .pgpass w katalogu domowym użytkownika Postgres zadanie zaczęło się wykonywać poprawnie.

postgres=# alter user test with password '123';

ALTER ROLE



postgres@vagrant:/var/log/postgresql$ cat ~/.pgpass

*:*:*:test:123



postgres@vagrant:/var/log/postgresql$ tail -f postgresql-15-main.log

2023-05-11 19:40:00.003 UTC [35712] LOG: - cron job 1 starting: SELECT 1

2023-05-11 19:40:00.011 UTC [35712] LOG: - cron job 1 completed: 1 row



postgres=# select * from cron.job_run_details where status = 'succeeded';

 jobid | runid | job_pid | database | username | command - | - status - | return_message |             start_time             |             end_time               

-------+-------+---------+----------+----------+----------+-----------+----------------+-------------------------------+-------------------------------

     1 | - 29 - | - 50703 | postgres | test      | SELECT 1 | succeeded | 1 row                | 2023-05-11 19:40:00.010307+00 | 2023-05-11 19:40:00.010722+00

Powyższy przykład obrazuje, jak w prosty sposób pg_cron może ułatwić planowanie oraz zarządzanie zadaniami cyklicznymi w Postgresie. Dzięki temu, że rozszerzenia oraz ich obiekty są zachowywane w kopiach zapasowych, możemy mieć pewność, iż wszystkie zaplanowane zadania zostaną zachowane nawet w przypadku odtwarzania klastra z backupu na innym serwerze.