This article will demonstrate how to list the PostgreSQL clusters on your Linux system, and how to switch the active cluster.
PostgreSQL is one of the most popular relation database servers. Multiple versions can be installed on the same host, so being able to switch between them is pretty useful. Read on to find out how to do it.
What is a Cluster
A PostgreSQL cluster is a collection of PostgreSQL databases with a shared configuration, managed. This configuration includes things like the executable that manages the cluster (allowing for different versions of PostgreSQL for different clusters), the port, users, and so on.
You can run multiple PostgreSQL clusters alongside each other on the same system. This is commonly done to isolate different deployments, and, often, when upgrading PostgreSQL so that the previous installation can be migrated to the new version.
Listing PostgreSQL Clusters
To list the PostgreSQL clusters installed on your system, use the pg_lsclusters command:
The output will look like this (with your own clusters listed, of course):
Ver Cluster Port Status Owner Data directory Log file 9.1 main 5433 online postgres /var/lib/postgresql/9.1/main /var/log/postgresql/postgresql-9.1-main.log 11 main 5432 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
PostgreSQL Default Port
The default port for PostgreSQL is port 5432 – so we’ll consider the cluster running on this port the active or default cluster for the system.
Other clusters will be set to run on a non-default port, or they can be shut down entirely.
Checking the Active Cluster
You can find the version details of the active cluster by running the pg_config command:
Similarly, you can find the path to the folder where the executable is located by running:
Switching Active Cluster / Changing PostgreSQL Port
Below are the instructions for changing the active cluster in PostgreSQL:
Stop the Running Server
Before you alter any configurations, stop each cluster on your system:
sudo pg_ctlcluster VERSION_NUMBER main stop
In my case (running versions 9.1 and 11) I ran the following two commands:
sudo pg_ctlcluster 9.1 main stop sudo pg_ctlcluster 11 main stop
Then confirm they have been stopped by again running:
Your clusters should now have a status of ‘down’ to indicate that they have been stopped:
Ver Cluster Port Status Owner Data directory Log file 9.1 main 5433 down postgres /var/lib/postgresql/9.1/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
Update the PostgreSQL Port Configurations
Next, you will need to update the configuration for each cluster. As mentioned earlier, the default port for PostgreSQL is 5432, and we are considering the PostgreSQL cluster running on the default port as the ‘active’ cluster.
You will need to locate the configuration file for the cluster you wish to make active, and change the value of the port in it using the nano text editor:
sudo nano /etc/postgresql/VERSION_NUMBER/main/postgresql.conf
Scroll down until you see the port configuration line and change it so it reads:
Then, locate the configurations for your other clusters and change the port configuration value to something that is not 5432. Incrementing from 5433 is a sensible way of doing this. Each cluster should have a unique port number, so make sure that they are all different.
If all of your PostgreSQL configuration files are found in their default location under /etc/postgresql, you can list the port value for each configuration by running:
grep -H '^port' /etc/postgresql/*/main/postgresql.conf
Start/Restart PostgreSQL Clusters to Apply New Configuration
Run the following command for each cluster to start them again:
sudo pg_ctlcluster VERSION_NUMBER main start
If you do not need multiple clusters running, you can only start the ones you require.
Confirm the change
You can now confirm that the default/active cluster has been changed by running the below commands:
pg_config --version pg_config ----bindir
The PostgreSQL command line tools will always default to connecting to the server running on 5432.