# PostgreSQL PG_CRON: harmonogram zadan bez zewnetrznego crona pg_cron przenosi harmonogram zadan bazodanowych tam gdzie realnie powinien byc - do samej bazy PostgreSQL. Ten tekst pokazuje instalacje, skladnie i typowe zastosowania. ![Pg cron: PG_CRON](https://jsystems.pl/nowy_blog/img/comm/pg_cron.jpg) Rozszerzenie PG\_CRON pozwala na automatyczne uruchamianie cyklicznych zadań w serwerze PostgreSQL. Możesz to wykorzystać np. do przeładowywania danych w hurtownii danych, czy uruchamiania procesów konserwacyjnych. **Z tego artykułu dowiesz się:** - czym jest pg\_cron i w jakim celu się go stosuje, - jak zainstalować pg\_cron na Ubuntu, - jak zainstalować pg\_cron na systemach z rodziny Red Hat, - w jaki sposób konfigurować automatyczne uruchamianie zadań w PostgreSQL za pomocą pg\_cron, - jak przeglądać zaplanowane zadania, - jak modyfikować zaplanowane zadania, - jak wyłączać zaplanowane zadania, - jak przeglądać historię wykonanych zaplanowanych zdań wraz z ewentualnymi błędami i ich przyczynami. 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 (), 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. [![Baner szkolenia Administracja, replikacja i tuning baz danych PostgreSQL w JSystems, terminy gwarantowane](https://jsystems.pl/new_page_resources/images/other/pgadm1.jpg)](https://jsystems.pl/szkolenia-postgresql;administracja_bazami_danych_postgresql_z_elementami_ha__optymalizacji_i_replikacji.szczegoly?utm_source=blog&utm_medium=article&utm_campaign=post237&utm_content=banner) [Szkolenie Administracja, replikacja i tuning baz danych PostgreSQL →](https://jsystems.pl/szkolenia-postgresql;administracja_bazami_danych_postgresql_z_elementami_ha__optymalizacji_i_replikacji.szczegoly?utm_source=blog&utm_medium=article&utm_campaign=post237&utm_content=link) To szkolenie może być [dofinansowane dla Ciebie z KFS lub BUR](https://jsystems.pl/dofinansowanie?utm_source=blog&utm_medium=article&utm_campaign=post237&utm_content=dofinansowanie). ★★★★★Średnia ocena naszych szkoleń w Google: **5/5** ## Powiązane artykuły - ›[Kurs administracji PostgreSQL - wybór preworka](https://jsystems.pl/blog/show_post/kurs_administracji_postgresql_-_wybor_preworka) - ›[Uruchamianie i zatrzymywanie PostgreSQL - pg\_ctl i systemctl](https://jsystems.pl/blog/show_post/postgresql_-_uruchamianie_zatrzymywanie_i_restartowanie_klastra_postgresql) - ›[PostgreSQL - role grupowe, czyli zbiorcze zarządzanie uprawnieniami](https://jsystems.pl/blog/show_post/postgresql_-_role_grupowe_czyli_zbiorcze_zarzadzanie_uprawnieniami) - ›[Kurs administracji PostgreSQL](https://jsystems.pl/blog/show_post/bezplatny_kurs_administracji_postgresql) - ›[PG\_PREWARM - prosty i szybki tuning w PostgreSQL](https://jsystems.pl/blog/show_post/PG_PREWARM_-_prosty_i_szybki_tuning_w_PostgreSQL) - ›[PostgreSQL - PG\_REPACK : przenoszenie, przebudowa i klastrowanie tabel i indeksów](https://jsystems.pl/blog/show_post/postgresql_-_pg_repack__przenoszenie_przebudowa_i_klastrowanie_tabel_i_indeksow_online)