How to Manage and Monitor Your Existing PostgreSQL Servers

We are excited to announce support for PostgreSQL, probably the world’s most advanced open source RDBMS. ClusterControl can now be used to monitor database metrics, queries and schedule backups. With this new addition, ClusterControl now supports MySQL with its main variations MariaDB, Percona XtraDB (single or clustered), MongoDB and PostgreSQL. Companies can manage all these databases through one unified interface.

At the time of writing, the following features are supported for PostgreSQL:

• Standalone PostgreSQL servers, v9.x
• Monitor and schedule backups
• Query monitor
• Configuration management
• Host monitoring

In this blog post, we are going to show you on how to manage and monitor your existing PosgtreSQL servers via ClusterControl. We have two standalone PostgreSQL servers running in our infrastructure and would like to add them into ClusterControl. Here’s the setup:

Installing ClusterControl 1.2.9

If you already had ClusterControl installed, ensure you update it to version 1.2.9 by following these upgrade instructions. Otherwise, here are the steps for a fresh install.

1. On the ClusterControl host, download the installation script here, add executable permission and run the installation script:

$ wget https://severalnines.com/downloads/cmon/install-cc.sh
$ chmod u+x install-cc.sh
$ ./install-cc.sh

2.  Follow the installation wizard. At the end of the installation, you should see the following:

=> ClusterControl installation completed!
Open your web browser and point it to http://192.168.50.100/clustercontrol and
enter an email address and new password for the default Admin User.

3. Open ClusterControl UI at http://192.168.50.100/clustercontrol and set the admin user email and password.

PostgreSQL

Before importing the PostgreSQL servers into ClusterControl, ensure you meet the following requirements:

• The PostgreSQL hosts are accessible via passwordless SSH from the ClusterControl host
• Servers with the same cluster ID are grouped together in the UI. If you are importing several PostgreSQL nodes under the same cluster ID, ClusterControl assumes all nodes are accessible using the same specified information in Add Existing Server/Cluster dialog. 
• You must have the PostgreSQL client program installed on all the DB nodes
• The DB nodes must run on the same operating system distribution as the ClusterControl node
• To store backups on the ClusterControl node, the DB nodes must have netcat installed

In some installations, PostgreSQL is installed with non-standard init script name, for example:

$ service postgresql-9.4 restart

We need to create a symbolic link for that script so ClusterControl is able to detect it using the standard name:

$ ln -s /etc/init.d/postgresql-9.4 /etc/init.d/postgresql

Ensure you can check the server status using the following command: 

$ service postgresql status
postgresql (pid  39940) is running...

Ensure the PostgreSQL superuser has a password:

$ su - postgres
$ psql
psql> ALTER USER postgres WITH ENCRYPTED PASSWORD 'new_password';

Copy the SSH key used by the ClusterControl node to the target PostgreSQL servers to setup passwordless SSH:

$ ssh-copy-id -i ~/.ssh/id_rsa 192.168.50.121
$ ssh-copy-id -i ~/.ssh/id_rsa 192.168.50.122

Finally, verify that the ClusterControl node can access the database nodes:

$ ssh 192.168.50.121 "hostname"
$ ssh 192.168.50.122 "hostname"

Importing PostgreSQL into ClusterControl

From the ClusterControl UI, go to Add Existing Server/Cluster and specify the following information:

Click on Add Cluster to start the importing process. ClusterControl will connect to each of the specified hosts, one at a time, using SSH and start collecting information about the node. All commands will be performed locally on that node by the CMON process, so there is no need to install the PostgreSQL client on the ClusterControl node.

Once completed, the database will be listed in the database cluster list, similar to screenshot below:

Your PostgreSQL servers are now being monitored by ClusterControl. Note here that cluster refers to a group of 2 standalone PostgreSQL servers.

Managing PostgreSQL with ClusterControl

Server Load

From the Overview page, you can see the server load and cache hit ratio presented in graphs:

Host/operating system statistics are shown under the the Nodes tab:

Database status and variables are listed under Performance tab:

Query Monitor

You can inspect the running queries directly with details from the Query Monitor tab:

Backups

Before scheduling backups, it is recommended to configure the backup default directory and retention period at Settings > Backup > Backup Directory. 

Then go to Backup > Schedule and configure the backup schedule:

That’s it. We welcome any feedback or suggestions on how to improve ClusterControl for PostgreSQL!