pg_cron extension is one of the most important extension for DWH and ETL, in this article I will show you how to install pg_cron extension topic.
pg_cron is an open source extension, you can find more information from their github page.
This extension is used to schedule jobs on database level, instead of using operating system’s task scheduler, this method completely relies on database level. Relying on database instead of operating system increases system portability.
Moving between different operating systems will not affect your scheduled database tasks. And we move between different servers (virtual and / or physical) more than expected, because of Vsphere migrations, new hardware and cloud migrations.
Installing pg_cron with Apt
You can use following command to install pg_cron in debian/ubuntu based distributions:
root@a5dd0e71635a:/# apt update
Get:1 http://deb.debian.org/debian bookworm InRelease [151 kB]
Get:2 http://deb.debian.org/debian bookworm-updates InRelease [52.1 kB]
Get:3 http://deb.debian.org/debian-security bookworm-security InRelease [48.0 kB]
Get:4 http://deb.debian.org/debian bookworm/main amd64 Packages [8,787 kB]
Get:5 http://apt.postgresql.org/pub/repos/apt bookworm-pgdg InRelease [123 kB]
Get:6 http://apt.postgresql.org/pub/repos/apt bookworm-pgdg/main amd64 Packages [301 kB]
Get:7 http://deb.debian.org/debian bookworm-updates/main amd64 Packages [11.3 kB]
Get:8 http://deb.debian.org/debian-security bookworm-security/main amd64 Packages [128 kB]
Fetched 9,601 kB in 3s (3,512 kB/s)
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
All packages are up to date.
root@a5dd0e71635a:/# apt install -y postgresql-16-cron
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following NEW packages will be installed:
postgresql-16-cron
0 upgraded, 1 newly installed, 0 to remove and 0 not upgraded.
Need to get 104 kB of archives.
After this operation, 249 kB of additional disk space will be used.
Get:1 http://apt.postgresql.org/pub/repos/apt bookworm-pgdg/main amd64 postgresql-16-cron amd64 1.6.2-1.pgdg120+1 [104 kB]
Fetched 104 kB in 1s (112 kB/s)
debconf: delaying package configuration, since apt-utils is not installed
Selecting previously unselected package postgresql-16-cron.
(Reading database ... 12106 files and directories currently installed.)
Preparing to unpack .../postgresql-16-cron_1.6.2-1.pgdg120+1_amd64.deb ...
Unpacking postgresql-16-cron (1.6.2-1.pgdg120+1) ...
Setting up postgresql-16-cron (1.6.2-1.pgdg120+1) ...
Processing triggers for postgresql-common (256.pgdg120+1) ...
debconf: unable to initialize frontend: Dialog
debconf: (No usable dialog-like program is installed, so the dialog based frontend cannot be used. at /usr/share/perl5/D
ebconf/FrontEnd/Dialog.pm line 78.)
debconf: falling back to frontend: Readline
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files:
To sum up commands:
apt update
apt install -y postgresql-16-cron
Installing pg_cron with yum
You can install pg_cron extension on redhat / yum based distributions via:
yum install -y pg_cron_16
Installing pg_cron via Building
It is not suggested to build and run packages by yourself, package managers help us to maintain our packages with latest releases. If you compile pg_cron extension by yourself, you may need to compile it again and again on each upgrade.
If you are not using a distribution that does not have a package manager, you can clone the repository and compile the extension like:
git clone https://github.com/citusdata/pg_cron.git
cd pg_cron
# Ensure pg_config is in your path, e.g.
export PATH=/usr/pgsql-16/bin:$PATH
make && sudo PATH=$PATH make install
Configuring pg_cron Extension
It’s only a little configuration is required to use pg_cron extension and does not require a specific skill to configure.
We need to edit postgesql.conf file to configure pg_cron extension. Update shared_preload_libraries value.
shared_preload_libraries = 'pg_cron'
Also, cron extension need a database to create it’s metadata tables:
cron.database_name = 'postgres'
And restart PostgreSQL server.
postgres=# CREATE EXTENSION pg_cron;
CREATE EXTENSION
To Sum Up
And we are ready! You can start using pg_cron extension. If you have a comment or question, please do not hesitate to leave a comment below. I’d love hear more about your thoughrs.
Leave a Reply