Controlling multiple PostgreSQL installations on Debian/Ubuntu

Hint

This applies only to distributions based on Debian.

Requirement

You need to setup the PostgreSQL Apt Repository Show archive.org snapshot first.

If you have multiple Postgres versions installed on a system and want to start/stop/restore/.. them separately you need to use pg_ctlcluster

CopyUsage: /usr/bin/pg_ctlcluster <version> <cluster> <action>

For example

Copy/usr/bin/pg_ctlcluster 47 main start

With systemd you can start/stop the services via:

Copysystemctl <action> postgresql@<version>-<cluster>.service

For example

Copysystemctl start postgresql@14-main.service

Details

<version>

The Version of PostgreSQL to manage. Must be installed. ;)

<cluster>

The cluster to use. This is nothing more than a working data directory and a configurationof PostgreSQL stored in the correct location. Usually you find the datadir in /var/lib/postgresql/$VERSION/$CLUSTERNAME and the config in /etc/postgresql/$VERSION/$CLUSTERNAME/.

<action>

start | stop | restart | reload

Available versions and clusters, Serverstatus

Copy~# pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
10  main    5433 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
11  main    5432 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
12  main    5435 online postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
13  main    5434 online postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log
14  main    5437 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
15  main    5436 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log
16  main    5438 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log

Creating or deleting clusters

create

This will run initdb and create an new cluster

Copy# pg_createcluster --locale $LOCALE --start <version> <cluster>
pg_createcluster --locale en_US.UTF-8 --start 16 main

delete

Copy# pg_dropcluster --stop <version> <cluster>
pg_dropcluster --stop 10 main

Managing the default version and cluster with the ~/.postgresqlrc

You can place the file ~/.postgresqlrc in the home directory of the postgres user do decide which PostgreSQL version is used by default. Even the binaries like pg_dump are chosen depending on the version configured in this file.

Copy# VERSION CLUSTERNAME DATABASE
14 main postgres

Connecting to clusters

The usual commands support the --cluster parameter which you can use to connect to the correct cluster. For e.g.

Copypsql --cluster 14/main