SQLreduce: Große SQL-Queries auf Minimum reduzieren

Entwicklern begegnen oft große SQL-Queries, die einen Fehler werfen. SQLreduce ist ein Tool, mit dem diese Komplexität auf eine minimale Query reduziert wird.

SQLsmith generiert zufällige SQL-Queries

SQLsmith ist ein Tool, das zufällige SQL-Queries generiert und diese gegen einen PostgreSQL-Server laufen lässt (und andere DBMS-Typen). Die Idee ist, dass im Query-Parser und -Executor mit Fuzz-Testing Corner-Case-Bugs gefunden werden können, die mit manuellen Testen oder der festen Menge an Queries aus der PostgreSQL-Regression-Testsuite nicht gefunden worden wären. Es hat sich als effektives Tool bewährt, das seit 2015 über 100 Bugs in verschiedenen Teilen des PostgreSQL-Servers und anderen Produkten gefunden hat, darunter Security-Bugs, von Executor-Bugs bis zu Segfaults in Typ- und Index-Methoden.

2018 fand SQLsmith beispielsweise, dass die folgende Query einen Segfault in PostgreSQL verursacht:

select
  case when pg_catalog.lastval() < pg_catalog.pg_stat_get_bgwriter_maxwritten_clean() then case when pg_catalog.circle_sub_pt(
          cast(cast(null as circle) as circle),
          cast((select location from public.emp limit 1 offset 13)
             as point)) ~ cast(nullif(case when cast(null as box) &> (select boxcol from public.brintest limit 1 offset 2)
                 then (select f1 from public.circle_tbl limit 1 offset 4)
               else (select f1 from public.circle_tbl limit 1 offset 4)
               end,
          case when (select pg_catalog.max(class) from public.f_star)
                 ~~ ref_0.c then cast(null as circle) else cast(null as circle) end
            ) as circle) then ref_0.a else ref_0.a end
       else case when pg_catalog.circle_sub_pt(
          cast(cast(null as circle) as circle),
          cast((select location from public.emp limit 1 offset 13)
             as point)) ~ cast(nullif(case when cast(null as box) &> (select boxcol from public.brintest limit 1 offset 2)
                 then (select f1 from public.circle_tbl limit 1 offset 4)
               else (select f1 from public.circle_tbl limit 1 offset 4)
               end,
          case when (select pg_catalog.max(class) from public.f_star)
                 ~~ ref_0.c then cast(null as circle) else cast(null as circle) end
            ) as circle) then ref_0.a else ref_0.a end
       end as c0,
  case when (select intervalcol from public.brintest limit 1 offset 1)
         >= cast(null as "interval") then case when ((select pg_catalog.max(roomno) from public.room)
             !~~ ref_0.c)
        and (cast(null as xid) <> 100) then ref_0.b else ref_0.b end
       else case when ((select pg_catalog.max(roomno) from public.room)
             !~~ ref_0.c)
        and (cast(null as xid) <> 100) then ref_0.b else ref_0.b end
       end as c1,
  ref_0.a as c2,
  (select a from public.idxpart1 limit 1 offset 5) as c3,
  ref_0.b as c4,
    pg_catalog.stddev(
      cast((select pg_catalog.sum(float4col) from public.brintest)
         as float4)) over (partition by ref_0.a,ref_0.b,ref_0.c order by ref_0.b) as c5,
  cast(nullif(ref_0.b, ref_0.a) as int4) as c6, ref_0.b as c7, ref_0.c as c8
from
  public.mlparted3 as ref_0
where true;

Allerdings sind die zufälligen Queries von SQLsmith wie in diesem 40-Zeilen, 2,2kB Beispiel oft sehr groß und enthalten viel Rauschen, das zum eigentlichen Fehler nicht beiträgt. Bisher musste die Query manuell inspiziert und aufwändig bearbeitet werden, um das Beispiel auf einen minimalen Reproducer zu reduzieren, der von Entwicklern benutzt werden kann, das Problem zu beheben.

Komplexität reduzieren mit SQLreduce

Dieses Problem wird von SQLreduce gelöst. SQLreduce nimmt eine beliebige SQL-Query als Eingabe und lässt sie gegen einen PostgreSQL-Server laufen. Verschiedene Vereinfachungsschritte werden angewandt und nach jedem Schritt überprüft, ob die vereinfachte Query immer noch den gleichen Fehler im PostgreSQL-Server verursacht. Das Endergebnis ist eine Query mit minimaler Komplexität.

SQLreduce kann die Queries aus den Original-Bugreports von SQLsmith effektiv auf Queries reduzieren, die manuell reduzierten Queries gleichen. Beispielsweise kann SQLreduce die riesen Query von oben auf diese Zeile reduzieren:

SELECT pg_catalog.stddev(NULL) OVER () AS c5 FROM public.mlparted3 AS ref_0

Dabei versucht SQLreduce nicht, eine Query zu erzeugen, die semantisch identisch zum Original ist, oder das gleiche Query-Ergebnis liefert. Die Eingabequery ist sowieso fehlerbehaftet, und es wird eine minimale Query gesucht, die die gleiche Fehlermeldung provoziert, wenn sie gegen PostgreSQL läuft. Sollte die Eingabequery keinen Fehler werfen, so findet SQLreduce einfach SELECT als minimale Query.

Wie es funktioniert

Wir benutzen eine einfachere Query, um zu zeigen, wie SQLreduce arbeitet, und welche Schritte genommen werden, um das Rauschen aus der Eingabe zu entfernen. Diese Query ist fehlerhaft und enthält einige Teile, die entfernt werden sollen:

$ psql -c 'select pg_database.reltuples / 1000 from pg_database, pg_class where 0 < pg_database.reltuples / 1000 order by 1 desc limit 10'
ERROR:  column pg_database.reltuples does not exist

Wir geben die Query an SQLreduce:

$ sqlreduce 'select pg_database.reltuples / 1000 from pg_database, pg_class where 0 < pg_database.reltuples / 1000 order by 1 desc limit 10'

SQLreduce parst die Eingabe mit pglast und libpg_query, die den original PostgreSQL-Parse als Bibliothek mit Python-Bindings bereitstellen. Das Ergebnis ist ein Parsetree, der die Basis für die nächsten Schritte bildet. Der Parsetree sieht so aus:

selectStmt
├── targetList
│   └── /
│       ├── pg_database.reltuples
│       └── 1000
├── fromClause
│   ├── pg_database
│   └── pg_class
├── whereClause
│   └── <
│       ├── 0
│       └── /
│           ├── pg_database.reltuples
│           └── 1000
├── orderClause
│   └── 1
└── limitCount
    └── 10

Pglast enthält außerdem einen Query-Renderer, der den Parsetree wieder in SQL übersetzen kann, unten als regenerierte Query gezeigt. Die Eingabequery wird mit PostgreSQL ausgeführt, um das Ergebnis zu erhalten, in diesem Fall ERROR: column pg_database.reltuples does not exist.

Input query: select pg_database.reltuples / 1000 from pg_database, pg_class where 0 < pg_database.reltuples / 1000 order by 1 desc limit 10
Regenerated: SELECT pg_database.reltuples / 1000 FROM pg_database, pg_class WHERE 0 < ((pg_database.reltuples / 1000)) ORDER BY 1 DESC LIMIT 10
Query returns: ✔ ERROR:  column pg_database.reltuples does not exist

SQLreduce erzeugt neue Parsetrees, die strukturell einfacher sind, generiert davon SQL, und lässt diese Queries mit PostgreSQL laufen. Die ersten Vereinfachungsschritte passieren auf dem Toplevel-Knoten, wo SQLreduce versucht, ganze Teilbäume zu entfernen, um schnell zu Ergebnissen zu kommen. Der erste Reduktions-Versuch ist, LIMIT 10 zu entfernen:

SELECT pg_database.reltuples / 1000 FROM pg_database, pg_class WHERE 0 < ((pg_database.reltuples / 1000)) ORDER BY 1 DESC ✔

Das Query-Ergebnis ist immer noch ERROR: column pg_database.reltuples does not exist, angezeigt durch ein ✔ Häkchen. Als nächstes wird ORDER BY 1 entfernt, wiederum erfolgreich:

SELECT pg_database.reltuples / 1000 FROM pg_database, pg_class WHERE 0 < ((pg_database.reltuples / 1000)) ✔

Nun wird die gesamte Target-Liste entfernt:

SELECT FROM pg_database, pg_class WHERE 0 < ((pg_database.reltuples / 1000)) ✔

Bezogen auf die Fehlermeldung von PostgreSQL ist diese kürzere Query immer noch äquivalent zur Original-Query. Nun wird der erste nicht erfolgreiche Reduktionsschritt versucht, das Entfernen der FROM-Klausel:

SELECT WHERE 0 < ((pg_database.reltuples / 1000)) ✘ ERROR:  missing FROM-clause entry for table "pg_database"

Diese Query ist ebenfalls fehlerhaft, aber verursacht eine andere Fehlermeldung, daher wird der bisherige Parsetree beibehalten. Wiederrum wird ein ganzer Teilbaum entfernt, diesmal die WHERE-Klausel:

SELECT FROM pg_database, pg_class ✘ no error

Wir haben die Eingabe nun so weit reduziert, dass gar kein Fehler mehr entsteht. Der bisherige Parsetree wird weiter beibehalten. Er sieht nun so aus:

selectStmt
├── fromClause
│   ├── pg_database
│   └── pg_class
└── whereClause
    └── <
        ├── 0
        └── /
            ├── pg_database.reltuples
            └── 1000

SQLreduce beginnt nun, in den Baum abzusteigen. In der FROM-Klausel sind mehrere Einträge, also versucht es, die Liste zu kürzen. Als erstes wird pg_database entfernt, was nicht funktioniert, also wird stattdessen pg_class entfernt:

SELECT FROM pg_class WHERE 0 < ((pg_database.reltuples / 1000)) ✘ ERROR:  missing FROM-clause entry for table "pg_database"
SELECT FROM pg_database WHERE 0 < ((pg_database.reltuples / 1000)) ✔

Da wir nun eine neue minimale Query gefunden haben, beginnt die Rekursion erneut beim Toplevel mit einem neuen Versuch, die WHERE-Klausel zu entfernen. Da das nicht funktioniert, versucht es, den Ausdruck durch NULL zu ersetzen, aber das funktioniert auch nicht:

SELECT FROM pg_database ✘ no error
SELECT FROM pg_database WHERE NULL ✘ no error

Nun wird eine neue Art von Schritt versucht: Expression Pull-Up. Wir steigen in die WHERE-Klausel ab, wo wir A < B zunächst durch A und dann durch B ersetzen:

SELECT FROM pg_database WHERE 0 ✘ ERROR:  argument of WHERE must be type boolean, not type integer
SELECT FROM pg_database WHERE pg_database.reltuples / 1000 ✔
SELECT WHERE pg_database.reltuples / 1000 ✘ ERROR:  missing FROM-clause entry for table "pg_database"

Der erste Versuch funktionierte nicht, aber der zweite. Da die Query vereinfacht wurde, beginnen wir erneut beim Toplevel mit einem Versuch, die FROM-Klausel zu entfernen, aber die wird weiterhin benötigt.

In A / B können wir nun A hochziehen:

SELECT FROM pg_database WHERE pg_database.reltuples ✔
SELECT WHERE pg_database.reltuples ✘ ERROR:  missing FROM-clause entry for table "pg_database"

SQLreduce hat die minimale Query gefunden, die immer noch ERROR: column pg_database.reltuples does not exist verursacht. Der Parsetree ist nun:

selectStmt
├── fromClause
│   └── pg_database
└── whereClause
    └── pg_database.reltuples

Am Ende des Laufs wird die Query mit ein paar Statistiken ausgedruckt:

Minimal query yielding the same error:
SELECT FROM pg_database WHERE pg_database.reltuples

