Table Of Contents
From the Editors
SQL Server on Linux
Today is April 1st. Having no thoughts on Fools jokes for today, I dug up one of our old April fools, and it was pretty scary how the joke is just about true now. Yes SQL Server now really does run on Linux and is on it's 2017th edition, but still a poor competition to PostgreSQL.
A goody from our old joke archivesCatchMe - Microsoft SQL Server for Unix and Linux
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.
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