English Version

Einleitung

Manchmal muss man Daten aus mehreren Datenbanken miteinander vergleichen, importieren oder aktualisieren. Wenn nicht gerade umständlich ein Programm mit mehreren Datenquellen geschrieben werden soll, bleiben einem nur die Tools der Datenbank selbst. Im Fall von PostgreSQL sind das das Kommandozeilen-Interface psql oder die GUI pgAdmin. Beide sind jedoch nicht in der Lage Abfragen über mehrere Datenbanken abzusetzen. Das liegt schlicht daran, dass beim Verbinden zu einer Datenbank dessen spezifischen Systemkataloge geladen werden und Postgres anscheinend nicht in der Lage ist mehrere dieser Art gleichzeitig zu verwalten. Es gibt allerdings einen Ausweg: dblink. Dabei handelt es sich um eine SQL-Bilbiothek, die mit Postgres ausgeliefert wird und per Datenbank installiert werden kann. Mit Hilfe dieser Bibliothek ist es möglich, Daten aus einer anderen Datenbank abzufragen und andere SQL-Statements zu dieser abzusetzen.

Installation

Die Installation von dblink ist einfach: die Bibliothek liegt im contrib-Ordner der PostgreSQL Installation und wird so in eine Datenbank installiert:

psql -U postgres -d -f `pg_config --sharedir`/contrib/dblink.sql

Das Installieren unter dem Benutzer postgres ist empfehlenswert, da hierbei alle Berechtigungen für etwaige zusätzliche Installationen vorhanden sind. Durch `pg_config --sharedir` findet die Shell das Share-Verzeichnis, in dem der contrib-Ordner liegt. Man sollte sich nur immer im Hinterkopf behalten, dass die Bibliotheken des contrib-Ordners per Datenbank installiert werden (und damit auch Teil eines vollständigen dump sind).

Verwendung

Für Abfragen aus anderen Datenbank muss die dblink-Funktion verwendet werden. Diese wird einfach in der FROM-Clause verwendet:

Select * From dblink('dbname=fauorgdb', 'select id, fauorgpath from orgunit') as t1(id bigint, fauorgpath text);
-- Beispiel auf anderer Postgres Instanz:
Select * From dblink('hostaddr=dbsrv42 dbname=fauorgdb user=fauorg password=drowssap', 'select id, fauorgpath from orgunit') as t1(id bigint, fauorgpath text);

Der erste Parameter der dblink-Funktion definiert die Datenbankverbindung (in diesem Fall lediglich den Datenbanknamen), der zweite Parameter enthält die Anfrage. Der Alias-Teil der FROM-Clause t1(id bigint, fauorgpath text) muss leider verwendet werden, weil Postgres das Schema der abgefragten Daten nicht kennt und sonst schimpft. Eine as-Deklaration ist zwar SQL-Standard, jedoch ist die Definition der Feldtypen eine Postgres-Erweiterung. Da die Verwendung von Abfragen auf einer anderen Datenbank jedoch nicht zur alltäglichen Arbeit gehört, empfiehlt sich die Definition von Views:

create or replace view provisionierte_daten as
select * from dblink('dbname=fauorgdb', 'select id from orgunit') as t1(id bigint);

Die View wird einmal definiert und fertig. Damit lassen sich dann Abfragen der folgenden Art definieren:

select * from provisionierte_daten
except
select "FAUORGId" from "IMP_FAUORG";

Das ist sehr nützlich, um zu prüfen, ob zwei Datenbanken den gleichen Datenbestand haben.

Ultraschnieker Anwendungsfall: selektives Replizieren ODER ‘PostgrESB’ (FT)

Die Kombination aus sehr mächtigem Regelsystem und der Fähigkeit auf entfernten Datenbanken DML-Statements abzusetzen, ermöglicht (selektives) Replizieren von Daten. Hierbei geht es darum eine gesamte Datenbank (oder Teile davon) auf eine andere Datenbank zu replizieren. Alles was man dafür tun muss, ist

1. das gesamte oder partielle Schema der primären Datenbank auf einer sekundären Datenbank zu installieren
2. für alle Tabllen und DML-Operationen (INSERT, UPDATE, DELETE) eine Regel wie folgt zu erstellen:

   CREATE OR REPLACE RULE replic_insert AS
     ON INSERT TO  DO 
       SELECT dblink_exec(
         'dbname=test', 
         'insert into  (id, name) values (' || quote_literal(new.id) || ',' || quote_literal(new.name) || ');
       );
   

Das Beispiel funktioniert für eine Tabelle mit den Feldern id und name. Leider muss das Statement recht kompliziert aufgebaut werden, da die sekundäre Tabelle mit den Pseudonamen NEW und OLD nichts anfangen kann. Wird die Regel darüberhinaus mit einer WHERE-Bedingung definiert, lässt sich die Replikation selektiv einschränken. Des Weiteren muss das Schema nicht notwendigerweise gleich sein, da auch heterogene Datenbanken mit Daten versorgt werden können – vorausgesetzt man kennt das Schema und kann die Regel dementsprechend definieren. Anders ausgedrückt ist also nicht nur die Replikation einer Datenbank möglich, sondern vielmehr die Provisionierung eines Teils des Datenbestands in eine andere PostgreSQL Instanz (um den Kreis zum Titel dieses Abschnitts abzuschließen).