Pretty-printed minimal query:
SELECT
FROM pg_database
WHERE pg_database.reltuples

Seen: 15 items, 915 Bytes
Iterations: 19
Runtime: 0.107 s, 139.7 q/s

Diese minimale Query kann nun benutzt werden, um den Bug in PostgreSQL oder der Anwendung zu fixen.

Über credativ

Die credativ GmbH ist ein herstellerunabhängiges Beratungs- und Dienstleistungsunternehmen mit Standort in Mönchengladbach. Mit über 22+ Jahren an Entwicklungs- und Dienstleistungserfahrung im Open Source Bereich, kann die credativ GmbH Sie mit einem beispiellosen und individuell konfigurierbaren Support professionell Begleiten und Sie in allen Fragen bei Ihrer Open Source Infrastruktur voll und ganz unterstützen.

Seit dem erfolgreichen Merger mit Instaclustr 2021 ist die credativ GmbH das europäische Hauptquartier der Instaclustr Gruppe und hilft Unternehmen bei der Realisierung eigener Applikationen im großen Umfang dank Managed-Plattform-Solutions für Open Source Technologien wie zum Beispiel Apache Cassandra®, Apache Kafka®, Apache Spark™, Redis™, OpenSearch™, Apache ZooKeeper™, PostgreSQL® und Cadence.
Instaclustr kombiniert eine komplette Dateninfrastruktur-Umgebung mit praktischer Expertise, Support und Consulting um eine kontinuierliche Leistung und Optimierung zu gewährleisten. Durch Beseitigung der Komplexität der Infrastruktur, wird es Unternehmen ermöglicht, ihre internen Entwicklungs- und Betriebsressourcen auf die Entwicklung innovativer kundenorientierter Anwendungen zu geringeren Kosten zu konzentrieren. Zu den Kunden von Instaclustr gehören einige der größten und innovativsten Fortune-500-Unternehmen.

SQLreduce: Reduzieren Sie ausführliche SQL-Abfragen auf minimale Beispiele

Entwickler sehen sich oft sehr großen SQL-Abfragen gegenüber, die Fehler verursachen. SQLreduce ist ein Werkzeug, um diese Komplexität auf eine minimale Abfrage zu reduzieren.

SQLsmith generiert zufällige SQL-Abfragen

SQLsmith ist ein Werkzeug, das zufällige SQL-Abfragen generiert und sie gegen einen PostgreSQL-Server (und andere DBMS-Typen) ausführt. Die Idee ist, dass durch Fuzz-Testing des Query Parsers und Executors Corner-Case-Bugs gefunden werden können, die sonst bei manuellen Tests oder mit dem festen Satz von Testfällen in der PostgreSQL-Regressionstestsuite unbemerkt bleiben würden. Es hat sich als ein effektives Werkzeug erwiesen, mit dem seit 2015 über 100 Bugs in verschiedenen Bereichen des PostgreSQL-Servers und anderer Produkte gefunden wurden, darunter Sicherheitslücken, die von Executor-Bugs bis hin zu Segfaults in Typ- und Indexmethodenimplementierungen reichen. Zum Beispiel fand SQLsmith im Jahr 2018 heraus, dass die folgende Abfrage einen Segfault in PostgreSQL auslöste:

select
  case when pg_catalog.lastval() < pg_catalog.pg_stat_get_bgwriter_maxwritten_clean() then case when pg_catalog.circle_sub_pt(
          cast(cast(null as circle) as circle),
          cast((select location from public.emp limit 1 offset 13)
             as point)) ~ cast(nullif(case when cast(null as box) &> (select boxcol from public.brintest limit 1 offset 2)
                 then (select f1 from public.circle_tbl limit 1 offset 4)
               else (select f1 from public.circle_tbl limit 1 offset 4)
               end,
          case when (select pg_catalog.max(class) from public.f_star)
                 ~~ ref_0.c then cast(null as circle) else cast(null as circle) end
            ) as circle) then ref_0.a else ref_0.a end
       else case when pg_catalog.circle_sub_pt(
          cast(cast(null as circle) as circle),
          cast((select location from public.emp limit 1 offset 13)
             as point)) ~ cast(nullif(case when cast(null as box) &> (select boxcol from public.brintest limit 1 offset 2)
                 then (select f1 from public.circle_tbl limit 1 offset 4)
               else (select f1 from public.circle_tbl limit 1 offset 4)
               end,
          case when (select pg_catalog.max(class) from public.f_star)
                 ~~ ref_0.c then cast(null as circle) else cast(null as circle) end
            ) as circle) then ref_0.a else ref_0.a end
       end as c0,
  case when (select intervalcol from public.brintest limit 1 offset 1)
         >= cast(null as "interval") then case when ((select pg_catalog.max(roomno) from public.room)
             !~~ ref_0.c)
        and (cast(null as xid) <> 100) then ref_0.b else ref_0.b end
       else case when ((select pg_catalog.max(roomno) from public.room)
             !~~ ref_0.c)
        and (cast(null as xid) <> 100) then ref_0.b else ref_0.b end
       end as c1,
  ref_0.a as c2,
  (select a from public.idxpart1 limit 1 offset 5) as c3,
  ref_0.b as c4,
    pg_catalog.stddev(
      cast((select pg_catalog.sum(float4col) from public.brintest)
         as float4)) over (partition by ref_0.a,ref_0.b,ref_0.c order by ref_0.b) as c5,
  cast(nullif(ref_0.b, ref_0.a) as int4) as c6, ref_0.b as c7, ref_0.c as c8
from
  public.mlparted3 as ref_0
where true;

Wie jedoch in diesem 40-zeiligen, 2,2 KB großen Beispiel sind die von SQLsmith generierten Zufallsabfragen, die einen Fehler auslösen, meist sehr groß und enthalten viel Rauschen, das nicht zum Fehler beiträgt. Bisher waren eine manuelle Überprüfung der Abfrage und eine mühsame Bearbeitung erforderlich, um das Beispiel auf einen minimalen Reproducer zu reduzieren, den Entwickler zur Behebung des Problems verwenden können.

Reduzieren Sie die Komplexität mit SQLreduce

Dieses Problem wird durch SQLreduce gelöst. SQLreduce nimmt als Eingabe eine beliebige SQL-Abfrage entgegen, die dann gegen einen PostgreSQL-Server ausgeführt wird. Es werden verschiedene Vereinfachungsschritte angewendet, wobei nach jedem Schritt geprüft wird, ob die vereinfachte Abfrage noch denselben Fehler von PostgreSQL auslöst. Das Endergebnis ist eine SQL-Abfrage mit minimaler Komplexität.

SQLreduce reduziert effektiv die Abfragen aus ursprünglichen Fehlerberichten von SQLsmith auf Abfragen, die manuell reduzierten Abfragen entsprechen. Zum Beispiel kann SQLreduce die obige Monsterabfrage effektiv auf Folgendes reduzieren:

SELECT pg_catalog.stddev(NULL) OVER () AS c5 FROM public.mlparted3 AS ref_0

Beachten Sie, dass SQLreduce nicht versucht, eine Abfrage abzuleiten, die semantisch identisch mit dem Original ist oder dasselbe Abfrageergebnis liefert – die Eingabe wird als fehlerhaft angenommen, und wir suchen nach der minimalen Abfrage, die dieselbe Fehlermeldung von PostgreSQL erzeugt, wenn sie gegen eine Datenbank ausgeführt wird. Wenn die Eingabeabfrage zufällig keinen Fehler erzeugt, ist die von SQLreduce ausgegebene Minimalabfrage nur SELECT.

Wie es funktioniert

Wir werden eine einfachere Abfrage verwenden, um zu demonstrieren, wie SQLreduce funktioniert und welche Schritte unternommen werden, um Rauschen aus der Eingabe zu entfernen. Die Abfrage ist falsch und enthält ein wenig Durcheinander, das wir entfernen wollen:

$ psql -c 'select pg_database.reltuples / 1000 from pg_database, pg_class where 0 < pg_database.reltuples / 1000 order by 1 desc limit 10'
ERROR:  column pg_database.reltuples does not exist

Übergeben wir die Abfrage an SQLreduce:

$ sqlreduce 'select pg_database.reltuples / 1000 from pg_database, pg_class where 0 < pg_database.reltuples / 1000 order by 1 desc limit 10'

SQLreduce beginnt mit dem Parsen der Eingabe mit pglast und libpg_query, die den ursprünglichen PostgreSQL-Parser als Bibliothek mit Python-Bindungen bereitstellen. Das Ergebnis ist ein Parse-Baum, der die Grundlage für die nächsten Schritte bildet. Der Parse-Baum sieht wie folgt aus:

selectStmt
├── targetList
│   └── /
│       ├── pg_database.reltuples
│       └── 1000
├── fromClause
│   ├── pg_database
│   └── pg_class
├── whereClause
│   └── <
│       ├── 0
│       └── /
│           ├── pg_database.reltuples
│           └── 1000
├── orderClause
│   └── 1
└── limitCount
    └── 10

Pglast enthält auch einen Query Renderer, der den Parse-Baum als SQL zurückgeben kann, wie in der regenerierten Abfrage unten gezeigt. Die Eingabeabfrage wird gegen PostgreSQL ausgeführt, um das Ergebnis zu bestimmen, in diesem Fall ERROR: column pg_database.reltuples does not exist.

Input query: select pg_database.reltuples / 1000 from pg_database, pg_class where 0 < pg_database.reltuples / 1000 order by 1 desc limit 10
Regenerated: SELECT pg_database.reltuples / 1000 FROM pg_database, pg_class WHERE 0 < ((pg_database.reltuples / 1000)) ORDER BY 1 DESC LIMIT 10
Query returns: ✔ ERROR:  column pg_database.reltuples does not exist

SQLreduce arbeitet, indem es neue Parse-Bäume ableitet, die strukturell einfacher sind, SQL daraus generiert und diese Abfragen gegen die Datenbank ausführt. Die ersten Vereinfachungsschritte arbeiten auf dem obersten Knoten, wo SQLreduce versucht, ganze Teilbäume zu entfernen, um schnell ein Ergebnis zu finden. Die erste versuchte Reduktion ist das Entfernen von LIMIT 10:

SELECT pg_database.reltuples / 1000 FROM pg_database, pg_class WHERE 0 < ((pg_database.reltuples / 1000)) ORDER BY 1 DESC ✔

Das Abfrageergebnis ist immer noch ERROR: column pg_database.reltuples does not exist, was durch ein ✔ Häkchen angezeigt wird. Als nächstes wird ORDER BY 1 entfernt, wieder erfolgreich:

SELECT pg_database.reltuples / 1000 FROM pg_database, pg_class WHERE 0 < ((pg_database.reltuples / 1000)) ✔

Nun wird die gesamte Zielliste entfernt:

SELECT FROM pg_database, pg_class WHERE 0 < ((pg_database.reltuples / 1000)) ✔

Diese kürzere Abfrage ist immer noch äquivalent zum Original bezüglich der Fehlermeldung, die zurückgegeben wird, wenn sie gegen die Datenbank ausgeführt wird. Nun wird der erste erfolglose Reduktionsschritt versucht, bei dem die gesamte FROM-Klausel entfernt wird:

SELECT WHERE 0 < ((pg_database.reltuples / 1000)) ✘ ERROR:  missing FROM-clause entry for table "pg_database"

Diese Abfrage ist auch fehlerhaft, löst aber eine andere Fehlermeldung aus, so dass der vorherige Parse-Baum für die nächsten Schritte beibehalten wird. Wieder wird ein ganzer Teilbaum entfernt, jetzt die WHERE-Klausel:

SELECT FROM pg_database, pg_class ✘ no error

Wir haben die Eingabeabfrage nun so weit reduziert, dass sie keine Fehler mehr ausgibt. Der vorherige Parse-Baum wird weiterhin beibehalten, der nun wie folgt aussieht:

