Postgres Enterprise Manager (PEM) is designed to allow IT staff to monitor and manage their PostgreSQL and EDB Postgres Advanced Server servers. PEM is capable of monitoring the following areas of the infrastructure:
- Hosts: One or more servers (physical or virtual) and their operating systems.
- Servers: One or more instances of PostgreSQL or EDB Postgres Advanced Server running on a host.
- Databases: One or more databases and the schema objects (tables, indexes etc) within them.
PEM is comprised of a number of individual software components; the individual components are described below. The following architectural diagram illustrates the relationships between the managed hosts and servers and the PEM server and clients.
The PEM Server¶
The PEM server stores metric data collected from each monitored host, server and database. The server is comprised of an instance of PostgreSQL and an instance of the Apache web server, providing web services to the client. PEM utilizes a server-side cryptographic plugin to generate authentication certificates.
We recommended that you use a dedicated machine to host production instances of the PEM backing database. The host may be subject to high levels of data throughput, depending on the number of database servers that are being monitored and the workloads the servers are processing.
The PEM Agent¶
Each PEM agent can monitor one or more physical or virtual machines, locally or remotely. The agent is responsible for the collection of monitoring data from the machine and operating system, as well as from each of the Postgres instances running on the machine. It is also responsible for executing other tasks that may be scheduled by the user (for example, server shutdowns and SQL Profiler traces).
Agents connect to the PEM server using PostgreSQL’s wire-level protocol, using SSL encryption and certificate-based authentication. The agent installer is responsible for registering each agent with the server, and generating and installing the required certificates.
The agent must be able to connect to each database server that it monitors. This connection may be made over a TCP/IP connection (or optionally a Unix Domain Socket on Unix hosts), and may optionally use SSL. The user must configure the connection and authentication to the monitored server; typically using a libpq password file in conjunction with MD5 password based authentication.
Once configured, each agent collects statistics and other information on the host and each database server and database that it monitors. Each piece of information is known as a metric, and is collected by a probe. Most probes will collect multiple metrics at once for efficiency. Examples of the metrics collected include:
- Disk I/O statistics
- Network statistics
- Database server version string
- Database server configuration option (GUC) values
- Table access statistics
- Table and index sizes
A list of PEM probes can be found here.
The PEM Client¶
The PEM client application runs in your choice of browser. The client’s web interface connects to the PEM server, and allows direct management of managed or unmanaged servers, and the databases and schemas that reside on them.
The client allows you to use PEM functionality that makes use of the data logged on the server through features such as the dashboards, the Postgres Log Analysis Expert and Capacity Manager.
The SQL Profiler Plugin¶
You are not required to install SQL Profiler on every server, but you must install and configure SQL Profiler on each server on which you wish to perform a scheduled trace. You may want to install and configure SQL Profiler on development servers. For ad-hoc use, you must install both the SQL Profiler plugin and the client.
The plugin is installed with the EDB Postgres Advanced Server distribution, but must be installed separately for use with PostgreSQL. The SQL Profiler installer is available from the EnterpriseDB website at www.enterprisedb.com.
SQL Profiler may be used on servers which are not managed through PEM, but to perform scheduled traces, a server must have the plugin installed, and must be managed by an installed and configured PEM agent.