Ab sofort steht die credativ PostgreSQL^® Appliance als vollständig freie Open Source-Lösung zum Download zur Verfügung. Die neue Appliance mit dem Namen ‚Elephant Shed PostgreSQL^® Appliance‚ bietet eine enorme Erleichterung für den Betrieb von PostgreSQL^® im Unternehmenseinsatz. Die zukünftige und langfristige Pflege der Appliance wird durch die PostgreSQL^®-Experten der credativ übernommen. Vorbereitete Images für die wichtigsten Cloud- und Virtualisierungsplattformen sind bereits in Arbeit und werden zeitnah für VMWare, Virtualbox, Vagrant und für die Cloud-Plattformen Microsoft Azure, Amazon Web Services und Google Cloud Platform veröffentlicht.

Elephant Shed baut auf bewährten Komponenten auf, die ausschließlich unter anerkannten Open Source Lizenzen veröffentlicht werden. Diese Tools sind eine wirkungsvolle Unterstützung für das Management eines PostgreSQL^® Servers. Alle verwendeten Komponenten sind vorinstalliert und in die integrale Automatisierung eingebunden. Die Mehrzahl dieser Tools kann über eine komfortable Weboberfläche benutzt werden.

Service und Support

Für Elephant Shed bietet die credativ einen umfassenden technischen Support mit garantierten Service-Level-Agreements, der optional auch an 365 Tagen im Jahr rund um die Uhr zur Verfügung steht. Eine Unterstützung bei der Installation und Integration, sowie eine Einführung in Elephant Shed ist selbstverständlich auch Bestandteil der Services von credativ.

Für Fragen zu Elephant Shed und unseren Service- und Supportleistungen stehen wir Ihnen gern zur Verfügung.

Weitere Informationen finden Sie auf unserer Elephant Shed-Projektseite sowie auf Github.

PostgreSQL^® hat heute Nachmittag Version 10 der „forschrittlichsten Open-Source-Datenbank der Welt“ veröffentlicht, wie der Wahlspruch des Projektes seit einigen Jahren lautet. Auch der Sprung von Version 9.x auf jetzt 10 weist auf etliche Neuheiten hin.

Die in Version 9.6 eingeführte Möglichkeit, die Ausführung einzelner SQL-Queries zu parallelisieren wurde für Version 10 auf deutlich mehr Query-Typen erweitert und ist nun standardmäßig aktiviert. Über den Konfigurationsparameter max_parallel_workers_per_gather wird festgelegt, wieviele zusätzliche Worker-Prozesse hinzugezogen werden können, um den eigentlichen Backend-Prozess bei der Query-Ausführung zu unterstützen. Die Standardeinstellung ist 2.

Bei sehr großen Tabellen kann es vorteilhaft sein, die Daten über mehrere Teil-Tabellen, Partitionen genannt, zu verteilen. Diese Möglichkeit bestand bei PostgreSQL^® schon immer, allerdings mussten aufwändig Trigger-Prozeduren geschrieben werden, um Daten beim Insert und Update in die gewünschte Partition zu lenken. In PostgreSQL^® 10 können Partitionen nun deklarativ beschrieben werden, womit der Overhead der Programmierung entfällt, was auch weniger fehleranfällig sein dürfte. Außerdem ist das Routing der Tupel in die Partitionen nun transparent, d.h. das bei der Benutzung von Triggern auftretende „0 rows affected“ gibt nun die korrekte Zeilenzahl zurück.

Der letzte große Versionssprung von 8.x auf 9.x rührte von der Einführung von Streaming Replication in PostgreSQL^®. Damit können ganze Cluster im wesentlichen Byte-identisch (physikalisch) auf Standby-Server repliziert werden. PostgreSQL^® 10 führt als Alternative nun logische Replikation ein, wodurch es möglich ist, einzelne Tabellen oder Datenbanken zu spiegeln. Die Basis bildet wie bei physikalischer Replikation das Write-Ahead-Log, so dass eine robuste Datenübertragung auf Tupel-Ebene gewährleistet wird. (Eine fehleranfällige „Replikation“ auf SQL-Statement-Ebene wie bei manchen anderen Datenbanken findet nicht statt.)

Die bisher schon vorhandene Möglichkeit der synchronen Replikation auf einen Slave-Server ist in PostgreSQL^® 10 nun um Quorum-Commit erweitert worden. Dabei gibt der Administrator an, wie viele Slave-Server die Daten einer Transaktion erhalten haben sollen, bevor der Commit zur Anwendung und dem User bestätigt wird.

Hash-Indexe gibt es schon seit langem in PostgreSQL^®, aber die Performance war schlecht bei vielen parallelen Zugriffen, und da Hash-Indexe nicht im WAL geloggt wurden, waren sie nicht Crash-sicher und konnten nicht auf Slave-Server repliziert werden. In PostgreSQL^® 10 ist das korrigiert, die Indexe können nun in allen Szenarien zum Einsatz kommen, und haben auch alle Performanceverbesserungen „nachgereicht“ bekommen, mit denen Btree-Indexe in den letzten Jahren verbessert wurden.

Beim Thema Sicherheit ist neben dem klassischen md5-Passwort-Hashing nun auch SCRAM-SHA-256 verfügbar, um den Zugriff auf den Datenbankserver zu härten.

Eine Zusammenfassung aller neuen Features kann in der Featurematrix und in den Release Notes eingesehen werden.

