Architecture overview

Understanding PPEM's architecture is essential for setting it up correctly. PPEM follows a monolithic architecture:

Frontend — a web-based application that users interact with through their browsers.
Backend — a server-side application that processes user requests and performs administrative tasks. We’ll refer to this backend as the Manager.
Repository — a Postgres instance where the Manager stores metadata required for administrative tasks.
Agents— helper components installed on database nodes. They execute tasks as directed by the Manager.

No complex Kubernetes setups or microservices — PPEM is built for practicality and efficiency. Additionally, PPEM can integrate with external components such as LDAP servers, metric storage solutions, log collectors, and backup systems. However, we'll cover those in a future discussion if you're interested.

We'll go through the usual steps: installation, setup, overview, evaluation, and conclusions. Before we proceed, let’s outline what we'll need. In my case, I used two virtual machines (VM):

A VM with Postgres, where we'll install both the PPEM Manager and an Agent — this will serve as the central management node.
A VM with Postgres, where we’ll install only the PPEM Agent — this instance will be managed via PPEM.

I'll assume you’re familiar with setting up virtual machines or containers, so I’ll skip the details on provisioning the environment. In my setup, I used two Debian 12 VMs deployed via corporate automation tools.

Installation

PPEM is available from the official Postgres Pro repository. The first step is to download and run the repository setup script:

wget https://repo.postgrespro.ru/ppem/ppem/keys/pgpro-repo-add.sh
sudo sh ./pgpro-repo-add.sh

Run this on both machines.

Installing and configuring the manager

The PPEM Manager includes a web interface for user interaction. To install both components, run:

apt install ppem ppem-gui

Next, configure the manager by setting up a database for the repository:

sudo -u postgres createuser --pwprompt ppem
sudo -u postgres createdb -O ppem ppem

Verify the connection:

psql -h localhost -U ppem -d ppem

If you encounter errors, check your pg_hba.conf authentication settings.

Now, update the manager configuration file (/etc/ppem-manager.yml) to specify the repository connection details:

repo:  url: "postgres://ppem:password@localhost/ppem"

Start the manager and enable it to run on boot:

sudo systemctl start ppem
sudo systemctl enable ppem

Now, open a browser and navigate to the server where the manager is installed. Use the default login credentials (admin/admin). The welcome page includes setup instructions for Agents, including an API key needed for their configuration.

Installing and configuring the agent

Install the PPEM Agent on both virtual machines:

apt install ppem-agent

Create a dedicated database user for the agent:

sudo -u postgres createuser --pwprompt ppem_agent

Verify the connection:

psql -h localhost -U ppem_agent -d ppem

To configure the agent, edit the /etc/ppem-agent.yml file.

You will need to do two things. First, specify the agent name and connection settings for the manager:

