pg_dirtyread

Vor Kurzem habe ich pg_dirtyread aktualisiert, damit es mit PostgreSQL 14 funktioniert. pg_dirtyread ist eine PostgreSQL-Erweiterung, die es erlaubt, „tote“ Zeilen aus Tabellen zu lesen, d.h. Zeilen, die bereits gelöscht oder aktualisiert wurden. Das funktioniert natürlich nur, wenn die Tabelle noch nicht durch einen VACUUM-Befehl oder autovacuum, der Müllentsorgung von PostgreSQL, bereinigt wurde.

Hier ist ein Beispiel für pg_dirtyread in Aktion:

# create table foo (id int, t text);
CREATE TABLE
# insert into foo values (1, 'Doc1');
INSERT 0 1
# insert into foo values (2, 'Doc2');
INSERT 0 1
# insert into foo values (3, 'Doc3');
INSERT 0 1

# select * from foo;
 id │ t
────┼──────
  1 │ Doc1
  2 │ Doc2
  3 │ Doc3
(3 rows)

# delete from foo where id < 3;
DELETE 2

# select * from foo;
 id │ t
────┼──────
  3 │ Doc3
(1 row)

Ups! Die ersten beiden Dokumente sind verschwunden.

Lassen Sie uns nun pg_dirtyread verwenden, um die Tabelle zu betrachten:

# create extension pg_dirtyread;
CREATE EXTENSION

# select * from pg_dirtyread('foo') t(id int, t text);
 id │ t
────┼──────
  1 │ Doc1
  2 │ Doc2
  3 │ Doc3

Alle drei Dokumente sind noch vorhanden, aber nur eines von ihnen ist sichtbar.

pg_dirtyread kann auch die Systemspalten von PostgreSQL mit den Informationen über die Position und Sichtbarkeit der Zeilen anzeigen. Für die ersten beiden Dokumente ist xmax gesetzt, was bedeutet, dass die Zeile gelöscht wurde:

# select * from pg_dirtyread('foo') t(ctid tid, xmin xid, xmax xid, id int, t text);

 ctid │ xmin │ xmax │ id │ t
───────┼──────┼──────┼────┼──────
 (0,1) │ 1577 │ 1580 │ 1 │ Doc1
 (0,2) │ 1578 │ 1580 │ 2 │ Doc2
 (0,3) │ 1579 │ 0 │ 3 │ Doc3
(3 rows)

Undelete

Vorbehalt: Ich kann nicht versprechen, dass eine der unten aufgeführten Ideen in der Praxis funktionieren wird. Es gibt einige Unsicherheiten und es könnte eine gute Portion kompliziertes Wissen über die PostgreSQL-Interna erforderlich sein, um Erfolg zu haben. Ich würde Sie bitten es in Erwägung zu ziehen, Ihren bevorzugten PostgreSQL-Dienstleister um Rat zu fragen, bevor Sie versuchen Daten auf einem Produktionssystem wiederherzustellen. Do not try this at work!

Ich hatte immer Pläne, pg_dirtyread um einen „Undelete“-Befehl zu erweitern, um gelöschte Zeilen wieder auftauchen zu lassen, bin aber in der Vergangenheit leider nie dazu gekommen das auszuprobieren. Jedoch können Zeilen bereits wiederhergestellt werden, indem man die Ausgabe von pg_dirtyread selbst verwendet:

# insert into foo select * from pg_dirtyread('foo') t(id int, t text) where id = 1;

Dies ist allerdings kein echtes “Undelete” bzw. „Rückgängigmachen“ – es werden lediglich neue Zeilen aus den aus der Tabelle gelesenen Daten eingefügt.

pg_surgery

Begrüßen wir pg_surgery, eine neue PostgreSQL-Erweiterung, die mit PostgreSQL 14 geliefert wird. Sie enthält zwei Funktionen zur „OP einer kaputten Beziehung“. Als Nebeneffekt können sie auch gelöschte Tupel wieder auftauchen lassen.