selectStmt
├── fromClause
│   ├── pg_database
│   └── pg_class
└── whereClause
    └── <
        ├── 0
        └── /
            ├── pg_database.reltuples
            └── 1000

Nun beginnt SQLreduce, sich in den Baum einzugraben. Es gibt mehrere Einträge in der -Klausel, also versucht es, die Liste zu verkürzen. Zuerst wird pg_database entfernt, aber das funktioniert nicht, also wird pg_class entfernt:

SELECT FROM pg_class WHERE 0 < ((pg_database.reltuples / 1000)) ✘ ERROR:  missing FROM-clause entry for table "pg_database"
SELECT FROM pg_database WHERE 0 < ((pg_database.reltuples / 1000)) ✔

Da wir eine neue minimale Abfrage gefunden haben, startet die Rekursion auf oberster Ebene mit einem weiteren Versuch, die WHERE-Klausel zu entfernen. Da das nicht funktioniert, versucht es, den Ausdruck durch NULL zu ersetzen, aber auch das funktioniert nicht.

SELECT FROM pg_database ✘ no error
SELECT FROM pg_database WHERE NULL ✘ no error

Nun wird eine neue Art von Schritt versucht: Expression Pull-up. Wir steigen in die WHERE-Klausel ab, wo wir A < B zuerst durch A und dann durch B ersetzen.

SELECT FROM pg_database WHERE 0 ✘ ERROR:  argument of WHERE must be type boolean, not type integer
SELECT FROM pg_database WHERE pg_database.reltuples / 1000 ✔
SELECT WHERE pg_database.reltuples / 1000 ✘ ERROR:  missing FROM-clause entry for table "pg_database"

Der erste Versuch hat nicht funktioniert, aber der zweite. Da wir die Abfrage vereinfacht haben, starten wir auf oberster Ebene neu, um zu prüfen, ob die FROM-Klausel entfernt werden kann, aber sie ist immer noch erforderlich.

Von A / B können wir wieder A hochziehen:

SELECT FROM pg_database WHERE pg_database.reltuples ✔
SELECT WHERE pg_database.reltuples ✘ ERROR:  missing FROM-clause entry for table "pg_database"

SQLreduce hat die minimale Abfrage gefunden, die immer noch ERROR: column pg_database.reltuples does not exist mit diesem Parse-Baum auslöst:

selectStmt
├── fromClause
│   └── pg_database
└── whereClause
    └── pg_database.reltuples

Am Ende des Laufs wird die Abfrage zusammen mit einigen Statistiken ausgegeben:

Minimal query yielding the same error:
SELECT FROM pg_database WHERE pg_database.reltuples

Pretty-printed minimal query:
SELECT
FROM pg_database
WHERE pg_database.reltuples

Seen: 15 items, 915 Bytes
Iterations: 19
Runtime: 0.107 s, 139.7 q/s

Diese minimale Abfrage kann nun überprüft werden, um den Fehler in PostgreSQL oder in der Anwendung zu beheben.

Über credativ

Die credativ GmbH ist ein herstellerunabhängiges Beratungs- und Dienstleistungsunternehmen mit Sitz in Mönchengladbach. Mit über 22 Jahren Entwicklungs- und Serviceerfahrung im open source Bereich kann die credativ GmbH Sie mit beispiellosem und individuell anpassbarem Support unterstützen. Wir sind hier, um Ihnen bei all Ihren open source Infrastruktur-Bedürfnissen zu helfen und Sie zu unterstützen.

Dieser Artikel wurde ursprünglich von Christoph Berg geschrieben.

Das Debian-Projekt hat soeben Version 11 („Bullseye“) ihres freien Betriebssystems veröffentlicht. Insgesamt haben über 6,208 Contributor am neuesten Release mitgearbeitet und zur erfolgreichen Umsetzung beigetragen. Wir möchten allen Beteiligten für ihre gemeinsamen Anstrengungen und harte Arbeit, die sie in den letzten Jahren in die Entwicklung dieser neuen Veröffentlichung investiert haben, danken.

Herzlichen Glückwunsch an die Debian-Community die wieder einmal gezeigt hat, dass internationale Zusammenarbeit für Freie Software einfach spitze sein kann.

Darüber hinaus möchten wir auch unseren eigenen Debian-Entwicklern die Anerkennung geben die sie sich verdient haben. Wir schätzen die Arbeit aller unser Kollegen sehr, und unterstützen sie ganz klar Beiträge zu Open Source und Freie Software Projekten weltweit einzureichen.

Das neue release, Debian 11, kann hier heruntergeladen werden:

https://www.debian.org/devel/debian-installer/index.en.html

Neues in Debian 11 „Bullseye“

Debian 11 kommt mit einer Menge Aktualisierungen und Upgrades. Dieser neue Release enthält über 13.370 komplett neue Pakete, insgesamt also über 57.703 Pakete bei der Veröffentlichung. Von diesen wurden 35.532 Softwarepakete auf neuere Versionen aktualisiert, einschließlich einer Aktualisierung des Kernels von 4.19 in „Buster“ auf 5.10 in „Bullseye“.

Weiterhin erweitert „Bullseye“ die Möglichkeiten des treiberlosen Druckens mit CUPS und des treiberlosen Scannens mit SANE. Während es mit „Buster“ möglich war, CUPS für das treiberlose Drucken zu verwenden, kommt „Bullseye“ mit dem Paket ipp-usb, das es erlaubt, ein USB-Gerät als Netzwerkgerät zu behandeln und damit die Möglichkeiten des treiberlosen Druckens zu erweitern. SANE lässt sich damit verbinden, wenn es korrekt eingerichtet und an einen USB-Port angeschlossen ist.

Wie in den letzten releases kommt Debian 11 auch mit einer Debian Edu / Skolelinux version. Debian Edu stellt seit vielen Jahren eine Komplettlösung für Schulen dar. Das gesamte Schulnetzwerk wird durch Debian Edu bereitgestellt und es müssen nach Installation nur noch Benutzer und Maschinen hinzugefügt werden. Dies kann ganz komfortabel über die Weboberfläche GOsa² erledigt werden.

Für weitere Informationen und technische Details zum neuen Debian 11 Release lesen Sie bitte die offiziellen Release Notes auf Debian.org

https://www.debian.org/releases/bullseye/amd64/release-notes/

Beiträge von credativ Mitarbeitern

credativ war schon immer ein aktiver Teil der Debian-Community und hat seit 2004 jede DebConf besucht. Desweiteren findet Debian Gebrauch als Haupt-Betriebssystem in der Managed Platform von Instaclustr.

credativ Mitarbeiter haben diverse Aufgaben im neuen Release von Debian 11 übernommen. Hier ist ein kleiner Ausschnitt an Beiträgen und Mitarbeit unserer Kollegen:

• 90% der PostgreSQL^® Paketierung des neuen Release
• Wartung mitarbeit an verschiedenen Paketen
• Unterstützung als Debian-Sys-Admin
• Mitarbeit and Debian Edu / Skolelinux
• Entwicklungsarbeit an Kernel-Images
• Entwicklungsarbeit an Cloud-Images
• Entwicklungsarbeit für diverse Debian Backports
• Mitarbeit und Unterstützung bei salsa.debian.org

Jeder unserer Kollegen hat einen  signifikanten Beitrag zum aktuellen Releasegeleistet und deshalb noch einmal einen herzlichen Dank an alle Debianer:

• Adrian Vondendriesch
• Alexander Wirt (Formorer)
• Bastian Blank (Waldi)
• Christoph Berg (Myon)
• Dominik George (Natureshadow)
• Felix Geyer (fgeyer)
• Martin Zobel-Helas (Zobel)
• Michael Banck (azeem)
• Dr. Michael Meskes (feivel)
• Noël Köthe (Noel)
• Sven Bartscher (kritzefitz)

Wie upgraden Sie am besten von Debian 10 zu Debian 11?

Debian 11 „Bullseye“ ist ein weiteres Major Release und als solches empfehlen wir allen Lesern zeitnah zu upgraden. Debian 10 “Buster” wird zwar noch bis zum Juni 2024 mit LTS supported, dennoch macht es Sinn sich jetzt schon mal mit einem möglichen Upgrade auseinander zu setzen.
Da es sich jedoch um ein Major Release handelt, erfordert der Upgrade-Prozess ein gewisses Maß an manueller Arbeit, um ein reibungsloses Upgrade zu gewährleisten.

Eine ausführliche Anleitung ist auf debian.org verfügbar, daher werden wir hier nur die fünf wichtigsten Schritte auflisten:

1. Zur Vorbereitung sollten Sie alle Daten, die nicht verloren gehen sollen, sichern und sich auf die Wiederherstellung vorbereiten
2. Entfernen Sie alle Nicht-Debian-Pakete und bereinigen Sie übrig gebliebene Dateien und alte Versionen
3. Führen Sie das ein Upgrade auf die aktuellste minor Version durch
4. Überprüfen Sie Ihre APT-Quelldateien und bereiten Sie sie vor, indem Sie die relevanten Internetquellen oder lokalen Spiegelserver hinzufügen
5. Aktualisieren Sie Ihre Pakete und aktualisieren Sie dann Ihr System

Alle bestehenden Kunden, die eine auf Debian basierende Installation betreiben, sind natürlich durch unseren Service und Support abgedeckt und können sich jederzeit an uns wenden.

Wenn Sie an einem Upgrade Ihrer alten Debian-Version interessiert sind oder wenn Sie Fragen zu Ihrer eigenen Open-Source-Infrastruktur haben, zögern Sie nicht, uns eine E-Mail zu schreiben oder uns unter info@credativ.de zu kontaktieren.

Oder Sie können in wenigen Minuten mit einer der Open-Source-Technologien wie Apache Cassandra, Apache Kafka, Redis und OpenSearch auf der Instaclustr Managed Platform loslegen. Melden Sie sich noch heute für eine kostenlose Testversion an.

Moodle ist eine beliebte Open Source Online-Lernplattform. Gerade in Zeiten von COVID19 und Distanzunterricht hat sich die Bedeutung von Moodle für Schulen und Universität weiter verstärkt, da ganze Bundesländer ihren Schulunterricht innerhalb weniger Tage auf Moodle und andere Plattformen wie BigBlueButton umgestellt haben. Dies führt unweigerlich zu Skalierungs-Problemen, wenn plötzlich mehrere zehntausend Schüler auf Moodle zugreifen müssen. Neben der Skalierung der Moodle Applikation selber muss auch die Datenbank bedacht werden, hier kann unter anderem PostgreSQL verwendet werden. Im Folgenden werden Möglichkeiten zum Load-Balancing für Moodle unter PostgreSQL vorgestellt.

Hochverfügbarkeit durch Patroni

Eine Online-Lernplattform ist aus Sicht des Bildungssystems kritische Infrastruktur und sollte dementsprechend Hochverfügbar ausgelegt sein, was insbesondere die Datenbank betrifft. Eine gute Lösung für PostgreSQL ist hier Patroni, über dessen Debian-Integration wir bereits früher berichtet haben. Kurz zusammengefasst verwendet Patroni einen “Distributed Consensus Store” (DCS) um aus einem typischerweise 3-Knoten Cluster einen sogenannten Leader zu wählen bzw. bei Ausfalls eines Leaders ein Failover zu initiieren und einen neuen Leader zu wählen, ohne dabei in ein Split-Brain Szenario zu gelangen. Zusätzlich bietet Patroni eine REST-API, die für die Kommunikation der Knoten untereinander und von dem patronictl Programm verwendet wird, z.B. um die Postgres-Konfiguration Online auf allen Knoten zu ändern oder einen Switchover einzuleiten.

Client-Lösungen für Hochverfügbarkeit

