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)
- Database type is EPAS
How to setup pgAgent remotely
BigAnimal supports users in creating extensions pgAgent.
edb_admin=> create extension pgagent;
CREATE EXTENSION
You can create scheduled jobs from DBMS_Scheduler or How to use pgAgent in pgAdmin 4
DBMS_Scheduler requires superuser privilege.
edb_admin=> create extension DBMS_Scheduler;
ERROR: permission denied to create extension "dbms_scheduler"
HINT: Must be superuser to create this extension.
Now we need to run pgAgent on a separate host remotely. The connection string can 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."
Error
Error 1 "ERROR: Could not find the table 'pgagent.pga_job'. Have you run pgagent.sql on this database?"
It's due to pgagent extension wasn't created on this database. Run create extension pgagent on the database will resolve the error.
Error 2 "ERROR: Unsupported schema version: 3. Version 4 is required - please run ALTER EXTENSION "pgagent" UPDATE;"
DB_Name=# CREATE EXTENSION pgagent;
ERROR: relation "pga_jobagent" already exists
DB_Name=# drop extension pgagent;
ERROR: extension "pgagent" does not exist
DB_Name=# CREATE EXTENSION pgagent FROM "3.4";
ERROR: function pgagent_schema_version() is not a member of extension "pgagent"
DETAIL: An extension is not allowed to replace an object that it does not own.
It may be caused by pgagent was not upgraded after the migration. Follow the documentation: Upgrading a pgAgent Installation, then run the script.
Could you please
1. Download the script and upload it to your VM
2. Connect to our DB
3. run command \i <your path>/dbms_job.upgrade.script.sql
If the script exists in the below path, you can also run the below command on DB.
\i /usr/share/edb-as/1x/contrib/dbms_job.upgrade.script.sql