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 database pem TO pgbouncer; GRANT usage ON schema pem 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 #:
5 of 5
Last update:
a month ago
Updated by:
 
Labels (3)
Contributors