Aus Sicht von Moodle muss allerdings zusätzlich gewährleistet sein, dass es mit dem Leader verbunden ist, da sonst keine schreibenden Transaktionen möglich sind. Herkömmliche Hochverfügbarkeits-Lösungen wie Pacemaker verwenden hier virtuelle IPs (VIPs), die im Failover-Fall zum neuen Primary-Knoten geschwenkt werden. Für Patroni gibt es stattdessen das vip-manager Projekt, welches den Leader-Key im DCS überwacht und Cluster-VIP lokal setzt oder entfernt. Dieses ist ebenfalls in Debian integriert.

Eine Alternative ist die Verwendung von Client-seitigem Failover basierend auf der libpq-Bibliothek von PostgreSQL. Hierfür werden alle Cluster-Mitglieder im Verbindungs-String aufgelistet und die Verbindungs-Option target_session_attrs=read-write gewählt. So konfiguriert versucht der Client bei einem Verbindungs-Abbruch die anderen Knoten zu erreichen, bis ein neuer Primary gefunden wird.

Eine andere Möglichkeit ist HAProxy, ein hoch-skalierender TCP/HTTP Load-Balancer. Durch die Möglichkeit periodische Health-Checks auf die Patroni REST-API der einzelnen Knoten durchzuführen kann es den aktuellen Leader ermitteln und Client-Anfragen dorthin weiterleiten.

Datenbank-Konfiguration von Moodle

Die Verbindung von Moodle zu einer PostgreSQL-Datenbank wird in config.php konfiguriert, z.B. für eine einfache Stand-Alone Datenbank:

$CFG->dbtype    = 'pgsql';
$CFG->dblibrary = 'native';
$CFG->dbhost    = '192.168.1.1';
$CFG->dbname    = 'moodle';
$CFG->dbuser    = 'moodle';
$CFG->dbpass    = 'moodle';
$CFG->prefix    = 'mdl_';
$CFG->dboptions = array (
  'dbport' => '',
  'dbsocket' => ''
);

Hier wird der Standard-Port 5432 verwendet.

Falls Streaming-Replication verwendet wird, können die Standbys zusätzlich als readonly definiert werden und einen eigenen Datenbank-Benutzer (der lediglich Leseberechtigungen benötigt) zugeordnet werden:

$CFG->dboptions = array (
[...]
  'readonly' => [
    'instance' => [
      [
      'dbhost' => '192.168.1.2',
      'dbport' =>  '',
      'dbuser' => 'moodle_safereads',
      'dbpass' => 'moodle'
      ],
      [
      'dbhost' => '192.168.1.3',
      'dbport' =>  '',
      'dbuser' => 'moodle_safereads',
      'dbpass' => 'moodle'
      ]
    ]
  ]
);

Failover/Load-Balancing mit libpq

Wenn allerdings ein hochverfügbarer Postgres-Cluster mit Patroni verwendet wird kann wie oben beschrieben der Primary im Failover- oder Switchover-Fall wechseln. Moodle bietet hier keine Möglichkeit generische Datenbank-Optionen zu setzen und damit ist die Einstellung von target_session_attrs=read-write direkt nicht möglich. Wir haben hierfür einen Patch entwickelt und in den Moodle Tracker eingestellt. Dieser erlaubt die zusätzliche Option 'dbfailover' => 1, im $CFG->dboptions-Array, was die nötige Verbindungs-Option target_session_attrs=read-write hinzufügt. Eine angepasste config.php sähe damit z.B. so aus:

$CFG->dbtype    = 'pgsql';
$CFG->dblibrary = 'native';
$CFG->dbhost    = '192.168.1.1,192.168.1.2,192.168.1.3';
$CFG->dbname    = 'moodle';
$CFG->dbuser    = 'moodle';
$CFG->dbpass    = 'moodle';
$CFG->prefix    = 'mdl_';
$CFG->dboptions = array (
  'dbfailover' => 1,
  'dbport' => '',
  'dbsocket' => '',
  'readonly' => [
    'instance' => [
      [
      'dbhost' => '192.168.1.1',
      'dbport' =>  '',
      'dbuser' => 'moodle_safereads',
      'dbpass' => 'moodle'
      ],
      [
      'dbhost' => '192.168.1.2',
      'dbport' =>  '',
      'dbuser' => 'moodle_safereads',
      'dbpass' => 'moodle'
      ],
      [
      'dbhost' => '192.168.1.3',
      'dbport' =>  '',
      'dbuser' => 'moodle_safereads',
      'dbpass' => 'moodle'
      ]
    ]
  ]
);

Failover/Load-Balancing mit HAProxy

Wenn stattdessen HAProxy verwendet werden soll, dann muss entsprechend der HAProyx-Host als $CFG->dbhost eingetragen werden, z.B. 127.0.0.1 wenn HAProxy lokal auf dem/den Moodle Server(n) läuft. Zusätzlich kann ein zweiter Port (z.B. 65432) für lesende Anfragen definiert werden, der analog zu einem Streaming-Replication Standby als readonly in $CFG->dboptions konfiguriert wird. Die config.php sähe dann z.B. so aus:

$CFG->dbtype    = 'pgsql';
$CFG->dblibrary = 'native';
$CFG->dbhost    = '127.0.0.1';
$CFG->dbname    = 'moodle';
$CFG->dbuser    = 'moodle';
$CFG->dbpass    = 'moodle';
$CFG->prefix    = 'mdl_';
$CFG->dboptions = array (
  'dbport' => '',
  'dbsocket' => '',
  'readonly' => [
    'instance' => [
      'dbhost' => '127.0.0.1',
      'dbport' =>  '65432',
      'dbuser' => 'moodle_safereads',
      'dbpass' => 'moodle'
    ]
  ]
);

Die HAProxy Konfigurations-Datei haproxy.cfg kann beispielhaft folgendermaßen aussehen:

global
    maxconn 100

defaults
    log global
    mode tcp
    retries 2
    timeout client 30m
    timeout connect 4s
    timeout server 30m
    timeout check 5s

listen stats
    mode http
    bind *:7000
    stats enable
    stats uri /

listen postgres_write
    bind *:5432
    mode tcp
    option httpchk
    http-check expect status 200
    default-server inter 3s fall 3 rise 3 on-marked-down shutdown-sessions
    server pg1 192.168.1.1:5432 maxconn 100 check port 8008
    server pg2 192.168.1.2:5432 maxconn 100 check port 8008
    server pg3 192.168.1.3:5432 maxconn 100 check port 8008

HAProxy erwartet eingehende schreibende Verbindungen (postgres_write) auf Port 5432 und sendet sie an Port 5432 der Cluster-Mitglieder weiter. Der Primary wird durch einen HTTP-Check auf Port 8008 (dem Standard Patroni REST-API Port) ermittelt; Patroni gibt hier für den Primary den Status 200 und für Standbys den Status 503 zurück.

Für die lesenden Abfragen (postgres_read) muss entschieden werden, ob der Primary auch read-only Anfragen beantworten soll oder nicht. Wenn dies der Fall ist kann ein einfacher Postgres-Check verwendet werden; allerdings kann dies zu Einträgen im Postgres-Log bzgl. fehlerhafter oder unvollständiger Logins führen:

listen postgres_read
    bind *:65432
    mode tcp
    balance leastconn
    option pgsql-check user haproxy
    default-server inter 3s fall 3 rise 3 on-marked-down shutdown-sessions
    server pg1 192.168.1.1:5432 check
    server pg2 192.168.1.2:5432 check
    server pg3 192.168.1.3:5432 check

Wenn der Primary nicht am Read-Scaling teilnehmen soll kann einfach der gleiche HTTP-Check wie in der postgres_write Sektion verwendet werden, wobei diesmal der HTTP-Status 503 erwartet wird:

listen postgres_read
    bind *:65432
    mode tcp
    balance leastconn
    option httpchk
    http-check expect status 503
    default-server inter 3s fall 3 rise 3 on-marked-down shutdown-sessions
    server pg1 192.168.1.1:5432 check port 8008
    server pg2 192.168.1.2:5432 check port 8008
    server pg3 192.168.1.3:5432 check port 8008

Überarbeitetes Ansible Playbook

HAProxy-Unterstützung wurde auch in Version 0.3 unseres Ansible-Playbooks für die automatisierte Einrichtung eines Drei-Knoten-PostgreSQL-Patroni-Clusters unter Debian implementiert. Mit der neuen Variable haproxy_primary_read_scale kann entschieden werden, ob HAProxy Anfragen auf den Read-Only Port auch an den Primary-Knoten oder nur an die Follower geben soll.

Unterstützung

Falls Sie Unterstützung bei PostgreSQL, Patroni, HAProxy, Moodle oder anderer Software benötigen, steht Ihnen unser Open Source Support Center zur Verfügung – Falls gewünscht auch 24 Stunden am Tag, an 365 Tagen im Jahr.

Wir freuen uns über Ihre Kontaktaufnahme.

Am 24.09.2020 veröffentlicht das PostgreSQL^®-Projekt die 13. Version der freien Datenbank PostgreSQL^®. Auch in diesem Jahr gibt es zahlreiche neue Features und Verbesserungen.

Einige dieser Neuerungen haben wir in vorherigen Blogartikel bereits vorgestellt. Hier noch einmal zwei wichtige Features im Überlick:

Parallel VACUUM

Mit PostgreSQL^® 13 lernt das VACUUM–Kommando eine neue Fähigkeit: Das parallele Aufräumen von Indexen. VACUUM in einer PostgreSQL^®-Datenbank ist im Wesentlichen für mehrere wichtige Aufgaben verantwortlich:

• Garbage Collection, also freigeben von gelöschten Tupel für die Wiederverwendung
• Verhindern von Bloat in einer Datenbank und damit von ausuferndem Plattenplatzbedarfs
• Verhindern des Überlaufs des Transaktionszählers (FREEZE)
• Diverses Aufräumen von Transaktionsinformationen, usw.

Ausführliche Informationen zum Thema Parallel VACUUM erhalten Sie in unserem Blogartikel: PostgreSQL^® 13 Bits: Parallel VACUUM

Deduplication in B-tree Indexe

Mit der kommenden Version 13 von PostgreSQL^® werden B-tree Indexe in der Lage sein, mehrfach identisch vorkommende Spaltenwerte physikalisch kompakter abzuspeichern. Bereits mit Version 12 wurde eine Optimierung eingeführt, die B-tree Indexe ein effizienteres Speichern von wiederholt vorkommenden Spaltenwerten gestattet. Hierzu werden diese Spalten in derselben Reihenfolge auch im Index abgelegt, wie diese auch physikalisch im sogenannten Heap, also der physischen Repräsentation der Tabelle, vorkommen. Dies vereinfacht die Verwaltung dieser Indexeinträge und kann auch bereits mit dieser Maßnahme das Wachstum eines Indexes positiv beeinflussen.

In Version 13 werden nun effektiv wiederholt vorkommende Spaltenwerte zusammengefasst im B-tree Index gespeichert, Deduplication genannt. Eine sogenannte Posting List verwaltet dabei, als alternative Repräsentation eines Index Tupel die physischen Positionen an welcher Stelle in der Tabelle der jeweilige Spaltenwert zu finden ist. Diese Repräsentation ist deutlich kompakter als die Spaltenwerte jeweils für sich zu speichern. Ferner ermöglicht es auch das schnellere Auffinden von entsprechenden Spaltenwerten und VACUUM kann solche Indexe auch effizienter warten.

Auch zu diesem Thema haben wir einen ausführlichen Blogartikel verfasst: PostgreSQL^® 13 Bits: Deduplication in B-tree Indexe

Fragen?

Bei Fragen rund um den Einsatz von PostgreSQL^® 13 stehen wir Ihnen natürlich gerne zur Verfügung. Sprechen Sie uns an! Unser PostgreSQL^® Competence Center ist bei Bedarf 24 Stunden am Tag, an 365 Tagen im Jahr für Sie verfügbar.

