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.