Installing Postgres Enterprise Manager in a Cloud Database Cluster

Installing Postgres Enterprise Manager on a Cloud Database Cluster

May 7, 2012

Version 1.0

by EnterpriseDB Corporation
Copyright © 2012 EnterpriseDB Corporation. All rights reserved.

Introduction

Postgres Enterprise Manager™ (PEM) is an enterprise management tool designed to assist database administrators, system architects, and performance analysts in administering, monitoring, and tuning large installations of PostgreSQL and EnterpriseDB Postgres Plus database servers. You can use PEM to monitor and manage a Cloud Database cluster. In this EnterpriseDB tutorial we will demonstrate:

    ● How to install the PEM server on a Cloud Database master node.

    ● How to install the PEM agent on a Cloud Database replica node.

    ● How to register a server with the PEM client, and monitor a Cloud Database cluster.

    ● How to install SQL Profiler on the master node of a Cloud Database cluster.

This tutorial assumes:

    ● You have created a Cloud Database cluster.

    ● You are using a Linux workstation to connect to Cloud Database.

      While this tutorial includes Linux commands and examples, comparable Windows-friendly utilities are available on the web; if you are using a Windows workstation, you should follow the basic steps outlined in the following tutorial, substituting platform-appropriate commands for the commands shown.

If you need more information about obtaining Cloud Database , visit the EnterpriseDB website at:

http://cloud.enterprisedb.com

For more information about obtaining Postgres Enterprise Manager™, visit the EnterpriseDB website at:

This tutorial uses the term Postgres to mean a Postgres Plus Advanced Server or PostgreSQL database.

Required Security Group Modifications

When you create a Cloud Database cluster, the server ports are closed, with the exception of the load balancing port (9999). To install a PEM client, PEM server or PEM agent, and for the PEM components to work together, you must expose selected ports on the cluster.

    ● Before connecting with ssh or scp, you must first modify the Cloud Database security group, opening port 22. When you are no longer using ssh or scp, you can safely disable this security rule.

    ● You must also modify the security group to allow connections from any monitored node to the listener port of the PEM server. The default listener port is 5432; if you are using PEM with a PostgreSQL Cloud Database cluster, you should instead use a listener port address of 5433.

    ● You must also modify the security group to allow connections from the PEM client on port 8080.

    ● You must also modify the security group to allow the PEM client to connect to the listener port of any registered server that you wish to manage or monitor.

For detailed information about modifying a security group, please see Section 12.3, Securing a Cloud Database Cluster in the Cloud Database Getting Started Guide, available through the Cloud Database Dashboard tab.

Installing the PEM Server

The PEM server consists of an instance of PostgreSQL, an associated database for storage of monitoring data, and a server providing web services for the PEM client. The PEM server may reside on a host outside of a monitored Cloud Database cluster, or on the master node of a Cloud Database cluster.

You can use the PEM server graphical installer to install the PEM server on the master node of Cloud Database. The graphical installer not only installs the PEM server, but also installs the PEM server pre-requisites:

    ● Apache/PHP

    ● PostgreSQL 9.0

You can obtain PEM installers from the EnterpriseDB website, at:

Step 1) Copy the PEM server installer to the Cloud Database master node

After downloading the PEM installers, use scp to copy the PEM server installer (see Figure 1.1) to the master node of the Cloud Database cluster:

      scp -i ssh_key pem_server-version-linux.run root@host_name:

Figure 1.1 - Moving a file to Cloud Database.

Where:

      ssh_key specifies the name of the Cloud Database ssh private key file. You can use the Download SSH Key button on the User tab of the Cloud Database management console to download your private key file.

      version specifies the version information of the pem installation file that you are copying to Cloud Database.

      host_name specifies the host name of the master node of the Cloud Database cluster. The host name is located in the DNSNAME column, on the Details panel of the Clusters tab in the Cloud Database console.

      The colon (:) at the end of this command specifies that the file will be copied to the root directory on the cluster's primary node; please note that you can specify a file destination by adding a destination path after the colon.

Step 2) Installation pre-requisites

