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.
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
Describing Dependencies using
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
pgbundle defines a simple DSL. We’ll cover it by examining the example file above.
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
Specify a Dependency using
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
Resolving Dependencies using
Some extensions may require other extensions. To allow
pgbundle to resolve dependencies
and install them in the right order you can define them with
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
Pgfile configured for your project, you can run the
pgbundle executable to actually download and setup the
pgbundle executable comes with 4 commands. All of these commands need a
Pgfile to run against and you can either
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.
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
install tries to install missing extensions. If
--force is given it installs
all extensions even if they are already installed.
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.
init is there to help you get started. If you already have a database with installed
extensions you get the content for an initial
pgbundle will figure out
which extension at which version are already in use and print a reasonable starting
point for your
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
and creating the extension into the database with
pgbundle reflects that with the two different commands
pgbundle runs along with your application on your application server
which often is different from your database machine. Thus the
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
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
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
and find the
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
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