PostgreSQL Version Managers

The ability to manage multiple PostgreSQL versions, similar to using Ruby Version Managers, is handy for client work, different applications using different databases, and so forth. Using a version manager also allows you to install the latest version of PostgreSQL without having to wait for a platform build which saves you time and allows you to work unhindered. This is a super power.

One way to manage multiple PostgreSQL versions is to use Homebrew but this can be slow since you have to wait for Homebrew to catch up to recent PostgreSQL releases. Additionally, switching between different versions, using Homebrew, is much more cumbersome and requires effort.

This is where pgenv comes into play which solves these ailments. We’ll look at how to install, setup, and use pgenv so you can install and manage multiple PostgreSQL versions as desired.

Table of Contents

Install
Setup
Configuration
Workflow
Troubleshooting
Resources
Conclusion

Install

To get started, you’ll want to use a XDG configuration in order to keep your Dotfiles clean. pgenv doesn’t support XDG by default, which is unfortunate, but we can make this work by installing as follows:

git clone https://github.com/theory/pgenv $HOME/.cache/pgenv

We’re using the XDG cache folder because pgenv will build and install different PostgreSQL versions within the pgenv folder. Some of the configuration will go here too, despite being slightly awkward, because pgenv doesn’t provide a way to place the configuration within your XDG config (i.e. $HOME/.config/pgenv).

Setup

Once pgenv is installed, you’ll need to teach your shell where to find pgenv. I use Bash but you can adopt to your specific shell. Assuming you’re using Bash, you only need the following to your .bashrc:

# Necessary to use the XDG cache.
export PGENV_ROOT="$HOME/.cache/pgenv"

# Necessary to ensure `pgenv` and all PostgreSQL CLIs are on your path.
export PATH="$HOME/.cache/pgenv/bin:$HOME/.cache/pgenv/pgsql/bin:$PATH"

With the above in place, you’ll be able to run pgenv. The PostgreSQL CLIs won’t be available until you install a version (more on this soon).

Configuration

With pgenv setup, you need to apply a default configuration by running the following:

pgenv config init

This’ll create a default $HOME/.cache/pgenv/config/default.conf file. I won’t detail all of what’s in this file since the generated documentation is self-describing but you’ll need to customize further especially if you want SSL and UUID support. Here are the changes you’ll want to apply:

# Configure PostgreSQL build flags.
PGENV_CONFIGURE_OPTIONS=(
  --enable-thread-safety
  --with-bonjour
  --with-llvm
  --with-openssl
  --with-uuid=e2fs
  PKG_CONFIG_PATH=$HOMEBREW_PREFIX/opt/icu4c/lib/pkgconfig
  LLVM_CONFIG=$HOMEBREW_PREFIX/opt/llvm/bin/llvm-config
  CLANG=$HOMEBREW_PREFIX/opt/llvm/bin/clang
  "CPPFLAGS=-I$HOMEBREW_PREFIX/opt/icu4c/include -I$HOMEBREW_PREFIX/opt/openssl/include -I$HOMEBREW_PREFIX/opt/readline/include"
  "CFLAGS=-I$HOMEBREW_PREFIX/opt/icu4c/include -I$HOMEBREW_PREFIX/opt/openssl/include -I$HOMEBREW_PREFIX/opt/readline/include"
  "LDFLAGS=-L$HOMEBREW_PREFIX/opt/icu4c/lib -L$HOMEBREW_PREFIX/opt/openssl/lib -L$HOMEBREW_PREFIX/opt/readline/lib"
)

# Path to the log file (must match your XDG cache path).
export PGENV_LOG="$HOME/.cache/pgenv/pgsql/data/server.log"

# Script to execute when initdb finishes (and the server has not started yet).
export PGENV_SCRIPT_FIRSTSTART="$HOME/.config/pgenv/initialize"

# Ensures configuration is preserved.
export PGENV_WRITE_CONFIGURATION_FILE_AUTOMATICALLY=no

The PGENV_CONFIGURE_OPTIONS variable is the most important and must not be preceded by an export statement or the options won’t be applied. Here’s the break down from top to bottom:

--enable-thread-safety: Ensures client libraries can make thread-safe concurrent connections.
--with-bonjour: Handy for macOS environments by allowing PostgreSQL servers to advertise their presence on the network, making it easier for clients to discover and connect to available PostgreSQL instances.
--with-llvm: Enables support for LLVM-based Just-In-Time (JIT) compilation which enhances query performance by allowing PostgreSQL to compile frequently executed code into machine-specific optimized instructions at runtime.
--with-openssl: Enables secure connections using OpenSSL.
--with-uuid=e2fs: Enables UUID support for primary keys and allows you to run migrations which enable UUIDs. Example enable_extension "uuid-ossp".
PKG_CONFIG_PATH: Ensures you have unicode support as installed and managed by Homebrew (see icu4c for details).
LLVM_CONFIG and CLANG: Ensures LLVM is configured as installed and managed by Homebrew (see LLVM for details).
CPPFLAGS, CFLAGS, LDFLAGS: All of these build flags are necessary to build PostgreSQL with unicode, OpenSSL, and readline support.