Wir bedanken uns bei allen Entwicklern des PostgreSQL^®-Projekts und natürlich ganz besonders bei unseren eigenen Mitarbeitern, die ebenfalls an den Arbeiten für PostgreSQL^® 13 beteiligt waren.

POSTGRES XIII

Während des Beta-Zeitraums von PostgreSQL^® 13 hat credativ auch in diesem Jahr wieder spannendes Merchandise gestaltet. Angelehnt an die Raumfahrtmission Apollo 13 wurde ein Design entwickelt, das den Fortschritt und die hohe Reichweite von PostgreSQL^® darstellt.

Gerne möchten wir von Ihnen wissen, warum Sie PostgreSQL^® einsetzen. Hierzu können Sie uns auf Twitter einen Retweet mit Kommentar auf unseren Beitrag schreiben.

Als Dankeschön verschenken wir an zehn zufällig ausgewählte Teilnehmer jeweils ein POSTGRES XIII Merchandise-Set mit T-Shirt, Patches und Stickern.

Teilnahmeschluss ist der 24.09.2020 um 16 Uhr.

Viel Glück und Erfolg wünscht das Team der credativ GmbH!

Teilnahmebedingungen

Die Teilnahme am Beitrag von credativ GmbH, nachfolgend Betreiber oder Veranstalter genannt, ist kostenlos und richtet sich ausschließlich nach diesen Teilnahmebedingungen.

Ablauf

Die Dauer des Ablaufes erstreckt sich vom 23.09.2020, 12:30 Uhr bis zum 24.09.2020, 16 Uhr. Innerhalb dieses Zeitraums erhalten Nutzer über die Plattform Twitter die Möglichkeit, teilzunehmen.

Teilnahme

Um teilzunehmen ist ein Retweet inklusive Kommentar über die Begründung zur Nutzung von PostgreSQL^® des entsprechenden Twitter-Beitrages von @credativde notwendig. Die Teilnahme ist nur innerhalb des Teilnahmezeitraums möglich. Nach Teilnahmeschluss eingehende Einsendungen werden bei der Auswahl nicht berücksichtigt.

Pro Teilnehmer nimmt nur eine übermittelte Anmeldung teil. Es ist untersagt, mehrere Twitter-Accounts zur Erhöhung der Chancen zu verwenden.

Die Teilnahme ist kostenlos.

Teilnahmeberechtigte

Teilnahmeberechtigt sind natürliche Personen, die das 14. Lebensjahr vollendet haben. Die Teilnahme ist nicht auf Kunden des Veranstalters beschränkt und nicht vom Erwerb einer Ware oder Dienstleistung abhängig.

Sollte ein Teilnehmer in seiner Geschäftsfähigkeit eingeschränkt sein, bedarf es der Einwilligung seines gesetzlichen Vertreters.

Nicht teilnahmeberechtigt sind alle an der Konzeption und Umsetzung beteiligte Personen und Mitarbeiter des Betreibers sowie ihre Familienmitglieder. Zudem behält sich der Betreiber vor, nach eigenem Ermessen Personen von der Teilnahme auszuschließen, wenn berechtigte Gründe vorliegen, beispielsweise

(a) bei Manipulationen im Zusammenhang mit der Durchführung, (b) bei Verstößen gegen diese Teilnahmebedingungen, (c) bei unlauterem Handeln oder (d) bei falschen oder irreführenden Angaben im Zusammenhang mit der Teilnahme.

Benachrichtigung und Übermittlung des Merchandise-Sets

Zehn von credativ zufällig ausgewählte Teilnehmer erhalten jeweils ein POSTGRES XIII Merchandise-Set mit T-Shirt, Patches und Stickern. Es kommen ausschließlich diejenigen Teilnehmer in die Auswahl, die einen Retweet mit Kommentar über Ihre Begründung zur Nutzung von PostgreSQL^® korrekt durchgeführt haben.

Die zufällig ausgewählten Teilnehmer werden zeitnah über eine gesonderte Privatnachricht bei Twitter informiert, sowie in einem öffentlichen Beitrag markiert.

Die Aushändigung der Merchandise-Sets erfolgt ausschließlich an die ausgewählten Teilnehmer oder an die gesetzlichen Vertreter der minderjährigen Teilnehmer. Ein Umtausch, eine Selbstabholung sowie eine Barauszahlung sind nicht möglich.

Für den Versand des Merchandise-Sets anfallende Kosten übernimmt der Betreiber. Mit der Inanspruchnahme verbundene Zusatzkosten gehen zu Lasten der ausgewählten Teilnehmer. Für eine etwaige Versteuerung der Merchandise-Sets sind die ausgewählten Teilnehmer selbst verantwortlich.

Meldet sich der ausgewählte Teilnehmer nach zweifacher Aufforderung innerhalb einer Frist von 3 Wochen nicht, kann das Merchandise-Set auf einen anderen Teilnehmer übertragen werden.

Beendigung

Der Veranstalter behält sich ausdrücklich vor, den Beitrag ohne vorherige Ankündigung und ohne Mitteilung von Gründen zu beenden. Dies gilt insbesondere für jegliche Gründe, die einen planmäßigen Ablauf des des Beitrages stören oder verhindern würden.

Datenschutz

Für die Teilnahme ist ein Twitter-Account notwendig.

Der Veranstalter weist darauf hin, dass sämtliche personenbezogenen Daten des Teilnehmers weder an Dritte weitergegeben noch diesen zur Nutzung überlassen werden.

Der Teilnehmer erklärt sich mit der Nennung seines Twitter-Profiles auf der Plattform Twitter einverstanden.

Sobald die personenbezogenen Daten des Teilnehmers nicht mehr benötigt werden, werden diese umgehend gelöscht.

Der Teilnehmer kann seine erklärte Einwilligung jederzeit widerrufen. Der Widerruf ist schriftlich an die im Impressumsbereich angegebenen Kontaktdaten des Betreibers zu richten. Nach Widerruf der Einwilligung werden die erhobenen und gespeicherten personenbezogenen Daten des Teilnehmers umgehend gelöscht.

Twitter Disclaimer

Diese Aktion steht in keiner Verbindung zu Twitter und wird in keiner Weise von Twitter gesponsert, unterstützt oder organisiert.

Anwendbares Recht

Fragen oder Beanstandungen sind an den Betreiber zu richten. Kontaktmöglichkeiten finden sich im Impressumsbereich des Betreibers.

Der Beitrag des Betreibers unterliegt ausschließlich dem Recht der Bundesrepublik Deutschland. Der Rechtsweg ist ausgeschlossen.

Salvatorische Klausel

Sollte eine Bestimmung dieser Teilnahmebedingungen ganz oder teilweise unwirksam sein oder werden, so wird dadurch die Gültigkeit dieser Teilnahmebedingungen im Übrigen nicht berührt. Statt der unwirksamen Bestimmung gilt diejenige gesetzlich zulässige Regelung, die dem in der unwirksamen Bestimmung zum Ausdruck gekommenen Sinn und Zweck wirtschaftlich am nächsten kommt. Entsprechendes gilt für den Fall des Vorliegens einer Regelungslücke in diesen Teilnahmebedingungen.

Dieser Artikel wurde ursprünglich von Philip Haas geschrieben.

Hintergrund

Mit PostgreSQL^® 13 lernt das VACUUM–Kommando eine neue Fähigkeit: Das parallele Aufräumen von Indexen. VACUUM in einer PostgreSQL^®-Datenbank ist im wesentlichen für mehrere wichtige Aufgaben verantwortlich:

• Garbage Collection, also freigeben von gelöschten Tupel für die Wiederverwendung
• Verhindern von Bloat in einer Datenbank und damit von ausuferndem Plattenplatzbedarfs
• Verhindern des Überlaufs des Transaktionszählers (FREEZE)
• Diverses Aufräumen von Transaktionsinformationen, usw.

Neben dem manuellen VACUUM Kommando gibt es noch den sogenannten Autovacuum Datenbankprozess, der sich automatisch im Hintergrund um diese Aufgaben kümmert. Im Groben entspricht die Arbeitsweise von Autovacuum auch dem Vorgehen bei Ausführung eines manuellem VACUUM-Kommandos. Diese gliedert sich im Wesentlichen in mehrere Phasen:

1. Scannen der Tabellenblöcke und Ermitteln von darin enthaltenen, toten Tupeln und, falls notwendig, FREEZE
2. Die toten Tupel werden im Speicher gesammelt (maintenance_work_mem ist hier die Obergrenze)
3. Löschen der Tupelreferenzen aus entsprechenden Indexen (Vacuum Index Phase)
4. Für jeden gescannten Tabellenblock, Löschen der darin enthaltenen toten Tupel
5. Für jeden gescannten Tabellenblock, Reorganisieren der lebenden Tupel
6. Aktualisieren der Visibility Map und Freespace Map
7. Ist maintenance_work_mem voll, bevor das Ende einer Tabelle erreicht wurde, zurück zu 2.)
8. Aufräumen (Cleanup Phase), u.a. Aktualisierung der Index-Statistiken für VACUUM
9. Löschen von leeren Tabellenblöcken am Ende der Tabelle (falls vorhanden)
10. Aktualisieren der Laufzeitstatistiken
11. Aufräumen von Transaktionsinformationen

Arbeitsteilung

Mit der neuen Kommandooption PARALLEL kann das VACUUM Kommando nun die Vacuum Index und Cleanup Phase mit mehreren Arbeitsprozessen (Workern) parallel durchführen, z.B.:

VACUUM (ANALYZE, PARALLEL 4) my_table;

Dies führt ein VACUUM mit bis zu vier parallelen Workern mit zusätzlicher Aktualisierung der Optimizer Statistiken durch. Das bisherige Verfahren bleibt für die Tabelle selbst unverändert, mit PARALLEL verarbeitet VACUUM nun mit jeweils einem dedizierten Worker einen spezifischen Index. Zu beachten ist, dass der Leader, also die Session die das VACUUM-Kommando ausführt, ebenfalls entsprechende Arbeit verrichtet. Wird „0“ als Anzahl der Worker spezifiziert, wird die parallele Verarbeitung der Indexe deaktiviert und der bisherige Arbeitsablauf verwendet. Sind weniger als vier Indexe auf der Tabelle vorhanden, so wird die Anzahl der Worker entsprechend angepasst. Auch kann es passieren, dass aufgrund nebenläufiger DDL Kommandos (anderes paralleles VACUUM oder parallelisiertes CREATE INDEX) weniger Worker ausgewählt werden, da die Anzahl der maximal verfügbaren Worker Prozesse entweder durch max_parallel_maintenance_workers oder max_worker_processes begrenzt wird.

Parallel VACUUM als Standardoption

PARALLEL ist in der aktuellen Beta2 von PostgreSQL^® 13 als Standardoption aktiviert. Wird die Option weggelassen, richtet sich die Zahl der Worker nach der Anzahl an Indexe pro Tabelle. Ob ein Index überhaupt für die parallele Verarbeitung vorgesehen wird, entscheidet auch der Konfigurationsparameter min_parallel_index_scan_size. Die Standardeinstellung ist 512kB.

Der Administrator sollte bei der Konfiguration von PostgreSQL^® darauf achten, entsprechenden Spielraum bei der Parametrisierung von max_worker_processes einzuplanen, da mit dem seit PostgreSQL^® 11 verfügbaren parallelisierten CREATE INDEX in PostgreSQL^® 13 nun ein neues parallelisierbares DDL-Kommando hinzukommt.

Verbesserung bei der Geschwindigkeit

Das parallele Verarbeiten der Indexe sollte VACUUM gerade bei größeren Mengen an gelöschten Tupel bei entsprechender Anzahl an Indexen deutlich beschleunigen. Um diesen theoretischen Vorteil in der Praxis zu zeigen, verwenden wir an dieser Stelle ein Sample Dataset der IMDb Datenbank. Die Tabelle cast_info hat folgendes Schema:

                                Table "public.cast_info"
     Column     │  Type   │ Collation │ Nullable │                Default                
