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
Using PostgreSQL Contribs
PostgreSQL 11 64-bit Windows FDWs
We are pleased to provide binaries for file_textarray_fdw and odbc_fdw for PostgreSQL 11 Windows 64-bit.
To use these, copy the files into your PostgreSQL 11 Windows 64-bit install folders in same named folders and then run CREATE EXTENSION as usual in the databases of your choice. More details in the packaged README.txt
We are no longer including 32-bit binaries since EDB has stopped supporting 32-bit windows for PostgreSQL 11 and up.
This package contains the following FDWs:
- odbc_fdw (version 0.4.0) for connecting to ODBC data sources such as SQL Server, Oracle, MS Access databases, and anything else that has a 64-bit ODBC driver. Note that since this is for PostgreSQL 64-bit, it can only use ODBC 64-bit connections.
- file_textarray_fdw, great and fast for working with arbitrary and pesky delimited data. Especially where they weren't considerate enough to give you the same number of columns per row.
Note this package does not include ogr_fdw since ogr_fdw is packaged as part of PostGIS packages from EnterpriseDb Stackbuilder (for PostGIS >= 2.2) and also BigSQL distributions.
If you do not have PostGIS installed (and don't want to for some reason) and want to use ogr_fdw on windows, you can download from: Winnie's PG 11 Extras. ogr_fdw is a great FDW for querying not just spatial data, but also a ton of other file formats or relational (including odbc, dbase files, spreadsheets) since spatial is a superset.