Schlagwort-Archive: MySQL

PostgreSQL

Erfahrungen mit PostgreSQL und dem MySQL-Foreign-Data-Wrapper

Schreibe einen Kommentar

(English version)

Foreign Data Wrapper in PostgreSQL sind eine sehr elegante Möglichkeit, andere Datenbanken und andere Datenquellen wie normale Tabellen anzusprechen.

Ich habe den MySQL-FDW eingehend getestet und möchte die Erfahrungen teilen.

Generell lassen sich auch Tabellen anlegen, die aus verschiedenen Gründen danach nicht vollständig in PostgreSQL nutzbar sind. Z. B. verursachen die berüchtigten ungültigen Timestamps von MySQL (0000-00-00 00:00) Lesefehler beim Abfragen der betreffenden Zeilen. Einige spezielle Datentypen, die in MySQL und PostgreSQL gleich heißen, sind trotzdem nicht lesbar, z. B. der Datentyp POINT.

Abgesehen von diesen Problemen geht das Lesen üblicherweise problemlos. In einigen Konstruktionen (z. B. wenn Werte verglichen werden sollen) versucht das Foreign-Wrapper-Modul, PostgreSQL-Sprachelemente wie ::TYP (statt CAST(… AS TYP)) an die fremde Datenbank zu senden, was einen Fehler ergibt.

Die Lösung ist, die betreffenden Werte selbst mit der standardkonformen CAST-Syntax umzuwandeln.

INSERTs und UPDATEs sind ein anderes Thema. Hat man sich mühsam eine Abfragesyntax erarbeitet, die von beiden Datenbanken akzeptiert wird, stürzen PostgreSQL-Backends sporadisch und nicht reproduzierbar ab, sodaß das Datenbanksystem sich automatisch neu startet und dabei alle bestehenden Verbindungen abreißt. Leider wird dabei nichts geloggt, sodaß ich den Grund noch nicht herausgefunden habe. Derzeit kann ich diese Funktionalität jedenfalls nicht verwenden.

Die Anwendung wäre, Änderungen an Datensätzen mit Hilfe von Triggern direkt in der anderen Datenbank nachzuziehen. So läuft weiterhin ein periodischer Pentaho-Data-Integration-Prozesses.

Update 2016-03-06: Mit aktuellen Versionen des Foreign Data Wrappers treten keine Abstürze mehr auf. INSERTs und UPDATEs sind jetzt stabil. Somit können datenbankübergreifende Trigger-Funktionen geschrieben werden, um Werte in der MySQL-Datenbank zu aktualisieren, wenn sich etwas in PostgreSQL ändert.

Practical experience with PostgreSQL and the MySQL Foreign Data Wrapper

Foreign Data Wrappers in PostgreSQL are a very elegant way to access other datasources and use them like normal database tables.

I’d like to share the results of my tests of the MySQL FDW.

It is possible to create tables that are not fully usable in PostgreSQL afterwards, for different reasons. For example, the infamous invalid MySQL timestamp (0000-00-00 00:00) makes the record unreadable. There are data types like POINT that are called the same in MySQL and PostgreSQL but they are different enough not to be readable, as well.

Aside from these problems, reading data usually works without issues. In some queries (e. g. when comparing values not having the same data type), the foreign data wrapper module tries to use PostgreSQL-specific syntax like ::TYPE instead of the standard CAST(… AS TYPE) in the foreign database, returning errors.

The solution for this problem is to explicitly cast the values with the standard CAST syntax.

INSERT and UPDATE don’t work quite as well. After finding a query syntax that is accepted by both database systems, PostgreSQL backends start crashing sporadically and in a non-reproducible way. The database server restarts automatically but kills all existing connections. Nothing is logged, so I couldn’t find the reason yet. Therefore, I can’t use this functionality yet.

I wanted to use write access to the MySQL tables to quickly enter changes in the foreign database from a PostgreSQL trigger function. As this doesn’t work, a Pentaho Data Integration process is started periodically for the same purpose.

Update 2016-03-06: Current versions of the Foreign Data Wrapper don’t crash the PostgreSQL backend anymore. INSERT and UPDATE on foreign tables is stable now. Therefore it is possible to write trigger functions that update values in the MySQL database automatically when a value changes in PostgreSQL.

 

Data Integration, PostgreSQL

PostgreSQL: Zugriff auf fremde Datenbanken

Ein Kommentar

(English version)
Komplexe Lösungen bestehen häufig aus mehr als einer Datenbank, manchmal sogar unterschiedlichen Typs. Im Fall von Sco2t haben wir eine „geerbte“ MySQL-Datenbank, während neue Daten wegen der erweiterten GIS-Funktionalität in PostgreSQL liegen. Natürlich müssen gewisse Daten zwischen beiden Systemen synchronisiert werden.

Pentaho Data Integration ist dafür eine sehr gute Lösung und wird auch intensiv verwendet. Allerdings möchte ich in einigen Fällen sofort auf Änderungen reagieren und nicht erst eine Minute später. (Und auch nicht ständig Java-Prozesse starten, die die meiste Zeit keine Änderungen finden.) Dafür bieten sich PostgreSQL-seitig Trigger an, die Änderungen an einer Tabelle in der MySQL-Datenbank nachvollziehen.

PostgreSQL 9.1 hat die erste Version der Foreign Data Wrappers genannten Funktionalität bekommen, seit 9.3 können diese sogar in fremde Datenbanken schreiben. Ein FDW-Modul für den Zugriff auf andere PostgreSQL-Server ist in 9.4 im PostgreSQL-Contrib-Bereich dabei, Module für viele andere Datenbanken (auch NoSQL und Hadoop!) sind verfügbar.