────────────────┼─────────┼───────────┼──────────┼───────────────────────────────────────
 id             │ integer │           │ not null │ nextval('cast_info_id_seq'::regclass)
 person_id      │ integer │           │ not null │ 
 movie_id       │ integer │           │ not null │ 
 person_role_id │ integer │           │          │ 
 note           │ text    │           │          │ 
 nr_order       │ integer │           │          │ 
 role_id        │ integer │           │ not null │ 
Indexes:
    "cast_info_pkey" PRIMARY KEY, btree (id)
    "cast_info_idx_cid" btree (person_role_id)
    "cast_info_idx_mid" btree (movie_id)
    "cast_info_idx_pid" btree (person_id)
    "test_idx" btree (note)
Foreign-key constraints:
    "movie_id_exists" FOREIGN KEY (movie_id) REFERENCES title(id)
    "person_id_exists" FOREIGN KEY (person_id) REFERENCES name(id)
    "person_role_id_exists" FOREIGN KEY (person_role_id) REFERENCES char_name(id)
    "role_id_exists" FOREIGN KEY (role_id) REFERENCES role_type(id)

Die Tabelle ist ca. 2759 MByte groß und verfügt über insgesamt fünf Indexe, deren Größen sich wie folgt manifestieren:

SELECT indexrelid::regclass, pg_size_pretty(pg_relation_size(indexrelid)) 
FROM pg_index WHERE indrelid = 'cast_info'::regclass;
    indexrelid     │ pg_size_pretty 
───────────────────┼────────────────
 cast_info_pkey    │ 1108 MB
 cast_info_idx_cid │ 421 MB
 cast_info_idx_mid │ 396 MB
 cast_info_idx_pid │ 452 MB
 test_idx          │ 388 MB
(5 rows)

Damit Autovacuum den Test nicht stört, wird er für die Tabelle deaktiviert:

ALTER TABLE cast_info SET(autovacuum_enabled = 'false');

Folgender SQL aktualisiert in der Tabelle ca. 1.05 Mio Tupel in dem es rein fiktiv die Rollenzuordnung von bestimmten Datensätzen ändert. Die Ausgabe ist ein wenig gekürzt, um das Beispiel besser zu veranschaulichen:

UPDATE cast_info SET role_id = 12 WHERE role_id = 6;
UPDATE 1055730

Anschließend wird der Parameter max_parallel_maintenance_workers auf 4 gesetzt, um maximal vier parallele Worker für VACUUM zu ermöglichen.

SET max_parallel_maintenance_workers TO 4;
VACUUM cast_info ;
Time: 6872,329 ms (00:06,872)

Wiederholt man den UPDATE mit umgekehrter Bedingung und anschließendem nicht-parallelisiertem VACUUM ergibt sich folgendes Bild:

UPDATE cast_info SET role_id = 6 WHERE role_id = 12;
UPDATE 1055730

VACUUM (PARALLEL 0) cast_info ;
Time: 45435,458 ms (00:45,435)

Die parallele Verarbeitung von Indexen in diesem Testfall zeigt einen mehr als deutlichen Vorteil zugunsten des parallelisierten VACUUM. Die folgende Grafik veranschaulicht die Laufzeiten noch einmal:

Fazit

Mit PostgreSQL^® 13 lernt VACUUM Indexe parallel zu verarbeiten. Dies sorgt für einen deutlichen Geschwindigkeitsvorteil bei der Wartung von Tabellen. Ein Wermutstropfen aktuell ist, dass der automatische Hintergrundprozess Autovacuum noch nicht von diesem Feature profitieren kann, nur manuelles VACUUM unterstützt aktuell parallele Verarbeitung. Administratoren, die beispielsweise jedoch große Batchverarbeitungen mit Tabellen mit vielen Indexen direkt im Anschluß mit manuellem VACUUM optimieren, können von parallel VACUUM direkt profitieren.

Bei Fragen rund um den Einsatz von PostgreSQL^® 13 parallel VACUUM stehen wir Ihnen natürlich gerne zur Verfügung. Sprechen Sie uns an! Unser PostgreSQL^® Competence Center ist bei Bedarf 24 Stunden am Tag, an 365 Tagen im Jahr für Sie verfügbar.

Mit der kommenden Version 13 von PostgreSQL^® werden B-tree Indexe in der Lage sein, mehrfach identisch vorkommende Spaltenwerte physikalisch kompakter abzuspeichern. Bereits mit Version 12 wurde eine Optimierung eingeführt, die B-tree Indexe ein effizienteres Speichern von wiederholt vorkommenden Spaltenwerten gestattet. Hierzu werden diese Spalten in derselben Reihenfolge auch im Index abgelegt, wie diese auch physikalisch im sogenannten Heap, also der physischen Repräsentation der Tabelle, vorkommen. Dies vereinfacht die Verwaltung dieser Indexeinträge und kann auch bereits mit dieser Maßnahme das Wachstum eines Indexes positiv beeinflussen.

In Version 13 werden nun effektiv wiederholt vorkommende Spaltenwerte zusammengefasst im B-tree Index gespeichert, Deduplication genannt. Eine sogenannte Posting List verwaltet dabei, als alternative Repräsentation eines Index Tupel die physischen Positionen an welcher Stelle in der Tabelle der jeweilige Spaltenwert zu finden ist. Diese Repräsentation ist deutlich kompakter als die Spaltenwerte jeweils für sich zu speichern. Ferner ermöglicht es auch das schnellere Auffinden von entsprechenden Spaltenwerten und VACUUM kann solche Indexe auch effizienter warten.

In schreibenden Workloads, die wenige oder gar keine mehrfach identischen Spaltenwerte enthalten, können unter Umständen kleinere Auswirkungen auf die Performance beobachtet werden. Die Deduplication Funktionalität kann beim Erzeugen eines Indexes mit dem Indexattribut deduplication_items abgeschaltet werden. In der aktuellen Beta 2 von PostgreSQL^® 13 ist Deduplication standardmässig aktiviert, was sich jedoch bis zum finalen Release noch ändern kann. Die folgende Syntax schaltet das Feature explizit beim Anlegen eines Index aus:

CREATE INDEX ON table (spalte) WITH(deduplicate_items=off);

Üblicherweise jedoch speichern Anwendungen in der Regel Spaltenwerte pro Spalte häufiger mehrfach ab. Ausnahme sind UNIQUE Constraints oder Primary Keys, aber selbst bei diesen kann Deduplication Vorteile bieten. Kommen mehrere Versionen einer Zeile mit dem spezifischen eindeutigen Spaltenwert vor, kann Deduplication unnötigen Bloat im Index ebenfalls verhindern.

Beispiel von Deduplication

Das Ergebnis der kompakteren Indexstruktur lässt sich mit nachfolgendem Beispiel illustrieren. Die Query in diesem Beispiel erzeugt eine Datenmenge aus 10.000.000 numerischen Werten, die zufällig per random() auf 1000 unterschiedliche Werte begrenzt wird. Dadurch ist die jeweilige Größe der mehrfach vorkommenden Spaltenwerte ungleich verteilt.

INSERT INTO unique_test
SELECT floor(generate_series(1, 10000000) % (random() * 1000)::numeric);

Vergleicht man in PostgreSQL^® 11.8, 12.3 und 13beta2 nun die Indexgrößen, ergibt sich folgendes Bild:

® 11, 12 und 13″ width=“1024″ height=“576″ /> B-tree Index Größe mit Deduplication, Vergleich zwischen PostgreSQL^® 11, 12 und 13

Man sieht deutlich die Größenunterschiede, die sich im Vergleich von PostgreSQL^® 11 und 12 zu Version 13 ergeben. Deduplication reduziert die Indexgröße hier auf ca. 60 MByte, während bei PostgreSQL^® 11 und 12 ca. 220 MByte belegt werden. Die Grafik zeigt auch, dass die in der Einleitung beschriebene Optimierung in PostgreSQL^® 12 für diesen simulierten Fall nicht funktionieren. Allerdings werden diese im Laufe des Lebenszyklus des Index dann zum Tragen kommen.

Einschränkungen

Migrationen mit pg_upgrade

Ein wichtiger Punkt für die Nutzung von Deduplication in B-tree Indexen in PostgreSQL^® 13 ist bei Migrationen zu beachten. Verwendet man pg_upgrade, um eine bestehende PostgreSQL^® Instanz von beispielsweise PostgreSQL^® 12 auf 13 zu aktualisieren, werden alle physische Objekte in den neuen Cluster übernommen. Da Deduplication in PostgreSQL^® auf jeden Fall eine neue Indexstruktur verwendet, können bestehende Indexe aus dem alten Cluster daher nicht ohne weiteres die Deduplication Funktionalität verwenden und müssen neu angelegt werden. Dies kann beispielsweise mit dem PostgreSQL^® Kommando REINDEX oder dem äquivalenten Kommandozeilentool reindexdb erreicht werden. Dies gilt für Upgrades von allen älteren Major-Releases. Auch für Migrationen auf PostgreSQL^® 12 müssen B-tree Indexe, die von den dort angesprochenen Optimierungen profitieren möchten, neu angelegt werden. In PostgreSQL^® 13 hilft dabei auch die neue Option –jobs von reindexdb diesen Prozess zu beschleunigen, indem Indexe parallel mit der angebenen Anzahl an Prozessen aufgebaut werden können.

Verwendung bestimmer Datentypen

Der numerische Datentyp numeric kann nicht dedupliziert werden, da der Skalierungsfaktor eines jeden Spaltenwertes erhalten bleiben muss. B-tree Indexe auf Spalten dieses Typs werden daher automatisch nicht dedupliziert. Dasselbe gilt im Wesentlichen für den Datentyp jsonb. Da dieser intern numeric verwendet unterliegt jsonb denselben Einschränkungen. Bei dem Datentyp float4 gibt es unterschiedliche Repräsentationen des Wertes 0. Wie bei numeric mit dem entsprechenden scale factor muss bei float4 die jeweils unterschiedliche Repräsentation erhalten bleiben, was die Verwendung von Deduplizierung ausschließt. Spalten vom Typ text die eine Sortierreihenfolge verwenden die nicht-deterministisch ist können ebenfalls nicht dedupliziert werden.

Auch sogenannte Composite, also zusammengesetzte Datentypen, Arrays sowie Range Type verhindern die Nutzung der Funktion, allerdings hat dies keine formalen Gründe, sondern ist aktuell nicht implementiert. B-tree Indexe die mit der INCLUDE Direktive angelegt wurden, können ebenfalls aktuell keine Deduplizierung verwenden.

Bei Fragen rund um den Einsatz von PostgreSQL^® 13 stehen wir Ihnen natürlich gerne zur Verfügung. Sprechen Sie uns an! Unser PostgreSQL^® Competence Center ist bei Bedarf 24 Stunden am Tag, an 365 Tagen im Jahr für Sie verfügbar.

100% Open Source – 100% Kostenkontrolle

Die PostgreSQL^® Competence Center der credativ Gruppe bieten ab dem 01. August 2020 ein umfassendes Service- und Supportpaket an, das alle notwendigen Leistungen für den Betrieb von PostgreSQL^® in Enterprise-Umgebungen beinhaltet.

Dr. Michael Meskes, Geschäftsführer der credativ International GmbH, sagt dazu:

„Motiviert durch die Anforderungen vieler unserer Kunden, haben wir ein neues und umfassendes PostgreSQL^®-Service-Paket geschnürt, das allen Anforderungen an den Betrieb von PostgreSQL^® in einer Enterprise-Umgebung gerecht wird. Ganz im Gegensatz zu anderen PostgreSQL^®-Enterprise-Angeboten legen wir dabei aber großen Wert darauf, dass keine proprietären Elemente verwendet werden müssen. Trotzdem bieten wir mit unserem Servicepaket die notwendige Absicherung für den Betrieb in unternehmenskritischen Bereichen.

