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
Usage: /usr/bin/pg_ctlcluster <version> <cluster> <action>
For example
/usr/bin/pg_ctlcluster 47 main start
With systemd you can start/stop the services via:
systemctl <action> postgresql@<version>-<cluster>.service
For example
systemctl 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
~# 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
# pg_createcluster --locale $LOCALE --start <version> <cluster>
pg_createcluster --locale en_US.UTF-8 --start 16 main
delete
# 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.
# 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.
psql --cluster 14/main