(Linked Server sind schon seit Längerem in vielen Datenbanksystemen verfügbar, PostgreSQL hatte dafür „dblink“. Diese Funktionalität hat sich aber großteils auf Verbindungen zum eigenen Datenbanktyp beschränkt.)

EnterpriseDB hat ein FDW-Modul für MySQL realisiert. Derzeit muß man es selbst kompilieren, da in den üblichen Repositories keine fertigen Pakete enthalten sind. Das ist aber nicht schwierig.

Installation auf einem Debian-System:


git clone https://github.com/EnterpriseDB/mysql_fdw.git

apt-get install make gcc libmysqlclient-dev postgresql-server-dev-9.4

cd mysql_fdw

make USE_PGXS=1

make USE_PGXS=1 install

Damit ist das Modul übersetzt und als PostgreSQL-Extension installiert. Der Rest passiert als PostgreSQL-User mit Superuser-Rechten in SQL:


CREATE EXTENSION mysql_fdw;

CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw

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

CREATE USER MAPPING FOR username SERVER mysql_server

OPTIONS (username 'myuser', password 'mypassword');

CREATE FOREIGN TABLE mysqltabelle (

-- Felder aus der MySQL-Tabelle mit ihrem Datentyp

)

SERVER mysql_server

OPTIONS (dbname='datenbankname', table_name='OriginalTabelleName');

Damit ist die Fremdtabelle „mysqltabelle“ in der PostgreSQL-Datenbank angelegt und kann wie eine normale Tabelle angesprochen, gelesen und beschrieben werden.

CREATE EXTENSION registriert das Modul in der aktuellen Datenbank. (Verwendet man mehrere Datenbanken auf einem Server, muß CREATE EXTENSION in jeder, die die Funktionalität braucht, ausgeführt werden.)

CREATE SERVER erzeugt die Verbindung zum fremden Datenbankserver und gibt die Verbindungsparameter an.

CREATE USER MAPPING ist notwendig, um PostgreSQL-seitige User mit einem User in der fremden Datenbank zu verknüpfen. Es können mehrere Mappings für unterschiedliche User angelegt werden.

CREATE FOREIGN TABLE definiert dann die Fremdtabelle und gibt ihren Namen in der fremden Datenbank an. Die Datentypen der Felder müssen in PostgreSQL existieren. Es ist möglich, Constraints wie NOT NULL oder Default-Werte schon PostgreSQL-seitig anzugeben.

Foreign Tables eröffnen ganz neue Möglichkeiten der Datenintegration: Daten müssen nicht mehr hin und her kopiert werden, stattdessen sind sie in der Master-Datenbank und die anderen Datenbanken arbeiten immer mit der aktuellen Kopie. Datenübernahmeprozesse können direkt in der Datenbank, ohne Zwischenschaltung einer ETL-Software ausgeführt werden. Ich bin gespannt, welche Anwendungsmöglichkeiten mir noch einfallen.

Accessing foreign databases in PostgreSQL

Many complex solutions use more than one database, sometimes even different database systems. At Sco2t we „inherited“ a MySQL database, but new data are stored in PostgreSQL because of the better GIS functionality. So some data need to be kept in sync between the two systems.

Pentaho Data Integration is a great solution for this and it’s already in heavy use. In some situations, however, I’d like to react to changes instantly instead of waiting a minute. (Also, I dont’t want to start Java processes frequently that don’t have to process any changes most of the time.) In PostgreSQL, we can use triggers for updating data in other tables, now even in the MySQL database.

PostgreSQL 9.1 was the first release with Foreign Data Wrappers functionality. With 9.3, even writing into foreign databases is supported. In 9.4 there is a FDW module for accessing other PostgreSQL servers in the Contrib area. Modules for accessing other database types (even NoSQL and Hadoop!) available.

(Linked server functionality has been available in many database systems for a long time. PostgreSQL had „dblink“. However, linked servers usually had to be of the same type.)

There is a FDW module for MySQL from EnterpriseDB. Currently we need to compile it ourselves, as there are no binary packages in the usual repositories. But it’s not hard.

This is the installation on a Debian system:


git clone https://github.com/EnterpriseDB/mysql_fdw.git

apt-get install make gcc libmysqlclient-dev postgresql-server-dev-9.4

cd mysql_fdw

make USE_PGXS=1

make USE_PGXS=1 install

After this, the module is compiled and registered as a PostgreSQL extension. The rest of the procedure has to be executed (with database superuser rights) in SQL:


CREATE EXTENSION mysql_fdw;

CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw

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

CREATE USER MAPPING FOR username SERVER mysql_server

OPTIONS (username 'myuser', password 'mypassword');

CREATE FOREIGN TABLE mysqltable (

-- Fields of the MySQL table, including their type

)

SERVER mysql_server

OPTIONS (dbname='databasename', table_name='OriginalTableName');

The result of this script is a foreign table „mysqltable“ in the PostgreSQL database. This table can be used like any normal table, you can read and write it.

CREATE EXTENSION registers the compiled module in the current database. (When using multiple databases on a server, CREATE EXTENSION must be executed in each database that will contain foreign tables.)

CREATE SERVER creates the link to the foreign database server and defines connection parameters.

CREATE USER MAPPING is necessary to link a PostgreSQL user to a user in the foreign database. Multiple mappings can be created for different users.

CREATE FOREIGN TABLE defines the foreign table and its name in the foreign database. The field types must exist in PostgreSQL. It is possible to define constraints like NOT NULL or give default values on the PostgreSQL side.

Foreign tables open entire new worlds of possibilities for data integration. Master data don’t need to be copied to multiple databases: other databases can work directly on the original which is always current. Data integration processes run in the database, without a separate ETL software. It’ll be interesting to explore further ideas and usage scenarios.