During the PEM installation process, you will be required to edit Postgres configuration files. You can use vi (already installed on Cloud Database), or you can install a graphical text editor.

To install a graphical text editor, use ssh to connect to the master node of the Cloud Database cluster:

      ssh -i ssh_key root@host_name

Where:

      ssh_key specifies the name of the Cloud Database ssh private key file.

      host_name specifies the IP address of the master node of the Cloud Database cluster.

Then, use the yum package manager to install gedit:

      yum install -y gedit

When the installation completes, exit the ssh session.

Step 3) Connect to Cloud Database, invoking X windows

Connect to the master node of the Cloud Database cluster, specifying the -X flag to invoke X Windows when opening programs with graphical user interfaces:

      ssh -i ssh_key -X root@host_name

Step 4) Update the permissions on the PEM server installer

Before invoking the PEM server installer, you must modify the file permissions:

      chmod u+x pem_server-version-linux.run

Step 5) Invoke the PEM server installer

Use the following command to invoke the PEM server installer:

      ./pem_server-version-linux.run

The PEM Server installer opens on your workstation desktop, as shown in Figure 1.2.

Figure 1.2 - The PEM Server installation wizard.

The PEM server installer will walk you through the installation process. During the installation, the wizard will check for the PEM server pre-requisites, Apache/PHP and PostgreSQL 9.0 (see Figure 1.3). If the installer detects that the pre-requisites are missing, it will invoke the appropriate component installers.

Figure 1.3 - The PEM server installer will check for pre-requisites.

The component installers will walk you through the installation of pre-requisites.

There may be a considerable pause between the time that the Apache/PHP installer completes, and the time that the PostgreSQL installer starts - please be patient.

During the PostgreSQL 9.0 installation, you will be prompted for:

    ● a password for the PEM postgres user; the PEM server will prompt you for this user name and password when the focus returns to the PEM server installer.

    ● a listener port for the PEM server; by default - PostgreSQL listens on port 5432. If you are installing the PEM server in an Advanced Server cluster, accept the default. If you are installing the PEM server in a PostgreSQL cluster, you should specify port 5433 (port 5432 will already be in use by your cluster).

When focus returns to the PEM server installer, and the installer opens the Network Details dialog (shown in Figure 1.4), you can specify the CIDR-formatted address of a node that will be monitored by the PEM server.

Figure 1.4 - The Network Details dialog.

The installer will write an entry to the pg_hba.conf file for the address specified, allowing the agent that resides on that node to connect to the PEM server.

Note that on Cloud Database, you will (most likely) have to manually update the pg_hba.conf file on the PEM server, allowing the server to communicate with each PEM agent and the PEM client.

Figure 1.5 - The PEM server installation is complete.

The installation wizard will inform you when the installation is complete (see Figure 1.5). Click Finish to exit the installer.

Step 6) Edit the pg_hba.conf file to allow connections from the client

By default, the PEM server listens for connections on Port 5432; in addition to modifying pg_hba.conf file to allow connections to the Postgres server, you must modify the cluster's security group (using the AWS Management Console), allowing connections to the server from the IP address on which the PEM client is running.

At the ssh command line, open your text editor:

      gedit

Browse to the location of the PEM server's pg_hba.conf file. By default, on an Advanced Server cluster, the pg_hba.conf file is stored in:

      /opt/PostgreSQL/9.0/data

At the top of the file, you will see entries generated by the PEM server installer during the installation; you must modify the file, specifying the address and authentication method for each connecting PEM agent and PEM client (see Figure 1.6).

Figure 1.6 - The pg_hba.conf file.

For more information about pg_hba.conf file entries, please refer to the PostgreSQL documentation, available from the EnterpriseDB website at:

Step 7) Reload the server's parameters

After saving your modifications to the pg_hba.conf file, you must reload the PEM server's parameters for the changes to take effect. To reload the parameters, navigate to the /etc/init.d directory, and enter:

      ./postgresql-9.0 reload

At this point, the PEM server and a PEM agent should be installed on the master node, and waiting for connections from additional PEM agents or the PEM client.

1.1 Confirming that PEM Services are Running