Das Release steht über www.postgresql.org, die Repositories apt.postgresql.org (Debian, Ubuntu) und yum.postgresql.org (RedHat, CentOS), und direkt über die Linux-Distributionen zur Verfügung.

Die credativ GmbH hat die Entwicklung von PostgreSQL^® mit Patches und Bugreports unterstützt, und ist der Hauptakteur bei der Pflege von apt.postgresql.org und PostgreSQL^® in Debian. Dort wurden in den vergangenen Wochen Dutzende von Paketen für PostgreSQL^® 10 aktualisiert und fit gemacht. Sollte „Ihre“ PostgreSQL^®-Extension dort noch nicht verfügbar sein, sprechen Sie uns an!

Die PGConf.EU ist die jährlich stattfindende Entwicklerkonferenz für die freie Datenbanksoftware PostgreSQL^® im europäischen Raum. Da viele unserer Kollegen privat, aber auch zusammen mit credativ zur Entwicklung von PostgreSQL^® beitragen, besuchen wir die PGConf.EU jedes Jahr sehr ger

Mit insgesamt 6 Kollegen werden wir dieses Jahr auf der Veranstaltung unterwegs sein. Im Gespräch mit uns können Besucher die credativ als Arbeitgeber kennenlernen und sich über unsere aktuellen Job-Angebote informieren.

Die Veranstaltung findet 2017 in Warschau, der Haupstadt von Polen statt. Die PGConf.EU läuft insgesamt 4 Tage, vom 24. Oktober bis 27. Oktober, wobei der erste Konferenztag ausschließlich für Trainings und Workshops vorgesehen ist.

Wir unterstützen die PGConf.EU dieses Jahr nicht nur mit einem Sponsoring. Unser Kollege Michael Banck wird auch wieder einen spannenden Vortrag halten.

Die Vorträge der letzten Jahre können PGConf.EU 2017 gibt es auf der Veranstaltungswebsite.

Über unseren Blog, Facebook, Twitter und Instagram halten wir euch natürlich auf dem Laufenden!

Im März diesen Jahres haben wir schon die PGConf.US in New Jersey besucht und mit einem Sponsoring unterstützt. Den Rückblick auf die Veranstaltung gibt es hier.

Dieser Artikel wurde ursprünglich von Philip Haas geschrieben.

Neben anderen Indextypen wie B-tree und BRIN, gibt es in PostgreSQL^® seit langer Zeit den Hashindex. Von diesem wurde jedoch bisher oft abgeraten, da dieser Index keine REDO-Informationen im Transaktionslog (Write Ahead Log, WAL) unterstützte und damit weder Crash noch Replikationssicher war. Stürzte die PostgreSQL^® unter Umständen ab, musste der Hashindex mit dem REINDEX Kommando neu erzeugt werden, da ansonsten Inkonistenzen auftreten konnten. PostgreSQL^® Standbysysteme konnten bei Abfragen entsprechende Hashindexe nicht verwenden, da durch das Fehlen jeglicher Protokollierungen über das WAL diese nicht repliziert wurden.

In PostgreSQL^®10 ändert sich dies, denn mit den Patch write-ahead logging support to hash indexes hat der Hashindex fehlende Funktionen dazugelernt und steht für das Featurerelease 10 bereit. Grund genug die Funktionsweise und Usecases zu beleuchten.

Theorie

In der Theorie sind Hashindexe zunächst recht simpel. Die Voraussetzung ist, dass das Hashing eines Wertes eine Position im Index definiert, welche die Zielinformation beinhaltet. Dadurch kann, mittels nur eines Indexschrittes, die Position im Heap bestimmt werden. Die Laufzeit in Relation zur Datenmenge ist demnach O(1), also somit sind die Zugriffszeiten im Hashindex im Ideal konstant. Ein sortierter Index, wie etwa der B-tree, erreicht dieses Ziel auch, jedoch in der Regel in O(log(n) ), denn hier muss eine Baumstruktur von Wurzel bis Blatt traversiert werden. Dies bedeutet in der Regel mehrere Zugriffszyklen, bis das richtige Blatt gefunden wurde.

Die PostgreSQL^® Implementierung des Hashindex basiert dabei auf dem Paper A new Hashing Package for UNIX von Selzer und Yigit. Diese Implementierung erreicht durch die Verwendung einer Metapage, den Zielbuckets sowie Overflowbuckets, dynamisches Wachstum des Indexes, behält jedoch das typische Laufzeitcharakteristikum der Hashindexe bei.

Laufzeit

In der Praxis divergiert primär die Rechenzeit. Hierbei gewinnt der Hash dann, wenn das Hashing des Wertes selbst billiger wird, als die notwendigen Vergleiche und Sprünge im B-tree. Hier ist eine Differenzierung des Vergleiches notwendig, denn der Vergleich zweier Ganzzahltypen ist beispielsweise deutlich billiger als der alphanumerische Vergleich zweier Zeichenketten, wie das folgende Beispiel anhand einer Sortierung illustriert.

EXPLAIN ANALYZE SELECT id FROM generate_series(1,100000)id ORDER BY 1 DESC Time: 0.3s
EXPLAIN ANALYZE SELECT id::text FROM generate_series(1,100000)id ORDER BY 1 DESC Time: 2.4s

Überträgt man diese Gegebenheit auf die Kosten der notwendigen Vergleiche eines Indexlookups, ergibt sich folgendes Bild:

