Crontab

Since Postgresql does not have an embedded scheduler, this need is solved with pg_cron and pgagent extensions or with crontab at the operating system level.

SQL to be executed, function procedure etc. The codes can be written in Linux bash
script and scheduled with crontab, the embedded scheduler of the Linux operating
system.

psql -d testdb
# Eğer önceden oluşturuldu ise
drop table test ;
CREATE TABLE test (id serial, isim text);

Example Script

vim /var/lib/postgresql/scripts/instest.sh

#!/bin/bash
PSQL=”/usr/lib/postgresql/14/bin/psql”
PORT=5432
HOST=”localhost”
USER=”postgres”
DB=”testdb”
$PSQL -d $DB -U $USER -p $PORT <<EOF
INSERT INTO test (isim) SELECT ‘Mehmed’ FROM generate_series(1, 5);
\q
EOF
exit

chmod +x /var/lib/postgresql/scripts/instest.sh
should be given the privilege to execute.

Note: In case of an incorrect insert operation, the following lines can be added to send
a warning to the vty@dataera team via e-mail.

if [ $? -eq 0 ]; then
echo ‘işlem başarılı’
else
echo “Müşteri-Sunucu-Bilgisi” | mailx -s “Veri girişi
hata aldı” -a “From: UYARICI@dataera.com.tr” vty@dataera.com.tr
exit
fi

Note: Scripts copied from Microsoft systems to Linux/Unix systems may give errors
and may not work. In this case, you may need to convert your script with dos2unix
software.
sudo apt install dos2unix
dos2unix instest.sh

# Add the following line with the postgres user, then save and
exit.
postgres@srv1:~$ crontab -e
6 22 * * * /var/lib/postgresql/scripts/instest.sh 2> /var/lib/
postgresql/scripts/instest.log
crontab -l # Listing crontab

Every day at 22:06 pm, it runs the instest.sh script and if it gets an error (2>), it writes
the error to the instest.log file. It is recommended to test by manually triggering the
record in the crontab.

For details about crontab, you can review the documentation of the operating system
you are using.

Note: If it does not open with vim application, the following commands can be run.
export EDITOR=vim