Recently we had a customer request to build a custom extension against Postgres by BigSQL distribution. Even though BigSQL ships with a large set of commonly used extensions and good collection of FDWs, these kind of user build requirements always crop up, based on how powerful the Postgres extension model is.

BigSQL makes it easy to build custom extensions for new requirements by including all of the required header files and libraries along with the Postgres by BigSQL installation. This is one of the key benefits of the open source BigSQL distribution as opposed to a proprietary open source-based approach.

One may think that, it will be a challenge because a BigSQL user has the freedom to relocate the the installation anywhere he/she wants or have a DevOps Sandbox. That means Postgres related files can exist anywhere the user wants. In this blog I’ll show you how easy it is. Here is an example of how to build extensions, where postgres binaries sits in my user home directory (I am using DevOps Sandbox on CentOS 6.7)

Building extension from Source

Extension build will use pg_config to understand the the location of the files. So only thing we have to do is to set the path to pg_config wherever it is:

export PATH=/home/vagrant/bigsql/pg95/bin:$PATH

After that, building any custom extension should be a breeze.
Get the required branch of extension:

git clone -b VERSION3_1_STABLE

Install any external library requirements

$sudo yum install openssl-devel.x86_64
$sudo yum install readline-devel

(of course, we need build tools like gcc and make)


The build should go through without any issues.

Getting Extension from PGXN (PostgreSQL Extension Network)

The Postgres community has many extensions available, and PGXN makes it easy to find and install open source extensions published by other community members. Postgres by BigSQL works perfectly with PGXN.

Just like above case, we need to add the path to pg_config before executing the PGXN

$ export PATH=/home/vagrant/bigsql1/pg95/bin:$PATH

Now we can install any extenion we need like:

$ pgxn install temporal_tables

Once installation complete, extension will be ready to use

postgres=# create extension temporal_tables;

Up Next

We just covered how easy it is to build and install PostgreSQL extensions on Linux. In my next blog post, I’ll show how to build and install extensions on Windows. Postgres by BigSQL uses Mingw (and gcc) to compile Postgres and related extensions on Windows which is great because it keeps the full tool chain open source (no proprietary compiler required) and enables extension authors to support Windows without making an MSVC specific build system or code and configuration changes.