Der hier beschriebene Benchmark basiert auf vier nebenläufigen Verbindungen, welche zufällige Elemente aus einer Tabelle selektieren. Diese Elemente sind Textfelder. Bei der Testgruppe ’similar‘, sind die ersten Stellen des Keys identisch, bei ‚random‘ ist die gesamte Zeichenfolge zufällig. Der Datenbestand ist komplett gecached. Gemessen wird die hauptsächlich durch Rechenoperationen bedingte Laufzeit der Abfragen. Die Keys der Lookups werden in den Abfragen durch Funktionen definiert, dadurch sind die beiden Testsets in dem konkreten Durchsatz durch den unterschiedlichen Aufwand nicht miteinander vergleichbar.

CREATE TABLE test(key_1 text, key_2 text);
CREATE INDEX ON test USING HASH(key_1);
CREATE INDEX on test(key_2);
 
Sorted:
INSERT INTO test SELECT repeat('x', :width )||id , repeat('x', :width )|| id FROM generate_series(1, :scale ) id;
 
Random:
INSERT INTO test SELECT substr(md5(id::text)||repeat('x', :width -32 ), 0, :width ) , substr(md5(id::text) || repeat('x', :width -32),0, :width ) FROM generate_series(1, :scale ) id;

Es wird deutlich, dass die Kosten der Schlüsselsuche im B-tree mit seiner Länge skalieren. Da ein B-tree von Links nach Rechts vergleicht, ist eine deutliche Regression bei Keys zu erkennen, die über viele Zeichen ähnlich sind, wie es beispielsweise für URIs typisch sein kann.

PageFetches

Die Laufzeit des Hashindex beruht auf der Annahme, das Zieltupel könne durch die vom Hash des Keys berechnete Indexseite bestimmt werden. Die zusätzliche Metapage und gegebenenfalls auftretenden Overflowpages fallen ebenfalls ins Gewicht, doch die Metapage ist immer im Cache, und die Overflowpage statistisch eher unwahrscheinlich.

Dadurch erreicht der Hashindex eine sehr gute Laufzeit bei langsamen Random-Reads und eignet sich damit für Indexe, die selten verwendet werden, für den verfügbaren RAM deutlich zu groß, oder aus anderen Gründen nicht im RAM sind.

Der Benchmark zeigt, dass die Pagefetches des Hashindex bei vielen Tupeln weit unter denen des B-tree liegt. Betrachtet man eine Fetchdifferenz von vier mit einer durchschnittlichen IO-Wartezeit der Reads von 8ms wie es für 7200rpm HDDs typisch ist, würden hier Latenzunterschiede von bis zu 32ms pro Abfrage verzeichnet werden. Zudem ist der Randomread Durchsatz jedes Systems technisch limitiert, wodurch der Hash durch bessere Ressourcennutzung mehr Nebenläufigkeit erlauben kann.

Größe

Hashindexe verwenden eine feste Keygröße. Damit ist die Größe des Index nur mit der Tupelmenge und nicht der Tupelgröße korelliert, was einen deutlichen Unterschied zu dem B-tree darstellt. Ein Hashindex ist bei der Betrachtung von Ganzzahltypen zwar bis zu 40% größer, bei variabler Keygröße wie sie bei Zeichenketten oft vorkommt dann jedoch deutlich kleiner.

Upgrade von früheren PostgreSQL^® Versionen mit pg_upgrade

Aufgrund der beschriebenen Änderungen ist die physische Repräsentation von Hashindexe zwischen älteren PostgreSQL^® Versionen (< 10) und PostgreSQL^® 10 nicht mehr kompatibel. Wählt man als Upgradepfad den Weg über physisches Upgrade per pg_upgrade, so müssen alle Hashindexe per REINDEX neu erzeugt werden. pg_upgrade erkennt alle verwendeten Hashindexe in einer Instanz und invalidiert diese. Daher sind diese nicht mehr unmittelbar von der Datenbank nutzbar. pg_upgrade gibt bei der Überprüfung des Datenbankclusters einen Hinweis aus, ob Hashindexe verwendet werden und diese überführt werden müssen:

Your installation contains hash indexes.  These indexes have different
internal formats between your old and new clusters, so they must be
reindexed with the REINDEX command.  After upgrading, you will be given
REINDEX instructions.

Nach dem pg_upgrade-Lauf findet man Arbeitsverzeichnis ein SQL-Skript reindex_hash.sql, das die notwendigen REINDEX-Kommandos für das Wiederaufbauen der Hashindexe enthält.

Zusammenfassung

Hashindexe bieten sich also besonders dort an, wo teure Operatoren oder große Datensätze den Index definieren. Die Verteilung der zugrundeliegenden Daten sollte zudem zufällig sein, denn der Hashindex unterstützt keine Bereichsabfragen (<, > sowie >= und <=) und auch eine Cacheaffinität ähnlicher Werte ist nicht gegeben. Können diese Annahmen über die Datenmenge getroffen werden, können die Vorteile des Hashindex einen deutlichen Mehrwert erreichen.

Weiterführende Links:

Dieser Artikel wurde ursprünglich geschrieben von Julian Schauder.

Im Zuge der Veröffentlichung von PostgreSQL^® 9.5 wurde bereits einen Blogpost zum Thema veröffentlicht um eine grobe Einführung in „Block Rang INdexe“ (BRIN) sowie deren Vor- und Nachteile zu gegeben. Durch PostgreSQL^® 10.0 wird hier jedoch ein kleines Update nötig. Sollten Sie sich für eine Einführung in das Thema interessieren, empfiehlt es sich erst den ursprünglichen Post PostgreSQL^® 9.5: Block Range Index (BRIN) zu lesen und anschließend dieses Update.

