Postgres Enterprise Manager or PEM is a comprehensive monitoring and management solution for PostgreSQL and EDB Postgres Advanced Server (EPAS) from EnterpriseDB. It is a distributed application with a number of components that can be installed in many different configurations. The following sections explain what those components are, what their purpose is, and how they work.
Note: In PEM parlance, a managed server refers to a database server that is being monitored and managed by PEM through a PEM Agent. An unmanaged server is one that the PEM user interface may be used to connect to and manage, but is not registered with a PEM Agent.
Agent
The PEM Agent is typically installed on each managed database server host in the environment. It is used to collect metrics about the database server and the underlying host that it is monitoring, and to execute tasks when required (for example, to update the database server configuration and restart it). Probes that collect metrics and all other tasks that an agent might execute on a host or database server are all scheduled; essentially, the PEM Agent is an execution engine for scheduled jobs.
In the ideal setup, there will be an agent running on every host (physical or virtual) that is running a database server, however it is possible for agents to monitor database servers remotely, albeit without the ability to monitor the underlying host or to execute tasks that require access to the underlying host operating system.
PEM Agents run under a superuser account by default. This is required to allow monitoring of certain operating system metrics or to execute tasks that require such privileges. It is possible to run agents under a more restricted account, but this will prevent the use of some functionality.
Agents communicate with the PEM Server by connecting to the Postgres database component (described in detail below), typically on port 5432 or 5444. When agents are first installed, a suitable user account is used to connect to the PEM Server to perform the registration process. A role in the PEM Server’s database is created for the agent, along with a certificate that will be used for authentication. When the agent service is started it will use this role and certificate to authenticate. Agents connect to the PEM Server, thus allowing them to run on hosts on the inside of a NAT firewall.
Agents have a configuration file that is created during the installation process. This file includes options for a number of purposes, including:
- Identification of itself, e.g. what role to use when connecting to the PEM server.
- Configuration of various tuning parameters, such as connection reuse, polling wait times and more.
- Enabling/disabling of certain functionality that may conflict with security policies, such as the ability to run custom batch/shell tasks.
- Enabling/disabling of functionality used only in the master agent that runs as part of the PEM Server.
The PEM Agent is designed to be extremely crash resistant; the service started by the operating system is actually a watchdog process that spawns a worker process that is responsible for doing the work of the agent. Should the worker process crash or exit, the watchdog will immediately restart it. The watchdog process is designed to be as simple as possible to minimise the risk that it will crash.
Server
The PEM Server is a collection of components that work together to host a PEM installation:
- A PostgreSQL or EPAS database server with the SSLUtils extension
- A PEM Agent instance
- An Apache HTTPD web server
Technically, all three components can be hosted on different servers, however typical installation will have all three running on the same server, or with the PEM Agent and Apache HTTPD server on one host and the database on another.
Database Server
The database server is used to store all the monitoring data, much of the system configuration, and scheduling information used to execute probes and other tasks. It is the central hub of PEM.
As noted above, agents connect to PEM using roles defined in the database server. This is also the case for end users who are also roles in the database. This allows most authentication mechanisms supported by Postgres to be used to authenticate end users, and it also allows monitoring data from different database servers to be automatically secured using Postgres’ Row Level Security functionality. Role Based Access Controls (RBAC) are also tied into the Postgres role system; PEM will check for membership in appropriate roles before allowing users to perform certain operations or use particular tools.
Data is stored in a number of different schemas with the PEM server database. The pem schema is used to store system configuration data such as agent and database server registrations, probe and alert definitions and configuration, and system parameters. The pemdata schema is used to store the most recently collected metrics from each probe in the system, with database being stored in one table for each probe. This data is automatically copied into the pemhistory schema which contains similar tables for each probe. Keeping a copy of the most recently recorded metric values in the pemdata schema allows for much faster access to the data when only the most recent values are required to render a chart or graph. Some probes may also be configured such that they only store the most recent data and no history at all.
The database server, through the use of the SSLUtils extension, also manages its own certificates used for authentication of agents. A Certificate Authority (CA) certificate is generated uniquely for each PEM Server installation. This is then used to generate a certificate for the PEM Server’s database server, as well as for all agents that are registered with the server.
Agent
The PEM Agent that is part of the PEM Server performs all the same functions that it does on any managed host (i.e. running probes and executing tasks), providing self-monitoring for PEM. In addition it typically also performs a number of other additional functions which are enabled automatically in the agent’s configuration file:
- Alert processing; checking the collected metric data to see if it matches any of the defined alert conditions and if so, taking appropriate action.
- Spoolers; managing queues of notifications, typically of alert conditions. These may be SMTP email, SNMP traps, or Nagios passive service checks.
- Garbage collection; cleanup and removal of old data that is no longer required from the PEM database.
The PEM Agent running as part of the PEM Server will always have an ID of 1.
Apache HTTPD Server
The Apache HTTPD Server (usually known simply as the web server) is used to host the user interface and REST-like APIs (or web application) for PEM. Both are implemented in a Python application written using the Flask framework, which is hosted in Apache using mod_wsgi. The web application communicates with other servers and processes using different methods:
- The psycopg2 driver is used to communicate with the PEM database server and managed database servers utilising the PostgreSQL wire protocol.
- REST-like APIs are used to communicate with EDB Ark.
The user interface is implemented in HTML 5 and Javascript and is served from the web application to the client browser. Ajax requests are made from the user interface to the backend server utilising the REST-like APIs implemented in Python.
SQL Profiler Plugin
The SQL Profiler Plugin is an optional component of PEM that can be installed on any managed or unmanaged database server that is registered with a PEM Server. The plugin is used by the SQL Profiler tool in PEM to monitor and log every operation that the database server performs for analysis.
The SQL Profiler plugin is designed to incur as little overhead as possible on the database server being monitored to minimise the chances of the monitoring itself skewing the results collected. It does this by writing data to files in the data directory of the monitored server, utilising one file per server backend to eliminate the need for file locking. Data is written in CSV format to minimise the need for any processing whilst the server is being profiled.
Conclusion
Postgres Enterprise Manager is a complex application offering a vast array of monitoring and management features for PostgreSQL and EDB Postgres Advanced Server database servers. It has a flexible architecture, designed to maximise deployment options, efficiency and extensibility.
NOTE: This article was written when PEM 7.11 was the current version, and applies to all PEM 7 release to date. Architectural changes may be made in future releases of PEM.