The PGENV_LOG variable must point to your XDG cache location. Otherwise, you’ll not be able to start PostgreSQL.

The PGENV_SCRIPT_FIRSTSTART variable allows you to run custom code once a specific PostgreSQL version has been installed and used for the first time (i.e. pgenv use <version>). This is handy for automating additional setup after the database has been initialized and the server started. For example, here’s what I’m using:

#! /usr/bin/env bash

set -o nounset
set -o errexit
set -o pipefail
IFS=$'\n\t'

psql --username postgres \
     --command "CREATE ROLE $USER WITH SUPERUSER CREATEDB CREATEROLE LOGIN PASSWORD '';"
psql --username postgres --command "CREATE DATABASE $USER;"

(
  cd "$HOME/.cache/pgenv/pgsql/data"

  if [[ ! -e "server.key" && ! -e "server.crt" ]]; then
    openssl req -new \
                -x509 \
                -days 365 \
                -nodes \
                -text \
                -out server.crt \
                -keyout server.key \
                -subj "/CN=postgres"

    chmod 0600 server.key
  fi
)

psql -c "ALTER SYSTEM SET ssl = 'on';"
psql -c "ALTER SYSTEM SET ssl_cert_file = 'server.crt';"
psql -c "ALTER SYSTEM SET ssl_key_file = 'server.key';"
psql -c "ALTER SYSTEM SET ssl_min_protocol_version = 'TLSv1.3';"

pgenv restart

The above performs the following steps:

Configures Bash for strict use.
Ensures the current user (i.e. $USER) is created with the ability to manage databases.
Creates a local SSL key and certificate but only if they don’t exist.
Enables SSL support.
Restarts the server to pick up the changes.

You can customize further as desired but all of this will ensure you can immediately build, install, and use a PostgreSQL version.

Lastly, the PGENV_WRITE_CONFIGURATION_FILE_AUTOMATICALLY is critical to prevent your configuration from being overwritten each time you remove or install the same and/or different version of PostgreSQL.

With this default configuration in place, you now have a foundation for installing multiple versions because they’ll inherit from this default. To provide specialized configurations for different versions, you can use pgenv config <command> <version>. For example, when pgenv config init was used above, that created the default configuration but if you want to start configuring a specific version, you’d use: pgenv config init 16.0.

Workflow

With all of the above in place, installing and using a PostgreSQL version is as simple as:

pgenv build 18.1
pgenv use 18.1

You can also check that SSL is enabled and your extensions are available:

# Check if SSL is enabled.
psql -c "SHOW ssl;"

# Check available extensions.
psql -c "SELECT * FROM pg_available_extensions;"

Troubleshooting

If you get install errors due to an outdated or updated icu4c, you might need to pin your PKG_CONFIG_PATH environment variable to a specific version. Example:
- PKG_CONFIG_PATH=$HOMEBREW_PREFIX/opt/icu4c@78/lib/pkgconfig
If you get install errors due to an outdated or updated LLVM, you might need to pin your LLVM_CONFIG and CLANG environment variables to a specific version. This can happen when using LLVM 21.0.0 or higher. You’ll have to downgrade to LLVM 20.x.x (i.e. brew install llvm@20) until PostgreSQL supports 21.0.0 and higher. Example:
- LLVM_CONFIG=$HOMEBREW_PREFIX/opt/llvm@20/bin/llvm-config
- CLANG=$HOMEBREW_PREFIX/opt/llvm@20/bin/clang
If you have trouble installing the pg gem due to reinstalling PostgreSQL, installing a different version, etc. then uninstall (i.e. gem uninstall pg) and install (i.e. gem install pg) to pick up the latest changes.

Resources

If you’d like to see how I manage and configure all of this, check out these projects:

Dotfiles: Used to maintain my Bash aliases, functions, configuration, and more.
macOS Configuration: Used to install and manage all software. Perfect for managing existing hardware or automating the setup of a brand new machine with a fully functional working development environment.

Conclusion

You’ve learned how to install, configure, and use pgenv for managing multiple PostgreSQL versions. You’ve also learned how customize pgenv so you can immediately create and use databases which pgenv doesn’t provide documentation for. This should make your workflow easier while also allowing you to more easily migrate to newer versions of PostgreSQL. Enjoy!