Postgres Enterprise Manager; I love it when a plan comes together

Postgres Enterprise Manager; I love it when a plan comes together's picture
Author: Dave Page

After 9 months of heads-down work, I'm glad to finally be able to talk about the project I and a number of colleagues here at EnterpriseDB have been working on... Introducing Postgres Enterprise Manager! Postgres Enterprise Manager, or PEM as we tend to call it, is based on the Open Source pgAdmin project which I started over thirteen(!) years ago to give users a graphical tool for managing and developing their Postgres databases. PEM builds on pgAdmin by adding a number of features for sysadmins, DBAs and developers with a focus on management of multiple servers, adding features such as: - A centralised directory of servers allows all users in the organisation to quickly and easily manage any server. - Monitoring and logging of hundreds of different metrics from database objects, the Postgres server, and the host/operating system. - Over 130 different alert templates that may be used to build customised alerts to warn when aspects of your database go outside of the norm. A number of preconfigured alerts help get you up and running quickly and easily. - A Capacity Manager that allows you to generate reports based on the metrics collected from your databases, Postgres servers and hosts/operating systems. Metrics from different aspects of your system can be charted together, allowing you to correlate database events with operating system activity for example. Linear trend analysis features allow you to project usage trends into the future to help with capacity planning. - The Postgres Expert analyses your Postgres servers and database schemas, advising on possible misconfigurations or sub-optimal designs. This "DBA in a box" can analyse your entire Postgres deployment in seconds. - A number of Performance Monitoring Dashboards give the DBA and sysadmin a global overview of the state of the database servers, with the ability to drill down and examine the status of individual hosts, Postgres servers and databases at a glance. - A SQL Profiler for developers working with Postgres. This tool allows users to capture workloads running on their server and examine each query in detail, viewing the query plan and resource/execution statistics for each individual query, and aggregate statistics for each different query executed with the workload captured. Poorly running queries can also be run through the Index Advisor when work working with Postgres Plus Advanced Server advising on and optionally creating new indexes to improve query performance. PEM uses a multi-tier architecture. Each managed host runs a copy of an efficient agent (written in C++, and available on Linux, Windows and Solaris) which executes tasks and reports metrics from the host/operating system and one or more Postgres servers running on the host, back to the PEM server. The PEM server is an instance of PostgreSQL 9.0 typically run on a dedicated host (running Windows or Linux), which includes various additional software components including functionality for managing SSL certificates and a dedicated instance of the PEM agent responsible for tasks such as alert processing and garbage collection. The PEM client runs on users Windows or Linux workstations and provides the user interface to the system allowing the user to connect to the PEM server and use all the PEM features, or connect to unmanaged servers as you would with pgAdmin. One feature that can be used on servers that aren't managed by PEM (for example, a server on a laptop) is the SQL Profiler. All that's required to use the SQL Profiler is a plugin to the Postgres server that you wish to profile which hooks into the server to log the trace data - ideal for developers. As far as I'm aware, PEM is the first management tool for Postgres that offers anything even close to this feature set, and we're already working on adding more for the next version, but while we're hacking on that why not give the first version a try.