Mittwoch, November 06, 2013

Postgres: Database Links und Foreign Data Wrapper

Es gibt viele Dinge, die mir an postgres ausgesprochen gut gefallen: die Datenbank hat einen sehr ausgereiften SQL-Dialekt, eine erstaunliche Erweiterbarkeit, zahllose Datentypen und mit psql ein sehr schönes command-line interface. Wenn ich allerdings darüber nachdenke, was mir an diesem RDBMS weniger gut gefällt, dann gehört dazu die Tatsache, dass die Datenbanken eines postgres Clusters ganz strikt voneinander getrennt sind, da ihre data dictionaries nichts voneinander wissen. Das FAQ im PostgreSQL Wiki sagt dazu: "There is no way to directly query a database other than the current one. Because PostgreSQL loads database-specific system catalogs, it is uncertain how a cross-database query should even behave." Wahrscheinlich kann man das Verhalten also theoretisch begründen - gefallen muss es mir deshalb aber noch nicht. Immerhin gibt es aber zwei Möglichkeiten, diese Beschränkung zu umgehen: die Verwendung von database links und den Einsatz eines foreign data wrappers.

Database Link

Die ältere Lösung ist dabei der database link, der es ermöglicht, eine Query zu definieren, die in einer entfernten Datenbank ausgeführt wird. Dabei gibt man eine Connection an und ergänzt das auszuführende Statement sowie eine Typ-Beschreibung der Ergebnisspalten. Dazu ein kleines Beispiel. Zunächst lege ich drei Datenbanken an, von denen zwei die Rolle von Quellsystemen spielen, während die dritte als Reporting-Datenbank fungiert. Im Beispiel liegen alle Datenbanken im gleichen postgres-Cluster (also dem, was man in anderen RDBMS als Instanz bezeichnet), aber sie könnten auch auf entfernten Rechnern und in unterschiedlichen Clustern liegen. Aus Gründen der Vereinfachung habe ich mich auch nicht um eine plausible Benutzerzuordnung gekümmert, sondern agiere überall als postgres:

-- Anlage von Test-Datenbanken
-- Reporting-DB
postgres=# create database dbadmin;
CREATE DATABASE
-- Quell-DBs
postgres=# create database t1;
CREATE DATABASE
postgres=# create database t2;
CREATE DATABASE

-- Quell-Tabellen
t1=# create table t1_tab1 as select generate_series(1, 10) id;
SELECT 10
t2=# create table t2_tab2 as select generate_series(1, 100) id;
SELECT 100

Dazu nun ein Zugriff über database link:

dbadmin=# SELECT *
dbadmin-#   FROM dblink('dbname=t1 user=postgres password=XXXXXX'
dbadmin-#             , 'select id from t1_tab1')
dbadmin-#     AS t1(id int)
dbadmin-# ;
 id
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 Zeilen)

In diesem kleinen Beispiel ist das Verfahren noch relativ handhabbar, aber für größere Queries kann es recht mühsam werden, die Spaltendefinitionen aufzuführen. Die Dokumentation empfiehlt, solche database links in Views zu packen, aber beim Zugriff auf eine größere Anzahl von Datenbanken wird auch diese Vorgehensweise sperrig.

Foreign Data Wrapper

Zur Vereinfachung des Vorgehens (und aus einer Reihe weiterer Gründe wie Transaktionsmanagement, Connection-Management und Performance, die in der Dokumentation detailliert erläutert werden) wurde mit postgres 9.3 die extension postgres_fdw in den Standard aufgenommen (für 9.2 gab es einen Backport). Diese Erweiterung erlaubt die Definition umfangreicherer statischer Objekte, die den Zugriff auf entfernte Datenbanken vereinfachen. Das folgende Beispiel basiert auf den Datenbank- und Tabellendefinitionen des database link Beispiels:

-- Installation der extension
dbadmin=# create extension postgres_fdw;
CREATE EXTENSION