Dabei spielt es keine Rolle, wie viele Datenbanken innerhalb des Unternehmens betrieben werden. Dadurch bietet credativ eine hundertprozentige Kostenkontrolle bei einer gleichzeitig beliebigen Skalierung der gesamten Datenbankumgebung.“

Darüber hinaus stellt der Datenbankbetrieb in Enterprise-Umgebungen sehr hohe Anforderungen an den benötigten Service und Support.

Eine extrem leistungsfähige, hochskalierbare und grundsolide, relationale Datenbank ist sicherlich die Basis für einen sicheren und performanten Betrieb. Trotzdem gehört zu einer Enterprise-tauglichen Betriebsumgebung weit mehr als nur die reine Datenbank.

Für den Betrieb benötigt man ein ganzheitliches Lifecycle-Management: Minor- und Major-Upgrades, Migrationen, Security, Services, Patchmanagement und Long-Term-Support (LTS) sind dabei nur einige wichtige Faktoren.

Aber auch das Thema „Immer am Ball bleiben“ gehört dazu. Regelmäßige Schulungen und Trainings sind wichtig.

Services für die gesamte Betriebsumgebung

Genauso wichtig wie die eigentliche Datenbank, ist eine stabile und hochskalierbare Betriebsumgebung, die alle notwendigen Open-Source-Tools für PostgreSQL^® mitbringt und allen Anforderungen hinsichtlich Hochverfügbarkeit, Sicherheit, Performance, Datenbankmonitoring, der benötigten Backup-Strategie und einer zentralen Orchestrierung der gesamten Datenbank-Infrastruktur gerecht wird

Zu diesen Tools gehören auch die freien Versionen zahlreicher Open Source-Projekete, wie pgAdmin, pgBadger, pgBackrest, Patroni, aber auch die jeweilige Betriebssystemumgebung und beliebte Projekte wie Prometheus und Grafana, oder auch Cloud-Infrastrukturen auf der Basis von Kubernetes.

Ebenfalls unabdingbar, wie die fehlerfreie Funktion der Datenbank selbst, ist auch die reibungslose Interaktion mit allen anderen Komponenten, die typischerweise mit der Datenbank verbunden sind. Daher ist es wichtig, auch diese Komponenten miteinzubeziehen und zu berücksichtigen.

Nur wenn alle Komponenten, wie z.B. Betriebssystem, Loadbalancer, Webserver, Applicationserver, oder PostgreSQL^®-Clusterlösungen optimal zusammenarbeiten, kann die Datenbank Ihre optimale Leistung erzielen.

Abgesichert wird das durch einen kontinuierlichen 24×7-Enterprise-Support mit garantierten Service-Level-Agreements und allen notwendigen Services für die gesamte Datenbankumgebung. Dazu gehören auch alle weiteren Open-Source-Tools des umfassenden PostgreSQL^®-Ökosystems, die heute üblicherweise in jeder PostgreSQL^®-Enterprise-Umgebung verwendet werden.

All diese Anforderungen werden mit dem PostgreSQL^®-Enterprise Paket von credativ abgedeckt und sind im Leistungsumfang enthalten.

Der Service steht für Nordamerika und Europa zur Verfügung.

Über credativ:

Die credativ Group ist ein herstellerunabhängiges Beratungs- und Dienstleistungsunternehmen mit Standorten in Deutschland, USA und Indien.

Bereits seit 1999 fokussiert sich credativ vollständig auf die Planung und Realisierung professioneller Businesslösungen unter Verwendung von Open Source Software. Seit Mai 2006 betreibt credativ das Open Source Support Center (OSSC) und bietet damit einen professionellen 24×7 Enterprise Support für zahlreiche Open Source Projekte.

Darüber hinaus leisten die PostgreSQL^® Competence Center der credativ mit ihren dedizierten Datenbank-Teams einen umfassenden Service für das objektrelationale Datenbanksystem PostgreSQL^®.

Das apt.postgresql.org-Repository war ursprünglich mit den beiden Architekturen amd64 und i386 (64- und 32-bit x64) gestartet. Im September 2016 kam dann ppc64el (POWER) hinzu. Über die Zeit gab es immer wieder einzelne Anfragen, ob wir vielleicht auch „arm“ unterstützen würden, womit meistens Raspberry Pi gemeint war. Die sind aber meistens nur 32-bit, und der weit verbreitete „armhf“-Raspbian-Port ist leider nur ARM6, eine ältere Hardware-Version.

Durch HUAWEI Cloud Services wurde der PostgreSQL^®-Community jetzt eine „arm64“-Buildmaschine zur Verfügung gestellt, was eine moderne Prozessorarchitektur ist, die auch für PostgreSQL^®-Server geeignet ist. Die Maschine wurde dann von uns eingerichtet und das apt.postgresql.org-Repository um diese Architektur erweitert. Bei den unterstützten Distributionen haben wir uns für Debian buster (stable), bullseye (testing) und sid (unstable) sowie Ubuntu bionic (18.04) und focal (20.04) entschieden.

Die Build-Maschine ist sehr performant. Alle Pakete wurden von uns in wenigen Tagen für die neue Architektur gebaut. Spezielle arm-spezifischen Probleme sind dabei nur sehr wenige aufgetreten, was für die Stabilität der Linux-Portierung auf dieser Architektur spricht.

Einem Einsatz von PostgreSQL^® auf arm64, auf Debian oder Ubuntu steht damit nichts mehr im Weg.

Parallel haben wir das Repository um den Support für die neue Ubuntu-LTS-Version erweitert: focal (20.04).
Diese Distribution kann damit ab sofort benutzt werden, mit Unterstützung für fünf Jahre bis April 2025.

Über Fragen zum Einsatz von PostgreSQL^® auf arm und anderen Architekturen auf Debian, Ubuntu und anderen Betriebssystem freut sich das credativ PostgreSQL^® Competence Center natürlich jederzeit. Sprechen Sie uns an!

Dieser Artikel wurde ursprünglich von Christoph Berg geschrieben.

PostgreSQL^® ist eine äußerst robuste Datenbank, der auch die meisten unserer Kunden ihre Daten anvertrauen. Kommt es jedoch trotzdem einmal zu Fehlern, so liegen diese meistens am Storage-System, in dem einzelne Bits oder Bytes kippen, oder ganze Blöcke verfälscht werden. Wir zeigen, wie man die Daten aus korrupten Tabellen retten kann.

Der Benutzer ist im Fehlerfall mit Meldungen konfrontiert, die aus dem Storagelayer oder anderen PostgreSQL^®-Subsystemen kommen:

postgres=# select * from t;
ERROR:  missing chunk number 0 for toast value 192436 in pg_toast_192430

postgres=# select * from a;
ERROR:  invalid memory alloc request size 18446744073709551613

Wenn nur einzelne Tupel defekt sind, kann man sich teilweise behelfen, indem man diese einzeln ausliest, z.B. nach id, was in vielen Fällen jedoch auch nicht weiter hilft:

select * from t where id = 1;

Erfolgversprechender ist, die Tupel direkt mit ihrer internen Tupel-ID, in PostgreSQL^® ctid genannt, anzusprechen:

select * from t where ctid = '(0,1)';

Um nun alle Tupel auszulesen, nutzen wir eine Schleife in plpgsql:

for page in 0 .. pages-1 loop
  for item in 1 .. ??? loop
     select * from t where ctid = '('||page||','||item||')' into r;
     return next r;
  end loop;
end loop;

Wir brauchen noch die Zahl der Seiten in der Tabelle, die bekommen wir von pg_relation_size(), und die Zahl der Tupel auf der Seite, wofür wir die Extension pageinspect bemühen.

select pg_relation_size(relname) / current_setting('block_size')::int into pages;

for page in 0 .. pages-1 loop
  for item in select t_ctid from heap_page_items(get_raw_page(relname::text, page)) loop
    SELECT * FROM t WHERE ctid=item into r;
    if r is not null then
      return next r;
    end if;
  end loop;
end loop;

Jetzt kommt der wichtigste Teil: Der Zugriff auf die beschädigten Tupel oder Seiten verursacht Fehler, die wir mit einem begin..exception..end-Block abfangen müssen. Die Fehlermeldungen geben wir als NOTICE an den Benutzer weiter. Außerdem soll die Funktion nicht nur für eine Tabelle funktionieren, sondern einen Parameter relname erhalten. Die gesamte plpgsql-Funktion sieht dann so aus:

create extension pageinspect;

create or replace function read_table(relname regclass)
returns setof record
as $$
declare
  pages int;
  page int;
  ctid tid;
  r record;
  sql_state text;
  error text;
begin
  select pg_relation_size(relname) / current_setting('block_size')::int into pages;

  for page in 0 .. pages-1 loop

    begin

      for ctid in select t_ctid from heap_page_items(get_raw_page(relname::text, page)) loop
        begin
          execute format('SELECT * FROM %s WHERE ctid=%L', relname, ctid) into r;
          if r is not null then
            return next r;
          end if;
        exception -- bad tuple
          when others then
            get stacked diagnostics sql_state := RETURNED_SQLSTATE;
            get stacked diagnostics error := MESSAGE_TEXT;
            raise notice 'Skipping ctid %: %: %', ctid, sql_state, error;
        end;
      end loop;

    exception -- bad page
      when others then
        get stacked diagnostics sql_state := RETURNED_SQLSTATE;
        get stacked diagnostics error := MESSAGE_TEXT;
        raise notice 'Skipping page %: %: %', page, sql_state, error;
    end;

  end loop;
end;
$$ language plpgsql;

Da die Funktion „record“ zurück gibt, muss beim Aufruf die Tabellensignatur mitgegeben werden:

postgres =# select * from read_table('t') as t(t text);
NOTICE:  Skipping ctid (0,1): XX000: missing chunk number 0 for toast value 192436 in pg_toast_192430
       t
───────────────
 one
 two
 three
...

Eine alternative Variante schreibt die gelesenen Daten direkt in eine neue Tabelle:

postgres =# select rescue_table('t');
NOTICE:  t: page 0 of 1
NOTICE:  Skipping ctid (0,1): XX000: missing chunk number 0 for toast value 192436 in pg_toast_192430
                                    rescue_table
─────────────────────────────────────────────────────────────────────────────────────
 rescue_table t into t_rescue: 0 of 1 pages are bad, 1 bad tuples, 100 tuples copied
(1 row)

Die Tabelle t_rescue wurde automatisch angelegt.

create extension pageinspect;

create or replace function rescue_table(relname regclass, savename name default null, "create" boolean default true)
returns text
as $$
declare
  pages int;
  page int;
  ctid tid;
  row_count bigint;
  good_tuples bigint := 0;
  bad_pages bigint := 0;
  bad_tuples bigint := 0;
  sql_state text;
  error text;
begin
  if savename is null then
    savename := relname || '_rescue';
  end if;
  if rescue_table.create then
    execute format('CREATE TABLE %s (LIKE %s)', savename, relname);
  end if;

  select pg_relation_size(relname) / current_setting('block_size')::int into pages;

  for page in 0 .. pages-1 loop
    if page % 10000 = 0 then
      raise notice '%: page % of %', relname, page, pages;
    end if;

    begin

      for ctid in select t_ctid from heap_page_items(get_raw_page(relname::text, page)) loop
        begin
          execute format('INSERT INTO %s SELECT * FROM %s WHERE ctid=%L', savename, relname, ctid);
          get diagnostics row_count = ROW_COUNT;
          good_tuples := good_tuples + row_count;
        exception -- bad tuple
          when others then
            get stacked diagnostics sql_state := RETURNED_SQLSTATE;
            get stacked diagnostics error := MESSAGE_TEXT;
            raise notice 'Skipping ctid %: %: %', ctid, sql_state, error;
            bad_tuples := bad_tuples + 1;
        end;
      end loop;

    exception -- bad page
      when others then
        get stacked diagnostics sql_state := RETURNED_SQLSTATE;
        get stacked diagnostics error := MESSAGE_TEXT;
        raise notice 'Skipping page %: %: %', page, sql_state, error;
        bad_pages := bad_pages + 1;
    end;

  end loop;

  error := format('rescue_table %s into %s: %s of %s pages are bad, %s bad tuples, %s tuples copied',
    relname, savename, bad_pages, pages, bad_tuples, good_tuples);
  raise log '%', error;
  return error;