The PEM client uses Apache/PHP to connect to the PEM server. You can use the ps command to confirm that the server is running on a Cloud database host:

      ps -ef | grep [a]pache

If the server is not running, the ps command will respond with an empty prompt (as shown in Figure 1.7).

Figure 1.7 - An inactive Apache server.

Use the following command to start the Apache/PHP server:

      /etc/init.d/EnterpriseDBApachePhp start

If the Apache server is running as expected, the ps command will display activity (as shown in Figure 1.8):

Figure 1.8 - Confirming an active Apache server.

You can confirm that the PEM server is actively monitoring the expected node with the following command:

      netstat -an | grep port_number

Where port_number is the listener port specified during the PEM server installation.

If the PEM server is running as expected, the netstat command will display activity on the PEM listener port (see port 5432 in Figure 1.9):

Figure 1.9 - Checking for PEM server activity.

If the server is not running enter:

      /etc/init.d/postgresql-9.0 start

The PEM server installer also installs a PEM agent. To confirm that the PEM agent is running, enter:

      service pemagent status

You can confirm that the PEM agent is running with the service command (see Figure 1.10):

Figure 1.10 - Checking for PEM agent activity.

If the PEM agent is not running, start the agent with the command:

      /etc/init.d/pemagent start

Installing the PEM Client

The PEM client is a graphical client application that allows you to manage your Postgres server, and access monitoring data on the PEM server. This program may reside on the PEM server host, or a client workstation (recommended).

After installing the PEM server, you can use the PEM client to monitor the server (and the agent that resides on that cluster node). The PEM Client is installed (by default) with Postgres Plus Advanced Server, and is available via StackBuilder for PostgreSQL users.

PEM client installers are available from the EnterpriseDB website at:

To invoke the PEM client installer on your local workstation:

    1) Modify the privileges of the PEM client installer with the command:
    chmod u+x pem_client-version_number-linux.run

    2) Invoke the installer with the command:
    ./pem_client-version_number-linux.run

The PEM client installer will walk you through the installation of the client; if you already have a PEM client installed on your workstation, the installer will upgrade your installation (if required).

To open the PEM client from a workstation running Advanced Server 9.1, navigate through the Applications menu to the Postgres Plus Advanced Server 9.1 menu, and select Postgres Enterprise Manager. The client window opens as shown in Figure 1.11.

Figure 1.11 - Provide a product key to continue.

The PEM client is fully functional for a 45 day trial period; after that, usage requires a valid product key. For information about purchasing a product key, please see the list of contacts at the end of this tutorial.

Select the OK button, or click Cancel to start the PEM client trial period. The PEM client opens as shown in Figure 1.12.

Figure 1.12 - The Postgres Enterprise Manager client window.

1.2 Connecting to the PEM Server

To connect to the PEM server, select PEM Server Logon from the File menu; the PEM Server Logon dialog opens (see Figure 1.13).

Figure 1.13 - The PEM Server Logon dialog.

Use the fields on the PEM Server Logon dialog to describe the connection to the Cloud Database node on which the PEM Server resides:

    ● Specify the public IP address of the PEM Server host in the Host field.

      The IP address of the host is a subset of the name specified in the DNSNAME column of the Details panel on the Clusters tab of the Cloud Database management console. For example, if the complete DNS name is ec2-23-21-163-207.compute-1.amazonaws.com, the IP address of the PEM host is 23.21.163.207.

    ● Specify the Port on which the Apache/PHP server is listening; by default, the Apache/PHP server listens on port 8080.

    ● If you have defined SSL on the PEM Server, and wish to enable it, check the Use SSL? box.

    ● Specify the name of the database superuser for the PostgreSQL 9.0 installation on which PEM stores information. By default, this user is postgres.

    ● Specify the Password associated with the PostgreSQL user.

    ● Check the box next to Login at startup? to instruct the PEM client to connect to the PEM server automatically when the client opens.

When you've completed the PEM Server Logon dialog, click the OK button to connect to the PEM server. The PEM Global Overview opens, as shown in Figure 1.14.

Figure 1.14 - The PEM Enterprise Dashboard.

Public vs. Private IP Addresses