Problem

Eines der Probleme bei Einsatz von BRIN ist, dass sich ändernde Tabellen die Qualität und damit Effektivität des Index verringern. Im Gegensatz zum Btree-Index, dem Standard in PostgreSQL^®, muss ein BRIN manuell gepflegt werden. Dies kann z.B. durch den Aufruf von REINDEX oder VACUUM FULL erreicht und durch Cron-Jobs automatisiert werden.

Diese Konstrukte sind jedoch problematisch, oder zumindest unschön und werden auch schnell vergessen. Daher sollten solche „Krücken“ immer mit einem entsprechenden Monitoring überwacht werden.

Lösung in PostgreSQL^® 10.0

Mit PostgreSQL^® 10.0 ändert sich die Situation jedoch. Die Erweiterungen sind selbstverständlich bereits in der Dokumentation erläutert (BRIN – Index Maintenance). Um die BRIN-Pflege zu automatisieren wurde VACUUM so erweitert, dass es beim bearbeiten einer Tabelle auch die zugehörigen Block-Range-Indizes aktualisieren kann. So wird die Aktualisierung sowohl beim manuellen als auch bei Autovacuum durchgeführt.

Anwendung

Wer bereits Block-Range-Indizes verwendet muss diese entsprechend anpassen. Damit VACUUM die Funktion übernimmt muss für jeden Index autosummarize gesetzt werden. Dies geschieht einfach beim CREATE durch den zusätzlichen Parameter autosummarize=true.

CREATE INDEX brin_test_time_auto_brin ON brin_test USING brin (TIME) WITH (autosummarize=true);
CREATE INDEX brin_test_time_auto_brin_128  ON brin_test USING brin (TIME) WITH (pages_per_range=128,  autosummarize=true);

Wenn Tabellen stark verkleinert werden und die dazugehörigen Indizes auch verkleinert werden sollen ist weiterhin ein VACUUM FULL notwendig. Auf Grund der geringen Größe der BRIN ist ein direkter Nutzen hier jedoch oft nicht gegeben.

Anwendertests

Um das Verhalten der Änderungen und die Auswirkungen besser zu verstehen, wurden von uns einige Tests durchgeführt. Hierbei wurde prompt ein Bug im geänderten Code entdeckt. Dieser führte zu einem Fehler, wenn ein BRIN mit abweichenden pages_per_range und autosummarize sowie CONCURRENTLY angelegt wurde. Trotz, dass es sich hier um eine Developmentversion handelt wurde der Fehler noch in der Nacht von der Community behoben.

Dies ist ein guter Anlass einmal darauf hinzuweisen, als interessierter PostgreSQL^®-User, gerade neue Features schon in einer frühen Phase ausgiebig zu testen.

PostgreSQL^® besitzt natürlich Regressiontests und auch das Fuzzingtool SQLsmith von unserem Kollegen Andreas Seltenreich trägt zur Qualitätssicherung bei. Jedoch sind ausgiebige Anwendertests immer sinnvoll und auch im eigenen Interesse der Nutzer. Aals Softwareentwickler bietet es sich zudem an, die eigene Anwendung hin und wieder gegen den aktuellen Stand der PostgreSQL^®-Entwicklung zu testen. Dies lässt sich natürlich auch gut in die eigene CI-Toolchain integrieren. So wird zusätzlich ganz nebenbei der Zeitaufwand, um die Kompatibilität mit dem nächsten Release sicher zu stellen, deutlich reduziert.

Weiterführende Links

Dieser Artikel wurde ursprünglich von Alexander Sosna verfasst.

Die pgAudit-Erweiterung erlaubt es, Zugriffe auf eine PostgreSQL^®-Datenbank feingranular zu auditieren. Unter anderem ist sie ein wichtiger Bestandteil des kürzlich veröffentlichten PostgreSQL^® STIG („Security Technical Implementation Guide“) des amerikanischen Verteidigungsministeriums. Von uns im Rahmen des pkg-postgresql Projekts erstellte Debian-Pakete sind nun im offiziellen PostgreSQL^® APT-Repository, sowie den Entwickler-Zweigen von Debian und Ubuntu verfügbar.

PostgreSQL^® bietet zwar von sich aus konfigurierbare Protollierung an, die von pgAudit ausgegeben Audit-Meldungen gehen hier aber deutlich weiter und decken auch die meisten Compliance-Richtlinien ab. Sie werden auch in das PostgreSQL^®-Log geschrieben, allerdings in einem einheitlichen Format und im Gegensatz zu dem üblichen Logging via z.B. log_statement auf der einen Seite deterministisch und umfassend und auf der anderen Seite gezielt. So war es zwar schon bisher möglich z.B. ausgeführte SELECT-Befehle protokollieren zu lassen um etwa nicht erwünschte Zugriffe auf eine bestimmte Tabelle zu sehen, da dies dann allerdings für alle SELECT-Befehle gilt ist dies in der Praxis nicht handhabbar. Mit dem sogenannten Objekt-Audit-Logging von pgAudit ist es möglich, nur Zugriffe auf bestimmte Tabellen in das Audit-Log zu schreiben, indem man einer Auditor-Rolle entsprechende Berechtigungen zuweist z.B.:

Datenbank vorbereiten

CREATE ROLE AUDITOR;
SET pgaudit.role = 'auditor';
CREATE TABLE account
(
    id INT,
    name TEXT,
    password TEXT,
    description TEXT
);
GRANT SELECT (password) ON public.account TO auditor;

