One of Postgres’ most powerful features is its extensibility. Although Postgres offers a large number of data types, functions, operators, and aggregates, sometimes you may still want more. Postgres itself already comes with a large amount of additional extensions. Even more can be installed through the PostgreSQL Extension Network and if that is not enough for you, you can also write your own.

However, there isn’t a standard tool for managing Postgres dependencies in applications. To avoid falling into the dependency hell and to enable lean extension development, we developed pgbundle - the Postgres extension management tool.

Installation

pgbundle has been inspired by the Ruby way of managing dependencies through bundler. It is distributed as a Ruby gem, but as you’ll see from this article, you don’t need any Ruby knowledge to use it.

The quickest way to get pgbundle is to install the gem through RubyGems with gem install pgbundle. In case you’re on a Ruby project, however, you might prefer to add pgbundle as a dependency to your Gemfile.

Describing Dependencies using Pgfile

Once you have pgbundle installed, you can and define your dependent Postgres extensions in a Pgfile like this:

1
2
3
4
5
6
7

database 'my_database', host: 'my.db.server', use_sudo: true, system_user: 'postgres'
database 'my_database', host: 'my.db.slave', use_sudo: true, system_user: 'postgres', slave: true

pgx 'hstore'
pgx 'my_extension', '1.0.2', github: me/my_extension
pgx 'my_other_extionsion', git: 'https://github.com/me/my_other_extionsion.git'
pgx 'my_ltree_dependend_extension', github: me/my_ltree_dependend_extension, requires: 'ltree'

For creating Pgfile configurations, pgbundle defines a simple DSL. We’ll cover it by examining the example file above.

The database command

database defines on which database(s) the extensions should be installed. The first argument is the database name, the additional options may specify your setup but come with reasonable default values.

1
2
3
4
5
6
7

user: 'postgres'          # the database user (needs privilege to CREATE EXTENSION)
host: 'localhost'         # the database host (needs to be accessible from where pgbundle runs)
use_sudo: false           # if true use sudo to run make install if needed
system_user: 'postgres'   # the (os) system user that is allowed to install an extension (through make)
port: 5432                # the database port
force_ssh: false          # run commands via ssh even if host is 'localhost'
slave: false              # defines if the database runs as a read-only slave thus skips any CREATE command

Specify a Dependency using pgx

The pgx command defines your actual extension. The first argument specifies the extension name, the second optional parameter defines the required version. If the extension is not yet installed on the server you may wish to define how pgbundle can find its source to build and install it. And which extensions may be required

1
2
3
4
5
6

git: 'url'                  # any git repository pgbundle can clone from
github: 'user/repo'         # any github repository in the form `user/repository`
branch: 'branch_name'       # an optional branch name for git or github sources defaults to `master`
requires: 'extension_name'  # an optional extension that the extension depends on
path: '/some/local/path'    # any absolute or relative local path e.g. './foo/bar'
pgxn: 'extension_name'      # any repository available on http://pgxn.org/

Resolving Dependencies using requires

Some extensions may require other extensions. To allow pgbundle to resolve dependencies and install them in the right order you can define them with requires. If the required extension is not yet available on the target server or the extension requires a specific version you should define it as well. E.g.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# Pgfile

# database configuration goes here

pgx 'hstore'

pgx 'foo', '0.1.2', github: me/foo

# set foo as dependency for bar
pgx 'bar', '1.2.3', github: me/bar, requires: 'foo'

# set bar and hstore as dependency for baz
# will automatically set foo as dependency as well
# note as hstore is a build in contrib module for postgres
# there is no need to explicitly define it, however for readability we recommend it
pgx 'baz', '0.2.3', github: me/baz, requires: ['bar', 'hstore']

The pgbundle executable

With a Pgfile configured for your project, you can run the pgbundle executable to actually download and setup the dependencies.

The pgbundle executable comes with 4 commands. All of these commands need a Pgfile to run against and you can either use the pgfile argument to provide a custom file path, or simply create a file named Pgfile in the current directory and define your dependencies in it. By default the pgfile executable will try loading that file.

Note that another benefit of maintaining a Pgfile, is that it will allow you to keep your Postgres extension dependencies, configured under version control.

Let’s go through each command that the pgbundle executable supports.

1
2

# checks availability of required extensions
pgbundle check [pgfile]

check does not change anything on your system, it only checks which of your specified extensions are available and which are missing. It returns with exit code 1 if any extension is missing and 0 otherwise.

1
2

# installs extensions
pgbundle install [pgfile] [-f|--force]

install tries to install missing extensions. If --force is given it installs all extensions even if they are already installed.

1
2

# create the extension at the desired version
pgbundle create [pgfile]

create runs the CREATE EXTENSION command on the specified databases. If a version is specified in the Pgfile it tries to install with CREATE EXTENSION VERSION version. If the extension is already created but with a wrong version, it will run ALTER EXTENSION extension_name UPDATE TO new_version.

1
2

# write an initial Pgfile to stdout
pgbundle init db_name -u user -h host -p port

init is there to help you get started. If you already have a database with installed extensions you get the content for an initial Pgfile. pgbundle will figure out which extension at which version are already in use and print a reasonable starting point for your Pgfile.

However this is only meant to help you get started; you would probably need to edit the generated file in order to specify sources and dependencies correctly.

How it works

You may already have noticed that using extensions on Postgres requires two different steps. Building the extension on the database cluster with make install and creating the extension into the database with CREATE/ALTER EXTENSION. pgbundle reflects that with the two different commands install and create.

Usually pgbundle runs along with your application on your application server which often is different from your database machine. Thus the install step will (if necessary) try to download the source code of the extension into a temporary folder and then copy it to your database servers into /tmp/pgbundle. From there it will run make clean && make && make install for each database. You may specify as which user you want these commands to run with the system_user option. Although for security reasons not recommended, you can specify to run the install step with sudo use_sudo: true. We prefer to give write permission for the postgres system user on the install targets. If you are not sure which these are, run

1

pg_config

and find the LIBDIR, SHAREDIR and DOCDIR.

Handling master/slave database setups

Every serious production database cluster usually has a slave often run as Hot Standby. You should make sure that all your extensions are also installed on all slaves. Because database slaves run as read-only servers any attempt to CREATE or ALTER extension will fail, these commands should only run on the master server and will be replicated to the slave from there. You can tell pgbundle that it should skip these steps with slave: true.