When you logon to the PEM server (using the PEM Server Logon dialog), you must provide the public IP address of the Cloud Database node that hosts the PEM server. When the PEM client connects, it adds a server definition for that server to the tree control, displayed under the PEM Server Directory node.

Because Cloud Database has multiple network adapters to choose from, occasionally, the PEM client will use the private IP address when defining the server properties. If the PEM client has difficulty connecting to a server, you may need to edit the server configuration, replacing the private IP address of the PEM server host with the public IP address of the node on which the PEM server resides.

The public and private IP addresses of your server are displayed on the Instances page of the AWS management console; highlight a server name in the list to display the IP addresses on the Description tab below.

Modifying Connection Properties

To edit the IP address that the PEM client uses when connecting to a monitored server, right click on the server name in the tree control, and select Properties. The Properties dialog opens as shown in Figure 1.15.

Figure 1.15 - The Postgres Enterprise Manager server properties dialog.

Edit the Host field, replacing the private IP address of the host on which the PEM server resides with the server's public IP address. Click OK to save your edits, and exit the Server dialog.

After modifying the connection details, you can right click on the server node and select Connect to access the server. After authenticating yourself to the server, you can view and manage the database objects that reside on the master node of the Cloud Database cluster.

Installing a PEM Agent on a Replica Node

PEM agents are responsible for executing tasks and reporting statistics from the host and monitored Postgres instances to the PEM server. A PEM agent must reside on each node of the Cloud Database that you wish to monitor. You can use the PEM agent graphical installer to install the PEM agent on a replica node, or on the master node of a Cloud Database cluster (if your PEM server resides outside of the cloud).

Step 1) Copy the PEM agent installer to the Cloud Database node

Use scp to copy the PEM agent installer to the node of the Cloud Database cluster that you wish to monitor:

      scp -i ssh_key pem_agent-version-linux.run root@host_name:

Figure 1.16 - Moving a PEM agent installer to Cloud Database.

Where:

      ssh_key specifies the name of the Cloud Database ssh private key file.

      version specifies the version information of the pem agent installation file that you are copying to Cloud Database.

      host_name specifies the IP address of the server you wish to monitor.

      The colon (:) at the end of this command specifies that the file will be copied to the root directory.

Step 2) Connect to Cloud Database, invoking X windows

Use ssh to connect to the node on which you are installing the PEM agent, specifying the -X flag, to invoke X Windows when opening programs with graphical user interfaces:

      ssh -i ssh_key_file.pem -X root@host_name

Step 3) Update the permissions on the PEM agent installer

Before invoking the PEM agent installer, you must modify the file permissions:

      chmod u+x pem_agent-version-linux.run

Step 4) Invoke the PEM agent installer

Use the following command to invoke the PEM agent installer:

      ./pem_agent-version-linux.run

The PEM agent installer opens on your workstation desktop, as shown in Figure 1.17.

Figure 1.17 - The PEM agent installation wizard.

The PEM agent installer will walk you through the installation process.

Figure 1.18 - The PEM agent installer will confirm the connection details.

When prompted, provide connection information for the PEM (see Figure 1.18):

    ● If the PEM server resides on the master node of a Cloud Database cluster, enter the private IP address of the host of the PEM server in the Host field.

      The Private IP Address is displayed on the Description tab of the My Instances page of the AWS management console when you highlight the server name (see Figure 1.19).

Figure 1.19 - Locating the Private IP Address.

    ● Enter the database superuser name (specified during the PEM server installation) in the User Name field; the default value is postgres.

    ● Enter the password associated with the PEM superuser (specified during the PEM server installation) in the Password field.

    ● Enter the Port on which the PEM server is listening; the default value is 5432.

The Description on the Agent Details dialog will default to contain the Private DNS name of the node on which the agent resides (as displayed on the AWS management console). This is the agent name that will be displayed on the PEM client tree control - you may wish to change this name to a more user-friendly or descriptive name.

Figure 1.20 - The PEM agent installation is complete.

