Background
Currently, BigAnimal doesn't officially support pgAgent. This article will introduce the way to run pgAgent remotely so that BigAnimal users can run scheduler jobs.
Prerequisite
- Superuser privilege - How to request superuser privilege?
- Another host has pgAgent installed. EDB Repo (EDB account is required)
How to setup pgAgent remotely
BigAnimal supports users in creating extension pgAgent and DBMS_Scheduler. After that, then users can create Scheduler Jobs successfully.
Now we need run pgAgent on a separate host remotely. The connection string could be found in BigAnimal Connect section.
Steps as below
1. Install pgAgent via steps in EDB Repo (EDB account is required, go to EDB Repos 2.0)
2. set the environment variable for PGPASSWORD, PGPORT
export PGPASSWORD=$PASSWORD
export PGPORT=5432
or you could set the password and port via .pgpass
*:5432:*:*:<password> //hostname:port:database:username:password
2. Launch pgAgent by running the command, you can monitor the connection status via /tmp/pgagent.log
/usr/lib/edb-as/14/bin/pgagent host=p-vj7n9p9b4n.xgv7pbgfawhhnakq.biganimal.io dbname=edb_admin user=edb_admin port=5432 -l 2 -s /tmp/pgagent.log &
3. If the connection is established successfully, then you are supposed to see the logs like
Tue Sep 27 09:27:37 2022 DEBUG: Returning the connection to the connection pool: 'user=edb_admin dbname=edb_admin host=p-vj7n9p9b4n.xgv7pbgfawhhnakq.biganimal.io'...
Tue Sep 27 09:27:37 2022 DEBUG: Parsing connection information...
Tue Sep 27 09:27:37 2022 DEBUG: user: edb_admin
Tue Sep 27 09:27:37 2022 DEBUG: dbname: edb_admin
Tue Sep 27 09:27:37 2022 DEBUG: host: p-vj7n9p9b4n.xgv7pbgfawhhnakq.biganimal.io
Tue Sep 27 09:27:37 2022 DEBUG: Returning the connection to the connection pool: 'user=edb_admin dbname=edb_admin host=p-vj7n9p9b4n.xgv7pbgfawhhnakq.biganimal.io'...
Tue Sep 27 09:27:37 2022 DEBUG: Completed job: 1
Tue Sep 27 09:27:37 2022 DEBUG: Destroying job thread for job 1
How to integrate with PgBouncer
pgAgent connection will be terminated during a switchover or DB restart. Enable PgBouncer with the below setting may avoid the issue.
1. Install and Configure edb-pgbouncer117 by following Configuring EDB PgBouncer for BigAnimal
1.1 /etc/edb/pgbouncer1.17/edb-pgbouncer-1.17.ini , [databases] format is
Below is an example.
[databases]
edb_admin = host=p-vj7n9p9b4n.xgv7pbgfawhhnakq.biganimal.io port=5432 user=edb_admin dbname=edb_admin
Other settings, please note pool_mode = transaction
[pgbouncer]
listen_addr = *
listen_port = 6432
auth_file = /etc/edb/pgbouncer1.17/userlist.txt
pool_mode = transaction
1.2 /etc/edb/pgbouncer1.16/userlist.txt
"user1" "password1"
2. Launch PgBouncer
systemctl start edb-pgbouncer-1.17 // start pgbouncer
systemctl status edb-pgbouncer-1.17 // check the pgbouncer status
3. Now launch pgAgent command will become
/usr/lib/edb-as/14/bin/pgagent host=<pgbouncer host> dbname=edb_admin user=edb_admin port=6432 -l 2 -s /tmp/pgagent.log &
Reference
How to secure Job scheduling with DBMS_SCHEDULER in EDB Postgres Advanced Server
PgBouncer Connection Pooling: What to do when persistent connectivity is lost
"pgAgent runs as a daemon on Unix systems, and a service on Windows systems. In most cases it will run on the database server itself - for this reason, pgAgent is not automatically configured when PEM is installed. In some cases however, it may be preferable to run pgAgent on multiple systems, against the same database; individual jobs may be targeted at a particular host, or left for execution by any host."
"System/database access- all jobs run by pgAgent will run with the security privileges of the pgAgent user. SQL steps will run as the user that pgAgent connects to the database as, and batch/shell scripts will run as the operating system user that the pgAgent service or daemon is running under. Because of this, it is essential to maintain control over the users that are able to create and modify jobs. By default, only the user that created the pgAgent database objects will be able to do this - this will normally be the PostgreSQL superuser."