Abfragen, die die Spalte password betreffen (und nur solche) erscheinen nun im Audit-Log:

SELECT id, name FROM account;
SELECT password FROM account;
AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.account,SELECT password FROM account

Inhalt des Logs

Das erste Feld ist entweder SESSION oder OBJECT für den entsprechenden Audit-Logging Typ. Die beiden darauffolgenden Felder sind Statement-IDs, das vierte Feld die Klasse der Abfrage (READ, WRITE, ROLE, DDL usw.), gefolgt von dem Kommando-Typ und (falls zutreffend) dem Objekt-Typ und -Namen; das letzte Feld ist schließlich das tatsächlich ausgeführte Kommando. Für das Auditing entscheidend ist, dass die tatsächlich ausgeführten Befehle protokolliert werden, so dass Umgehungen durch mutwillige Obfuskation nicht möglich sind. Ein Beispiel hierfür aus der pgAudit-Dokumentation ist:

AUDIT: SESSION,1,1,FUNCTION,DO,,,"DO $$
BEGIN
    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;"
AUDIT: SESSION,1,2,DDL,CREATE TABLE,TABLE,public.important_table,CREATE TABLE important_table (id INT)

Wie man am verwendeten Kommando (Zeilen 1-4) sieht wird hier versucht, den Tabellennamen important_table nicht in der Logdatei erscheinen zu lassen, pgAudit loggt hier allerdings zuverlässig den Tabellennamen (Feld 7), sowie die tatsächlich Ausgeführte CREATE TABLE-Anweisung. Im Fall des konventionellen PostgreSQL^®-Logs ist dieser Versuch allerdings erfolgreich, hier wird lediglich der eingegebene Befehl geloggt:

LOG:  statement: DO $$
BEGIN
    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;

Die pgAudit-Erweiterung kann prinzipiell ab Version 9.5 von PostgreSQL^® verwendet werden. Die von uns paketierte Version 1.1 von pgAudit unterstützt offiziell zwar nur 9.6, das erstellte Debian-Paket kann allerdings durch einen zusätzlichen Patch auch mit 9.5 verwendet werden. Auf apt.postgresql.org stehen deshalb Pakete sowohl für 9.6 als auch für 9.5 unter allen unterstützten Debian- und Ubuntu-Version bereit.

Lesen Sie auch:

Vor einiger Zeit gab es einen großflächigen Ausfall bei Gitlab. Die Administratoren haben diesen in beispielhafter Weise dokumentiert und auch ein Post-Mortem dazu veröffentlicht. Wir von credativ haben nun eine erste Änderung für eines der Probleme in Postgres eingebracht, die Datenbank-Administratoren in ähnlicher Lage in Zukunft helfen soll.

Ein Teil des Problems trat bei der Verwendung des pg_basebackup-Programms zum Aufsetzen eines neuen lesenden Postgres-Standbys auf, nachdem der eigentliche Standby auf Grund von zu großem Replikations-Verzug und fehlender Transaktionslog-Archivierung nicht mehr verwendbar war.

Die Administratoren haben pg_basebackup ausgeführt, jedoch dieses mehrmals abgebrochen, nachdem weder im normalen Betrieb, noch mit der Option –verbose eine Ausgabe erfolgte und weder Netzwerkverkehr stattfand noch Daten in das Speichersystem geschrieben wurden. Offenbar haben sie vor jedem Aufruf von pg_basebackup das gewünschte Daten-Verzeichnis gelöscht, wobei schließlich aus Versehen das Datenverzeichnis des Master-Servers gelöscht wurde.

Was die Gitlab-Administratoren zu dem Zeitpunkt nicht wussten: Vor Beginn der Übertragung des Datenverzeichnisses vom entfernten Server muss auf diesem ein sogenannter Checkpoint geschrieben werden, bei dem alle ausstehenden Transaktionslogs in das Datenverzeichnis persistiert werden, so dass dieses konsistent ist. Dies kann bei einer hohen Anzahl an ausstehenden Änderungen an dem Datenbestand unter Umständen mehrere Minuten dauern.

Das Postgres-Projekt hat jetzt mit einer ersten kleinen Änderung auf das Gitlab-Problem reagiert: Zum einen wurde die Durchführung des Checkpoints in der Dokumentation des pg_basebackup-Programms unter „Notes“ erwähnt, zum anderen wurde die Ausgabe von pg_basebackup so angepasst, dass zumindest bei der Verwendung der –verbose oder –progress Optionen eine hilfreiche Mitteilung bzgl. dem andauernden Checkpoint erfolgt. Diese Änderungen wurden von credativ erstellt und wurden inzwischen in den offiziellen Postgres-Quellcode aufgenommen und werden auch in den nächsten Wartungs-Releases der Postgres-Versionen 9.4 bis 9.6 enthalten sein.

Seit dem 07. April 2017 befindet sich die aktuelle Entwicklerversion von PostgreSQL^® für das nächste Hauptrelease im Feature Freeze. Dies bedeutet, dass keine neuen Features in den aktuellen Entwicklerzweig aufgenommen werden dürfen. Zeit also, einen Blick auf die kommende Version 10.0 der freien Datenbank zu werfen.

Die augenscheinlichste Neuerung finden aufmerksame Leser bereits in der Einleitung dieses Artikels: Die Änderung an der Versionsnummer. Diese folgte seither einer dreistelligen Nomenklatur:

HAUPTVERSION - FIRST . HAUPTVERSION - SECOND . MINOR

