MySQL benaderen vanuit PostgreSQL: Foreign Data Wrappers

Foreign Data Wrappers (FDW) is een API binnen PostrgreSQL waar externe partijen tools voor kunnen schrijven om vanuit PostgreSQL toegang te krijgen tot externe bronnen zoals MySQL, Oracle, CSV files, JSON files, Elasticsearch, Cassandra, CouchDB, MongoDB, Twitter, noem maar op. Welke functies per wrapper worden ondersteunt is afhankelijk van de productiviteit van de makers en er staat een mooi overzichtje https://wiki.postgresql.org/wiki/Foreign_data_wrappersin de wiki.

Waarom?

Je kunt dit toch ook allemaal doen via scripts?

Je mist het punt. Scripts kunnen toegang hebben tot meerdere databronnen en dan data heen en weer gaan pompen maar alle bronnen bestaan alleen binnen dat script. FDW's zijn permanente objecten binnen de database die toegang geven tot de externe bron alsof het interne data is. Je hoeft die bronnen niet meer in je scripts beschikbaar te maken, je hoeft allen te zorgen dat de user waarmee je aanlogt rechten heeft op de objecten die de FDW heeft gemaakt.

Voorbeeld

In dit voorbeeld worden alle tabellen van de 'tesdt_db' database op de MySQL server gekoppeld aan het 'remote_mysql' schema in PostgreSQL. Er wordt een apart PostgreSQL schema gebruikt zodat er een strikte en heldere scheiding blijft tussen wat lokaal is en wat foreign is.

Installeer de extensie in het OS van de database server.

sudo apt-get install postgresql-9.5-mysql-fdw

Maak de extensie aan in de database waaruit hij benaderd gaat worden

CREATE EXTENSION mysql_fdw;

Maak een server aan die verwijst naar de externe bron

CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host '<mysql_server>', port '3306');

Maak een koppeling tussen de PostgreSQL user die in PostgreSQL wordt gebuikt om de FDW te benaderen en de user die aan de externe kant bestaat en de benodigde tabellen mag benaderen

CREATE USER MAPPING FOR postgres SERVER mysql_server OPTIONS (username '<mysql_user>', password '<mysql_password>');

Maak een schema in PostgreSQL om de verwijzingen naar de externe bron in te plaatsen.

CREATE SCHEMA remote_mysql;

Importeer het schema uit de externe bron en plaats de inhoud in het 'remote_mysql' schema.

IMPORT FOREIGN SCHEMA test_db FROM SERVER mysql_server INTO remote_mysql;

Nu bestaan er verwijzingen naar de geimporteerde tabellen in het remote_mysql schema. Stel dat er op de MySQL server een tabel testtable bestaat dan kun je er nu uit lezen via:

SELECT * FROM remote_mysql.testtable;

En schrijven op dezelfde manier

UPDATE remote_mysql.testtable SET answer=42 WHERE username='Vroomvondle';