Managing Multiple PostgreSQL Installations with pgenv

pgenv is another pearl from theory. It is a bash single script that allows you to download, build, start and stop (as well as nuke) several PostgreSQL installations within the same host.
It is worth noting that pgenv is not, at least now, an enterprise-level PostgreSQL management tool, rather an easy way to keep test instances clean and organized. It can be very useful to keep several clusters on which doing experiments, testing, and so on.

I first discovered pgenv reading this blog post by David, and I thought it was cool to have a single script to help me manage several environments. I must be honest, this is not the first tool like this I have seen for PostgreSQL, but somehow it caught my attention. I then cloned the repository and start using it. And since I’m curious, I read the source code. Well, ehm, bash? Ok, it is not my favourite shell anymore, but surely it can speed up development while shorting the code with respect to more portable shells.

pgenv works with a command-oriented interface: as in git or other developer-oriented tools you specify a command (e.g., build) and optionally a specific PostgreSQL version to apply the command to. pgenv works on a single cluster at time, by linking and unlinking the specific instance directories (binary and PGDATA) so that it always knows where to find PostgreSQL commands. You can read the documentation for more details.

My Contribution to pgenv

After reading the code, I decided to provide a few improvements to the script, so I forked the repository and issued a few pull requests. The most complex, long and I hope useful one is the implementation of an available command.

The available command automatically downloads the list of available PostgreSQL versions, group them by major version number (according to the changes from 10 ongoing) and shows them to the user. Then I also added the capability to filter by version numbers, so that the user will not get always the long list of all PostgreSQL versions, but only the ones he is interested into.

So why did the implementation of the command take so long? First of all, because I made several minor mistakes, or inaccuracies, and I had to fix them of course. Second, because of a problem with temporary files that made the script less portable, so I had to implement back again the grouping by using associative arrays (I did not want to introduce another Perl dependency).

I have to say that David is a great mentor, and even a simple project like this helped me learning more and more skills.

An example workflow

Enough blather, let’s see pgenv in action and got thru an example workflow. Please note that some output could be different because, at the time of writing, I’m still submitting new contributionms.

Check everything is fine

I’ve added the check command just to check that pgenv can work in your environment, so once installed, running the check command can help you find problems:
% pgenv check
[OK] make:      /usr/bin/make
[OK] curl:      /usr/local/bin/curl
[OK] patch:     /usr/bin/patch
[OK] tar:       /usr/bin/tar
[OK] sed:       /usr/bin/sed
[OK] perl:      /usr/local/bin/perl
Everything seems fine!

Choosing a version to build

Suppose I want to build one of the 10 series version, I can use the new available command to get the full list of versions that pgenv can download, but that would be a rather long listing (after all, PostgreSQL has a looong history), so I can pass a major version to the available command to see only the 10 releases:
% pgenv available 10
             Available PostgreSQL Versions
========================================================

                     PostgreSQL 10
    ------------------------------------------------
     10.0    10.1    10.2    10.3    10.4    10.5 
If I’m in doubt, or if I want to see more releases, I can filter them specifying a major release on the command line. As an example, to get back in time and keep up to date:
% pgenv available 10 7.4
             Available PostgreSQL Versions
========================================================

                     PostgreSQL 7.4
    ------------------------------------------------
     7.4     7.4.1   7.4.2   7.4.3   7.4.4   7.4.5  
     7.4.6   7.4.7   7.4.8   7.4.9   7.4.10  7.4.11 
     7.4.12  7.4.13  7.4.14  7.4.15  7.4.16  7.4.17 
     7.4.18  7.4.19  7.4.21  7.4.22  7.4.23  7.4.24 
     7.4.25  7.4.26  7.4.27  7.4.28  7.4.29  7.4.30 

                     PostgreSQL 10
    ------------------------------------------------
     10.0    10.1    10.2    10.3    10.4    10.5   

Build a version

The build command downloads and compiles the chosen version:
% pgenv build 10.5
...
PostgreSQL, contrib, and documentation installation complete.
PostgreSQL 10.5 built
I can further iterate the process for other versions:
% pgenv build 9.6.10
...
PostgreSQL, contrib, and documentation installation complete.
PostgreSQL 9.6.10 built

Use a version

Let’s see what choice of PostgreSQL do I have installed on the machine:
% pgenv versions
       10.5    pgsql-10.5
     9.6.10    pgsql-9.6.10
None of the two above versions have been selected as default version, in order to use a version the use command must be issued:
% pgenv use 9.6.10
The files belonging to this database system will be owned by user "luca".
This user must also own the server process.
...
Logs are in [/home/luca/tmp/pgsql/data/server.log]
The very first time a database is selected its PGDATA directory is properly initialized by initdb. The PGDATA directory is set to pgsql/data, while the cluster has been linked to pgsql` directory:
% ls -l pgsql
lrwxr-xr-x  1 luca  luca  12 Aug 30 06:59 pgsql -> pgsql-9.6.10
If I inspect again the installed versions, an asterisk will mark the currently selected version as the one in use:
% pgenv versions
       10.5    pgsql-10.5
 *   9.6.10    pgsql-9.6.10

% pgenv version 
9.6.10
The versions command (mind the s) shows all installed versions, while version shows the currently selected one.

Start and Stop

The start and stop commands can be used to activate and deactivate the currently selected cluster:
% pgenv start
PostgreSQL 9.6.10 is already running

% pgenv stop 
waiting for server to shut down.... done
server stopped
PostgreSQL 9.6.10 stopped

Clear and Nuke

The clear command unsets the currently selected version, that is un-use a version:
% pgenv clear
PostgreSQL 9.6.10 cleared

% pgenv versions
       10.5    pgsql-10.5
     9.6.10    pgsql-9.6.10
The remove command nukes the specified version, removing all the files and database content.
% pgenv remove 9.6.10
PostgreSQL 9.6.10 removed

% pgenv versions     
       10.5    pgsql-10.5

There is more

There are other commands and usage tricks, please read the documentation for a deeper understanding of this tool.

The article Managing Multiple PostgreSQL Installations with pgenv has been posted by Luca Ferrari on August 30, 2018