Read more

controlling multiple PostgreSQL installations on Debian/Ubuntu

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

This applies only to distribusions based on Debian

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

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

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

For e.g.

/usr/bin/pg_ctlcluster 9.5 main start

With systemd you can start/stop the services via:

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

For e.g.

systemctl start postgresql@10-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
9.3 main    5433 down   postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log
9.5 main    5432 down   postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log
9.6 main    5434 down   postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-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 9.5 main

delete

# pg_dropcluster --stop <version> <cluster>
pg_dropcluster --stop 9.5 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
9.6 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 11/main
Posted by Claus-Theodor Riegg to makandra Operations (2017-03-31 08:26)