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.
1 2 | $ 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.
1 2 3 4 5 6 7 | $ 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.
1 2 3 | $ 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.
1 2 3 4 5 6 7 8 9 10 11 | $ 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
1 2 3 | $ 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:
1 2 | $ 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | $ 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:
- New datadirectory : /var/lib/postgresql/11/standby1 is initialized. Remember that default instance had data directory at /var/lib/postgresql/11/main
- Initdb is called with most of the default parameter values
- Now onwards pg_ctlcluster can be utilized for starting and stopping Instance/Cluster like pg_ctlcluster 11 standby1 start
- Execution of sudo systemctl daemon-reload is recommended for ensuring proper registration of service with systemd
- 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:
1 | $ 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.
1 2 3 | $ 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:
1 | pg_ctlcluster 11 anotherdb stop |
or
1 | 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.
1 | $ sudo apt install postgresql-10 |
Just like the PostgreSQL 11 version discussed, this also will create a “main” instance/cluster by default.
1 2 | 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:
1 | $ pg_createcluster 10 pg10test |
Now running pg_lscluster reveals all the postgresql instances running:
1 2 3 4 5 6 7 | $ 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:
1 2 3 | $ ls -R /etc/postgresql/11/standby1 /etc/postgresql/11/standby1: conf.d environment pg_ctl.conf pg_hba.conf pg_ident.conf postgresql.conf start.conf |
- 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
Interesting! mysqld_multi is a very old and well known binaries to manage multiple instances of mysql service running on the same server, we do use for our spoof envs. Good to have similar tool on Postgres as well but the only thing is we would have a redundant configuration whereas on mysqld_multi it’s inherited from the default block if not specified. Nice ?
Hi Jobin Augustine, how can I access all clusters in differents clients inside my network, pg_hba.conf not work for clusters.
Hi Omar, are you are asking about multi-host PostgreSQL server clusters?. If that is the case, clients may need a proxy which can be configured using HAProxy OR same can be achieved using multi-host connection string which we are already blogged about.
“cluster” is a word used in PostgreSQL world in multiple meaning, if the question is about single host, each PostgreSQL instance will be available in different ports and each will have their own pg_hba.conf file.