Neu in PostgreSQL® 9.5 ist das Feature ROW LEVEL SECURITY.
Was ist ROW LEVEL SECURITY
Mit Hilfe von ROW LEVEL SECURITY lässt sich bestimmen, welche Voraussetzungen erfüllt sein müssen, damit ein Datenbankbenutzer ein Tupel sieht, einfügen, löschen oder bearbeiten darf.
Kurz gesagt, es beseht nun die Möglichkeit Zugriffsberechtigungen auf Tupelebene zu vergeben.
Zur Konfiguration dieser Zugriffsberechtigungen kommen die in PostgreSQL® 9.5 eingeführten, SECURITY POLICIES zum Einsatz. Mit Hilfe dieser können feingranulare Regeln definiert werden, die bei den Zugriff auf Tuple ausgewertet werden.
Beispiel
In unserem Beispiel möchten wir den Zugriff auf eine Tabelle einschränken, die die Verkaufszahlen verschiedener Abteilungen beinhaltet. Wir möchten den Benutzern den Zugriff aber nicht komplett entziehen. Jeder Benutzer soll auf die Zahlen der Abteilung zuzugreifen können der er angehört. Die Zahlen der anderen Abteilungen sollen hingegen nicht sichtbar oder veränderbar sein.
Zunächst benötigen wir eine Zuordnung von Benutzern zu Abteilungen:
avo@[local]:5495 [postgres] > CREATE TABLE user_in_department ( username text, dep text, PRIMARY KEY (username, dep));
avo@[local]:5495 [postgres] > INSERT INTO user_in_department
VALUES ('manuel_mueller' , 'games') ,
('michael_schneider' , 'games') ,
('michael_schneider' , 'tv') ,
('bobdan_muel' , 'tv') ,
('tizian_martin' , 'audio');
Nachdem wir nun eine Zuordnung von Benutzern zu Abteilungen haben, benötigen wir noch unsere „sales“ Tabelle:
avo@[local]:5495 [postgres] > CREATE TABLE sales ( id SERIAL PRIMARY KEY, department text NOT NULL, target_range tsrange, sum numeric);
Als Beispieldaten dienen uns die Verkaufszahlen vier verschiedener Abteilungen:
avo@[local]:5495 [postgres] > INSERT INTO sales (department, target_range, sum)
VALUES ('tv' , '[2015-01-01 00:00 , 2015-02-01 00:00)' , 120000) ,
('games' , '[2015-01-01 00:00 , 2015-02-01 00:00)' , 140000) ,
('computer' , '[2015-01-01 00:00 , 2015-02-01 00:00)' , 2000) ,
('audio' , '[2015-01-01 00:00 , 2015-02-01 00:00)' , 90000);
Die Prüfung der Zugriffsberechtigungen wird über die folgende SECURITY POLICY definiert:
avo@[local]:5495 [postgres] > CREATE POLICY user_in_department
ON sales
FOR ALL
TO public
USING (
(
SELECT COUNT(uid.username) >= 1
FROM user_in_department uid
WHERE uid.username = current_user AND
uid.dep = department
)
);
Zu guter Letzt müssen wir ROW LEVEL SECURITY für die Tabelle „sales“ aktivieren, und einen Beispielbenutzer anlegen:
avo@[local]:5495 [postgres] > ALTER TABLE sales ENABLE ROW LEVEL SECURITY; CREATE USER manuel_mueller; GRANT SELECT ON user_in_department TO manuel_mueller; GRANT SELECT, INSERT, UPDATE, DELETE ON sales TO manuel_mueller;
Frag der Benutzer „manuel_mueller“ nun die Verkaufszahlen ab, so erhält er als Ergebnis nur die Verkaufszahlen der Abteilung der er angehört:
manuel_mueller@[local]:5495 [postgres] > SELECT * FROM sales; id | department | target_range | sum ----+------------+-----------------------------------------------+-------- 2 | games | ["2015-01-01 00:00:00","2015-02-01 00:00:00") | 140000 (1 row)
Selbst wenn es ihm ermöglicht ist, selber Eintragungen innerhalb der Tabelle sales vorzunehmen, wird verhindert, dass er Eintragungen für andere Abteilungen vornimmt. Auch ist es Ihm nicht möglich Eintragungen anderer Abteilungen zu bearbeiten:
manuel_mueller@[local]:5495 [postgres] > UPDATE sales SET sum=sum+100; UPDATE 1
Die aktuellen SECURITY POLICIES können mit SELECT * FROM pg_policies; oder mit Hilfe des Backslashcommands \dp abgefragt werden. In unserem Beispiel sieht das Ergebnis wie folgt aus:
avo@[local]:5495 [postgres] > SELECT * FROM pg_policies ;
-[ RECORD 1 ]-------------------------------------------------------------------------------------
schemaname | public
tablename | sales
policyname | user_in_department
roles | {public}
cmd | ALL
qual | ( SELECT (count(uid.username) >= 1) +
| FROM user_in_department uid +
| WHERE ((uid.username = ("current_user"())::text) AND (uid.dep = sales.department)))
with_check | __NULL__
Zusätzlich zu USING kann die WITH CHECK Option angegeben werden. Diese greift, sobald ein Benutzer Tupel einfügen, ändern oder entfernen möchte.
Das folgende Beispiel unterbindet die nachträgliche Bearbeitung von Eintragungen:
ALTER POLICY user_in_department ON sales WITH CHECK ( target_range @> NOW()::timestamp );
Technische Details
Neben der Einschränkung aller Operationen können auch spezifische Operationen eingeschränkt werden. Dazu wird bei der Erstellung der SECURITY POLICY nicht das Schlüsselwort ALL, sondern die entsprechende Operation eingetragen (SELECT, INSERT, UPDATE oder DELETE).
In unserem Beispiel wirkt sich die Einschränkung auf alle Benutzer aus (TO). Aber auch dies lässt sich spezifizieren. Soll die Überprüfung nur für einen gewissen Kreis an Benutzern geprüft werden, so kann anstelle von „public“ auch eine spezielle Gruppe angegeben werden. Achtung: allen anderen Benutzern wird der Zugriff ohne Prüfung verwehrt.
public
Mit ROW LEVEL SECURITY wurde der Parameter row_security eingeführt. Dieser nimmt einen von drei möglichen Werten an: off, onund force.
- off deaktiviert die Überprüfung der
SECURITY POLICIES. Dies führt dazu, dass alle Tabellen für dieROW LEVEL SECURITYaktiviert worden ist, für normale Benutzer gesperrt werden. Ein versuchter Zugriff auf solch eine Tabelle wird mir der Fehlermeldung „ERROR: insufficient privilege to bypass row security.“ geblockt. - on aktiviert die Überprüfung. Es ist zugleich die Standardeinstellung.
- force aktiviert die Überprüfung. Im Gegensatz zu
onunterliegen in dieser Einstellung auchTABLE OWNERder Überprüfung, welche normalerweise nicht von der Überprüfung betroffen sind.
SUPERUSER unterliegen der ROW LEVEL SECURITY nicht.
Fazit
Das oben genannte Beispiel ist nur eins von vielen denkbaren Einsatzszenarien. Zwar lassen sich viele Operationen auch mit entsprechenden VIEWS oder TRIGGERN realisieren, jedoch bietet PostgreSQL® mir ROW LEVEL SECURITY nun eine einfacher zu administrierende Methode zur Umsetzung. Zugleich entfällt der zusätzliche Overhead für die Ausführung der entsprechenden Trigger.
Details können der PostgreSQL® Dokumentation entnommen werden.
Unter zahlreichen neuen Features der kommenden PostgreSQL®-Version 9.5 sticht ein Feature ganz besonders hervor: In manchen anderen Datenbanksystem bereits seit längerer Zeit verfügbar, bietet PostgresSQL nun ebenfalls die Möglichkeit, UPSERT zu verwenden. Mit Hilfe von UPSERT lassen sich alternative Aktionen beschreiben, die beim Fehlschlagen eines Inserts ausgeführt werden sollen. Mehrere Schritte können in einem einzigen Befehls zusammengefasst werden. Ein Beispiel für solch eine alternative Aktion ist das Ignorieren eventueller Constraint-Fehler oder das Update der bereits vorhandenen Tupel. Ein Beispiel sagt mehr als 1000 Worte. Das folgende Beispiel soll zeigen, wozu das neue Feature eingesetzt werden kann. Als Basis dient uns hierzu eine Anwendung aus dem Bereich einer Autovermietung. Die Tabelle „vermietungen“ beinhaltet den aktuellen Status jedes erfassten Autos: Als Beispieldaten dienen uns zwei einfache Datensätze: Nun soll der Status eines neuen Fahrzeugs eingetragen werden. Normalerweise müsste entweder seitens der Anwendung oder durch entsprechende Trigger sichergestellt sein, dass das Fahrzeug bereits in der Tabelle „vermietungen“ vorhanden ist. Erst dann kann der Status entsprechend geändert werden. Es muss also zwischen bereits vorhandenen und noch nicht vorhandenen Fahrzeugen / Datensätzen unterschieden werden. Dies erzeugt eine Reihe von zusätzlichen SQL-Statements. Mit Hilfe von Wie im obigen Beispiel bereits gezeigt, muss nicht nur angegeben werden was ausgeführt wird, sondern auch wann. So ist es möglich bei Verletzung unterschiedlicher Constraints unterschiedliche Aktionen auszuführen. Ein Auszug aus der PostgreSQL® 9.5 Dokumentation zeigt die Syntax: Im Gegensatz zu zwei separaten Aktionen ( UPSERT ist nur eines von vielen neuen und interessanten Features, die mit Version 9.5 von PostgreSQL® eingeführt werden. Ein Übersicht über die neuen Features bietet das Pgloader ist ein Werkzeug, um große Datenmengen schnell und effizient in eine PostgreSQL®-Datenbank zu laden. Eine Vielzahl an Quellformaten wird unterstützt, natürlich CSV, aber auch andere Datenbanken wie MySQL, SQLite, dBase und MSSQL. Die Daten können beim Import flexibel konvertiert werden. Sollten einige Datensätze nicht importierbar sein, weil sie z.B. ein ungültiges Datumsformat benutzen, das von PostgreSQL® zurückgewiesen wird, so bricht nicht der gesamte Import ab, sondern diese Datensätze werden in Reject-Files abgelegt, wo sie später inspiziert und z.B. manuell korrigiert werden können. Hier ein Beispiel, wie man eine CSV-Datei lädt. Man erstellt zunächst eine Beschreibungsdatei für den Import, hier gc.load: … und ruft dann Pgloader auf: Die aktuelle Version 3 von Pgloader wurde in Common Lisp implementiert und ist laut Dimitri Fontaine, dem Autor von Pgloader, um ein Vielfaches schneller als der Vorgänger in Python. Wir haben in der Vergangenheit schon mit Dimitri zusammengearbeitet, um die notwendigen Common Lisp-Pakete ins Debian-Archiv zu bekommen, die beim Kompilieren von Pgloader benötigt werden, und die jetzt auch im aktuellen Debian-Release 8 „Jessie“ enthalten sind. In den letzten Wochen hat die credativ diese Arbeit nun auf apt.postgresql.org ausgedehnt, womit Pgloader nun auch für die ältere Debian-Version 7 „Wheezy“ und die Ubuntu-Versionen 12.04 „precise“, 14.04 „trusty“ und 14.10 „utopic“ verfügbar ist. Hierfür haben wir Backports des benötigten sbcl-Compilers sowie von über 50 Common Lisp-Bibliotheken erstellt. Damit gibt es nun fertige Pgloader-Pakete für diese Linux-Distribtionen.ROW LEVEL SECURITY ist nur eines von vielen neuen und interessanten Features, die mit Version 9.5 von PostgreSQL® eingeführt werden. Eine Übersicht über die neuen Features bietet das INSERT ... ON CONFLICT ..., oft einfach auch „UPSERT“ genannt.Was ist UPSERT?
Beispiel
CREATE TABLE vermietungen (
kennzeichen TEXT PRIMARY KEY,
status TEXT NOT NULL
)
INSERT INTO vermietungen
(kennzeichen, status)
VALUES
('MG-CD-5432', 'vermietet'),
('MG-CD-6000', 'nicht vermietet');
INSERT ... ON CONFLICT ... kann dies vereinfacht werden.INSERT INTO vermietungen
(kennzeichen, status)
VALUES ('MG-CD-5432', 'nicht vermietet')
ON CONFLICT ON CONSTRAINT vermietungen_pkey DO UPDATE SET status = 'nicht vermietet';
INSERT 0 1
Technische Details
[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
[ ON CONFLICT [ conflict_target ] conflict_action ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
where conflict_target can be one of:
( { column_name_index | ( expression_index ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
ON CONSTRAINT constraint_name
and conflict_action is one of:
DO NOTHING
DO UPDATE SET { column_name = { expression | DEFAULT } |
( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |
( column_name [, ...] ) = ( sub-SELECT )
} [, ...]
[ WHERE condition ]
INSERT mit nachfolgendem UPDATE) handelt es sich bei INSERT ... ON CONFLICT DO UPDATE ... um eine atomare Aktion. Das bedeutet, dass keine zusätzlichen Locks benötigt werden, um nebenläufige Aktionen zu berücksichtigen.LOAD CSV
FROM 'gc.csv' WITH ENCODING iso-8859-1 (latitude, longitude, description, note)
INTO postgresql:///postgres?geocaches (latitude, longitude, description, note)
WITH fields terminated by ',';
$ pgloader gc.load
table name read imported errors time
------------------------------ --------- --------- --------- --------------
fetch 0 0 0 0.005s
------------------------------ --------- --------- --------- --------------
geocaches 81824 81824 0 1.273s
------------------------------ --------- --------- --------- --------------
Total import time 81824 81824 0 1.278s
Seit PostgreSQL® 9.1 implementiert PostgreSQL® eine Schnittstelle für den Zugriff auf externe Datenquellen. Die im SQL Standard festgelegte Schnittstelle (SQL/MED) gestattet den transparenten Zugriff und Manipulation externer Datenquellen in der gleichen Art und Weise wie mit PostgreSQL®-eigenen Tabellen. Die externen Datenquellen erscheinen als Tabellen in der jeweiligen Datenbank und lassen sich in SQL-Anweisung uneingeschränkt verwenden. Der Zugriff wird über einen sogenannten Foreign Data Wrapper (FDW) implementiert, der die Schnittstelle zwischen PostgreSQL® und der externen Datenquelle bildet. Der FDW ist auch dafür verantwortlich, Datentypen oder nicht-relationale Datenquellen entsprechend auf die Tabellenstruktur abzubilden. Dies ermöglicht somit auch die Anbindung von nicht-relationalen Datenquellen wie Hadoop, Redis u.a. Eine Übersicht einiger verfügbarer Foreign Data Wrapper gibt es im PostgreSQL® Wiki.
Informix FDW
Im Kontext vieler Informix-Migrationen entwickelte die credativ GmbH einen Foreign Data Wrapper (FDW) für Informixdatenbanken. Dies unterstützt die Migration, aber auch die Integration von PostgreSQL® in bestehende Informixinstallationen, um Datenaustausch und -verarbeitung zu erleichtern. Der Informix FDW unterstützt alle PostgreSQL® Versionen mit SQL/MED Implementierung, also von 9.1 bis aktuell 9.3. Der Informix FDW unterstützt mit PostgreSQL® 9.3 darüber hinaus schreibende Operationen auf Foreign Tables.
Installation
Die Installation setzt mindestens ein vorhandenes CSDK von IBM für Informix voraus. Das CSDK kann direkt per Download bezogen werden. Im Folgenden gehen wir davon aus, dass eine CSDK-Installation in /opt/IBM/informix vorhanden ist. Der Pfad kann je nach Installation variieren. Die Quellen des Informix FDW lassen sich direkt vom credativ github Repository klonen, oder man besorgt sich ein Release Tarball (zum Zeitpunkt dieses Artikels Version 0.2.1). Die folgende Installationsbeschreibung geht von einem System mit CentOS 6 aus, kann jedoch mit Abweichungen auf jedem anderen Linuxsystem erfolgen.
% wget 'https://github.com/credativ/informix_fdw/archive/REL0_2_1.tar.gz' % tar -xzf REL0_2_1.tar.gz
Anschließend lässt sich der FDW mit Angabe des CSDK-Installationsordners bauen:
% export INFORMIXDIR=/opt/IBM/informix % export PATH=$INFORMIXDIR/bin:$PATH % PG_CONFIG=/usr/pgsql-9.3/bin/pg_config make
Im Listing wurde die Umgebungsvariable PG_CONFIG explizit auf die PostgreSQL® 9.3 Installation gesetzt. Es handelt sich hierbei um eine Installation von PGDG-RPM Paketen auf CentOS 6, in der pg_config außerhalb des Standardpfades liegt. Ferner muss das Paket postgresql93-devel installiert sein. Wurden alle Pfade richtig gesetzt kann der FDW installiert werden.
% PG_CONFIG=/usr/pgsql-9.3/bin/pg_config make install
Dies installiert alle notwendigen Bibliotheken. Damit diese korrekt funktionieren und beim Laden in die PostgreSQL® Instanz ebenfalls die benötigten Informixbibliotheken finden, müssen letztere noch im Dynamic Linker des Systems konfiguriert werden. Ausgehend von der bereits angesprochenen CentOS 6 Plattform geschieht dies am einfachsten über eine zusätzliche Konfigurationsdatei in /etc/ld.so.conf.d (dies erfordert in diesem Fall root-Berechtigung!):
% vim /etc/ld.so.conf.d/informix.conf
Diese Datei sollte die Pfade zu den benötigten Informixbibliotheken enthalten:
/opt/IBM/informix/lib /opt/IBM/informix/lib/esql
Anschließend muss der Cache des Dynamic Linker erneuert werden:
% ldconfig
Die Installation des Informix FDW sollte nun einsatzbereit sein.
Konfiguration
Um den Informix FDW in einer Datenbank verwenden zu können, muss der FDW in die betreffende Datenbank geladen werden. Der FDW ist eine sogenannte EXTENSION und diese werden mit dem CREATE EXTENSION Kommando geladen:
#= CREATE EXTENSION informix_fdw; CREATE EXTENSION
Um eine Informixdatenbank über den Informix FDW nun anzubinden, benötigt man zunächst eine Definition, wie auf die externe Datenquelle zugegriffen werden soll. Hierzu erstellt man mit dem CREATE SERVER Kommando eine Definition mit dem vom Informix FDW benötigten Informationen. Zu beachten ist, dass die Optionen, die einer SERVER-Direktive mitgegeben werden, vom jeweiligen FDW abhängig ist. Für den Informix FDW benötigt man mindestens folgende Parameter:
- informixdir – Installationsverzeichnis des CSDK
- informixserver – Name der Serververbindung, die über das Informix CSDK konfiguriert wurde (siehe hierzu die Informix Dokumentation, bzw. $INFORMIXDIR/etc/sqlhosts)
Das Erstellen der SERVER-Definition erfolgt dann wie folgt:
=# CREATE SERVER centosifx_tcp
FOREIGN DATA WRAPPER informix_fdw
OPTIONS(informixdir '/opt/IBM/informix',
informixserver 'ol_informix1210');
Die Variable informixserver ist der Instanzname der Informixinstanz, hier muss man einfach den Vorgaben der Informixinstallation folgen. Der nächste Schritt erzeugt nun ein sogenanntes Usermapping, mit der man eine PostgreSQL®-Rolle (bzw. Benutzer) auf die Informixzugangsdaten bindet. Ist diese Rolle am PostgreSQL®-Server angemeldet, so nutzt diese automatisch über das Usermapping die angegebenen Logininformation, um sich am Informixserver centosifx_tcp anzumelden.
=# CREATE USER MAPPING FOR bernd
SERVER centosifx_tcp
OPTIONS(username 'informix',
password 'informix')
Auch hier sind die in der OPTIONS-Direktive spezifizierten Parameter vom jeweiligen FDW abhängig. Nun können sogenannte Foreign Tables zum Einbinden von Informixtabellen angelegt werden. Im folgenden Beispiel wird eine einfache Relation mit Strassennamen vom Informixserver ol_informix1210 in die PostgreSQL®-Instanz eingebunden. Wichtig ist, dass die Konvertierungen von Datentypen von Informix zu PostgreSQL® mit kompatiblen Datentypen erfolgt. Zunächst die Definition der Informixtabelle, wie sie in der Informixinstanz angelegt wurde:
CREATE TABLE osm_roads(id bigint primary key,
name varchar(255),
highway varchar(32),
area varchar(10));
Die Definition in PostgreSQL® sollte analog erfolgen, können Datentypen nicht konvertiert werden wird eine Fehler beim Erzeugen der Foreign Table geworfen. Wichtig beim Konvertieren von Zeichenkettentypen wie bspw. varchar ist auch, dass beim Erzeugen der Foreign Table eine entsprechende Zielkonvertierung definiert wird. Der Informix FDW verlangt daher folgende Parameter beim Anlegen einer Foreign Table:
- client_locale – Locale des Clients (sollte identisch zum Server Encoding der PostgreSQL®-Instanz sein)
- db_locale – Locale des Datenbankservers
- table oder query – Die Tabelle bzw. SQL Abfrage, auf der die Foreign Table basiert. Eine auf einer Abfrage basierende Foreign Table kann keine modifizierenden SQL-Operationen ausführen.
#= CREATE FOREIGN TABLE osm_roads(id bigint,
name varchar(255),
highway varchar(32),
area varchar(10))
SERVER centosifx_tcp
OPTIONS(table 'osm_roads',
database 'kettle',
client_locale 'en_US.utf8',
db_locale 'en_US.819');
Wer sich nicht sicher ist, wie die Standardlocale der PostgreSQL® Datenbank aussehen, kann diese sich am einfachsten per SQL anzeigen lassen:
=# SELECT name, setting FROM pg_settings WHERE name IN ('lc_ctype', 'lc_collate');
name | setting
------------+------------
lc_collate | en_US.utf8
lc_ctype | en_US.utf8
(2 rows)
Die Locale- und Encodingeinstellungen sollten unbedingt möglichst übereinstimmend gewählt werden. Sind alle Einstellungen korrekt gewählt worden, kann direkt über PostgreSQL® auf die Datenbestände der Tabelle osm_roads zugegriffen werden:
=# SELECT id, name FROM osm_roads WHERE name = 'Albert-Einstein-Straße'; id | name ------+------------------------ 1002 | Albert-Einstein-Straße (1 row)
Je nach Anzahl an Tupel in der Foreign Table kann das Selektieren eine signifikante Dauer aufweisen, da bei inkompatiblen WHERE-Klauseln die gesamte Datenmenge per Full Table Scan erst an den PostgreSQL®-Server übermittelt werden muss. Der Informix FDW unterstützt jedoch Predicate Pushdown unter bestimmten Voraussetzungen. Darunter versteht man die Fähigkeit, Teile der WHERE-Bedingung, die die Foreign Table betreffen an den Remote Server zu übermitteln und dort bereits die Filterbedingung anzusetzen. Das erspart die Übertragung an sich nutzloser Tupel, da diese ja im PostgreSQL® Server herausgefiltert werden würden. Obiges Beispiel sieht im Ausführungsplan beispielsweise so aus:
#= EXPLAIN (ANALYZE, VERBOSE)
SELECT id, name
FROM osm_roads WHERE name = 'Albert-Einstein-Straße';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Foreign Scan on public.osm_roads (cost=2925.00..3481.13 rows=55613 width=24) (actual time=85.726..4324.341 rows=1 loops=1)
Output: id, name
Filter: ((osm_roads.name)::text = 'Albert-Einstein-Straße'::text)
Rows Removed by Filter: 55612
Informix costs: 2925.00
Informix query: SELECT *, rowid FROM osm_roads
Total runtime: 4325.351 ms
Die Filter-Anzeige in diesem Ausführungsplan zeigt, dass insgesamt 55612 Tuple ausgefiltert wurde, letztlich nur ein einziges Tupel wurde zurückgegeben, da es die Filterbedingung erfüllte. Das Problem liegt hier am impliziten Cast, den PostgreSQL® in der WHERE-Bedingung über die Zeichenkettenspalte name legt. Aktuelle Versionen des Informix FDW berücksichtigen dies noch nicht. Jedoch können Prädikate an den Foreign Server übermittelt werden, wenn diese bspw. Ganzahltypen entsprechen:
#= EXPLAIN (ANALYZE, VERBOSE) SELECT id, name FROM osm_roads WHERE id = 1002;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Foreign Scan on public.osm_roads (cost=2925.00..2980.61 rows=5561 width=40) (actual time=15.849..16.410 rows=1 loops=1)
Output: id, name
Filter: (osm_roads.id = 1002)
Informix costs: 2925.00
Informix query: SELECT *, rowid FROM osm_roads WHERE (id = 1002)
Total runtime: 17.145 ms
(6 rows)
Datenmodifikation
Mit PostgreSQL® 9.3 unterstützt der Informix FDW auch das Manipulieren (UPDATE), Löschen (DELETE) und Einfügen (INSERT) von Daten in die Foreign Table. Darüber hinaus interagieren Transaktionen, die im PostgreSQL® Client gestartet wurden implizit mit Transaktionen auf dem Foreign Server (unter der Voraussetzung, dass die Informixdatenbank mit Logging angelegt wurde und daher transaktionsfähig ist). Folgendes Beispiel demonstriert diese Fähigkeit:
=# BEGIN; BEGIN *=# INSERT INTO osm_roads(id, name, highway) VALUES(55614, 'Hans-Mustermann-Straße', 'no'); INSERT 0 1 *=# SAVEPOINT A; SAVEPOINT *=# UPDATE osm_roads SET area = 'Nordrhein-Westfalen' WHERE id = 55614; ERROR: value too long for type character varying(10) !=# ROLLBACK TO A; ROLLBACK *=# UPDATE osm_roads SET area = 'NRW' WHERE id = 55614; UPDATE 1 *=# COMMIT; COMMIT =# SELECT * FROM osm_roads WHERE id = 55614; id | name | highway | area -------+------------------------+---------+------ 55614 | Hans-Mustermann-Straße | no | NRW (1 row)
Das Beispiel erzeugt zunächst einen neuen Datensatz nach dem Start einer Transaktion. Anschließend wird ein SAVEPOINT gesetzt, um den aktuellen Zustand dieser Transaktion zu sichern. Im nächsten Schritt wird der neue Datensatz noch modifiziert, da man vergessen hatte, die Lokalität des neuen Datensatzes zu spezifizieren. Da jedoch nur Länderkürzel erlaubt sind, schlägt dies aufgrund der Überlänge des neuen Bezeichners fehl, die Transaktion wird ungültig. Durch den SAVEPOINT wird auf den zuletzt angelegten Sicherungspunkt der Transaktion zurückgerollt, der Informix FDW setzt ebenfalls implizit auf diesen SAVEPOINT auf den Foreign Server zurück. Anschließend kann innerhalb dieser Transaktion der korrekte Ländercode eingetragen werden. Nach dem COMMIT bestätigt der Informix FDW ebenfalls die Transaktion auf dem Foreign Server, der Datensatz ist korrekt eingetragen worden.
Zusammenfassung
Foreign Data Wrapper sind ein sehr leistungsfähiges und flexibles Werkzeug, um PostgreSQL® in heterogenen Datenbanklandschaften zu integrieren. Dabei sind diese Schnittstellen keinesfalls auf reine relationale Datenquellen beschränkt. Mit der Unterstützung von modifizierenden SQL-Abfragen (DML) ermöglicht die FDW-API ferner auch die Integration schreibender Datenquellen in PostgreSQL®-Datenbanken.
Dieser Artikel wurde ursprünglich von Bernd Helmle geschrieben.
In den aktuellen Versionen von PostgreSQL® hat sich ein kritischer Fehler eingeschlichen.
Betroffen sind alle aktuellen Versionen der Hauptzweige 9.0, 9.1, 9.2 und alle Versionen für 9.3. Dies sind im einzelnen
- 9.0.14
- 9.1.10
- 9.2.5
- 9.3.0 und 9.3.1
Der Fehler an sich ist relativ schwer zu diagnostizieren und betrifft ausschließlich Standbyserver, die mit dem Parameter hot_standby=on betrieben werden. Betroffene Standbyserver können folgende Symptome aufweisen:
- Fehlende Zeilen in Tabellen auf dem Standbyserver
- Zeilen sind auf dem Standby noch vorhanden obwohl auf dem Master gelöscht
- Alte Werte tauchen in den Zeilen auf Standbyserver auf, obwohl diese auf dem Master aktualisiert wurden
- Index und Constraint Fehler (teilweise auch als Folgefehler)
Durch sogenannte Hintbits können diese Symptome verdeckt werden, so dass die Fehler erst viel später erkannt werden. Die Fehlerbedingung wird durch (Neu-)Starten des Standbyservers ausgelöst, während des Startens werden fälschlicherweise bereits bestätigte Transaktionen als Uncommitted markiert. Auch müssen sehr viele Schreibaktionen innerhalb einer bestimmten Zeit aufgelaufen sein, um diesen Bug auszulösen. Nutzer der Versionen 9.0.13, 9.1.9 und 9.2.4 sollten die nächste Version überspringen und auf die aktualisierten Versionen 9.0.15, 9.1.11 und 9.2.6 warten. Diese werden in der ersten Dezemberwoche veröffentlicht.
Wurde bereits auf ein betroffenes Release aktualisiert, so sollte für die Standbyserver mit den Versionen aus den 9.0, 9.1 und 9.2 Zweigen unbedingt ein Downgrade in Erwägung gezogen werden. Ein anschließendes neues Aufsetzen des Standby ist angeraten. Alternativ kann auch mit einem bereits aktualisiertem Standby nach erneutem Basebackup und Neuaufsetzen der Betrieb fortgesetzt werden. Voraussetzung ist, dass während der Basissicherung für den Standby und bis zu dessem Start keinerlei Schreibaktivität auf dem Primary stattfindet. Der Fehler wird nur während des Starts des Standby ausgelöst, dies bedeutet jedoch eine Downtime für den Master. Anschließend sollte ein Neustart des Standby bei gleichzeitig hohem Schreibaufkommen auf dem Master unbedingt vermieden werden.
Wer auf Leseabfragen verzichten kann, sollte bis zur Verfügbarkeit einer neuen fehlerbereinigten Version auf jeden Fall auch das Abschalten des Parameters hot_standby in Erwägung ziehen. Dies ist auch für alle Versionen des 9.3 Zweiges angeraten, da hier der Fehler in allen verfügbaren Versionen enthalten ist. Auch hier gilt, dass der Standby komplett ohne Schreibaktivität auf dem Master neu aufgebaut werden sollte, um Korruption vorzubeugen.
Nutzer, die direkt aus den Quelltext ihre Systeme aufbauen, können den Patch direkt aus dem git Master in ihre Buildumgebung integrieren. Weiterführende Informationen findet sich auch auf einer eigenen Seite im PostgresQL Wiki.
Sollten Sie unsere Unterstüzung benötigen, dann hilft Ihnen unser Open Source Support Center gerne weiter.
Das Standardtool zur Überwachung von PostgreSQL®-Datenbanken ist check_postgres. Die jetzt veröffentlichte und mit Hilfe von credativ entwickelte Version 2.21.0 unterstützt nun auch PostgreSQL® 9.3. Auf apt.postgresql.org ist die neue Version bereits als Paket für Debian und Ubuntu verfügbar.
check_postgres besteht aus fast 60 einzelnen Tests mit eigenen Namen, z.B. check_postgres_connection. Da die meisten dieser Tests nur für spezielle Setups sinnvoll sind, sollte man sich zunächst auf einige Basistests beschränken.
Wichtige Checks
- check_postgres_archive_ready: Wenn WAL-Archiving aktiviert ist: Die Zahl der noch nicht archivierten xlog-Files sollte in der Regel 0 sein.
- check_postgres_backends: Zahl der Verbindungen. Das Charmante an diesem Check ist, dass er sich über max_connections automatisch einstellt. Ggf. ist 90% aber zu nah am kritischen Wert dran, so dass man in der Praxis evtl. früher gewarnt werden möchte.
- check_postgres_connection: Einfacher Verbindungs-Test. Leider ohne Möglichkeit bei langsamem Verbindungsaufbau zu warnen. (Die Zeit wird aber als „performance data“ angezeigt.)
- check_postgres_hot_standby_delay: Wenn (Streaming-)Replikation benutzt wird: Verzögerung des Slave-Servers. Das Delay wird leider in Transaktionen gemessen, was schwierig einzustellen ist.
- check_postgres_locks: Die Zahl der normalen Locks hängt stark von der Datenbank ab, aber eine übermäßige Zahl sollte eine Warnung ergeben. Sinnvoll erscheint, den Test überall auszurollen, und bei einzelnen Datenbanken zu ändern/deaktivieren, bei denen es falsche Alarme gibt.
- check_postgres_txn_idle: Prüft auf langlaufende „idle“-Transaktionen. Lang laufende Transaktionen, die nichts tun, sind meistens ein Anwendungsfehler oder ein Bedienfehler. Wenn dies über längere Zeit vorliegt, kann Vacuum nicht richtig arbeiten, da die Transaktion die alten Tupel noch sehen kann. Eine Einstellung der Größenordnung zwischen warning=1h/critical=4h bis warning=4h critical=12h erscheint sinnvoll.
- check_postgres_wal_files: Prüft die Zahl WAL-Dateien im pg_xlog-Verzeichnis. Dieser Check prüft etwas ähnliches wie archive_ready; in Hochlast-Situationen kann es allerdings auch ohne WAL-Archiving vorkommen, dass hier viele Files in pg_xlog/ anfallen. Wenn es deutlich mehr sind als 3*checkpoint_segments sollte man die Ursache prüfen. (Der Check konfiguriert sich leider nicht automatisch über diese Einstellung.)
Nützliche Checks
Diese Checks sind nützlich, aber nicht immer anwendbar, oder benötigen spezielle Konfiguration pro Datenbank:
- check_postgres_autovac_freeze: Prüft wie nah Datenbanken an autovacuum_freeze_max_age sind. Normalerweise passiert ein „Freeze“ automatisch, sobald die Datenbank sich autovacuum_freeze_max_age nähert. Falls Transaktionen sehr lange offen sind (mehrere 100 Millionen andere Transaktionen), kann das problematisch werden. In der Praxis tritt das Problem wenn überhaupt nur bei sehr aktiven Datenbanken auf, der Check ist aber sehr leicht zu installieren und benötigt kein Tuning.
- check_postgres_bloat: Tabellen- und Index-Bloat. Vacuum ist meist das größte Thema für den PostgreSQL®-Administrator. Dieser Check überwacht, ob Tabellen größer sind, als sie von der Zahl der Tupel her sein sollten. Die dafür angestellte Berechnung ist aber nur eine sehr grobe Schätzung, und auch die Definition, welcher Overhead (Bloat) für eine gegebene Tabelle noch OK ist, hängt stark von der Benutzung der Tabelle ab. Der Check ist außerdem nicht einfach zu konfigurieren. Auf keinen Fall sollte man sich durch Warnungen verunsichern lassen, sondern diesen Check nur als Hinweis nehmen, ggf. genauer hin zu schauen.
- check_postgres_custom_query: Kein wirklicher Check, aber eine einfache Möglichkeit, eigene SQL-Queries in Warnungen umzusetzen.
- check_postgres_database_size: Prüft auf zu große Datenbanken. Nur sinnvoll, wenn man eine obere Grenze für die DB-Größe hat. Ggf. kann man das am Anfang einstellen, um bei DB-Wachstum benachrichtigt zu werden.
- check_postgres_disabled_triggers: Ggf. sinnvoll, um Trigger zu finden, die bei Wartungsarbeiten abgeschaltet und vergessen wurden.
- check_postgres_disk_space: Alternative zum direkten Check mit anderen Nagios-Plugins.
- check_postgres_hitratio: Dieser Check könnte einen Hinweis geben, dass der DB-Server vielleicht zu wenig RAM (shared_buffers) hat. Je nach Workload kann das aber auch völlig normal sein. Schwierig ohne genauere Kenntnis der Datenbank einzustellen.
- check_postgres_last_analyze: Zeit seit dem ältesten Analyze.
- check_postgres_last_autoanalyze: Zeit seit dem ältesten Autoanalyze.
- check_postgres_last_autovacuum: Zeit seit dem ältesten Autovacuum.
- check_postgres_last_vacuum: Zeit seit dem ältesten Vacuum. Ähnlich wie der Bloat-Checks sind diese Check schwierig einzustellen, Tabellen an denen keine Änderungen passieren müssen nicht jedesmal neu von Analyze oder Vacuum besucht werden. Als Anfang könnte man die Schwellwerte sehr hoch stellen (3 Monate?). Wenn sich in der Praxis herausstellt, dass man hier überwiegend nur falsche Alarme erhält, sollte man den Check einfach weglassen.
- check_postgres_prepared_txns: Offene Prepared Transactions (nicht zu verwechseln mit Prepared Statements) sind ein großes Problem, da sie auch über Server-Neustarts liegen bleiben, und dann Vacuum verhindern und allgemein mit laufenden Transaktionen kollidieren können. Im schlimmsten Fall läuft man in eine Datenbank, die nicht „gefreezt“ werden kann (siehe autovac_freeze). Eigentlich ein wichtiger Check, aber das Feature ist per default in der Server-Config deaktiviert.
- check_postgres_query_runtime: Prüft Laufzeit einer angegebenen Query. Analog custom_query eine Möglichkeit, eigene Checks zu bauen.
- check_postgres_query_time: Prüft, ob es akutell lang laufende Queries gibt. Die Default-Einstellungen sollte man deutlich erhöhen, z.B. auf 4h/24h. (Oder man passt es nach Erfahrung im Betrieb an.)
- check_postgres_sequence: Sequenzen haben per Default ein Maximum (bei bigserial allerdings ziemlich groß). Wenn das Ende erreicht wird, kann die Datenbank keine neuen Werte mehr bekommen.
- check_postgres_txn_time: Prüft, ob es akutell lang laufende Transaktionen gibt. Analog query_time. (Vermutlich ist es nicht sinnvoll, beide Checks zu benutzen.)
- check_postgres_txn_wraparound: Prüft wie nah Datenbanken am Transaktions-ID-Wraparound sind. Analog autovac_freeze prüft dieser Check, wie nah an der maximalen Transaktionszahl die Datenbank ist, bevor „vacuum freeze“ spätestens aufgerufen werden muss (normal passiert das automatisch durch Autovacuum). Beide Checks prüfen das gleiche, autovac_freeze wird viel früher anspringen, das eigentliche Problem wird dann durch txn_wraparound geprüft.
Häufig gab es den Wunsch, auf Debian- und Ubuntu-Systemen eine aktuellere (oder ältere) PostgreSQL®-Version einsetzen zu können, als mit der benutzen Debian- oder Ubuntu-Distribution mitgeliefert wird. Dieses Problem wurde mit Unterstützung der credativ GmbH, gemeinsam von den Debian- und PostgreSQL®-Entwicklern gelöst, indem Ende 2012 apt.postgresql.org ins Leben gerufen wurde. Dieses Apt-Repository bietet PostgreSQL®-Pakete für die aktuell unterstützten Versionen an. Momentan sind dies PostgreSQL® 8.4, 9.0, 9.1, 9.2 und seit letzter Woche auch 9.3, jeweils für Debian Wheezy (7.0), Squeeze (6.0) und Ubuntu Precise (12.04) und Lucid (10.04) für amd64 (64-bit) und i386 (32-bit).
In diesen Pakten ist der PostgreSQL®-Server enthalten, sowie der Client psql, und mit postgresql-contrib eine Sammlung von Zusatzmodulen („Extensions“), die mit dem PostgreSQL®-Source ausgeliefert werden.
Das Repository geht über diese Auswahl hinaus und bietet außerdem vorkompilierte Binärpakete (.deb) und Extensions für die verschiedenen PostgreSQL®-Versionen. Seit dem 9.3-Release arbeiten wir mit anderen in der Community zusammen daran, diese Extensions auch für 9.3 als Pakete zu erstellen. Bisher sind verfügbar:
- postgresql-9.3-ip4r – IPv4 and IPv6 types for PostgreSQL® 9.3
- postgresql-9.3-pgmp – arbitrary precision integers and rationals for PostgreSQL® 9.3
- postgresql-9.3-pgpool2 – connection pool server and replication proxy for PostgreSQL® – modules
- postgresql-9.3-plproxy – database partitioning system for PostgreSQL® 9.3
- postgresql-9.3-plsh – PL/sh procedural language for PostgreSQL® 9.3
- postgresql-9.3-plv8 – Procedural language interface between PostgreSQL® and JavaScript
- postgresql-9.3-slony1-2 – replication system for PostgreSQL®: PostgreSQL® 9.3 server plug-in
- postgresql-9.3-pgmemcache – PostgreSQL® interface to memcached
- postgresql-9.3-postgis-2.1 – Geographic objects support for PostgreSQL® 9.3
Erwähnenswert ist hier insbesondere, dass es bislang nur Pakete für PostGIS 1.5 gab, und jetzt auch PostGIS 2-Pakete zur Verfügung stehen, die ebenfalls in Zusammenarbeit in der Community erstellt wurden.
Jede Anwendung oder Anwender kommt direkt oder indirekt mit ihnen in Berührung: Prepared Statements, oder auf gut Deutsch, Vorbereitete Abfragen. Hierbei handelt es sich einfach erklärt um parametrisierte Abfragen, die einfach wiederverwendet werden können. Ziel ist es, die Analyse und Planerstellung für die späteren Ausführungen der Abfrage nur einmal durchzuführen und den dabei entstehenden Abfrageplan einfach wieder zu verwenden. In PostgreSQL® kommen Prepared Statements in vielfältiger Weise zum Zuge: Per SQL mittels PREPARE und EXECUTE Kommandos oder auf Protokollebene, indem der Datenbanktreiber wie JDBC oder DBD:Pg hierfür per API-Aufrufe mit bspw. execute() diese implizit verwenden.
Fast jede PostgreSQL®-Anwendung nutzt heutzutage daher Prepared Statements. Bis PostgreSQL® 9.1 hatte diese Funktionalität jedoch eine Schwachstelle. Wurde eine Abfrage vorbereitet, so erzeugte der PostgreSQL® Optimizer stets einen sogenannten generischen Plan. Ein Plan ist eine Art Arbeitsanweisung, wie die Abfrage innerhalb der Datenbank abgewickelt werden soll. Mittels PREPARE wird zum Beispiel zur Laufzeit bis PostgreSQL® 9.1 ein generischer Abfrageplan für alle weiteren Ausführungen dieses Prepared Statements festgelegt:
PREPARE ps_get_customerid(text) AS SELECT customerid FROM customers WHERE username LIKE $1;
Dies erzeugt ein Prepared Statement mit dem Bezeichner ps_get_customer_id, das entsprechend mit EXECUTE wiederverwendet werden kann:
EXECUTE ps_get_customerid('user26');
Die zugrundeliegende Abfrage nutzt den LIKE Operator, um auch eine Prefixsuche zu ermöglichen:
EXECUTE ps_get_customerid('user26%');
Beide Abfragen haben jedoch denselben generischen Abfrageplan:
EXPLAIN EXECUTE ps_get_customerid('user26');
QUERY PLAN
-------------------------------------------------------------
Seq Scan on customers (cost=0.00..738.00 rows=100 width=4)
Filter: ((username)::text ~~ $1)
(2 rows)
EXPLAIN EXECUTE ps_get_customerid('user26%');
QUERY PLAN
-------------------------------------------------------------
Seq Scan on customers (cost=0.00..738.00 rows=100 width=4)
Filter: ((username)::text ~~ $1)
(2 rows)
Beide Abfragen könnten jedoch auch einen Index verwenden, denn die customers Tabelle definiert bereits einen Index mit text_pattern_ops Operatorklasse über die username Spalte. Dies ermöglicht die Indexbenutzung auch für LIKE-Operatoren, die das Patternmatching am Ende des Suchbegriffes verwenden. Die verwendete Indexdefinition sei hier der vollständighalber ebenfalls genannt:
CREATE INDEX ON customers (username text_pattern_ops);
Die Abfrage direkt ohne PREPARE auszuführen zeigt das Dilemma:
EXPLAIN SELECT * FROM customers WHERE username LIKE 'user26%';
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on customers (cost=6.30..382.35 rows=202 width=268)
Filter: ((username)::text ~~ 'user26%'::text)
-> Bitmap Index Scan on customers_username_idx (cost=0.00..6.25 rows=200 width=0)
Index Cond: (((username)::text ~>=~ 'user26'::text) AND ((username)::text ~<~ 'user27'::text))
(4 rows)
In PostgreSQL® 9.1 kann dieser Index in Prepared Statements jedoch nicht verwendet werden. Der generische Abfrageplan kann eine derartige Parametrisierung nicht berücksichtigen. Ab PostgreSQL® 9.2 jedoch versucht der Optimizer einen spezifischen Abfrageplan für jeden möglichen Zugriffspfad zu erzeugen. Die Tiefe ist jedoch begrenzt, maximal fünf Pläne werden berücksichtigt. Diese Pläne nennen sich Custom Plans und können auch parametrisierte Zugriffspade berücksichtigen. Wiederholt man das Beispiel von oben ergibt sich in PostgreSQL® 9.3 dann folgendes Bild:
EXPLAIN SELECT * FROM customers WHERE username LIKE 'user26%';
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on customers (cost=6.30..382.35 rows=202 width=268)
Filter: ((username)::text ~~ 'user26%'::text)
-> Bitmap Index Scan on customers_username_idx (cost=0.00..6.25 rows=200 width=0)
Index Cond: (((username)::text ~>=~ 'user26'::text) AND ((username)::text ~<~ 'user27'::text))
(4 rows)
EXPLAIN EXECUTE ps_get_customerid('user26%');
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on customers (cost=6.30..382.35 rows=202 width=4)
Filter: ((username)::text ~~ 'user26%'::text)
-> Bitmap Index Scan on customers_username_idx (cost=0.00..6.25 rows=200 width=0)
Index Cond: (((username)::text ~>=~ 'user26'::text) AND ((username)::text ~<~ 'user27'::text))
(4 rows)
Der Optimizer wählt nun denselben Ausführungsplan für beide Abfragen, analog zur direkt ausgeführten Abfrage.
Besonders interessant ist dies für alle implizit erzeugten Prepared Statements. Diese spielen eine besondere Rolle in PL/pgSQL, da hier jede Abfrage implizit ein Prepared Statement ist. Der Artikel zur Serie
CREATE SEQUENCE test_id_seq;
CREATE TABLE test
AS
SELECT nextval('test_id_seq'::regclass), t.datum
FROM generate_series('2008-01-01'::timestamptz, '2013-12-31'::timestamptz, interval '1 hour') AS t(datum);
CREATE INDEX ON test(datum);
CREATE OR REPLACE FUNCTION get_test_datum_ids(p_datum timestamp)
RETURNS SETOF integer
STABLE
LANGUAGE plpgsql
AS
$$
DECLARE
v_id int;
BEGIN
FOR v_id IN SELECT * FROM test WHERE datum < p_datum
LOOP
RETURN
NEXT v_id;
END LOOP;
RETURN;
END;
$$;
Analog zum Artikel wird anschließend die Analyse mittels auto_explain wiederholt:
LOAD 'auto_explain';
SET auto_explain.log_min_duration TO '0ms';
SET auto_explain.log_nested_statements TO 'on';
SELECT get_test_datum_ids('01.02.2008'::timestamp);
Ist auto_explain korrekt konfiguriert, findet man den Abfrageplan für den SELECT innerhalb der FOR-Schleife der Prozedur im Logfile der PostgreSQL®-Instanz:
Query Text: SELECT * FROM test WHERE datum < p_datum Index Scan using test_datum_idx on test (cost=0.00..33.89 rows=779 width=16) Index Cond: (datum < '2008-02-01 00:00:00'::timestamp without time zone)
Es zeigt sich, dass der Optimizer nun tatsächlich einen Custom Plan auf Basis eines Indexscan für diesen Parameter wählt. In PostgreSQL® 9.1 wäre dies ein generischer Plan, der in diesem Beispiel einfach wieder einen sequentiellen Scan (SeqScan) der Tabellen zur Folge hätte (sieher hierzu auch das Listing im bereits genannten vorhergehenden Blogartikel dieser Serie). Dies vereinfacht die Entwicklung solcher Prozeduren nun erheblich, da auf dynamische Abfragen mittels EXECUTE in PL/pgSQL verzichtet werden kann.
VACUUM in PostgreSQL® ist seit jeher mit Mythen und falschen Informationen behaftet. Besonders verbreitet ist offenbar die Einstellung, VACUUM FULL helfe vorbeugend. Das genaue Gegenteil ist häufig der Fall.
VACUUM – Der Staubsauger
Seit der Einführung von MVCC (Multi Version Concurrency Control) in PostgreSQL® 6.5 im Jahr 1999 gibt es das Kommando VACUUM. Mit Hilfe dieses Kommandos wird der sogenannte Heap, also die Dateien, die die Tabellendaten enthalten, defragmentiert und nicht mehr belegter Speicherplatz freigegeben. Dies ist notwendig, da PostgreSQL® Zeilen bei UPDATE oder DELETE nicht etwa physikalisch löscht, sondern eine neue Version der Zeile anlegt bzw. die Zeile einfach als gelöscht markiert. Die alte Version muss noch so lange beibehalten werden, wie es auch Transaktionen gibt, die diese Zeilenversion noch „sehen“ können. Ist eine Tabelle sehr stark durch UPDATE oder DELETE/INSERT frequentiert, und passiert VACUUM zu selten (beispielsweise weil Autovacuum nicht verwendet wird), so kann der sogenannte „tote“ Speicherplatz in einer Tabelle sehr stark anwachsen.
VACUUM FULL – Vorbeugende Reorganisation?
Viele Administratoren sind daher der Auffassung, dass es aus diesem Grund angebracht ist, dies im Vorfeld durch nächtliche VACUUM FULL Jobs dem Anwachsen der Tabelle vorzubeugen. Dies ist eine schlechte Strategie, aus mehreren Gründen:
- VACUUM FULL benötigt im Gegensatz zu normalem VACUUM eine exklusive Tabellensperre, d.h. der Zugriff ist für alle nebenläufigen Transaktionen nicht möglich (auch reine Leseanfragen).
- VACUUM FULL führt eine komplette physische Reorganisation der Tabelle durch, nicht jedoch der Indexe. Dies hat sich mit PostgreSQL® 9.0 geändert. Eine exklusive Tabellensperre ist weiterhin notwendig.
- Läuft eine Datenbank mit WAL-Archiving, so kommt es durch VACUUM FULL zu massiv erhöhtem Datenaufkommen im Transaktionslog. Dies kann Probleme mit dem Backuparchiv nach sich ziehen.
- Man benötigt auf jeden Fall ein Wartungsfenster für exklusiven Zugriff der Tabellen.
- Im Gegensatz zu normalen VACUUM ist VACUUM FULL daher nicht für den Einsatz in 24/7-Datenbanken geeignet.
Während sich die meisten Nachteile durch ein Wartungsfenster umschiffen lassen, sind die Nachteile durch sehr häufiges VACUUM FULL gravierender. Besonders PostgreSQL®-Versionen bis einschließlich 8.4 sind davon betroffen. Um das zu verstehen, muss man sich die Funktionsweise des VACUUM FULL Kommandos in diesen Versionen ansehen:
- VACUUM FULL untersucht die Tabelle sequentiell nach totem Speicherplatz. Hierzu werden die gefundenen toten Bereiche während VACUUM FULL in einem Array im Hauptspeicher gespeichert. Ist das Array voll (begrenzt durch maintenance_work_mem), so werden sichtbare (also aktive) Zeilen von unten her in die gefundenen toten Bereiche verlagert (sofern Platz hierfür ausreichend zur Verfügung steht).
- Sind Indexe auf der Tabelle vorhanden, so müssen diese ebenfalls aktualisiert werden.
- Ist das Array abgearbeitet, beginnt der Algorithmus wieder von vorne, solange, bis das Ende der Tabelle erreicht ist.
- Anschließend wird die Tabelle physisch verkleinert.
Das Hauptproblem ist das Umsortieren der Zeilen in den freigewordenen Speicherplatz. Dies sorgt für massive I/O auf dem Speichersystem. Noch schwerwiegender ist jedoch die Tatsache, dass beim Umsortieren der Index ebenfalls aktualisiert werden muss. Passiert das sehr häufig, so kann es passieren, dass der Index selbst sehr stark fragmentiert. In diesem Fall wächst der Index selbst an, man spricht dann vom sogenannten Index Bloat. Daher kann es erforderlich sein, direkt nach dem VACUUM FULL ein REINDEX auf die Tabellen auszuführen, insbesondere wenn Tabellen sehr stark fragmentiert waren und viele Tupel umsortiert wurden. Dies alles sorgt bei sehr großen Tabellen auch für sehr lange Laufzeiten. Ab PostgreSQL® 9.0 verhält sich VACUUM FULL wie das CLUSTER Kommando, d.h. die Tabelle wird sequentiell gelesen und parallel komplett neu aufgebaut. Dies hat den Vorteil, dass man nur die Zeilen liest, die aktiv sind und die „toten“ Zeilen außen vor lässt. Anschließend werden die Indexe neu erzeugt. Dies eliminiert viele Nachteile des alten Algorithmus, vermeidet jedoch nicht die Notwendigkeit exklusiver Tabellensperren. Ferner benötigt die Reorganisation der Tabelle im schlechtesten Falle nochmal soviel Speicherplatz, wie die aktuell zu bearbeitende Tabelle.
VACUUM und Autovacuum für tägliche oder sehr granulare Wartung
VACUUM bzw. Autovacuum sind für die tägliche oder dauerhafte Wartung von PostgreSQL®-Datenbanken ausgelegt.
- Wer sich eine sorgfältige VACUUM-Policy mit normalem VACUUM oder, noch besser, Autovacuum zurechtlegt, benötigt kein VACUUM FULL.
- Autovacuum sollte auf jeden Fall in Betracht gezogen werden, muss jedoch an den Workload angepasst werden.
- Ist dennoch mal eine Tabelle sehr stark aufgebläht, so kann mit aktuellen 8er PostgreSQL®-Versionen mit CLUSTER die Tabelle häufiger deutlich schneller verkleinert werden, ohne das Problem der Indexfragmentierung. Da CLUSTER anhand eines Index die Tabelle reorganisiert, benötigt man mindestens einen Index. Ferner sollte unbedingt danach die Optimizerstatistiken mit ANALYZE aktualisiert werden.
- Bis einschließlich PostgreSQL® 8.3 ist es unbedingt notwendig, sich vor Inbetriebnahme die Parameter max_fsm_pages und max_fsm_relations anzuschauen. Die Werte dieser Parameter kann nur durch einen Neustart der Datenbank geändert werden und beeinflussen die Anzahl an erfassten fragmentierten Speicherplatz in Tabellen und Indexe sowie die Anzahl an Tabellen und Indexe die durch VACUUM erfasst werden können (VACUUM FULL benutzt die sogenannte Free Space Map nicht). Ab PostgreSQL® 8.4 werden die FSM pro Tabelle automatisch angepasst.
- Auch VACUUM kann unter günstigen Umständen eine Tabelle verkleinern. Wenn die Tabelle am Ende nur noch leere Blöcke enthält und aktuell keine Transaktion neue Zeilen in diese Bereiche einlagern möchte, dann kann auch normales VACUUM die Tabelle entsprechend eindampfen.
Warum dann überhaupt noch VACUUM FULL?
VACUUM FULL ist ein Kommando, das nicht für die tägliche Wartung ausgelegt ist. Ist das Kind einmal in den sprichwörtlichen Brunnen gefallen und eine Tabelle stark aufgebläht, so ist es je nach PostgreSQL®-Version unausweichlich mit VACUUM FULL den Speicherplatz freizugeben. Bei älteren PostgreSQL®-Versionen sollte sich der Administrator besonders bei sehr großen Speicherbedarf der Tabelle besser überlegen, auf das CLUSTER-Kommando auszuweichen. Möchte man dennoch VACUUM FULL benutzen, so sollte man bei älteren PostgreSQL®-Versionen mit REINDEX ebenfalls die Indexe neu erzeugen. Weitere Infos zu diesem Thema finden sich im PostgreSQL® Wiki.
Weitere Informationen
Alle Blog-Artikel zum Thema PostgreSQL® werden auch als Kategorie PostgreSQL® samt eigenem Feed angeboten. Wir helfen auch gerne mit Support und Services für PostgreSQL®.
Die PostgreSQL® Community hat heute die Veröffentlichung der stabilen Version 9.0.0 bekanntgegeben.
Mit der Version 9.0 verfügt PostgreSQL® erstmals über eine eingebaute Replikationslösung (Streaming Replication) und die Möglichkeit, Standbyknoten im reinen Lesemodus zu betreiben (Hot Standby). Streaming Replication ermöglicht die transparente Replikation auf einen oder mehrere Standbyknoten mit geringer Latenz. Des Weiteren gibt es viele Änderungen im Bereich Skalierbarkeit, Geschwindigkeit und Wartung:
- JOIN Removal
- Unterstützung für 64 Bit Windows
- Trigger mit Bedingungen
- Spaltenbasierte Trigger
- Anonyme Prozedurale Codeblöcke mit DO
- Verbessertes Nachrichtensystem mit LISTEN/NOTIFY
Weitergehende Informationen können direkt über die Release Notes der PostgreSQL® Global Development Group eingesehen werden.
In dieser Folge stellen wir im Rahmen der „Optimizer Bits“ das Modul auto_explain vor, das seit PostgreSQL® Version 8.4 Bestandteil des contrib-Zweiges ist. Das Modul ermöglicht das Protokollieren von Abfrageplänen im PostgreSQL®-Log und so eine bessere Analyse von Abfrageproblemen während der Laufzeit.
Problemstellung
Für viele Datenbank-Entwickler und -Administratoren stellt sich täglich das Problem, problematische Abfragen zu finden, zu analysieren und effizienter zu gestalten. Hauptproblem dieser Aufgabe ist das Identifizieren solcher Abfragen. Erstes Mittel ist der Logparameter
log_min_duration_statement = '30s'
In diesem Falle werden alle Abfragen, die länger als 30 Sekunden dauern, in das Log der PostgreSQL®-Datenbank geschrieben. Der Administrator hat dann die Möglichkeit, diese Abfrage aus dem Logfile zu ermitteln oder aber über weitere Tools wie bspw. pgfouine zu analysieren. Allerdings kann es unter Umständen passieren, dass bei der späteren Analyse andere Pläne entstehen, die es schwer machen, das tatsächliche Problem zu spezifizieren. Solche Abhängigkeiten machen es dem Entwickler schwer, das tatsächliche Problem genau einzugrenzen.
Das Modul auto_explain
Seit PostgreSQL® 8.4 gibt es das contrib-Modul auto_explain, dass die Ausgabe von Abfrageplänen während der Testphase von Abfragen gestattet. Beispielsweise lassen sich damit Läufe von umfangreichen Batchjobs protokollieren, die Pläne später analysieren und entsprechende Optimierungen an den entsprechenden Abfragen vornehmen. auto_explain kann permanent oder nur zur Fehlersuche in die Datenbank geladen werden.
Zunächst müssen die contrib-Module von PostgreSQL® 8.4 oder höher installiert sein. Dies ist von Distribution zu Distribution unterschiedlich, in der Regel sollte man nach einen Paket postgresql-contrib Ausschau halten. Wenn man PostgreSQL® selbst aus den Tarballs baut, wechselt man in der Verzeichnis des entpackten Quelltextes und von dort aus in das entsprechende contrib-Verzeichnis (die folgenden Schritte erfordern in der Regel Rootrechte auf dem System):
$ cd $ cd contrib/auto_explain
Je nachdem. ob bereits PostgreSQL® komplett gebaut wurde (in der Regel hat man dann ja noch alle benötigten Sourcen), kann man dann auto_explain zusätzlich bauen:
$ make install
Sollte der Quelltextbaum bereits bereinigt worden (make clean), aber eine komplette Installation zur Verfügung stehen, so kann man mit PGXS-Unterstützung, ohne den kompletten Quelltextbaum nochmals kompilieren zu müssen, das Modul wie folgt bauen:
$ USE_PGXS=1 make install
Dies erfordert jedoch mindestens die Präsenz des Tools pg_config im Pfad der aktuellen Umgebung.
Ist alles installiert, so kann das Modul direkt in eine Datenbankverbindung geladen werden. Dies ist nur als Superuser möglich, wie in diesem Beispiel über eine lokale Verbindung:
$ psql -U #= LOAD 'auto_explain'; LOAD
Ist das Modul erfolgreich geladen worden, so steht es nur in dieser Datenbankverbindung zur Verfügung und kann auch nur von dort aus verwendet werden. Interessant ist dies, um nur Abfragen aus speziellen Verbindungen heraus zu protokollieren. Mit der folgenden SQL-Abfrage können die nun hinzugekommenen Konfigurationsparameter für auto_explain abgefragt werden:
#= SELECT name, setting FROM pg_settings WHERE name LIKE 'auto_explain%';
Dies sollte folgende Liste liefern:
name | setting ------------------------------------+--------- auto_explain.log_analyze | off auto_explain.log_buffers | off auto_explain.log_format | text auto_explain.log_min_duration | -1 auto_explain.log_nested_statements | off auto_explain.log_verbose | off (6 rows)
Der wichtigste Parameter hier ist
auto_explain.log_min_duration
Dieser aktiviert (Werte ab 0ms) oder deaktiviert (Wert -1) das Protokollieren von Abfrageplänen. Die weiteren Einstellungen sind im Einzelnen:
- auto_explain.log_analyze = true|false: Aktiviert oder deaktiviert das Loggen von EXPLAIN ANALYZE. Dies bedeutet das Timinginformationen aller Abfragen erfasst werden (auch diejenigen, die schneller ausgeführt werden als auto_explain.log_min_duration). Dies hat einen signifikanten Einfluss auf die Ausführungsgeschwindigkeit und sollte mit Bedacht gewählt werden.
- auto_explain.log_verbose = true|false: Ausgabeformat mit zusätzlichen Informationen für EXPLAIN.
- auto_explain.log_nested_statements = true|on: Hiermit werden auch Ausführungspläne von Statements innerhalb von Funktionen mitprotokolliert. So ist es nun auch möglich, die Pläne von SQL-Abfragen, die bspw. aus pl/pgsql-Prozeduren heraus ausgeführt werden, genauer zu untersuchen.
Mit PostgreSQL® 9.0 kommen zwei weitere Konfigurationsmöglichkeiten hinzu:
- auto_explain.log_format = ‚text’|’xml’|’json’|’yaml‘: Ermöglicht die Ausgabe der Abfragepläne im XML, JSON, oder YAML Format. text entspricht dem Standardformat.
- auto_explain.log_buffers = true|false: Aktiviert oder deaktiviert die Ausgabe von Bufferinformationen in der Ausgabe des Planes. Dies enthält u.a. Informationen über Bufferhits (Treffer im Shared Buffer Pool). Voraussetzung hierfür ist das gleichzeitige Aktivieren des Parameters log_analyze.
Anwendungsbeispiel
Im folgenden betrachten wir ein wegen der Übersichtlichkeit ein stark vereinfachtes Anwendungsbeispiel. In einer Datenbank gibt es seit kurzem ein Geschwindigkeitsproblem mit einer Funktion, die plötzlich stark variierende Ausführungszeiten aufweist. Die Funktion wird vielfältig eingesetzt, da sie bestimmte ID-Nummern einem Datum zuordnet. Die Definition dieser Funktion sei wie folgt:
CREATE OR REPLACE FUNCTION get_test_datum_ids(p_datum timestamp)
RETURNS SETOF integer
STABLE
LANGUAGE plpgsql
AS
$$
DECLARE
v_id int;
BEGIN
FOR v_id IN SELECT * FROM test WHERE datum < p_datum
LOOP
RETURN
NEXT v_id;
END LOOP;
RETURN;
END;
$$;
Geübte PostgreSQL®-Anwender werden schnell bemerken, dass diese Funktion deutlich effizienter implementiert werden kann, für dieses Beispiel jedoch ist eine derartige Implementierung gut geeignet. Der Administrator kann nun über log_min_duration_statement langsame Funktionsaufrufe zwar protokollieren, muss jedoch um dem Geschwindigkeitsproblem auf den Grund zu gehen, u.U. auf das System übertragen oder von Hand ausführen. Bei näherer Betrachtungsweise entsteht dann der Verdacht, dass die Schleife und die dort enthaltene Abfrage suboptimal sein könnte. Üblicherweise wird dann die Abfrage mit EXPLAIN geprüft:
#= \timing on
#= EXPLAIN ANALYZE SELECT id FROM test WHERE datum < '01.02.2008'::timestamp;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=4.50..16.90 rows=32 width=4) (actual time=0.039..0.054 rows=31 loops=1)
Recheck Cond: (datum < '2008-02-01 00:00:00'::timestamp without time zone)
-> Bitmap Index Scan on test_datum_idx (cost=0.00..4.49 rows=32 width=0) (actual time=0.025..0.025 rows=31 loops=1)
Index Cond: (datum < '2008-02-01 00:00:00'::timestamp without time zone)
Total runtime: 0.114 ms
(5 rows)
Insofern nichts Verdächtiges, die Abfrage nutzt einen vorhandenen Index auf dem Feld datum. Mit auto_explain können wir nun jedoch ebenfalls direkt die Pläne aus dem Funktionskörper heraus prüfen:
#= SET auto_explain.log_analyze TO on;
SET
#= SET auto_explain.log_nested_statements TO on;
SET
#= SET auto_explain.log_min_duration TO '0ms';
SET
#= SELECT get_test_datum_ids('01.02.2008'::timestamp);
Durch das Setzen von auto_explain.log_analyze TO on wird die Funktion tatsächlich ausgeführt und alle Timingparameter erfasst. Nach dem Ausführen sollten sich folgende Zeilen auf STDOUT, im Logfile oder syslog finden, je nach dem was für ein log_destination verwendet wird:
LOG: duration: 0.616 ms plan: Query Text: SELECT * FROM test WHERE datum < p_datum Seq Scan on test (cost=0.00..25.70 rows=365 width=12) (actual time=0.424..0.597 rows=31 loops=1) Filter: (datum < $1) ZUSAMMENHANG: PL/pgSQL function "get_test_datum_ids" line 1 at FOR über SELECT-Zeilen
Dieser Plan sieht schon deutlich anders aus. Zwar ist die Ausführungsgeschwindigkeit aufgrund der in diesem Beispiel recht kleinen Datenmengen noch überschaubar, jedoch kann man sich jetzt schon vorstellen, dass bei einer größeren Datenmenge dieser Plan schnell ineffizient werden kann. Doch warum wird an dieser Stelle ein anderer Plan verwendet?
Des Rätsels Lösung liegt an der parametrisierten Form dieser Abfrage, die in der FOR-Schleife verwendet wird. Der Optimizer kann nur einen generischen Plan für diese Art der WHERE-Bedingung erzeugen. Da der Offset für den Bereich innerhalb der Bedingung nicht zur Planungszeit zur Verfügung steht, muss der Optimizer den Plan auf einem möglichst allgemeingültigen Kostenmodell berechnen, der effizient für jeden Wert in der WHERE-Bedingung ist.
Konfiguration über postgresql.conf
auto_explain lässt sich auch global über die postgresql.conf konfigurieren. Möchte man als DBA beispielsweise das Modul auf jeden Fall für jede Datenbankverbindung laden, so benötigt man einen entsprechend konfigurierten Parameter shared_preload_libraries in der postgresql.conf (diese befindet sich in der Regel im Datenbankverzeichnis ihrer PostgreSQL®-Installation, kann aber bei einigen Distributionen abweichen):
## globales Aktivieren von auto_explain shared_preload_libraries = 'auto_explain'
Dies lädt das Modul bereits beim Start für jede Datenbankverbindung. Da PostgreSQL® noch nicht die Konfigurationsparameter beim Laden der Konfigurationsdatei kennt, muss dies noch zusätzlich über den Parameter custom_variable_classes dem Server bekannt gemacht werden:
custom_variable_classes = 'auto_explain'
Nun kann in der Datei postgresql.conf der Parameter global konfiguriert werden, wie an folgendem Listing beispielhaft gezeigt:
auto_explain.log_min_duration = '30s' auto_explain.log_format = 'xml'
Zusammenfassung
auto_explain ist ein nützliches Tool, um Geschwindigkeitsproblemen innerhalb der Datenbank anhand der EXPLAIN-Ausgaben auf den Grund zu gehen. Als wertvoll stellt sich die Möglichkeit heraus, eingebettete Abfragen innerhalb von SQL- oder PL/pgsql-Prozeduren mitprotokollieren zu können, um so auch die Abfragen im entsprechenden Kontext auf Fehler oder unterschiedliche Pläne hin untersuchen zu können.
auto_explain eignet sich jedoch nicht, um dauerhaft auf produktiven Datenbankmaschinen eingeschaltet zu sein, hierfür ist der zusätzliche Aufwand für das Ausschreiben der Pläne zu groß. Insofern sollte auf jeden Fall Gebrauch von auto_explain.log_min_duration gemacht werden, so dass wirklich nur sehr problematische Abfragen bei Überschreiten einer bestimmten Zeitschwelle protokolliert werden. Auch sollte dann auf produktiven Maschinen auf jeden Fall auto_explain.log_analyze deaktiviert sein, da dies auch Abfragen, die noch unterhalb der Zeitschwelle von auto_explain.log_min_duration liegen, negativ beeinflusst.
Kürzlich wurde eine Statistik veröffentlicht, bei der die einzelnen Committer des PostgreSQL®-Projekts aufgelistet wurden. Mit dabei sind mehrere Mitarbeiter von credativ.
PostgreSQL®s Andrew Dunstan Statistiken hat eine Statistik über die Produktivität der PostgreSQL®-Committer veröffentlicht. Darin ist aufgeführt, welche Entwickler mit Commit-Rechten wie viele Commits durchgeführt haben. Zwar lässt dies keine Rückschlüsse darauf zu, wie viel Code der jeweilige Entwickler tatsächlich beigesteuert oder wie viel Review-Mühen er sich gemacht hat; aber es es ist ein Indiz dafür, dass sich bestimmte Entwickler viel in das Projekt einbringen. Die credativ GmbH versteht sich als Teil der Community vieler Open-Source-Projekte – so auch bei PostgreSQL®. Dass dies ernst gemeint ist und in der Firma gelebt wird, zeigt sich so auch in der von Andrew Dunstan erstellten Statistik: von den aufgeführten Committern arbeiten gleich mehrere in den verschiedenen internationalen Büros von credativ: Michael Meskes, Joe Conway und davec, Dave Cramer. Dabei fehlen aber auch noch die Mitarbeiter von credativ, die selbst viel Code beisteuern, aber keine Commits vornehmen. So ist z.B. Bernd Helmle den Lesern dieses Blogs wegen seiner umfangreichen Artikel zu PostgreSQL® nicht nur als Autor, sondern auch als Entwickler bestens bekannt, taucht aber in Andrews Statistik nicht auf. Als Indiz bestätigt die Liste uns aber, dass die Bemühungen der Firma, aber auch die Verbundenheit unserer Mitarbeiter mit Open Source Früchte trägt. Wenn Ihr mehr über unser Open-Source-Engagement wissen wollt, hinterlasst einfach einen Kommentar. Falls Ihr euch für unser Open Source Support Angebot interessiert, gibt es auch noch diverse andere Kontaktmöglichkeiten.
Dieser Artikel wurde ursprünglich geschrieben von Roland Wolters.
Sie müssen den Inhalt von reCAPTCHA laden, um das Formular abzuschicken. Bitte beachten Sie, dass dabei Daten mit Drittanbietern ausgetauscht werden.
Mehr InformationenSie sehen gerade einen Platzhalterinhalt von Brevo. Um auf den eigentlichen Inhalt zuzugreifen, klicken Sie auf die Schaltfläche unten. Bitte beachten Sie, dass dabei Daten an Drittanbieter weitergegeben werden.
Mehr InformationenSie müssen den Inhalt von reCAPTCHA laden, um das Formular abzuschicken. Bitte beachten Sie, dass dabei Daten mit Drittanbietern ausgetauscht werden.
Mehr InformationenSie müssen den Inhalt von Turnstile laden, um das Formular abzuschicken. Bitte beachten Sie, dass dabei Daten mit Drittanbietern ausgetauscht werden.
Mehr InformationenSie müssen den Inhalt von reCAPTCHA laden, um das Formular abzuschicken. Bitte beachten Sie, dass dabei Daten mit Drittanbietern ausgetauscht werden.
Mehr InformationenSie sehen gerade einen Platzhalterinhalt von Turnstile. Um auf den eigentlichen Inhalt zuzugreifen, klicken Sie auf die Schaltfläche unten. Bitte beachten Sie, dass dabei Daten an Drittanbieter weitergegeben werden.
Mehr Informationen