Postgres has the ability to create clusters of databases that run on the same machine concurrently. One thing that I did not know about postgres is that you can also run multiple versions of postgres at the same machine concurrently along with the mutliple clusters mentioned earlier. But how can one view information about all these instances and clusters and how can we connect to each of them and how can one upgrade the clusters running on one version to another?

I am running Debian on my personal computer and the rest of this post is going to be a very brief description of how I managed to upgrade and tidy up after my postgres instances running on my local computer. Please note that this is not an in depth post nor I suggest in any way this approach for a live / production upgrade of postgres.

So let’s get started!

First of all let’s see which versions of postgres are running on our box. As a super user execute the following command

#ps -afe | grep postgres

This will show you the list of processes currently running on your system that contain the word postgres. This is part of the output I got when I originally run this command on my box.

postgres  4460     1  0 10:05 ?        00:00:00 /usr/lib/postgresql/9.1/bin/postgres -D /var/lib/postgresql/9.1/main -c config_file=/etc/postgresql/9.1/main/postgresql.conf
postgres  4528     1  0 10:07 ?        00:00:00 /usr/lib/postgresql/9.0/bin/postgres -D /var/lib/postgresql/9.0/main -c config_file=/etc/postgresql/9.0/main/postgresql.conf
postgres  4650     1  0 10:10 ?        00:00:00 /usr/lib/postgresql/9.3/bin/postgres -D /var/lib/postgresql/9.3/main -c config_file=/etc/postgresql/9.3/main/postgresql.conf

This means I am running three different versions of postgres on my box, namely version 9.0, 9.1 and 9.3. Ok, but where am I connecting when I use psql? This is something easy enough to notice (or miss) when using psql command line. Switching to the postgres user and simply typing psql will connect to postgres and show the following message

psql (9.3.0, server 9.1.9)

This means that the version of psql is 9.3.0 but I am connecting to the server running version 9.1.9. But why is this happening and where does psql get this information from? Reading through the manual will tell us that psql is going to connect by default to the server running on port 5432 which is the default port for postgres. This can be changed by simply providing a different port number to connect to which will allow us to connect to a different instance of postgres. For example issuing the following command will have a different output in my system

$psql -p 5433
psql (9.3.0, server 9.0.6)

Right, but how can we easily tell at which port each of the postgres servers is running at? Here we are going to introduce some commands I have found very useful lately. There is a command called pg_lsclusters that allows us to view a listing of all database clusters, along with information about their version, port, name, status and data directory. Running this command on my box produced the following outcome

Ver Cluster Port Status Owner    Data directory               Log file
9.0 main    5433 online postgres /var/lib/postgresql/9.0/main /var/log/postgresql/postgresql-9.0-main.log
9.1 main    5432 online postgres /var/lib/postgresql/9.1/main /var/log/postgresql/postgresql-9.1-main.log
9.3 main    5434 online postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log

So, when using psql from the command line psql makes the assumption that you wish to connect to the default port 5432 (this is the port of the default postgres server on your system) and you wish to connect to the default postgres cluster which is always named main.
Another command that gives us control over the status of each of the database clusters is the pg_cltcluster which allows us to stop, start and restart any of the available clusters in our system. For example if you wished to stop the 9.0 main cluster we would issue the following command

pg_ctlcluster 9.0 main stop

This command will switch the status of the cluster from online to down and we would no longer be able to connect to it. The 9.0 postgres server will be stopped as well if no more online clusters run out of it.

So, how can we upgrade now from one cluster to another? It is strongly recommended to backup all your data and database settings before attempting to upgrade to a newer version because as we all know upgrades are always the easiest way to loose data! But let’s assume that you have hapilly backed up everything and you are now ready to migrate from 9.0 to 9.1. The command to use is pg_upgradecluster and simply typing it in your prompt will show you the default usage of the command.

$ pg_upgradecluster
Usage: /usr/bin/pg_upgradecluster [OPTIONS]   []

If you wish to upgrade a cluster I found it easier to stop the cluster in order to do the upgrade, check that everything is fine with your databases and then drop the old cluster in order to clean up your box.

If we examine the man pages we will see that if a cluster has been succesfully migrated from one version to the other then the main cluster in the latest version will have the default port whereas the old cluster will be given another port. This means that from that point on the old cluster will still exist in the system, have all the data it had when we performed the migration but it will no longer be used as the default one to connect to. So, if we had another look at the output of pg_lsclusters from my box we will see that the 9.0 main cluster has been migrated to the 9.1 version but I had never actually deleted the cluster from my system. Before we continue let’s delete the cluster and partially clean up my box. A quick note here: while stopping a cluster make sure that you do not have any active connections to it otherwise issuing the stop command via

$ pg_ctlcluster 9.0 main stop

will just wait for all connections to terminate before it actually shuts down the cluster. Dropping a cluster is an action that can be performed via

$ pg_dropcluster 9.0 main

After that it is time to upgrade cluster 9.1 main to the new version I just installed which is 9.3. Following the same procedure as before I stopped the 9.1 main cluster and I tried to issue the

$ pg_upgradecluster 9.1 main

command which since I did not provide a new target version will try to migrate everything to the latest version found in my system which is 9.3. Running this command will throw the following error

Error: target cluster 9.3/main already exists

which is something that we should have actually expect to happen since in our cluster listing we have indeed seen that the we do have a 9.3 main cluster already created. This cluster was created when we installed the latest version of postgres and has three databases which were created by default which are the postgres, template0 and template1. This cluster can be safely deleted as long as we have not created any user databases in it. So, we should issue the following commands now

$ pg_dropcluster 9.3 main
$ pg_upgradecluster 9.l main

These will eventually allow us to do the migration of the 9.1 main cluster to the 9.3 main cluster and you should be able to see information about each of the databases being migrated. If you now do a pg_lsclusters you will see that the newly created 9.3 main cluster will now be the one using the default port and therefore running psql command line will connect to the new version! Finally, after you have checked that all the databases are there and everything works as expected you can issue the following command to drop the 9.1 main cluster.

$ pg_dropcluster 9.1 main

And now you are done!