Database Management & Monitoring for PostgreSQL 12

A few months ago we blogged about the release of PostgreSQL 12, with notable improvements to query performance (particularly over larger data sets and overall space utilization) among other important features. Now, with the ClusterControl 1.7.5 version, we’re glad to announce support for this new PostgreSQL version.

This new ClusterControl 1.7.5 version comes with many new features for managing and monitoring your database cluster. In this blog, we’ll take a look at these features and see how to deploy PostgreSQL 12 easily.

Easily Deploy PostgreSQL 12

To perform a new installation of PostgreSQL 12 from ClusterControl, just select the “Deploy” option and follow the instructions that appear. Note that if you already have a PostgreSQL 12 instance running, then you need to select the “Import Existing Server/Database” instead.

When selecting PostgreSQL, you must specify User, Key or Password, and port to connect by SSH to your PostgreSQL hosts. You also need the name for your new cluster and if you want ClusterControl to install the corresponding software and configurations for you.

Please check the ClusterControl user requirement for this step here.

After setting up the SSH access information, you must define the database user, version, and datadir (optional). You can also specify which repository to use. In this case, we want to deploy PostgreSQL 12, so just select it and continue.

In the next step, you need to add your servers to the cluster you’re going to create.

When adding your servers, you can enter IP or hostname.

In the last step, you can choose if your replication will be Synchronous or Asynchronous.

You can monitor the status of the creation of your new cluster from the ClusterControl Activity Monitor.

Once the task is finished, you can see your new PostgreSQL 12 cluster in the main ClusterControl screen.

Once you have your cluster created, you can perform several tasks on it, like adding a load balancer (HAProxy, Keepalived) or a new replica, and also different management or monitoring tasks.

PostgreSQL 12 Database Management

As you probably know, using ClusterControl you can perform different management tasks like add/remove load balancers, add/remove slave nodes, automatic fail-over and recovery, backups, create/modify advisors, and even more.

Schedule Maintenance Mode

One of the new ClusterControl management features is the option to schedule maintenance mode for the database cluster. If you need to modify something in your environment or if for some reason you need to schedule a maintenance window, you can set it with ClusterControl.

Go to ClusterControl -> Cluster Actions -> Schedule Maintenance Mode, to enable the maintenance window for all the cluster.

After enabling it, you won’t receive alarms and notifications from this cluster during the specified period.

In case you will work over one specific node, you can enable this maintenance mode just for that node and not for all the cluster by using the “Schedule Maintenance Mode” in the Node Actions section.

PostgreSQL User Management

Now, in the ClusterControl 1.7.5 version, you’ll be able to manage users/roles for your PostgreSQL cluster. Go to ClusterControl -> Select Cluster -> Manage -> User Management.

Here you can see all the accounts with the privileges assigned, and you can create a new one, or modify/edit an existing account.

Now, let’s see how to monitor this new PostgreSQL version by using ClusterControl.

PostgreSQL 12 Database Monitoring

Monitoring is a must in all environments, and databases aren’t the exception. If you select your cluster in the ClusterControl main screen, you’ll see an overview of it with some basic metrics.

But probably this is not enough to see what is happening in your database cluster. So if you go to ClusterControl -> Select your Cluster -> Dashboards, you can enable this agent-based dashboard to monitor your database in more detail.

Once it is enabled, you’ll have detailed information from both the database and the operating system side.

This dashboard method is useful to see, in a friendly way,  if everything is going fine.

You can also take advantage of the old monitoring features like query monitor, performance, advisors, and more features for PostgreSQL or different database technologies.

Conclusion

PostgreSQL 12 comes with many improvements to query performance and new features. If you’re looking for a quick way to give it a try, ClusterControl can help you to deploy, manage and monitor it in an easy way.