Jobs

https://www.pgadmin.org/docs/pgadmin4/development/pgagent_install.html mht - Installing pgAgent
https://gist.github.com/peterneave/83cefce2a081add244ad7dc1c53bc0c3 mht - Install pgAgent on Postgres 10 (Debian Linux)

1. Установить pgagent

sudo apt install pgagent
 
# Create .pgpass file.
sudo su - postgres
echo localhost:5432:*:pgagent:securepassword >> ~/.pgpass
chmod 600 ~/.pgpass
chown postgres:postgres /var/lib/postgresql/.pgpass

2. Setup directory for logging

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

3. Create a config file

/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"

4. Create systemd service

/usr/lib/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

5. Start Service

sudo -i
systemctl daemon-reload
systemctl disable pgagent
systemctl enable pgagent
systemctl start pgagent

6. Enable auto rotation of logs

/etc/logrotate.d/pgagent

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

logrotate -f /etc/logrotate.d/pgagent

7. В базе postgres

CREATE EXTENSION pgagent;
 
CREATE USER "pgagent" WITH
  LOGIN
  NOSUPERUSER
  INHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
  encrypted password 'securepassword';
 
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;

8. В целевой базе

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