How to switch the active PostgreSQL cluster

A how to of switching the active PostgreSQL cluster version to make it bind to port 5432.

Let’s define the active PostgreSQL cluster as the one that binds to port 5432 and having multiple versions running in Ubuntu.

Check with pg_lscluster what is running:

$ sudo pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.5 main    5433 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log
11  main    5432 online postgres /var/lib/postgresql/11/main  /var/log/postgresql/postgresql-11-main.log

And what is the active PostgreSQL version for command line commands, for example pg_dump.

$ pg_dump --version
pg_dump (PostgreSQL) 11.5 (Ubuntu 11.5-1.pgdg16.04+1)

We want to switch and make 9.5 version active (binding to port 5432) and make version 11 bind to port 5433. Stop the running versions and check if the servers are down.

$ sudo pg_ctlcluster 9.5 main stop
$ sudo pg_ctlcluster 11 main stop
$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.5 main    5433 down   postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log
11  main    5432 down   postgres /var/lib/postgresql/11/main  /var/log/postgresql/postgresql-11-main.log

Change the port in the configuration files and list the new config to be sure.

$ sudo mcedit /etc/postgresql/9.5/main/postgresql.conf
change port=5433 to port=5432 
$ sudo mcedit /etc/postgresql/11/main/postgresql.conf
change port=5432 to port 5433

$ grep -H '^port' /etc/postgresql/*/main/postgresql.conf
/etc/postgresql/9.5/main/postgresql.conf:port = 5432
/etc/postgresql/11/main/postgresql.conf:port = 5433

Start the clusters and check what is running

$ sudo pg_ctlcluster 9.5 main start
$ sudo pg_ctlcluster 11 main start

$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.5 main    5432 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log
11  main    5433 online postgres /var/lib/postgresql/11/main  /var/log/postgresql/postgresql-11-main.log

Mission accomplished! Let’s see what is the active PostgreSQL version for command line commands.

$ pg_dump --version
pg_dump (PostgreSQL) 9.5.19

How does this work that the PostgreSQL command line commands automatically adapts to the active version. Because /usr/bin/pg_dump is a symlink to pg_wrapper. And pg_wrapper checks which version is running on port 5432 (or environment variables) to find out what the active version is.

$ ls -al /usr/bin/pg_dump
/usr/bin/pg_dump -> ../share/postgresql-common/pg_wrapper