Quick Start with PostgreSQL

Practice

INTRODUCTION

Haven't you ever, for one reason or another, thought about PostgreSQL? Freely distributed, accessible, simple, flexible, extensible, unpretentious, yet proud of its undeniable strengths. It combines simplicity with the utmost logical consistency of use, and at the same time it's a genuinely, boundlessly extensible tool. It supports multithreading, letting you run parallel transactions without Read locks. It has extensions to solve tasks to suit any taste. Ah, alas, alas, it just can't cook you borscht.

One of the most popular and powerful features of PostgreSQL, which we'll talk about today, is Foreign Data Wrappers - that is, software wrappers for all kinds of data sources that let you use them from inside this DBMS.

This article is a quick introduction meant for everyone, though it's best if you have linux, and generally it doesn't require any prior preparation.

WHAT is PostgreSQL

or what it actually is

Foreign Data Wrappers are extensions for PostgreSQL that let you access data on a remote server.

The nature and structure of that data can vary widely. The official Postgres wiki has a concise collection of links to the sources and brief information about all the officially supported foreign data wrappers (see “The Gist of It”).

The key point is the ability to connect to a third-party server. The idea is simple. You have a server with postgreSQL, and a third-party server that holds - it doesn't really matter what anymore. You need a software shell to access this “doesn't really matter what,” one that would take from you only a login and password to connect to the server, and then - user data to access the data source within it (a login and password for the database, for example). And for each of these “doesn't really matter whats,” regardless of its nature, this wrapper has to be built into postgres, and its use has to be unified with other such wrappers.

And that's exactly what foreign data wrappers are.

PREPARING to install PostgreSQL

installing what's needed

First you'll need postgres itself. On linux, you'll get the core functionality by typing

1 2	sudo apt-get update sudo apt-get install postgresql-9.4 pgadmin3 postgresql-9.4-postgis-2.1

More detailed information about the downloads is on the official wiki. As for other operating systems - it's never too late to try linux… Though, seriously, installing on Windows is trivial and no different from installing an ordinary music player.

Now we need a more or less interesting source of external data. I happened to have a MySQL lying around on my machine. In my case, it's the one that will serve as the source of external data. However, other DBMSs can also serve as possible data sources, including non-relational ones, as well as plain files, and much more besides (see “The Gist of It”).

We'll create a new database with a single table, and try to make use of that data from postgres.

(So it's not boring, let's not use the users table with contact-info fields that every tutorial has beaten to death, but instead a table storing information about the victims of a crazy maniac who wants to sow chaos and anarchy across the whole world, regularly kills innocent people, and in his free time sits with his laptop sipping coffee. Until recently he kept track of his victims in MySQL, but now he's decided to build a more serious project and needs to sync a bit of his data from mysql to postgres).

CREATE DATABASE cruel;

USE cruel;

CREATE TABLE victims (

victim_id SERIAL PRIMARY KEY,

first_name VARCHAR(30),

last_name VARCHAR(30),

birthdate DATE,

murdertime DATETIME

);

We'll also fill this table with at least a tiny bit of reasonable data.

1 2	INSERT INTO victims VALUES (1, 'john', 'lennon', 1940-10-9, 1980-12-8-00-00-00); INSERT INTO victims VALUES (2, 'john', 'kennedy', 1917-5-29, 1963-11-22-00-00-00);

We can kill Kenny from South Park several times – that's perfect for an example.

INSERT INTO victims VALUES (3, 'Kenny', 'McCormick', 1987-01-01, 1997-08-13-00-00-00);

INSERT INTO victims VALUES (4, 'Kenny', 'McCormick', 1987-01-01, 1997-08-20-00-00-00);

INSERT INTO victims VALUES (5, 'Kenny', 'McCormick', 1987-01-01, 1997-08-20-00-00-00);

(We won't include Kenny's full list of deaths, we'll limit ourselves to the first three, and we'll take the episode air dates as the dates of death).

QUICK START WITH POSTGRES

launching what we need

Now let's get set up in postgres. We need a new database in which we'll create a foreign table based on data from MySQL.

Let's start postgreSQL as the DBMS superuser, called “postgres”:

1	sudo -u postgres psql

Connecting to the database, let's briefly check out the situation.

\list

-view existing databases

1	CREATE DATABASE pg_cruel;

-create a new database

1	\connect pg_cruel

-select the database to work with

\dt

-view the tables of the selected database

Finally, we have almost all the tools we need. There's one left – the foreign data wrapper itself.

THE HEART OF THE MATTER: mysql_fdw

For our situation we need a foreign data wrapper (hereafter “wrapper”) for data from MySQL. In theory, you could build a wrapper yourself, and postgreSQL provides beautiful, concise documentation for it. However, for DBMSs of every sort and kind, and not only DBMSs, but pretty much any data source you can imagine, you can find a ready-made fdw to suit any taste. For our simple example, though, we'll need mysql_fdw. For a different data source, it would be a different wrapper.

Every wrapper is a postgreSQL extension and has its own installation procedure.

We download the source archive from the mysql_fdw page on GitHub. We unpack it, build it, compile it. The full 4-step installation guide is in the README.md file, nothing unusual or complicated – just add the two specified paths to the $PATH system variable, and run the compilation.

FINALLY, USAGE

We have postgres on a local server, we have the extension for handling external data, and we have MySQL. Also on a local server, but no matter what the address is, the usage is identical.

For the data from cruel in MySQL to show up in our pg_cruel database, we need to complete 4 steps:

Create the extension for postgresql with the data wrapper.
Create a connection to the server with the external data.
Set up user access to the data source on the external server (user mapping)
Finally, create the foreign table.

Creating the extension

1	CREATE EXTENSION mysql_fdw;

Let's create a foreign server for the connection, specifying the address and port, and possibly a password and other options (there's quite a wide list of them here)

CREATE SERVER f_server

FOREIGN DATA WRAPPER mysql_fdw

OPTIONS (hostaddr '127.0.0.1', port '3306');

Let's create user access to the data source. Here everything depends on the specific FDW; mysql_fdw requires a login and password. We also specify the foreign server for which these user credentials are valid.

CREATE USER MAPPING FOR postgres

SERVER f_server

OPTIONS (user 'mysqluser', password 'mysqlpassword');

Let's create a foreign table, specifying the foreign server.

CREATE FOREIGN TABLE victims

(

victim_id SERIAL,

first_name VARCHAR(30),

last_name VARCHAR(30),

birthdate DATE,

murdertime TIME

)

SERVER f_server OPTIONS (table_name 'victims');

Now we can fully enjoy the data from the external source. For example, one of the most important uses is that we can query mixed data: some of it sits on our own server, some on the remote one.