cancel
Showing results for 
Search instead for 
Did you mean: 

PgBouncer with PEM 7.6

As a resolution to a few issues raised earlier with the EnterpriseDB clients, that the PEM agent creates sevrer load in case there are too many databases to be monitored, EnterpriseDB has included another feature in PEM , which allows us to configure the PEM agent connections through PgBouncer.

This would help us pool the agent connections for better handling of both local and remote agents.

 

PEM Version: 7.6
Recommended pgBouncer version 1.9

 

You could use the below set of steps to configure PEM agent to work with pgbouncer. The below example steps are to configure the local PEM agent to connect pgbouncer.

 

Step 1: Configure Database.

 

>> Create a user named pgbouncer on the pem database.
CREATE USER pgbouncer PASSWORD 'edb';

 

>> Create another user pem_adm1.
CREATE USER pem_adm1 PASSWORD 'edb' LOGIN CREATEROLE;

 

>> Grant privileges to the new users.

GRANT pem_admin TO pem_adm1;
GRANT pem_agent_pool TO pem_adm1;
GRANT connect ON pem DATABASE TO pgbouncer;
GRANT usage ON pem SCHEMA TO pgbouncer;
GRANT execute ON FUNCTION pem.get_agent_pool_auth(text) TO pgbouncer;

 

>> Create PEM proxy user.

SELECT pem.create_proxy_agent_user('agent_user1');

 

>> Add an entry for the new users in the pg_hba.conf file of the PEM server.

local pem pgbouncer,pem_adm1 md5

 

>> Restart the PEM database server.

systemctl restart edb-as-10.service


>> Change PgBouncer directory ownership.

chown postgres:postgres /opt/PgBouncer

chmod 700 /opt/PgBouncer

 

>> Make sure certificate path is correct in pgbouncer.ini


>> Copy the user details in userlist.txt file. Login to pem database and run:

 

COPY (
SELECT 'pgbouncer'::text, 'edb'
UNION ALL
SELECT 'pem_adm1'::text, 'edb'
)
TO '/opt/PgBouncer/etc/userlist.txt'
WITH (FORMAT CSV, delimiter ' ', FORCE_QUOTE * );

 

Step 2: Configure pgbouncer.

 

>> Edit hba_file for pgbouncer and add below:

hostssl pem agent_user1 0.0.0.0/0 cert

 

>> Change owner of userlist to postgres and permission to 600.

chown postgres:postgres /opt/PgBouncer/etc/userlist.txt

chmod 600 /opt/PgBouncer/etc/userlist.txt

 

>> Enable and start pgbouncer service

systemctl enable pgbouncer.service
systemctl start pgbouncer.service


Step 3: Configure PEM Agent

 

>> Register PEM agent using the agent_user1 user.

PGSSLMODE=require PEM_SERVER_PASSWORD=edb /opt/edb/pem/agent/bin/pemworker --register_agent --pem-server <host> --pem-port 6432 --pem_user pem_adm1 --pem_agent_user agent_user1 --display-name PGBAgent1

 

This will create new certificates at /root/.pem/ and update the agent.cfg with new entries like new port, new agent id.

 

>> Update the pem agent table to disable the current agent registration. Connect to pem database and run below:

UPDATE pem.agent SET active='f' WHERE id=1;

 

>> Add new entry to hba_file to allow access to pgbouncer

local pgbouncer all md5
local pem pem_adm1 md5


>> Restart pgbouncer service.

systemctl restart pgbouncer.service

 

>> Connect to the pem database using agent_user1 to confirm connetcivity using below:

PGSSLMODE=require PGSSLCERT=/root/.pem/agent_user1.crt PGSSLKEY=/root/.pem/agent_user1.key PEM_SERVER_PASSWORD=edb /opt/PostgreSQL/10/bin/psql -p 6432 -U agent_user1 -d pem

 

>> Backup and Remove old certificates from /root/.pem/

 

>> Restart the pem agent service
systemctl restart pemagent.service

 

>> Change pem server properties. (agent tab)

Right click on the server > properties > Agent tab.
SELECT the new agent (PGBAgent1) from the drop down.

>> Check the agent in the monitoring tab of PEM client. You should now see the agent PGBAgent1 instead of the default agent 'Postgres Enterprise Manager Host'.

Version history
Revision #:
1 of 1
Last update:
‎03-28-2019 01:52 AM
Updated by:
 
Labels (3)
Contributors