====== Jobs ======
[[https://www.pgadmin.org/docs/pgadmin4/development/pgagent_install.html]] {{it:postgresql:jobs:installing_pgagent.mht|mht}} - Installing pgAgent\\
[[https://gist.github.com/peterneave/83cefce2a081add244ad7dc1c53bc0c3]] {{it:postgresql:jobs:install_pgagent_on_postgres_10_debian_linux_.mht|mht}} - Install pgAgent on Postgres 10 (Debian Linux)\\

1. Установить pgagent
<code bash>
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
</code>

2. Setup directory for logging
<code bash>
mkdir /var/log/pgagent
chown -R postgres:postgres /var/log/pgagent
chmod g+w /var/log/pgagent
</code>

3. Create a config file
<file bash /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"
</file>

4. Create systemd service
<file bash /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
</file>

5. Start Service
<code bash>
sudo -i
systemctl daemon-reload
systemctl disable pgagent
systemctl enable pgagent
systemctl start pgagent
</code>

6. Enable auto rotation of logs
<file bash /etc/logrotate.d/pgagent>
/var/log/pgagent/*.log {
       weekly
       rotate 10
       copytruncate
       delaycompress
       compress
       notifempty
       missingok
       su root root
}
</file>
<code bash>
logrotate -f /etc/logrotate.d/pgagent
</code>

7. В базе postgres
<code sql>
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;
</code>

8. В целевой базе 
<code sql>
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;
</code>