Postgres OnLine Journal: Jan 2019 - Dec 2019
An in-depth Exploration of the PostgreSQL Open Source Database
 

Table Of Contents

Using PostgreSQL Contribs

Using PostgreSQL Contribs

 

Compiling http extension on ubuntu 18.04



We recently installed PostgreSQL 11 on an Ubuntu 18.04 using apt.postgresql.org. Many of our favorite extensions were already available via apt (postgis, ogr_fdw to name a few), but it didn't have the http extension we use a lot. The http extension is pretty handy for querying things like Salesforce and other web api based systems. We'll outline the basic compile and install steps. While it's specific to the http extension, the process is similar for any other extension you may need to compile.

For this exercise, I'm going to assume you have a working PostgreSQL install you got from apt.postgresql.org. If you don't you can check out PostGIS 2.4 and PostgreSQL 10 install on Ubuntu which covers installing PostgreSQL, PostGIS, and pgRouting on Ubuntu.

Building extensions general requirements

For any extension written in C or C++, You'll need a couple extra things, you may not have

sudo -i  #get into root mode
apt install make gcc g++
apt install postgresql-server-dev-11

If you are running a different version of PostgreSQL, replace the number 11 with the version you are running.

Building http

The http extension relies on curl development library, so in addition to the aforementioned, you'll need to do:

apt install libcurl4-openssl-dev

Now we are ready to build. The latest as of this writing is 1.3.0, you should check if there is newer when you build.

wget https://github.com/pramsey/pgsql-http/archive/v1.3.0.tar.gz
tar xvfz v1.3.0.tar.gz
cd pgsql-http-1.3.0
make && make install

If all is successful you should see something like this:

/usr/bin/install -c -m 644 .//http--1.3.sql .//http--1.2--1.3.sql .//http--1.1--1.2.sql .//http--1.0--1.1.sql  
'/usr/share/postgresql/11/extension/'

Installing http extension in your database

As a general rule we don't like our public schema cluttered with extension functions, so we usually install extensions in contrib schema or a schema dedicated to the extension or related extensions. So our steps below are a few more than a standard vanila install.

su postgres
psql
CREATE DATABASE test_http;
\connect test_http;
CREATE SCHEMA contrib;
GRANT USAGE ON SCHEMA contrib TO some_user_group;
ALTER DATABASE test_http SET search_path=public,contrib;
CREATE EXTENSION http SCHEMA contrib;
\connect test_http -- I do this so search path takes effect
-- test the extension by grabbing first 100 characters of web page
SELECT left(content,100) FROM http_get('https://postgis.net');

You can find more examples of usage at https://github.com/pramsey/pgsql-http