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 I had 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 article, 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.

$ dpkg -l | grep postgresql-common
ii postgresql-common 201.pgdg18.10+1 all PostgreSQL database-cluster manager

Default Installation

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

$ sudo apt install postgresql-11
Reading package lists... Done
...
The following additional packages will be installed:
  libllvm7 libpq5 libsensors4 pgdg-keyring postgresql-client-11 postgresql-client-common postgresql-common ssl-cert sysstat
Suggested packages:
...

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

$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
11  main    5432 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log

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

$ ls -R /etc/postgresql
/etc/postgresql:
11

/etc/postgresql/11:
main

/etc/postgresql/11/main:
conf.d  environment  pg_ctl.conf  pg_hba.conf  pg_ident.conf  postgresql.conf  start.conf

/etc/postgresql/11/main/conf.d:

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

$ pg_ctlcluster 11 main status
pg_ctl: server is running (PID: 15544)
/usr/lib/postgresql/11/bin/postgres "-D" "/var/lib/postgresql/11/main" "-c" "config_file=/etc/postgresql/11/main/postgresql.conf"

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:

$ sudo systemctl start postgresql@11-main
$ sudo systemctl stop postgresql@11-main

Multiple Instances

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

$ pg_createcluster 11 standby1
Creating new PostgreSQL cluster 11/standby1 ...
/usr/lib/postgresql/11/bin/initdb -D /var/lib/postgresql/11/standby1 --auth-local peer --auth-host md5
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/11/standby1 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctlcluster 11 standby1 start

Warning: systemd does not know about the new cluster yet. Operations like "service postgresql start" will not handle it. To fix, run:
  sudo systemctl daemon-reload
Ver Cluster  Port Status Owner    Data directory                  Log file
11  standby1 5433 down   postgres /var/lib/postgresql/11/standby1 /var/log/postgresql/postgresql-11-standby1.log

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:

$ pg_createcluster 11 anotherdb -d /home/postgres/adb -p 5439

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.

$ pg_ctlcluster 11 anotherdb start
Warning: the cluster will not be running as a systemd service. Consider using systemctl:
  sudo systemctl start postgresql@11-anotherdb

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

pg_ctlcluster 11 anotherdb stop

or

sudo systemctl stop postgresql@11-anotherdb

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.

$ sudo apt install postgresql-10

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

Ver Cluster   Port Status Owner    Data directory                  Log file
10  main      5434 online postgres /var/lib/postgresql/10/main     /var/log/postgresql/postgresql-10-main.log

We can add more clusters using the new version:

$ pg_createcluster 10 pg10test

Now running pg_lscluster reveals all the postgresql instances running:

$ pg_lsclusters
Ver Cluster   Port Status Owner    Data directory                  Log file
10  main      5434 online postgres /var/lib/postgresql/10/main     /var/log/postgresql/postgresql-10-main.log
10  pg10test  5435 online postgres /var/lib/postgresql/10/pg10test /var/log/postgresql/postgresql-10-pg10test.log
11  anotherdb 5439 online postgres /home/postgres/adb              /var/log/postgresql/postgresql-11-anotherdb.log
11  main      5432 online postgres /var/lib/postgresql/11/main     /var/log/postgresql/postgresql-11-main.log
11  standby1  5433 online postgres /var/lib/postgresql/11/standby1 /var/log/postgresql/postgresql-11-standby1.log

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