end;
$$ language plpgsql;

Die SQL-Skripte sind auch im git-Repository von pg_dirtyread verfügbar.

Unterstützung

Falls Sie Unterstützung bei der Rettung Ihrer Daten oder dem allgemeinen Einsatz von PostgreSQL^® benötigen, steht Ihnen unser PostgreSQL^® Competence Center zur Verfügung – Falls gewünscht auch 24 Stunden am Tag, an 365 Tagen im Jahr.

Wir freuen uns auf Ihre Kontaktaufnahme.

Dieser Artikel wurde ursprünglich von Christoph Berg geschrieben.

Der Open Source Summit ist die weltweit größte, allumfassende Open Source Konferenz. Hier werden unter anderem Themen wie aktuelle Infrastruktur-Software, Entwicklungen am Linux-Kernel, aber auch die Arbeit in der Community besprochen. Ein bisher fehlender Bestandteil des Konferenzprogrammes waren Open Source Datenbanken.

Dabei bildet Datenbanksoftware wie PostgreSQL^® oder Apache Cassandra eine der wichtigsten Säulen in modernen Open Source Infrastrukturen.

Zusammen mit der Linux Foundation freuen wir uns, ankündigen zu dürfen, dass der diesjährige Open Source Summit North America und Europe jeweils einen eigenen Datenbank-Track haben wird.

Dabei bildet unser Geschäftsführer Dr. Michael Meskes zusammen mit Sunil Kamath (Microsoft) und Divya Bhargov (Pivotal) das Programmkomitee für den neuen Track des Open Source Summit.

In seinem Blog-Beitrag bei der Linux Foundation sagte Dr. Michael Meskes hierzu:

„The open source database track will feature topics specific to databases themselves and their integration to the computing backbone for applications.  The track will focus on databases of all kinds, as long as they are open source, and any deployment and integration topics.“

Der vollständige Blogbeitrag kann auf der Seite der Linux Foundation gelesen werden.

Die Linux Foundation und das Programmkomitee freuen sich über alle Einreichungen zum neuen Datenbank-Track. Vorträge können noch bis zum 16. Februar (North America) und 14. Juni (Europe) eingereicht werden.

Der Open Source Summit North America findet in diesem Jahr in Austin, Texas statt. Der Austragungsort des Summit in Europa befindet sich 2020 in Dublin, Irland. Beide Veranstaltungen unterstützt credativ auch in diesem Jahr mit einem Sponsoring.

Dieser Artikel wurde ursprünglich von Philip Haas geschrieben.

Patroni ist eine Cluster-Lösung für PostgreSQL^®, die sich gerade im Cloud- und Kubernetes-Umfeld auf Grund seiner Integration mit z.B. Etcd immer größerer Beliebtheit erfreut. Vor einiger Zeit haben wir über die Integration von Patroni in Debian berichtet. Seit kurzem ist auch das eng mit Patroni verzahnte vip-manager Projekt in Debian verfügbar, welches im Folgenden vorgestellt wird.

Patroni verwendet für Leader-Election und Failover einen sogenannten „Distributed Consensus Store“ (DCS). Der momentane Cluster-Leader aktualisiert laufend seinen Leader-Key im DCS. Sobald dieser von Patroni nicht mehr aktualisiert werden kann und obsolet wird, erfolgt eine neue Leader-Election unter den verbleibenden Knoten.

Client-Lösungen für Hochverfügbarkeit

Allerdings muss auch aus Anwendersicht gewährleistet sein, dass die Anwendung mit dem Leader verbunden ist, da sonst keine schreibenden Transaktionen möglich sind. Herkömmliche Hochverfügbarkeits-Lösungen wie Pacemaker verwenden hier virtuelle IPs (VIPs), die im Failover-Fall zum neuen Primary-Knoten geschwenkt werden.

Für Patroni gab es diesen Mechanismus bisher nicht. Üblicherweise wird entweder HAProxy (oder eine ähnliche Lösung) verwendet, welche einen periodischen Health-Check auf die Patroni-API der einzelnen Knoten durchführt und so den aktuellen Leader ermittelt und Client-Anfragen dorthin weiterleitet.

Eine Alternative ist die Verwendung von Client-seitigem Failover, welches seit PostgreSQL^® 10 verfügbar ist. Hierbei werden alle Mitglieder des Clusters beim Client konfiguriert. Dieser versucht nach einem Verbindungs-Abbruch reihum die anderen Knoten zu erreichen, bis er erneut einen Primary findet.

vip-manager

Ein komfortabler neuer Ansatz ist vip-manager. Dieser in Go geschriebene Service wird auf allen Cluster-Knoten gestartet und verbindet sich mit dem DCS.
Falls der lokale Knoten den Leader-Key besitzt, startet vip-manager die konfigurierte VIP. Im Fall eines Failovers entfernt vip-manager die VIP vom alten Leader und der entsprechende Service des neuen Leaders startet diese dort. Der Client kann nun für diese VIP konfiguriert werden und wird sich stets mit dem Cluster-Leader verbinden.

Debian-Integration von vip-manager

Für Debian wurde das pg_createconfig_patroni Programm angepasst, so dass es nun auch eine vip-manager Konfiguration erstellen kann:

pg_createconfig_patroni 11 test --vip=10.0.3.2

Den Service starten wir analog zu Patroni für jede Instanz:

systemctl start vip-manager@11-test

+---------+--------+------------+--------+---------+----+-----------+
| Cluster | Member |    Host    |  Role  |  State  | TL | Lag in MB |
+---------+--------+------------+--------+---------+----+-----------+
| 11-test |  pg1   | 10.0.3.247 | Leader | running |  1 |           |
| 11-test |  pg2   | 10.0.3.94  |        | running |  1 |         0 |
| 11-test |  pg3   | 10.0.3.214 |        | running |  1 |         0 |
+---------+--------+------------+--------+---------+----+-----------+

Im Journal von pg1 kann man sehen, dass die VIP konfiguriert wurde:

Dez 09 14:53:38 pg1 vip-manager[9314]: 2019/12/09 14:53:38 IP address 10.0.3.2/24 state is false, desired true
Dez 09 14:53:38 pg1 vip-manager[9314]: 2019/12/09 14:53:38 Configuring address 10.0.3.2/24 on eth0
Dez 09 14:53:38 pg1 vip-manager[9314]: 2019/12/09 14:53:38 IP address 10.0.3.2/24 state is true, desired true

Im Fall von LXC-Containern sieht man dies auch im Output von lxc-ls -f:

NAME    STATE   AUTOSTART GROUPS IPV4                 IPV6 UNPRIVILEGED 
pg1     RUNNING 0         -      10.0.3.2, 10.0.3.247 -    false
pg2     RUNNING 0         -      10.0.3.94            -    false
pg3     RUNNING 0         -      10.0.3.214           -    false

Die vip-manager Pakete sind für Debian testing (bullseye) und unstable in den offiziellen Debian-Repositories erhältlich.
Für Debian stable (buster), sowie Ubuntu 19.04 und 19.10 sind Pakete auf dem von credativ gepflegten apt.postgresql.org verfügbar, zusammen mit den aktualisierten Patroni-Paketen mit vip-manager Integration.

Switchover-Verhalten

Im Falle eines geplanten Switchovers wird z.B. pg2 zum neuen Leader:

# patronictl -c /etc/patroni/11-test.yml switchover --master pg1 --candidate pg2 --force
Current cluster topology
+---------+--------+------------+--------+---------+----+-----------+
| Cluster | Member |    Host    |  Role  |  State  | TL | Lag in MB |
+---------+--------+------------+--------+---------+----+-----------+
| 11-test |  pg1   | 10.0.3.247 | Leader | running |  1 |           |
| 11-test |  pg2   | 10.0.3.94  |        | running |  1 |         0 |
| 11-test |  pg3   | 10.0.3.214 |        | running |  1 |         0 |
+---------+--------+------------+--------+---------+----+-----------+
2019-12-09 15:35:32.52642 Successfully switched over to "pg2"
+---------+--------+------------+--------+---------+----+-----------+
| Cluster | Member |    Host    |  Role  |  State  | TL | Lag in MB |
+---------+--------+------------+--------+---------+----+-----------+
| 11-test |  pg1   | 10.0.3.247 |        | stopped |    |   unknown |
| 11-test |  pg2   | 10.0.3.94  | Leader | running |  1 |           |
| 11-test |  pg3   | 10.0.3.214 |        | running |  1 |         0 |
+---------+--------+------------+--------+---------+----+-----------+

Die VIP wurde nun auf den neuen Leader geschwenkt:

NAME    STATE   AUTOSTART GROUPS IPV4                 IPV6 UNPRIVILEGED 
pg1     RUNNING 0         -      10.0.3.247          -    false
pg2     RUNNING 0         -      10.0.3.2, 10.0.3.94 -    false
pg3     RUNNING 0         -      10.0.3.214          -    false

Dies ist auch in den Journals zu sehen, hier vom alten Leader:

Dez 09 15:35:31 pg1 patroni[9222]: 2019-12-09 15:35:31,634 INFO: manual failover: demoting myself
Dez 09 15:35:31 pg1 patroni[9222]: 2019-12-09 15:35:31,854 INFO: Leader key released
Dez 09 15:35:32 pg1 vip-manager[9314]: 2019/12/09 15:35:32 IP address 10.0.3.2/24 state is true, desired false
Dez 09 15:35:32 pg1 vip-manager[9314]: 2019/12/09 15:35:32 Removing address 10.0.3.2/24 on eth0
Dez 09 15:35:32 pg1 vip-manager[9314]: 2019/12/09 15:35:32 IP address 10.0.3.2/24 state is false, desired false

Sowie vom neuen Leader pg2:

Dez 09 15:35:31 pg2 patroni[9229]: 2019-12-09 15:35:31,881 INFO: promoted self to leader by acquiring session lock
Dez 09 15:35:31 pg2 vip-manager[9292]: 2019/12/09 15:35:31 IP address 10.0.3.2/24 state is false, desired true
Dez 09 15:35:31 pg2 vip-manager[9292]: 2019/12/09 15:35:31 Configuring address 10.0.3.2/24 on eth0
Dez 09 15:35:31 pg2 vip-manager[9292]: 2019/12/09 15:35:31 IP address 10.0.3.2/24 state is true, desired true
Dez 09 15:35:32 pg2 patroni[9229]: 2019-12-09 15:35:32,923 INFO: Lock owner: pg2; I am pg2

Wie man sehen kann erfolgt der Schwenk der VIP innerhalb einer Sekunde.

Aktualisiertes Ansible Playbook

Unser Ansible-Playbook für die automatisierte Einrichtung eines Drei-Knoten-Clusters unter Debian wurde ebenfalls aktualisiert und kann nun bei Bedarf eine VIP konfigurieren:

# ansible-playbook -i inventory -e vip=10.0.3.2 patroni.yml

Unterstützung

Falls Sie Unterstützung beim Einsatz von PostgreSQL^®, Patroni, vip-manager oder anderer Software für Hochverfügbarkeit benötigen, steht Ihnen unser Open Source Support Center zur Verfügung – Falls gewünscht auch 24 Stunden am Tag, an 365 Tagen im Jahr.

Wir freuen uns auf Ihre Kontaktaufnahme.