Running multiple PostgreSQL versions simultaneously on macOS/Linux
Introduction
Nowadays, as programmers, we have loads of different tools for different purposes. For a web developer specialising in Ruby on Rails apps, such as me, the most important are: the language itself (Ruby), Ruby on Rails framework, Node.js framework and some database (PostgreSQL FTW!).
They all come in variety of versions, and sometimes we do want to have multiple versions installed, and even running, side by side. Fortunately, most of the core tools have their respective version managers, e.g. RVM/rbenv/chruby for Ruby, Bundler for gems, nvm/n for Node and npm/yarn for managing JavaScript packages… And these work pretty well, so we are covered there — we can work on multiple projects, each using the required versions of its dependencies.
The only thing that I feel is being left behind is the database — I’ve never heard about any dedicated solutions for managing database versions (PostgreSQL specifically) since the beginning of my professional career. On the other hand, I’ve been introduced to rbenv on the day one!
But before we continue to the heart of this article, I’d like to answer some questions that I found asking myself in the early days of my webdev path: “Why should I have multiple versions of a given tool installed? Do I really need to bother with such version managers?”. The answer is pretty simple and straightforward: if you are working on a single project at a time, you might not need to bother at all! You could just install the required versions of the dependencies and you would be done.
This might complicate a bit in case you are then switching to a new project, but want to keep the old versions in place. Sometimes you can just move on, install some new dependencies and not worry about the others, and everything works fine… Until it doesn’t. Only in production environment. Major WTF?! moment incoming! This is because sometimes it’s possible to run the app with different dependencies installed across different environments, which might lead to super-weird and hard to track bugs.
The situation complicates even more in case you are working on several projects simultaneously (even if talking about some pet-projects on the side). In many cases, each project requires specific versions of the tools to run properly.
TL;DR You might not need to run multiple PostgreSQL versions if you are working on a single project. If you are working on many projects at once, it might save you from some nasty bugs!
Setup
So how do you setup an environment which enables you to install and use multiple versions of PostgreSQL simultaneously? I’m going to guide you through the process in a few easy steps!
macOS
The easiest way of installing multiple versions of PostgreSQL on macOS is using Homebrew — you should be familiar with this package manager, so I won’t go into details here. You have to tap this repo https://github.com/petere/homebrew-postgresql by running:
$ brew tap petere/postgresqlNow you can install multiple versions of PostgreSQL on your machine:
$ brew install postgresql-9.3
$ brew install postgresql-9.6If you had the default postgresql package already installed, you might encounter some issues later on, so it would be best to get rid of it:
$ brew uninstall postrgresqlLinux
Just install the versioned PostgreSQL packages using your package manager and that’s it! You should still get rid of postgresql if you had it installed before.
Usage
Since we have all the versions we need installed, we now need a nice and easy way of using the one we actually need at the moment. We have a few options:
- explicitly running the desired binaries, e.g.
/usr/local/opt/postgresql-9.3/bin/... - updating our
$PATH - using
brew link -f postgresql-9.3 - using
postgresql-commonpackage
I find the last way the easiest one, so let’s do that! You have to install the postrgresql-common package (for macOS users: it's in the tap we added earlier) and you're set—you have the all the things installed, let's advance!
We now have to create a few database clusters. Please note, that in PostgreSQL term “database cluster” does not mean “group of database servers” — simply put, it’s a set of databases managed by a single server. postgresql-common package provides special wrapper scripts for running server-side operations, such as managing the clusters. You can create a new cluster with pg_createcluster [options] <version> <name>, like that:
$ pg_createcluster 9.3 main
> Creating new cluster 9.3/main ...
> config /usr/local/etc/postgresql/9.3/main
> data /usr/local/var/lib/postgresql/9.3/main
> locale en_US.UTF-8
> port 5432You have to specify the name of the cluster and the version of PostgreSQL the server will be running. Port which the server will listen on is assigned automatically, starting with 5432 by default (if it's available), and then incremented for subsequent clusters.
You can list all your clusters with the following command:
$ pg_lsclusters
> Ver Cluster Port Status Owner Data directory Log file
> 9.3 main 5432 online mtunski /usr/local/var/lib/postgresql/9.3/main /usr/local/var/log/postgresql/postgresql-9.3-main.log
> 9.6 main 5433 down mtunski /usr/local/var/lib/postgresql/9.6/main /usr/local/var/log/postgresql/postgresql-9.6-main.logOutput is pretty self-explanatory. If you want to remove a cluster, there’s pg_dropcluster <version> <name>. You can start/stop/restart/reload them with pg_ctlcluster <version> <name> <action>:
$ pg_ctlcluster 9.6 main startClient commands are run with a special wrapper script pg_wrapper. You just have to specify the cluster (--cluster version/name) when running a given command. Or, if you remember them, you can always use the ports with -p port option:
$ psql --cluster 9.3/main
> psql (9.6.1, server 9.3.15)
> …Notice that psql uses the latest version available, regardless of the server you are connecting to. This is because it’s one of the few client tools that are backwards compatible:
For psql, pg_archivecleanup, and pg_isready, pg_wrapper will always use the binary from the newest PostgreSQL version installed, as these are downwards compatible.
And that’s basically it! Trivial, right? All you have to do now is to update your apps’ configuration files to connect to the correct PostgreSQL server instances. For Rails, it would be updating your database.yml file by setting the proper port.
Other solutions
Just before publishing this article, I’ve found out about asdf version manager—I didn't have time to look at it yet, but I really like the idea of a Swiss Army knife of version management!
As for Docker, I've never really been a fan… It does not run natively on macOS and I find it relatively (to the tool presented) difficult to configure. For this simple use case, I would say that using it would be like using a harvester to mow your lawn. But, if you use Docker in your day-to-day work (I don’t), it might be the most feasible solution for you!
The last (but not least… or maybe?), pgvm, seems to do exactly what we need, but it looks like it's no longer maintained. It might still work, but I haven't tried it, so can't say anything more except "it exists".
References
Bonus
If you find yourself using psql a lot, give pgcli a try! It's an alternative interface with some nice goodies like auto-completion and syntax highlighting!
