When it comes to our PostgreSQL customers we basically have two types: There are customers that do not want to have a subscription. For these types of customers we go with plain community PostgreSQL and a choice of other open source components for supporting backup and recovery, high availability and monitoring/alerting. The other type of customer usually already has a subscription for the underlying Linux operating system and wants to have the same supported model for their PostgreSQL deployment. For these types of customers we usually recommend EnterpriseDB. One advantage of EnterpriseDB is, that you get all the tools from one vendor and that these tools are integrated into the management solution that you get with an EDB subscription. In this post we’ll not dig into how these tools need to be setup, but rather have a look at how these tools look like, how they integrate and what you can do with them.
When you go with community PostgreSQL and it comes to monitoring, you have plenty of choices. Selecting the right tool for your environment or using an already existing monitoring solution in your company can be a challenging task. When it comes to EnterpriseDB to you get PEM (Postgres Enterprise Manager):
This is how the screen looks like when login: You have a general overview over your PEM agents and the PostgreSQL instances which have been added to the PEM directory. This gives you a raw idea on how your PostgreSQL setup is currently operating. If something is shown in the “Agents Down”, “Server Down” or “Alerts” section, you know immediately that you might have an issue. In general PEM works as many other monitoring solutions: You need to deploy the PEM agents on the hosts you want to have monitored (can be a host with or without a PostgreSQL instance) and the agents then report the statistics to the central PEM repository (which is a PostgreSQL instance). On top of that PEM repository database sits the PEM server which is responsible for the browser based graphical user interface.
In the lower area of the home screen you will see the latest alerts (these are the default alerts and thresholds, you will anyway need to adjust that):
Up to now this is nothing special and every serious monitoring solution for PostgreSQL comes with some sort of that information. What makes PEM a great choice is the integration of the other EDB tools. What you can see in the upper left corner of the screen is, that there is an EDB BART (Backup and recovery tool) server configured into PEM:
That kicks off a full backup which will be stored on the BART server. But even for looking at the existing backups you do not need to logon to the BART server anymore. PEM allows you to view that directly in the console:
Restoring backups to the same or to another server works from the console as well (as long as the PEM Agent and the EDB BART are installed on that server and password-less ssh connectivity is configured):
That’s it for the ad-hoc backups. Scheduling backups from PEM is the next great feature. Without PEM you usually do that via cron and need a script that sends notifications if the backup fails for whatever reason. With PEM you can just do it over the console:
From now on backups are scheduled and we do not need to worry about scheduling the backups with our own scripts. What you definitely should do in addition, is to setup an “Email Group” for notifications:
Conclusion: I must say that PEM improved quite well over the last years. It still takes some time until you get used to the interface and you know where to find the stuff you are looking for. Once you know the integration of BART works really well and I am sure a lot of people will appreciate that. Having a central place where you can manage your PostgreSQL backups in a graphical way really adds value for people not so deeply into PostgreSQL. And even for people that work with PostgreSQL every day a single entry point can quite save some time if you need to get an overview quickly.
In the next post we’ll look at the integration of EDB EFM (Failover Manager) into PEM.