The PEM agent installation wizard will inform you when the installation is complete (see Figure 1.20. Click Finish to exit the installer.

After completing the PEM agent installation, you will need to modify the pg_hba.conf file on the PEM server node to allow connections from the PEM agent. Please see Step 6 of Installing the PEM Server (in this tutorial) for information about modifying the pg_hba.conf file on the server node.

After establishing a connection with the PEM server, the PEM agent will be displayed in the PEM client Enterprise Dashboard (see Figure 1.21)

Figure 1.21 - The PEM client Enterprise Dashboard displays the new agent.

Installing and Configuring SQL Profiler

SQL Profiler captures performance statistics and query plans for SQL statements executed during a trace session (see Figure 1.22). You can then use the resulting performance profile to analyze and improve a monitored server's performance.

Figure 1.22 - A SQL Profiler trace session.

SQL Profiler cannot be installed on a read-only replica node; it's functionality is limited to transactions performed against the master node of a Cloud Database cluster. If you have a specific SQL statement that you wish to profile, you should instruct the client executing that statement to connect to the DBPORT of the master node (shown on the Details panel of the Clusters tab) for the duration of the trace session.

By default, SQL Profiler is installed in Cloud Database clusters that use an Advanced Server 9.0 or 9.1 server; if you are using an Advanced Server cluster, use the steps described in Configuring SQL Profiler on an Advanced Server Cluster to configure your SQL Profiler installation.

If you are using a PostgreSQL cluster, you must run the SQL Profiler installer before performing the configuration steps. See Section 1.5, Installing and Configuring SQL Profiler in a PostgreSQL Cluster for detailed instructions.

1.3 Configuring SQL Profiler on an Advanced Server Cluster

To enable SQL Profiler on the master node of an Advanced Server Cloud Database cluster:

    1. Connect to the server using ssh, and (with your choice of editor) edit the postgresql.conf file, adding $libdir/sql-profiler to the shared_preload_libraries parameter. The postgresql.conf file is located in:

      /opt/PostgreSQL/data/postgresql.conf

    2. Restart the Postgres server with the command:

      /etc/init.d/ppas-9.1 restart

    3. Source the environment variables with the command:

      source /opt/PostgresPlus/9.1AS/pgplus_env.sh

    4. Invoke EDB-PSQL with the command:

      edb-psql -U enterprisedb edb

    5. Use the EDB-PSQL command line to invoke the SQL Profiler installer with the command:

      edb=#-\i-/opt/PostgresPlus/9.1AS/share/contrib/sql-profiler.sql

After performing these steps, you need to register the server as described in Section 1.4.

1.4 Registering a Server

Before you can use SQL Profiler to capture a trace session, you must register the monitored server with the PEM client, providing the connection properties for the Postgres server on the New Server Registration dialog. To access the New Server Registration dialog, select Add Server from the File menu.

    Figure 1.23 - The Server Registration dialog.

When the New Server Registration dialog (shown in Figure 1.23) opens, use the fields on the Properties tab to supply information about the monitored server:

    ● Provide a descriptive Name for the new server.

    ● Specify the name or IP address of the server's Host.

    ● Specify the Port number that the server is monitoring.

    ● Specify the name of the maintenance database in the Maintenance DB field.

    ● Specify the Username and Password that PEM should use when connecting to the server.

To ensure that the new server will be managed by PEM, check the Store on PEM Server checkbox.

    Figure 1.24 - The PEM Agent tab.

Use the PEM Agent tab (see Figure 1.24) to specify an agent binding for the new server:

    ● Use the drop-down list box in the Bound Agent field to select the PEM Agent that is installed on the server.

    ● Specify the host server’s public IP address in the Host field.

    ● Specify the Port number of the managed system.

    ● Use the Database field to specify the name of the Postgres Plus database to which the agent will initially connect.

    ● Provide the name of the role that agent should use when connecting to the server in the Username field.

    ● Provide the password that the agent should use when connecting to the server in the Password field, and verify it by typing it again in the Confirm Password field.

When you've completed the New Server Registration dialog, click OK.

    Figure 1.25 - The Cloud Database server is added to the tree control.

The name of the new server will be displayed under the PEM Server Directory node in the tree control (see Figure 1.25).

1.5 Installing and Configuring SQL Profiler in a PostgreSQL Cluster

The SQL Profiler installer for PostgreSQL is available from the EnterpriseDB website at:

http://www.enterprisedb.com/download-postgres-enterprise-manager

SQL Profiler installers are version specific - you must download the SQL Profiler installer for PostgreSQL 9.1, for either a Linux x86-32 or Linux x86-64 bit server. The engine version of your cluster is displayed on the Details panel of the Clusters tab of the Cloud Database management console.

Step 1) Copy the SQL Profiler installer to the master node of the Cloud Database cluster

