Practice
Has it ever happened to you, for all sorts of reasons, to think about PostgreSQL? Freely distributed, accessible, simple, flexible, extensible, unpretentious, yet proud of its undeniable strengths. It combines simplicity with an extremely logical way of working, and at the same time it is a truly, boundlessly extensible tool. It supports multithreading, letting you run parallel transactions without Read locks. It has extensions to solve tasks for every taste. Ah, too bad, too bad — it still 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 sorts of data sources that let you work with them from inside this DBMS.
This article is a quick introduction meant for everyone, although it's preferable that you have linux, and in general it doesn't require any special preparation.
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 this data can be quite varied. The official postgres wiki has a concise collection of links to sources and brief information about all the officially supported foreign data wrappers (see "The Essence of the Issue").
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 has whatever it has — it doesn't really matter what. You need a software shell to access this "whatever," one that would take from you only a login and password to connect to the server, and then the user data needed to access the data source inside it (a login and password for the database, for example). And for each of these "whatevers," regardless of its nature, this wrapper has to be built into postgres, and its usage has to be unified with other such wrappers.
That's exactly what foreign data wrappers are.
installing what we need
To start, you'll need postgres itself. On linux, you'll get the core functionality by running
|
1
2
|
sudo apt-get update
sudo apt-get install postgresql-9.4 pgadmin3 postgresql-9.4-postgis-2.1
|
More detailed download information is on the official wiki. And as for other operating systems — it's never too late to try linux... Although, 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 be possible data sources, including non-relational ones, as well as plain files, and a lot more besides (see "The Essence of the Issue").
We'll create a new database with one table, and try to use its data from postgres.
(To keep things from getting boring, let's not use the users table with contact-data fields that every tutorial drags out, but instead a table storing information about the victims of a crazed maniac who wants to sow chaos and anarchy across the whole world, regularly kills innocent people, and in his free time sits with a laptop sipping coffee. Until recently he kept his victim records in MySQL, but now he's decided to develop a more serious project, and he needs to sync a bit of his data from mysql to postgres).
|
1
2
3
4
5
6
7
8
9
|
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
);
|
Let's also fill this table with at least somewhat plausible 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.
|
1
2
3
|
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 the full list of Kenny's deaths, we'll limit ourselves to the first three, and we'll treat the episode air dates as the dates of death).
launching what's needed
Now let's settle into postgres. We need a new database in which we'll create a foreign table based on data from MySQL.
We launch postgreSQL as the DBMS superuser, named “postgres”:
|
1
|
sudo -u postgres psql
|
Once connected to the database, let's briefly get our bearings.
|
1
|
\list
|
-view the existing databases
|
1
|
CREATE DATABASE pg_cruel;
|
-create a new database
|
1
|
\connect pg_cruel
|
-select the database to work with
|
1
|
\dt
|
-view the tables of the selected database
Finally, we have almost all the tools we need. One is left – the foreign data wrapper itself.
For our situation we need a foreign data wrapper (hereafter, “wrapper”) for data from MySQL. In theory, a wrapper can be built from scratch, and postgreSQL provides beautiful and concise documentation for that. However, for DBMSs of every kind and stripe, and not only DBMSs but, arguably, for pretty much any conceivable data source, 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.
Each wrapper is a postgreSQL extension and has its own installation procedure.
We download the source archive from the mysql_fdw page on GitHub. Unpack it, build it, compile it. The complete 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.
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 whatever 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:
Creating the extension
|
1
|
CREATE EXTENSION mysql_fdw;
|
Creating an external server for the connection, specifying the address and port, and possibly a password and other options (there's quite a wide range of options here)
|
1
2
3
|
CREATE SERVER f_server
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (hostaddr '127.0.0.1', port '3306');
|
Creating user access to the data source. Here everything depends on the specific FDW; mysql_fdw requires a login and password. We also specify the external server for which the user credentials are valid.
|
1
2
3
|
CREATE USER MAPPING FOR postgres
SERVER f_server
OPTIONS (user 'mysqluser', password 'mysqlpassword');
|
Creating the foreign table, specifying the external server.
|
1
2
3
4
5
6
7
8
9
|
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 you can fully enjoy the data from the external source. For example, one of the most important applications is that we can query mixed data: part of it resides on our server, part – on the remote one.
|
1
|
\connect pg_cruel
|
Let's create a couple of regular, local tables, linked to each other and to the foreign table:
|
1
2
3
4
5
6
|
CREATE TABLE murders
(
murder_id SERIAL PRIMARY KEY,
victim_id BIGINT REFERENCES victims(victim_id),
way_of_death_id BIGINT REFERENCES ways_of_death(way_id)
);
|
|
1
2
3
4
5
|
CREATE TABLE ways_of_death
(
way_id SERIAL PRIMARY KEY,
value VARCHAR(140)
);
|
Filling with data – to taste.
And now… Voila! We can get the names of our victims along with their cause of death!
|
1
2
3
4
|
SELECT victims.first_name, victims.last_name, way.value
FROM victims
LEFT JOIN murders ON (victims.victim_id=murders.victim_id)
LEFT JOIN ways_of_death AS way ON (murders.way_of_death_id=ways.way_id);
|
Comments