Probleme

Probleme bei der Verwendung traten bei mir lediglich mit Passwörtern auf. Die ‘Fremddatenbank’ benötigt ja ebenfalls Authentifizierung. In meinen Tests wurden lediglich Datenbanken auf derselben lokalen Postgres-Instanz abgefragt und ich habe den lokalen Zugriff in der pg_hba.conf auf trust gesetzt:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
host    all         all         127.0.0.1/32          trust

Eine andere Möglichkeit besteht in der Verwendung der Passwortdatei.

Links

• Postgres Dokumentation
• psql
• pgAdmin
• dblink Bibliothek
• Das Regelsystem von Postgres
• Tieferer Einblick in das PostgreSQL System (inkl. Regelsystem)
• Verwendung einer Passwortdatei

Querying multiple databases in PostgreSQL

Introduction

It is sometimes necessary to compare, import or update data from several databases. Unless you want to write a cumbersome program with several data sources, you are stuck with the database tools themselves. In PostgreSQL’s case, that is the command line interface psql or the GUI pgAdmin. Both of them are unable to process queries for several databases at once. The reason for this is simply that, upon connection to a database, its specific system catalogues are loaded, and Postgres seems unable to manage several of these at once. There is however, a way around this: dblink. This is an SQL library shipped with Postgres that can be installed via database. With this library, it is possible to query data from other databases and make SQL statements for these.

Installation

Installing dblink is easy: The library can be found in the PostgreSQL installation’s contrib folder. You can install it like this:

psql -U postgres -d -f `pg_config --sharedir`/contrib/dblink.sql

It is advisable to install as user postgres, as that gives you all the permissions necessary for possible additional installations. With `pg_config --sharedir`, the Shell finds the share directory that contains the contrib folder. Just keep in mind that the libraries within the contrib folder are installed via database (and are thus part of a complete dump).

Usage

For queries from other databases, you have to use the dblink function. You can use it with a simple FROM clause:

Select * From dblink('dbname=fauorgdb', 'select id, fauorgpath from orgunit') as t1(id bigint, fauorgpath text);
-- Example from another Postgres Instance:
Select * From dblink('hostaddr=dbsrv42 dbname=fauorgdb user=fauorg password=drowssap', 'select id, fauorgpath from orgunit') as t1(id bigint, fauorgpath text);

The dblink functions’s first parameter defines the database connection (in this case, the database name). The second parameter contains the query. The FROM clause’s alias part t1(id bigint, fauorgpath text) must be used because Postgres doesn’t know the queried data’s schema and starts whining otherwise. Although an as declaration would be SQL standard, definition of field types is part of the Postgres expansion. Since the use of queries in another database is probably not part of your everyday work, it is advisable to define Views:

create or replace view provisionierte_daten as
select * from dblink('dbname=fauorgdb', 'select id from orgunit') as t1(id bigint);

You only have to define the View once, and you are done. With this you can then use queries of the following kind:

select * from provisionierte_daten
except
select "FAUORGId" from "IMP_FAUORG";

This is very useful for checking if two databases have the same dataset.

Application deluxe: selective replicaion OR ‘PostgrESB’ (FT)

The combination of a very powerful set of rules and the possibility to put statements into remote databases, allows the selective replication of data. This means replicating a complete database (or parts thereof) into another database. All you have to do for this is

1. installing the primary database’s complete or partial schema on the secondary database.
2. create a rule for all tables and DML operations (INSERT, UPDATE, DELETE) as follows:

   CREATE OR REPLACE RULE replic_insert AS
     ON INSERT TO  DO 
       SELECT dblink_exec(
         'dbname=test', 
         'insert into  (id, name) values (' || quote_literal(new.id) || ',' || quote_literal(new.name) || ');
       );
   

The example works with a table containing the fields id and name. The statement needs to be rather complicated, as the secondary table cannot interprete the pseudo names NEW and OLD. If the rule is futher defined with a WHERE clause, the replication can be made very selective. In addition, the schemata don’t need to be identical, as you can also provide heterogenical databases with data – if you know the schema and can define your rule accordingly. In other words, this allows not only replication of a database, but also provisioning part of your dataset into another PostgreSQL instance.

Problems

The only problems I found were with passwords. The ‘foreign database’ needs authentication as well. In my tests, I only queried local databases within the same Postgres instance, and set the local access in pg_hba.conf to trust:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
host    all         all         127.0.0.1/32          trust

Another possibility is the use of a password file.

Links

• Postgres documentation
• psql
• pgAdmin
• dblink library
• Postgres rule system
• A closer look at the PostgreSQL system (including the rule system)
• Using a password file