Use scp to copy the SQL Profiler installer to the master node:

      scp -i ssh_key sqlprofiler-version-linux.run root@host_name:

Where:

      ssh_key specifies the name of the Cloud Database ssh private key file.

      version specifies the version specific information of the SQL Profiler installation file that you are copying.

      host_name specifies the IP address of the master node of the Cloud Database cluster.

      The colon (:) at the end of this command specifies that the file will be copied to the root directory on the cluster's primary node.

Step 2) Connect to Cloud Database, invoking X windows

Connect to the Cloud Database master node, specifying the -X flag to invoke X Windows when opening programs with graphical user interfaces:

      ssh -i ssh_key_file.pem -X root@host_name

Step 3) Update the permissions on the SQL Profiler installer

Before invoking the SQL Profiler installer, you must modify the file permissions:

      chmod u+x sqlprofiler-version-linux.run

Step 4) Invoke the SQL Profiler installer

Use the following command to invoke the SQL Profiler installer:

      ./sqlprofiler-version-linux.run

The SQL Profiler installer opens on your workstation desktop, as shown in Figure 1.26.

Figure 1.26 - The SQL Profiler installation wizard.

The SQL Profiler installer will lead you through the installation of the SQL Profiler plugin.

Figure 1.27 - The SQL Profiler installation is complete.

When the installation completes (see Figure 1.27), you are ready to configure SQL Profiler.

Configuring SQL Profiler on a PostgreSQL Cluster

To enable the plugin on the master node of a Cloud Database cluster:

    1. Connect to the server using ssh, and edit the postgresql.conf file, adding $libdir/sql-profiler to the shared_preload_libraries parameter. On a PostgreSQL cluster, the postgresql.conf file is located in:

      /opt/PostgresPlus/data/postgresql.conf

    2. Restart the Postgres server with the command:

      /etc/init.d/postgresql-9.1 restart

    3. Source the environment variables

      source /opt/PostgreSQL/9.1/pg_env.sh

    4. Start a session with the psql client:

      psql -U postgres postgres

    5. Invoke the SQL Profiler installer with the command:

      postgres=#-\i-/opt/PostgreSQL/9.1/share/postgresql/contrib/sql-profiler.sql

After performing these steps, you are ready to register the server, as described in Section 1.3.

Postgres Enterprise Manager Resources

For more information about Postgres Enterprise Manager, and to download PEM documentation, please visit the EnterpriseDB website at:

http://www.enterprisedb.com/products-services-training/products/postgres-enterprise-manager

Sales

For information about purchasing Postgres Enterprise Manager (PEM), please contact your nearest sales office:

Bedford, MA, USA

EnterpriseDB Corporate Headquarters and Sales

1-781-357-3390

1-877-377-4532 (US only)

The Hague/The Netherlands

EMEA

+31 70 891 8451

Japan

EnterpriseDB K.K. Waseda Univ. Incubation Center

+81-3-5488-6007

UK

Enterprise Performance Centre/EnterpriseDB UK Ltd.

+44 (0) 1235 227276

Pakistan

EnterpriseDB PK Limited

+92-51-8358874

India

EnterpriseDB Software India Private Limited

+91-20-30589500/01

Support

There are several ways to get help with a Cloud Database or PEM problem. If you have purchased support, you can log a support ticket:

If you have not purchased support, and would like to, you can view your support options at:

You can also find free help on a wide variety of topics in the EnterpriseDB User Forums, at:

Postgres documentation and helpful tutorials are available from the Cloud Database bookshelf, located on the Dashboard tab of the management console.