Die Hauptversion wird durch die ersten beiden Stellen der Versionsnummer gebildet. Die dritte Stelle gibt immer die sogenannte Minorversion an, also z.B. den Patchlevel der jeweiligen Hauptversion. Aktuell sind also demnach folgende Hauptversionen von PostgreSQL^® unterstützt:

Hauptversionen sind einander inkompatibel, d.h. ein Datenbankcluster initialisiert mit PostgreSQL^® 9.2 kann nicht einfach mit einer Version 9.3 gestartet werden. Datenbankkatalog aber auch binäre Abweichungen spielen hier eine wichtige Rolle, weshalb man nicht einfach durch Installation und Neustart auf eine neue Version wechseln kann. Die dritte Minornummer gibt den Patchlevel der jeweiligen Hauptversion an und diese sind natürlich ohne weiteres durch ein Update der PostgreSQL^® Installation aktualisierbar.

Die kommende Version 10 von PostgreSQL^® bricht nun mit dieser Methodik und kürzt die Hauptversion auf eine einzige Zahl, die zweite Nummer ist dann der jeweilige Patchlevel:

HAUPTVERSION . MINOR

D.h. mit Erscheinen von PostgreSQL^® 10 im Herbst diesen Jahres sieht die Liste der Versionen (vorerst) aus wie folgt:

PostgreSQL^® 10.0 markiert dann die erste Version im neuen Zweig. Die folgenden Patchlevel werden dann 10.1, 10.2, 10.3 usw. bekommen. Die nächste Hauptversion wird dann folglich PostgreSQL^® 11.0 heißen. Die Version 9.2 wird dann auch im September 2017 auslaufen. Die alten Versionen werden die bisherige dreistellige Versionsnummer beibehalten.

Dieser Artikel markiert den Auftakt zu einer Serie von Themen rund um die kommende Version von PostgreSQL^® 10.

Vom 28.03.2017 bis 31.03.2017 fand die PGConf US statt. Vor der beeindruckenden Skyline New Yorks hatte man dieses Jahr als Austragungsort das Westin Hotel in Jersey City am Ufer des Hudson Rivers ausgewählt.

Ein buntes Vortragsprogramm von Anwendern und Entwicklern rund um PostgreSQL^® bot einen gelungenen Kontrast zum leider sehr stürmischen und regnerischen Wetter der Metropole. Die credativ war in Person von Geschäftsführer Dr. Michael Meskes und dem Leiter des Datenbank Supportteams Bernd Helmle vertreten. Beide berichteten in einem Vortrag über Ihre Erfahrungen von PostgreSQL^® auf der IBM POWER Plattform.

Daneben gab es viele interessante Themen auch von Anwendern. Einige berichteten über ihre Erfahrungen bei der Migration von Microsoft SQL Server oder Oracle zu PostgreSQL^®. Andere Vorträge von PostgreSQL^® Entwicklern beschäftigten sich mit den Neuerungen in PostgreSQL^® 10, geplanten Verbesserungen bei der Ausführung von Abfragen in der freien Datenbank aber auch aktuellem Stand von Parallel Query Execution. Besonders erwähnenswert waren hier auch die detaillierten Ausführungen zum Thema Replikation und dem neuen Logical Replication Feature von Coreentwickler Peter Eisentraut.

pgJDBC-Maintainer Dave Cramer schilderte in seinem Vortrag den aktuellen Stand des pgjdbc Projektes und gab einen interessanten Einblick in die Architektur des Treiber und die Motivation hinsichtlich der aktuellen Verbesserungen.

Am Donnerstag lud die Konferenz zu einer Round Table Diskussion mit führenden PostgreSQL^® Entwicklern. Thema waren die vielen Änderungen und Funktionen im kommenden PostgreSQL^® 10.0 Release. Die Entwickler gaben Einblick in den aktuellen Stand der Entwicklungen und ihre favorisierten Features.

Die PGConf US bot auch dieses Jahr wieder eine gelungene Mischung aus Community- und Geschäftsnahen Themen und gab so einen wichtigen Beitrag zum Austausch zwischen den Interessengruppen. Die Vorträge wurden aufgezeichnet und können zu einem späteren Zeitpunkt dann über das Vortragsprogramm der Website bzw. Youtube abgerufen werden.

Einleitung

Heute muss nicht mehr argumentiert werden, warum zentrales Logging sinnvoll oder sogar notwendig ist. Die meisten mittelständischen Unternehmen haben mittlerweile ein zentrales Logging oder führen dieses gerade ein.

Wenn die Infrastruktur einmal geschaffen ist, gilt es diese sinnvoll und effizient zu nutzen! Gerade als Infrastrukturbetreiber oder Dienstleister geht es darum verschiedene Akteure, mit verschiedenen Anforderungen, bestmöglich zu unterstützen. So sollte z.B. die Entwicklungsabteilung kontinuierlich Zugriff auf alle Logs ihrer Testsysteme haben. Aus der Produktion werden aber vielleicht nur alle Fehlermeldungen in Echtzeit benötigt, auf Freigabe jedoch auch mehr.

Solche Modelle lassen sich mit graylog® oder Kibana® leicht umsetzen und testen. Die Klassifizierung und Auswertung mag im Testbetrieb oder in kleinen Umgebungen auch mit den PostgreSQL^®-Standardeinstellungen gut und performant funktionieren. Werden jedoch sehr viele Datenbanken betrieben oder die Logs lange aufgehoben, kann die Nutzung schnell schwierig werden.

