Configuring the PEM Agent to use pgBouncer

You can use an RPM package to install a PEM Agent; for detailed installation information, please see the PEM Installation Guide, available from the EnterpriseDB website

Configuring a New PEM Agent (installed using an RPM)

After using an RPM package to install the PEM agent, you will need to configure it to work it against a particular PEM database server. Use the following command:

$ PGSSLMODE=require PEM_SERVER_PASSWORD=pem_admin1_password /usr/edb/pem/agent/bin/pemworker --register-agent --pem-server 172.16.254.22 --pem-port 6432 --pem-user pem_admin1 --pem-agent-user pem_agent_user1 --display-name *Agent_Name*
Postgres Enterprise Manager Agent registered successfully!

In above command, the command line argument –pem-agent-user instructs the agent to create an SSL certificate and key pair for the pem_agent_user1 database user in /root/.pem directory. For example:

/root/.pem/pem_agent_user1.crt
/root/.pem/pem_agent_user1.key

They will be used by the PEM agent to connect to the PEM database server as pem_agent_user1. It will also create /usr/edb/pem/agent/etc/agent.cfg.

You will find a line mentioning the agent-user to be used in the agent.cfg configuration file. For example:

$ cat /usr/edb/pem/agent/etc/agent.cfg
[PEM/agent]
pem_host=172.16.254.22
pem_port=6432
agent_id=12
agent_user=pem_agent_user1
agent_ssl_key=/root/.pem/pem_agent_user1.key
agent_ssl_crt=/root/.pem/pem_agent_user1.crt
log_level=warning
log_location=/var/log/pem/worker.log
agent_log_location=/var/log/pem/agent.log
long_wait=30
short_wait=10
alert_threads=0
enable_smtp=false
enable_snmp=false
allow_server_restart=true
allow_package_management=false
allow_streaming_replication=false
max_connections=0
connect_timeout=-1
connection_lifetime=0
allow_batch_probes=false
heartbeat_connection=false

Configuring an Existing PEM Agent (installed using an RPM)

If you are using an existing PEM agent, you can copy the SSL certificate and key files to the target machine, and reuse the files. You will need to modify the files, adding a new parameter and replacing some parameters in the existing agent.cfg file.

Add a line for agent_user to be used for the agent. For example:

agent_user=pem_agent_user1

Update the port to specify the pgBouncer port. For example:

pem_port=6432

Update the certificate and key path locations. For example:

agent_ssl_key=/root/.pem/pem_agent_user1.key
agent_ssl_crt=/root/.pem/pem_agent_user1.crt

Please note: as an alternative, you can run the agent self registration, but that will create a new agent id. If you do run the agent self-registration, you must replace the new agent id with existing id, and disable the entry for the new agent id in the pem.agent table. For example:

pem=# UPDATE pem.agent SET active = false WHERE id = *new_agent_id*;
UPDATE 1

Please keep a backup of the existing SSL certificate, key file, and agent configuration file.