-- Anlage von Metadaten-Objekten in der Reporting-Datenbank (DBADMIN)
-- Definition der Server
dbadmin=# CREATE SERVER fdw_t1 FOREIGN DATA WRAPPER postgres_fdw 
dbadmin=# OPTIONS (dbname 't1', host 'localhost');
CREATE SERVER
dbadmin=# CREATE SERVER fdw_t2 FOREIGN DATA WRAPPER postgres_fdw 
dbadmin=# OPTIONS (dbname 't2', host 'localhost');
CREATE SERVER

-- Definition von user-mappings
dbadmin=# CREATE USER MAPPING for postgres
dbadmin-#   SERVER fdw_t1
dbadmin-#   OPTIONS (user 'postgres', password 'XXXXXX');
CREATE USER MAPPING

dbadmin=# CREATE USER MAPPING for postgres
dbadmin-#   SERVER fdw_t2
dbadmin-#   OPTIONS (user 'postgres', password 'XXXXXX');
CREATE USER MAPPING

-- Definition von foreign tables
dbadmin=# CREATE FOREIGN TABLE t1_tab1
dbadmin-#   (
dbadmin(#     id integer
dbadmin(#   )
dbadmin-#   SERVER fdw_t1 OPTIONS (table_name 't1_tab1');
CREATE FOREIGN TABLE

dbadmin=# CREATE FOREIGN TABLE t2_tab2
dbadmin-#   (
dbadmin(#     id integer
dbadmin(#   )
dbadmin-#   SERVER fdw_t2 OPTIONS (table_name 't2_tab2');
CREATE FOREIGN TABLE

-- eine übergreifende Abfrage auf Zieltabellen 
-- aus zwei unterschiedlichen Datenbanken
dbadmin-# select 't2_tab2' table_name, count(*) cnt from t2_tab2;
 table_name | cnt
------------+-----
 t1_tab1    |  10
 t2_tab2    | 100
(2 Zeilen)

Natürlich bleibt auch in diesem Fall das Problem, dass man zur Anlage der foreign tables deren Definitionen im Quellsystem kennen muss, aber um diese Information zu bekommen, könnte man zusätzlich eine foreign table für information_schema.tables einrichten. Nützlich ist dabei auch, dass die Definition für eine foreign table nicht alle Spalten der Quelltabelle enthalten muss, so dass man sich auf die relevanten Angaben beschränken kann:

dbadmin=# CREATE FOREIGN TABLE t2_columns
dbadmin-#   (
dbadmin(#     table_name text
dbadmin(#   , column_name text
dbadmin(#   , data_type text
dbadmin(#   )
dbadmin-# SERVER fdw_t2 OPTIONS (schema_name 'information_schema', table_name 'columns');
CREATE FOREIGN TABLE
dbadmin=# select * from t2_columns limit 10;
  table_name  | column_name | data_type
--------------+-------------+-----------
 pg_statistic | starelid    | oid
 pg_statistic | staattnum   | smallint
 pg_statistic | stainherit  | boolean
 pg_statistic | stanullfrac | real
 pg_statistic | stawidth    | integer
 pg_statistic | stadistinct | real
 pg_statistic | stakind1    | smallint
 pg_statistic | stakind2    | smallint
 pg_statistic | stakind3    | smallint
 pg_statistic | stakind4    | smallint
(10 Zeilen)

Ich will nicht behaupten, dass damit alle Probleme gelöst wären - und finde immer noch, dass die entsprechenden Optionen anderer RDBMS (z.B. database links bei Oracle, linked server im SQL Server) etwas besser zu handhaben sind, aber ich denke, dass postgres_fdw einen signifikanten Fortschritt darstellt.

Übrigens fällt mir gerade noch ein Punkt ein, der mich bei postgres deutlich mehr stört als der gerade ausgeführte, nämlich das Fehlen detaillierter Informationen zu den ausgeführten Queries und ihrer Performance. Aber das ist wieder einmal eine Geschichte, die ein andermal erzählt werden soll.

Keine Kommentare:

Kommentar veröffentlichen

Kommentar veröffentlichen