This is second post of Postgres Enterprise Manager 2.1 series.

Question which people ask, Is Postgres Enterprise Manager tool useful for Companies, which provide services for PostgreSQL? Answer is yes. What about companies, which provide infrastructure support, where they provide server and PostgreSQL database, for them is this is a good tool for monitoring PostgreSQL? Answer is yes, you can use it.

Companies, which are giving Infrastructure support and providing Database As service always want everything to be automated. i.e with provisioning server, server should have installed PostgreSQL and its components plus they want automatic installation of pemagent, which is also acheivable. However, they stumped on Automatic Bidning of PEM Agent with PostgreSQL.

For binding PostgreSQL with PEM Agent, Simple Method is using PEM Client.
In PEM Client, Add a PostgreSQL in PEM Directory and then user can bind the PEM agent with PostgreSQL Cluster.

File -> Add Server -> which will popup screen for "New serve Registeration" -> After adding server information (Also click on Store PEM Server)

Then in “New server Registeration Window” ->

 Click on tab "PEM Agent" -> Choose agent from drop down window of "Bound Agent", Then user can include the information. 

Well above is one method. However for Company which provides infrastructure support doesn’t want to do this manual work. They want this activity to be automated.

For automating this process, user can do following:
1. Install the PEM agent in unattended mode. For detail of installing pemagent in unattended mode, user can use command like:

./pem_agent-2.1.0.2-linux-x64.run --prefix /opt/PEM --mode unattended --pghost <pem server hostname> --pguser <pem server username and password> --agent_description <Agent Hostname>

2. Create .pgpass file in root/admin user home directory. Like on linux root home directory is /root.

# cat .pgpass
#hostname:port:database:username:password
*:5432:*:postgres:<your password>

Change the permission on .pgpass:

chmod 600 .pgpass

3. After creating the .pgpass file in home directory, execute following SQL:

/opt/PEM/agent/bin/edb-psql -h <PEM Server hostname> -p 5432 -c "
INSERT INTO pem.server(description,server,port,database) VALUES('New server','IP Address',5432,'postgres',2);" -U postgres

/opt/PEM/agent/bin/edb-psql -h <PEM Server hostname> -p 5432 -c "INSERT INTO pem.server_option(server_id,pem_user,username,server_group) VALUES((SELECT id FROM pem.server WHERE server='IP address' AND active=true),'postgres','postgres','PEM Server Directory');" -U postgres

/opt/PEM/agent/bin/edb-psql -h <PEM Server hostname> -p 5432 -c "INSERT INTO pem.agent_server_binding(agent_id,server_id,server,port,username,database)
VALUES((SELECT id FROM pem.agent WHERE description ='Agent Hostname' AND active=true),(SELECT id FROM pem.server WHERE server='IP address' AND active=true),5432,'postgres','postgres');" -U postgres

After executing above SQLs, you have successfully binded agent with Server for monitoring.

User can also include above steps in shell script and can execute while provisioning new server for their client.

Have Fun!

5 Comments

  1. Thank you so much for the script. Very useful. I was almost lost in PEM, for all the doings by click and tab.. With this script, I’ll get a beer paid by my sysadmin. cheers!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s