Wie ich jetzt entdeckt habe, funktioniert eine der Funktionen, heap_force_freeze(), gut mit pg_dirtyread. Sie nimmt eine Liste von ctids (Zeilenpositionen), die sie als „eingefroren“, aber gleichzeitig auch als „nicht gelöscht“ markiert.

Wenden wir es auf unsere Testtabelle an, indem wir die ctids verwenden, die pg_dirtyread lesen kann:

# create extension pg_surgery;
CREATE EXTENSION

# select heap_force_freeze('foo', array_agg(ctid))
    from pg_dirtyread('foo') t(ctid tid, xmin xid, xmax xid, id int, t text) where id = 1;
 heap_force_freeze
───────────────────

(1 row)

Et voilà, unser gelöschtes Dokument ist wieder da:

# select * from foo;
 id │ t
────┼──────
  1 │ Doc1
  3 │ Doc3
(2 rows)

#select * from pg_dirtyread('foo') t(ctid tid, xmin xid, xmax xid, id int, t text);
 ctid │ xmin │ xmax │ id │ t
───────┼──────┼──────┼────┼──────
 (0,1) │ 2 │ 0 │ 1 │ Doc1
 (0,2) │ 1578 │ 1580 │ 2 │ Doc2
 (0,3) │ 1579 │ 0 │ 3 │ Doc3
(3 rows)

Haftungsausschluss

Am wichtigsten ist, dass keine der oben genannten Methoden funktioniert, wenn die Daten, die Sie gerade gelöscht haben, bereits durch VACUUM oder Autovacuum bereinigt wurden. Dadurch wird der wiedergewonnene Speicherplatz aktiv gelöscht. Stellen Sie Ihre Daten aus dem Backup wieder her.

Da sowohl pg_dirtyread als auch pg_surgery außerhalb der normalen PostgreSQL-MVCC-Maschinerie operieren, ist es leicht, damit korrupte Daten zu erzeugen. Dazu gehören doppelte Zeilen, doppelte Primärschlüsselwerte, Indizes, die nicht mit den Tabellen synchronisiert sind, defekte Fremdschlüsselbegrenzungen und andere. Sie sind gewarnt worden.

Weiterhin funktioniert pg_dirtyread (noch) nicht, wenn die gelöschten Zeilen irgendwelche getoasteten Werte enthalten. Mögliche andere Ansätze sind die Verwendung von pageinspect und pg_filedump, um die ctids der gelöschten Zeilen zu erhalten. Bitte stellen Sie sicher, dass Sie funktionierende Backups haben und keine der oben genannten Methoden benötigen.

Wir unterstützen Sie gerne

Die Notwendigkeit der oben genannten Operationen kann in den meisten Fällen durch eine professionelle Administration oder Einrichtung der PostgreSQL-Infrastruktur durch einen kompetenten Dienstleister komplett verhindert werden. Sollten Sie gerade vor einem dieser Probleme stehen und auf der Suche nach Auswegen aus einer Mislage befinden, melden Sie sich doch kurz bei uns per Email an info@credativ.de oder über das Kontaktformular.

Mit über 22+ Jahren an Entwicklungs- und Dienstleistungserfahrung im PostgreSQL- und 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.

Über credativ

Die credativ GmbH ist ein herstellerunabhängiges Beratungs- und Dienstleistungs- unternehmen mit Standort in Mönchengladbach. Seit dem erfolgreichen Merger mit Instaclustr 2021 ist die credativ GmbH das europäische Hauptquartier der Instaclustr Gruppe.

Die Instaclustr Gruppe 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™, und PostgreSQL®.

Instaclustr kombiniert eine komplette Dateninfrastruktur-Umgebung mit praktischer Expertise, Support und Consulting um eine kontinuierliche Leistung und Optimierung zu gewährleisten. Durch Beseitigung der Konplexitä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.