Managing Multiple PostgreSQL Instances on Ubuntu/Debian

Introduction

Those DBAs who are experts in one database system look for other database systems to have “similar features”. It is a human tendency to look at any new technology and compare it with a world they are familiar with. Most of the time, I keep addressing Oracle DBAs who are looking for similar features or setup in PostgreSQL, but this time it is for MySQL DBA. MySQL historically ships mysqld_multi to manage multiple instances of MySQL on a server. Even though this is not a core feature in MySQL, but rather a wrapper, I understand that it is something widely used and my colleague Fernando blogged about it.

In a discussion a few months back, one of my friends asked how PostgreSQL manages multiple instances and I agreed to write on that as it will be useful for anyone who looks for a similar setup. In short, the answer is that the PostgreSQL community manages it using wrapper scripts and the Linux service manager. During this time, systemd started becoming a standard among Linux distros and every Linux package started relying on it for service management. To my surprise, some of the MySQL packages started removing mysqld_multi from the bundle and started moving towards systemd as the way to manage multiple instances. So everything is getting converged to systemd in the new world. In this blog, I want to show how this can be done on Ubuntu/Debian Linux because all necessary wrapper scripts already exist on those distros as part of postgres-common package which gets installed by default with every PostgreSQL installation. Nothing prevents us from doing a similar wrapper on other Linux distros also.

Default Installation

The default installation of PostgreSQL from apt repo of postgresql.org will contain postgresql-common backage for Debian/Ubuntu.

By default, a PostgreSQL instance/cluster will be created with name “main” which uses the default port of PostgreSQL 5432.

All the configuration files including the PostgreSQL parameter file sits under the /etc/postgresql directory.

postgres-common gives a wrapper pg_ctlcluster which is wrapper on the top of pg_ctl

However, many of the files including security certificates are accessible only by the root user. The integration with systemd allows an easy solution to start and stop as follows:

Multiple Instances

Adding a new PostgreSQL is as simple as executing pg_createcluster with the version of the PostgreSQL and clustername.

Now let us examine the above output and understand what is happening:

1. New datadirectory : /var/lib/postgresql/11/standby1 is initialized. Remember that default instance had data directory at /var/lib/postgresql/11/main
2. Initdb is called with most of the default parameter values
3. Now onwards pg_ctlcluster can be utilized for starting and stopping Instance/Cluster like pg_ctlcluster 11 standby1 start
4. Execution of sudo systemctl daemon-reload is recommended for ensuring proper registration of service with systemd
5. Next available port: 5433 is automatically selected for the new instance

The automatic values taken by pg_clustercreate can be overridden by specifying explicit values as a parameter as follows:

The above command will create a PostgreSQL 11 instance with name “anotherdb” with data directory at /home/postgres/adb and listening at TCP/IP port 5439.

The start command for pg_ctlcluster shows a more reliable way to start the same instance using systemd.

Stopping the instance also can be done in either way. For example:

or

So far we have seen instances of the same version, but we are not limited to the same version. If we need to create an instance of other PostgreSQL version, we just need to install the PostgreSQL binaries for the same.

Just like the PostgreSQL 11 version discussed, this also will create a “main” instance/cluster by default.

We can add more clusters using the new version:

Now running pg_lscluster reveals all the postgresql instances running:

Summary

The major difference is that postgres-common tools keep all configurations in a directory structure under /etc/postgres, while MySQL world deals with single configuration files with multiple sections as documented. For example all the configuration files for PostgreSQL 11 instance “anotherdb” will be under: /etc/postgresql/11/anotherdb. There are mainly 3 configuration files which decide the behavior of service:

• Environment file contains environment variables which are to be applicable for PostgreSQL instance
• pg_ctl.conf contains cluster specific options which need to be passed to pg_ctl
• start.conf decides the startup behavior whether an automatic startup or manual startup is required