Problemstellung

Die Logeinträge sind zentral erfasst und können prinzipiell verwendet werden. In der Praxis ist es jedoch schwierig bis unmöglich alle relevanten Informationen zeitnah zu extrahieren. Die Suche nach bestimmten Einträgen erfordert Volltextsuchen mit Wildcards, dies ist bei großen Datenbeständen nicht mehr praktikabel ist.

Möchte man beispielsweise bestimmten Gruppen ausschließlich Zugriff auf Logs geben, die bestimmten fachlichen Kriterien entsprechen, z.B. Datenbankname, error_severity oder der gleichen, muss dies über Volltextsuche und fehleranfällige Filter realisiert werden.

Möchte ein DBA z.B. alle Meldungen eines bestimmten Users, einer bestimmten Query oder einer Session sehen, wird hierzu eine besonders aufwendige Indizierung für Wildcard-Suchen benötigt. Alternativ sind solche Anfragen sehr langsam und können nicht sofort beantwortet werden.

Ausgangspunkt:	Es wurde bereits ein Zentrales Logging eingeführt, z.B. ELK-Stack oder graylog®.
Ziel:	Wir möchten die PostgreSQL^®-Logmeldungen semantisch erfassen und dadurch effizient: klassifizieren, gruppieren und auswerten.

Alternative

Die Alternative besteht nun darin, die Logmeldungen semantisch zu erfassen und die einzelnen Felder in einer entsprechenden Datenstruktur abzulegen.

Wird das normale stderr-Log verwendet, gestaltet sich das Parsen als schwierig bis unmöglich, da die einzelnen Felder hier nicht zu erkennen sind. PostgreSQL^® bietet jedoch auch die Möglichkeit Logmeldungen im CSV-Format zu produzieren (csvlog). Hiermit werden alle Felder Komma separiert ausgegeben.

Exkurs: Die Idee ist nicht neu. Seit Langem laden einige DBAs ihre Logs direkt wieder in eine PostgreSQL^®-Tabelle. So können die Logs mit SQL und allen bekannten Boardmitteln durchsuchen und bearbeiten. Die DBAs sind meist sehr zufrieden mit diesem Konstrukt, es stellt jedoch eine Insellösung dar. Beispieltabelle PostgreSQL^® 9.5:

CREATE TABLE postgres_log
(
  log_time timestamp(3) with time zone,
  user_name text,
  database_name text,
  process_id integer,
  connection_from text,
  session_id text,
  session_line_num bigint,
  command_tag text,
  session_start_time timestamp with time zone,
  virtual_transaction_id text,
  transaction_id bigint,
  error_severity text,
  sql_state_code text,
  message text,
  detail text,
  hint text,
  internal_query text,
  internal_query_pos integer,
  context text,
  query text,
  query_pos integer,
  location text,
  application_name text,
  PRIMARY KEY (session_id, session_line_num)
);

Aufbau

Umsetzung

Das csvlog kann als Grundlage für das effiziente Befüllen eines zentralen Loggingsystems dienen. Um hier die weitere Verarbeitung möglichst einfach zu gestalten und um sich auf kein Loggingsystem festlegen zu müssen, übersetzen wir das Log von CSV nach Json. Im Anschluss kann es dann beliebig eingespeist werden. Im folgenden Beispiel wird TCP verwendet.

Um das Logformat zu ändern müssen in PostgreSQL^® folgende Optionen angepasst werden:

#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------
 
# - Where to Log -
 
log_destination = 'csvlog'              # Valid values are combinations of
                                        # stderr, csvlog, syslog, and eventlog,
                                        # depending on platform.  csvlog
                                        # requires logging_collector to be on.
 
# This is used when logging to stderr:
logging_collector = on                  # Enable capturing of stderr and csvlog
                                        # into log files. Required to be on for
                                        # csvlogs.
                                        # (change requires restart)
                                        # These are only used if logging_collector is on:
log_directory = '/var/log/postgresql'   # directory where log files are written,
                                        # can be absolute or relative to PGDATA

Zum Parsen und Übersetzen in Json sowie zum Einliefern ins Loggingsystem verwenden wir logstash® mit folgender Konfiguration als Jinja2-Template:

input {
        file {
                "path" => "/var/log/postgresql/*.csv"
                "sincedb_path" => "/tmp/sincedb_pgsql"
                # fix up multiple lines in log output into one entry
                codec => multiline {
                   pattern => "^%{TIMESTAMP_ISO8601}.*"
                   what => previous
                   negate => true
           }
        }
}
 
# Filter is testet for PostgreSQL^® 9.5
filter {
        csv {
 columns => [  "pg_log_time", "pg_user_name", "pg_database_name",
               "pg_process_id", "pg_connection_from", "pg_session_id",
               "pg_session_line_num", "pg_command_tag",
               "pg_session_start_time", "pg_virtual_transaction_id",
               "pg_transaction_id", "pg_error_severity", "pg_sql_state_code",
               "pg_sql_message", "pg_detail", "pg_hint", "pg_internal_query",
               "pg_internal_query_pos", "pg_context", "pg_query",
               "pg_query_pos", "pg_location", "pg_application_name"  ]
        }
 
  date {
        #2014-05-22 17:02:35.069 CDT
        match => ["log_time", "YYYY-MM-dd HH:mm:ss.SSS z"]
        }
 
  mutate {
    add_field => {
      "application_name" => "postgres"
    }
  }
 
}
 
