Read more

Controlling multiple PostgreSQL installations on Debian/Ubuntu

Claus-Theodor Riegg
March 31, 2017Software engineer at makandra GmbH

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>
Illustration UI/UX Design

UI/UX Design by makandra brand

We make sure that your target audience has the best possible experience with your digital product. You get:

  • Design tailored to your audience
  • Proven processes customized to your needs
  • An expert team of experienced designers
Read more Show archive.org snapshot

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
Claus-Theodor Riegg
March 31, 2017Software engineer at makandra GmbH
Posted by Claus-Theodor Riegg to makandra Operations (2017-03-31 08:26)