К сожалению PostgreSQL в отличие от MSSQL или MySQL не имеет встроенных средств планировщика.

Здесь описаны различные варианты планировщика для PostgreSQL. Это обычный Linux cron(tab), pg_cron и pgAgent. О последнем и пойдет речь.

Установку и настройку pgagent в документации нельзя назвать исчерпывающей из-за чего собственно и появилась эта заметка. Тем не менее ссылки на документацию оставлю

• Installing pgAgent
• How to Set Up pgAgent for Postgres Plus в формате PDF и локальная копия PDF файла.

apt-get update
apt-get install pgagent

Чтобы избежать неприятностей и быть в безопасности следуем рекомендациям сайта pgadmin и создаём файл pgpass.

Отдельно про файл pgpass я уже создал заметку, так что не буду повторяться.

По-умолчанию используется пользователь postgres с домашней директорией /var/lib/postgresql

su - postgres
echo localhost:5432:*:pgagent:password1337 >> ~/.pgpass
chmod 600 ~/.pgpass
chown postgres:postgres /var/lib/postgresql/.pgpass

Создаём директорию для логов и выставляем права

mkdir /var/log/pgagent
chown -R postgres:postgres /var/log/pgagent
chmod g+w /var/log/pgagent

Создаём в базе postgres новое расширение, пользователя pgagent

CREATE EXTENSION pgagent;
 
CREATE USER "pgagent" WITH
  LOGIN
  NOSUPERUSER
  INHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
  encrypted password 'password1337';
 
GRANT USAGE ON SCHEMA pgagent TO pgagent;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA pgagent TO pgagent;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA pgagent TO pgagent;

Проверяем соединение (журнал Postgres в файле /var/log/postgresql/postgresql-1{X}-main.log)

psql

psql -h localhost -d postgres -U pgagent

pgAgent

su - postgres
/usr/bin/pgagent -f -l 2 host=localhost port=5432 user=pgagent dbname=postgres

Создаём файл /etc/pgagent.conf

DBNAME=postgres
DBUSER=pgagent
DBHOST=localhost
DBPORT=5432
# ERROR=0, WARNING=1, DEBUG=2
LOGLEVEL=1
LOGFILE="/var/log/pgagent/pgagent.log"

Создаём файл /etc/systemd/system/pgagent.service

[Unit]
Description=pgAgent for PostgreSQL
After=syslog.target
After=network.target
 
[Service]
Type=forking
 
User=postgres
Group=postgres
 
# Location of the configuration file
EnvironmentFile=/etc/pgagent.conf
 
# Where to send early-startup messages from the server (before the logging
# options of pgagent.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog
 
# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000
 
ExecStart=/usr/bin/pgagent -s ${LOGFILE}  -l ${LOGLEVEL} host=${DBHOST} dbname=${DBNAME} user=${DBUSER} port=${DBPORT}
KillMode=mixed
KillSignal=SIGINT
 
Restart=on-failure
 
# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300
 
[Install]
WantedBy=multi-user.target

Для удобства можно изменить расположение конфигурационного файла

EnvironmentFile=/etc/systemd/system/pgagent.env

Запускаем наш systemd сервис

systemctl daemon-reload
systemctl enable pgagent
systemctl start pgagent

Теперь нужно настроить logrotate о чём мы все с вами обычно забываем.

Создаём файл /etc/logrotate.d/pgagent

/var/log/pgagent/*.log {
       weekly
       rotate 14
       copytruncate
       delaycompress
       compress
       notifempty
       missingok
       su root root
}

Проверка logrotate

logrotate -f /etc/logrotate.d/pgagent

EOM