output {
        tcp {
                host => "{{ log_server }}"
                port => {{ log_port }}
                codec => "json_lines"
        }
}

Wichtig ist hier, dass der Filter zum Übersetzen der CSV-Felder an die verwendete PostgreSQL^®-Hauptversion angepasst wird. Die Felder können sich von Version zu Version unterscheiden. In den meisten Fällen werden neue Felder hinzugefügt.

Auch zu beachten ist, dass logstash® Probleme hat den Timestamp eigenständig zu erkennen. Hier sollte das konkrete Format vorgegeben werden (Zeitzone).

Folgenden Variablen müssen gesetzt werden:

{{ log_server }}	Logserver, in unserem Fall ein graylog®
{{ log_port }}	Port auf dem Logserver

So konfiguriert, können die Logfiles effizient klassifiziert und durchsucht werden. Auch die Berechtigungen gestalten sich einfacher.

Berechtigung auf Datenbankebene => pg_database_name
Severity => pg_error_severity
Berechtigung für Loggs durch bestimmte Hosts => pg_connection_from
Berechtigung für Loggs durch bestimmte Anwendungen => pg_application_name

Wichtig: Das normale stderr-Log sollte in jedem Fall noch mit ins zentrale Logging aufgenommen werden. Hier landen zwar nach dem Start keine normalen Betriebsmeldungen von PostgreSQL^® mehr, jedoch Fehlerausgaben von beteiligten Prozessen. Hier kann z.B. die stderr-Ausgabe eines fehlgeschlagenen Archivecommands gefunden werden. Dieses Informationen sind für die Administration essentiell.

Wir unterstützen bereits zahlreiche Kunden dabei das beschriebene Verfahren in Produktion zu betreiben. Falls Sie Fragen zu diesem Thema haben oder Unterstützung benötigen, dann können Sie sich selbstverständlich gern an unser PostgreSQL^® Competence Center wenden.

Dieser Artikel wurde ursprünglich von Alexander Sosna verfasst.

Die PGConf.EU findet alljährlich an wechselnden Austragungsorten innerhalb der EU statt. Dieses Jahr fand die Konferenz in Tallinn, Estland ihr Zuhause. Waren winterliche Verhältnisse draußen angesagt, versammelten sich in den Konferenzräumen des Radisson Blu Olympik Hotel PostgreSQL^®-Interessierte aus aller Welt.

Das credativ-Team war mit insgesamt 8 Kollegen an der Konferenz vertreten, Andreas Seltenreich gab unter anderem auch sein umfangreiches Wissen rund um das von ihm entwickelte Tool SQLsmith in einem Vortrag kund. SQLsmith hat bereits über 45 Bugs in PostgreSQL^® aufgedeckt und wird für die Qualitätssicherung in einer Vielzahl von Projekten beziehungsweise Erweiterungen um PostgreSQL^® eingesetzt.

Wie gewohnt war das Vortragsprogramm breit aufgefächert, es gab Anwenderorientierte aber auch inhaltlich sehr technisch geprägte Vorträge, die Einblicke in aktuelle Entwicklungsarbeiten, aber auch in Forschungsprojekte wie JIT-Kompilierung von PostgreSQL^® Abfrageplänen gaben. Zwischen den Vorträgen ergaben sich die Möglichkeiten von Fachgesprächen.

Die PGConf.EU unterstreicht hiermit auch ihren Anspruch, eine Konferenz gleichzeitig für den technisch Interessierten, aber auch für Anwender und Entscheider zu sein, die einen Einblick in die sehr lebhafte PostgreSQL^® Community gewinnen wollen. Ein großes Dankeschön geht auch an die Organisatoren der Konferenz, die auch dieses Jahr wieder eine großartige Veranstaltung auf die Beine gestellt haben.

Bis zur nächsten PGConf.EU 2017 in Warschau!

Das Repository für Debian- und Ubuntu-Pakete für PostgreSQL^® auf apt.postgresql.org wurde um eine weitere Prozessor-Architektur erweitert. Ab sofort stehen auch fertige Binär-Pakete für ppc64el, die little endian-Version der IBM POWER-Architektur, zur Verfügung. Neben amd64 (64-bit x86 Intel) und i386 (32-bit x86 Intel) ist dies die nun dritte unterstützte Variante.

Das heutige Release der neuen PostgreSQL^®-Version 9.6 ist bereits im Repository enthalten.

Genau wie auf den bisherigen Architekturen werden alle Pakete für alle PostgreSQL^®-Versionen unterstützt, momentan sind dies alle Versionen von 9.1 bis 9.6. Mit nur wenigen Ausnahmen sind alle bisher enthaltenen Pakete nun auch für ppc64el kompiliert worden. [1]

Unterstützt werden die Debian-Versionen 8 (jessie) und unstable (sid), und die Ubuntu-Versionen 14.04 (trusty) und 16.04 (xenial).

Die Arbeiten an diesem Projekt wurden von credativ gemeinsam mit 2ndQuadrant und IBM Italien durchgeführt. Der ppc64el-Buildhost wird vom IBM Power Systems Linux Center in Montpellier bereit gestellt.

Für unsere Support-Kunden unterstützt die credativ natürlich auch den PostgreSQL^®-Betrieb auf POWER. Bei Fragen stehen wir gerne zur Verfügung, sprechen Sie uns an!

[1] plv8 ist nicht verfügbar; pg-partman ist noch nicht für 9.6 verfügbar; psqlodbc und libpqtypes sind auf trusty nicht verfügbar.