agent.name — a unique name for the agent (if not specified, the server's hostname will be used).
agent.manager.url — the URL for connecting to the manager. The URL must start with a scheme (http or https) and include the API version path; the current version is v1. Example: https://ppem.example.org/v1.
agent.manager.api_key — the API key used to authenticate with the manager.

Second, configure the connection settings for detected database instances. Here, we use the database user previously created for the agent:

agent.instance.connection_defaults.user — the database user.
agent.instance.connection_defaults.password — the database user's password.

Additionally, you can configure the listening address for the HTTP server. By default, the agent listens on all available addresses:

http.server.address — the address for incoming network connections (an empty string enables listening on all addresses).

Next, configure the agent in /etc/ppem-agent.yml:

agent:
  name: "my-favorite-database-server"
  manager:
    url: "http://192.168.23.168/v1"
    api_key: "dcf3975c-8044-4624-a581-842dab8d0158"
  instance:
    connection_defaults:
      user: "ppem_agent"
      password: "ppem_agent_password"
http:
  server:
    address: "192.168.23.168"

Start and enable the agent service:

sudo systemctl start ppem-agent
sudo systemctl enable ppem-agent

The agent will automatically detect local database instances and register them with PPEM. Refresh the web interface to see the discovered instances.

Features overview

Let’s take a look at what PPEM brings to the table. According to the docs, it offers:

Database instance maintenance
Configuration management
Monitoring and observability tools
Backup management

This isn’t the full list, but let’s go through the key points.

Postgres maintenance

Here’s what the description says:

PPEM provides tools for reviewing and monitoring the entire enterprise database infrastructure. It allows you to deploy new database instances and manage their full lifecycle.

And yeah, if you check out the Clusters and Instances sections, you’ll find various tools for both viewing and managing instances and their objects. Some of the key maintenance operations include:

Instance service management — start, stop, and restart database instances
Instance lifecycle management — create new instances, add existing ones, and remove instances
Database object lifecycle management –—handle tablespaces, databases, schemas, tables, indexes, functions, and sequences
Routine database maintenance — run vacuum, gather statistics, and rebuild indexes

Observability & monitoring

Let’s check out what PPEM offers in terms of monitoring:

PPEM provides tools for observing the internal operations of database instances.

So, what do we actually get?

Tools to inspect current activity, based on pg_stat_activity and the pg_stat_progress_* family
Query statistics via pg_stat_statements and pgpro_stats
Activity log for tracking database events
Performance metrics
pgpro_pwr profiler support

PPEM comes with several tools to keep an eye on what’s happening inside your database instances. One of the most frequently used — especially when things start going sideways — is Activity.

If you’ve worked with pg_stat_activity before, the Activity page in PPEM will feel familiar. It gives you a tabular snapshot of what’s happening in the database right now.

Here’s what you can do with it:

Track long-running queries and inspect their execution plans (via pg_query_state)
Identify suspicious sessions with abnormal states
Cancel problematic queries or even forcefully terminate sessions

This is a powerful tool for DBAs, but if you’re new to it, expect a bit of a learning curve — you might want to check the docs first.

Another key tool is SQL Statistics, which provides historical query data and resource usage insights. It relies on either pg_stat_statements or pgpro_stats (depending on what’s installed on the server). This feature helps pinpoint the most resource-intensive queries.

While pg_stat_statements is fairly common, pgpro_stats takes things even further, though it’s more advanced and might require some extra effort — even for experienced DBAs — to navigate efficiently. There’s definitely room for improvement in making this data more accessible.

Some monitoring features in PPEM depend on pgpro-otel-collector, which handles metric and log collection. The Collector gathers data from database instances and stores it either in PPEM or, even better, in OpenTelemetry-compatible storage. To get the full monitoring experience, make sure to install and configure pgpro-otel-collector.

For log analysis, there’s a dedicated Message log page. With built-in filtering, you can quickly search for events within a specific timeframe, making troubleshooting much easier.

All performance graphs are located on a dedicated Metrics page.

Right now, all available charts are packed into a single page, which isn’t the most convenient setup — there’s definitely room for improvement.

If pgpro_pwr is installed in the instance, PPEM can also leverage its capabilities to generate and display performance reports for database instances.

There are a few more observability tools we haven’t covered in detail, including query progress tracking, lock tree analysis, live execution plan viewer (yes, you read that right — live query execution plans!), plan visualization. We’ll leave those for independent exploration or another deep dive in a future post.

PPEM provides multiple tools for different monitoring tasks. Some present data in tabular format, but navigating through them can be challenging — especially if you’re unsure what to look for. There’s definitely potential for improvements in this area. As for graphs and visualizations, the selection is fairly limited, especially compared to the richness of Grafana dashboards. There’s still a long way to go, but the foundation is promising!

Postgres configuration

The documentation keeps it brief:

PPEM provides tools for configuring database instances.

So, what’s actually available?

Viewing and modifying PostgreSQL configuration parameters
Managing authentication rules (pg_hba.conf)
Database role management

This is your standard Postgres configuration editor — you can review and tweak instance settings as needed. That said, any experienced DBA who’s seen their fair share of database tuning will likely spot areas where things could be streamlined or expanded.

Backup management

From the documentation:

PPEM provides tools for configuring and managing database instance backups, as well as deploying new instances from backups.

PPEM relies on pg_probackup for backup operations, but keep in mind — you’ll need to install it yourself. If it’s present on the system, PPEM will detect it and unlock its features, including:

Creating and managing backup storage (both local and S3-compatible)
Running backups on-demand or on a schedule
Restoring instances from backups

At first glance, all the essential features are here. However, DBAs with hands-on experience using pg_probackup in the command line may notice that PPEM’s approach differs from the traditional pg_probackup workflow. For seasoned users, some familiar tools and workflows might be missing, which could take some getting used to.

Conclusion

Reflecting on my early days as a Postgres administrator in 2012, I recall a time when tools like PPEM didn't exist. We had to rely on custom shell scripts and collections of SQL queries to manage and monitor databases. If PPEM had been available back then, it would have made my job significantly easier.

Today, PPEM consolidates many essential tools into a single interface, reducing the need to rely on SSH and psql for routine tasks. While PPEM already offers great functionality, I see room for improvement—some features could be expanded, and others better integrated.

If you've tried PPEM and have feedback, let us know in the comments. Our development team is always looking to enhance the tool, making Postgres administration more efficient and accessible.

Getting to know PPEM 2