tl;dr
Wenn man bei PostgreSQL einzelne gelöschte Datenbanken, deren Nutzer sowie alle damit zusammenhängenden Berechtigungen wiederherstellen möchte, reichen pg_dump und pg_dumpall --globals-only nicht aus. Zusätzlich muss man kreativ im vollständigen Dump von pg_dumpall rumgreppen.

Wie ich schon in mehreren Blogartikeln schrub, ist die Betätigung im Umfeld von PostgreSQL lehrreich und bisweilen überraschend. Das Folgende wird ein etwas längeres Posting, da der Kontext des beschriebenen Phänomens/Problems etwas umfangreicher ist.

Daneben sollte ich für einige kritische Leser evtl. noch vorher erwähnen, dass ich den ganzen Kram hier auch schreibe, damit ich mich später erinnere, warum ich es so gemacht habe…

Hintergrund

Wie sicherlich die meisten meiner Leser wissen, betreut meine Wenigkeit hauptberuflich (und das natürlich nur neben unzähligen anderen Dingen 😉 ) die zentralen PostgreSQL-Datenbankserver der Humboldt-Universität, auf denen Wissenschaftler, Projektmitarbeiter, usw. ihre Datenbanken ablegen können.

Auf PostgreSQL-Ebene wird das Ganze so umgesetzt, dass jeder Nutzer nach Beantragung ein oder mehrere Datenbanken (in den folgenden Beispielen “datenbank” genannt) mit je einem eigenen Datenbankaccount (im Folgenden “nutzer”) erhält. Zusätzlich kann es je nach Bedarf weitere Datenbankaccounts (im Folgenden “unternutzer”) mit optional eingeschränkten Zugriffsrechten geben.

Standardrechte in PostgreSQL

Bereits beim Anlegen der Datenbanken muss man tierisch aufpassen, da das Standardverhalten von PostgreSQL offensichtlich für andere Einsatzszenarien vorgesehen ist: auf eine neu angelegte Datenbank hat automatisch jeder, dem Server bekannte Nutzer Zugriff!

Um dies zu verhindern, lautet die erste Anweisung, die bei uns automatisch bei jeder neuen Datenbank ausgeführt wird:

REVOKE ALL ON DATABASE datenbank FROM PUBLIC;


Damit werden der magischen Rolle PUBLIC alle Rechte entzogen. Dass der Nutzer dennoch an seine Datenbank kommt, liegt daran, dass sein Account als Owner der Datenbank weiterhin alle Rechte besitzt.

Da dies jedoch nicht für die weiteren Accounts des Nutzers gilt, müssen für diese die Zugriffsberechtigungen (bei uns ebenfalls automatisch) nachgetragen werden:

GRANT CONNECT ON DATABASE datenbank TO unternutzer;


Die zweite Falle lauert eine Ebene tiefer: Auf das Standardschema namens public (nicht zu verwechseln mit der bereits genannten, gleichnamigen Rolle) darf jeder mit der Datenbank verbundene Nutzer (egal welchen Einschränkungen er sonst unterliegt) per default eigene Objekte (also Tabellen, Views, usw.) anlegen.

Also folgen bei uns den bereits genannten Befehlen beim Anlegen einer Datenbank noch:

\c datenbank;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO nutzer;


…sowie für jeden einzelnen Unteraccount:

\c datenbank;
GRANT USAGE ON SCHEMA public TO unternutzer;


Soweit so gut – kommen wir zum Backup:

Logisches Backup/Restore im Allgemeinen

Bei PostgreSQL hat man, wenn man nur logische Backups betrachtet, die Wahl zwischen 2,5 Werkzeugen: pg_dump, pg_dumpall und pg_dumpall mit dem Schalter -g.

Ohne zu tief in die Details einzusteigen (die ich teilweise schon an anderer Stelle beschrieben habe), leisten die Werkzeuge folgendes:

• pg_dump zieht eine einzelne Datenbank ab
• pg_dumpall zieht den gesamten Cluster ab (Datenbanken und sonstige Objekte, z.B. Rollen)
• pg_dumpall -g zieht nur die sonstigen Objekte (globals) ab

Wir lassen täglich alle genannten Werkzeuge laufen: je einmal pg_dumpall und pg_dumpall -g auf jedem unserer Cluster und dann noch einmal pg_dump für jede einzelne Datenbank.

Interessant ist nun die Frage, welches Backup wir zur Wiederherstellung verwenden und die wenig überraschende Antwort lautet: Kommt drauf an…

Wenn man den gesamten Cluster wiederherstellen muss, ist die Antwort leicht: man legt einen neuen Cluster an und spielt den pg_dumpall-Dump ein (kam bisher erfreulicherweise nur geplant bei großen Datenbankupdates vor).

Hat ein Nutzer nur eine Tabelle gelöscht, kann man mit dem pg_dump-Dump sehr selektiv nur diese wiederherstellen (kommt sehr selten vor).

Der mit Abstand häufigste Fall ist jedoch das komplette Wiederherstellen einer einzelnen Datenbank, gerne nachdem wir(!) sie gelöscht haben – nein, nur weil ein Nutzer sagt, dass er eine Datenbank nicht mehr braucht, heißt nicht, dass er sie nicht mehr braucht – und bisweilen merkt er das erst deutlich später. In diesem Fall existiert nicht nur die Datenbank nicht mehr; auch die Datenbankaccounts haben durch uns bereits das Zeitliche gesegnet.

Aus diesem Grund hilft dann auch der reine pg_dump-Dump nicht mehr weiter, da dieser die Existenz aller Datenbankaccounts voraussetzt.

Logisches Backup/Restore einzelner Datenbanken inkl. Nutzer

Naiver Ansatz: man greppt aus dem pg_dumpall -g (geht deutlich schneller als den pg_dumpall-Dump zu nehmen) alle CREATE- und ALTER-ROLE-Statements raus und führt diese vor dem Einspielen des Dumps aus. Dieser Ansatz hat aber leider zwei Nachteile: erstens muss man alle betroffenen Datenbankaccounts kennen und, viel schlimmer, einige Berechtigungen werden nicht wiederhergestellt. pg_dump speichert nämlich nur die Informationen inklusive Berechtigungen die sich innerhalb einer Datenbank befinden. Das CREATE-DATABASE-Statement legt pg_dump nur auf besonderen Wunsch und dann aber auch nur mit dem ganz oben beschriebenen Standardverhalten (alle haben Zugriff) an.

Der pg_dumpall-Dump enthält alle notwendigen Informationen; leider kann man aus diesem aber keine einzelnen Datenbanken wiederherstellen (es sei denn, man editiert von Hand in dem riesigen Dumpfile, bei uns im GB-Bereich, rum).

Als Lösung verwenden wir folgenden Ansatz: wir greppen im pg_dumpall-Dump nach allen GRANTS auf die wiederherzustellende Datenbank und greppen die CREATE-ROLE-Anweisungen der dabei gefundenen Datenbankaccounts dann aus dem pg_dumpall -g-Dump. Dann müssen wir nur das CREATE-DATABASE- sowie die GRANT- und REVOKE-Statements aus dem pg_dumpall-Dump holen.

Sobald wir PostgreSQL mit den gesammelten SQL-Anweisungen gefüttert haben, erhalten wir eine leere Datenbank samt aller Nutzer und deren Berechtigungen, die nur noch darauf wartet, mittels des pg_dump-Dumps befüllt zu werden.

Das beschriebene Zusammenklauben aller SQL-Anweisungen leistet das folgende Shellskript:

#!/bin/bash

# Nach allen Nutzern suchen, die Rechte
# auf der Datenbank haben...
for i in `\
	grep "^GRANT .* ON DATABASE $DATABASE TO" $PGDUMPALLFILE \
	| sed "s/^GRANT .* ON DATABASE $DATABASE TO \(.*\);/\1/" \
	`;
do
	# ... und zu diesen Nutzern die CREATE- und
	# ALTER-ROLE-Anweisungen holen
	fgrep -w "ROLE $i" $PGDUMPALLGLOBALFILE ;
done

# Die CREATE- und ALTER-DATABASE- sowie die zugehörigen
# REVOKE- und GRANT-Anweisungen holen
fgrep -w "DATABASE $DATABASE" $PGDUMPALLFILE;

Nur der Vollständigkeit halber – der anschließende Aufruf von pg_restore sieht folgendermaßen aus:

pg_restore -1 -d $DATABASE $PGDUMPFILE

Yep, PostgreSQL macht Spaß…

Wie ich schon irgendwie erwartet hatte, gefiel Uwe meine Regex-SQL-Mischung nicht.

Mit seiner Erlaubnis veröffentliche ich im Folgenden seinen (von mir reparierten 🙂 ) Gegenvorschlag:

select pers_name, sp_id, max(strikesequence) as strikesinarow from
( select *,
        length(
            unnest(
                string_to_array(
                    regexp_replace(
                        regexp_replace(
                            concat(
                                spiel.sp_w01_1, ',',
                                spiel.sp_w02_1 , ',',
                                spiel.sp_w03_1 , ',',
                                spiel.sp_w04_1 , ',',
                                spiel.sp_w05_1 , ',',
                                spiel.sp_w06_1 , ',',
                                spiel.sp_w07_1 , ',',
                                spiel.sp_w08_1 , ',',
                                spiel.sp_w09_1 , ',',
                                spiel.sp_w10_1 , ',',
                                spiel.sp_w10_2 , ',',
                                spiel.sp_w10_3 , ','
                            ), '10,', 'X', 'g'
                        ), '[^X]+', '-', 'g'
                    ), '-'
                )
            )
        ) as strikesequence
    from spiel natural join person
) as sub
group by pers_name, sp_id
order by pers_name, strikesinarow desc; 

Sein Ansatz ist definitiv kürzer und beim tippen deutlich weniger fehleranfällig, dafür aber IMHO nicht ganz so leicht nachvollziehbar.

Unbestritten sollte jedoch sein, dass weder seine noch meine Lösung in Produktivsystemen eingesetzt werden sollten…

SQL und Regex – zwei Garanten für genialen und im Nachhinein völlig unverständlichen Code. Was liegt also näher, als beides zu kombinieren…

Im konkreten, unglaublich wichtigen Problemfall ging es darum, aus einer Tabelle mit Bowlingergebnissen die Anzahl der unmittelbar nacheinandergeworfenen Strikes (strikes in a row) zu berechnen.

Ja, mit einer PROCEDURE wäre das Ganze trivial, aber es bestand der Wille, das Ganze in Plain-SQL hinzubekommen, also insbesondere ohne Schleifen. Dank regulären Ausdrücken klappt das nicht nur, sondern sieht darüber hinaus auch noch unglaublich ästhetisch aus.

Die zugrundeliegende Tabelle enthält in den Spalten sp_w01_1, sp_w01_2, sp_w02_1 usw. die im jeweiligen Wurf gefallenen Pins (w01_1 == erster Frame, erster Wurf; w01_2 == erster Frame, zweiter Wurf usw.). Für das Aufsummieren der Strikes sind nur die jeweils ersten Würfe interessant, lediglich im zehnten Frame müssen auch der zweite und dritte Wurf berücksichtigt werden.

Die folgende SQL-Abfrage liefert das Gewünschte – eine Erklärung für Denkfaule folgt darunter

select pers_name, sp_id,

regexp_replace(
regexp_replace(
regexp_replace(
regexp_replace(
regexp_replace(
regexp_replace(
regexp_replace(
regexp_replace(
regexp_replace(
regexp_replace(
regexp_replace(
regexp_replace(
regexp_replace(
regexp_replace(

CASE WHEN spiel.sp_w01_1 = 10 THEN 'X'::text ELSE '-'::text END ||
CASE WHEN spiel.sp_w02_1 = 10 THEN 'X'::text ELSE '-'::text END ||
CASE WHEN spiel.sp_w03_1 = 10 THEN 'X'::text ELSE '-'::text END ||
CASE WHEN spiel.sp_w04_1 = 10 THEN 'X'::text ELSE '-'::text END ||
CASE WHEN spiel.sp_w05_1 = 10 THEN 'X'::text ELSE '-'::text END ||
CASE WHEN spiel.sp_w06_1 = 10 THEN 'X'::text ELSE '-'::text END ||
CASE WHEN spiel.sp_w07_1 = 10 THEN 'X'::text ELSE '-'::text END ||
CASE WHEN spiel.sp_w08_1 = 10 THEN 'X'::text ELSE '-'::text END ||
CASE WHEN spiel.sp_w09_1 = 10 THEN 'X'::text ELSE '-'::text END ||
CASE WHEN spiel.sp_w10_1 = 10 THEN 'X'::text ELSE '-'::text END ||
CASE WHEN spiel.sp_w10_2 = 10 THEN 'X'::text ELSE '-'::text END ||
CASE WHEN spiel.sp_w10_3 = 10 THEN 'X'::text ELSE '-'::text END,

'.*XXXXXXXXXXXXX.*'::text, '13'::text, 'g'::text),
'.*XXXXXXXXXXXX.*'::text, '12'::text, 'g'::text),
'.*XXXXXXXXXXX.*'::text, '11'::text, 'g'::text),
'.*XXXXXXXXXX.*'::text, '10'::text, 'g'::text),
'.*XXXXXXXXX.*'::text, '9'::text, 'g'::text),
'.*XXXXXXXX.*'::text, '8'::text, 'g'::text),
'.*XXXXXXX.*'::text, '7'::text, 'g'::text),
'.*XXXXXX.*'::text, '6'::text, 'g'::text),
'.*XXXXX.*'::text, '5'::text, 'g'::text),
'.*XXXX.*'::text, '4'::text, 'g'::text),
'.*XXX.*'::text, '3'::text, 'g'::text),
'.*XX.*'::text, '2'::text, 'g'::text),
'.*X.*'::text, '1'::text, 'g'::text),
'.*-.*'::text, '0'::text, 'g'::text)

::integer AS strikesinarow

from spiel natural join person
order by pers_name, strikesinarow desc;


Zuerst wird ein String zusammengesetzt, in dem die gesamte Abfolge Strikes/Nichtstrikes des gesamten Spiels codiert ist ('X' == Strike, '-' == kein Strike).

Dann geht die Reg(h)exerei los: Sollte der String 13 mal hintereinander 'X' enthalten, so wird er (als Ganzes) durch den String "13" ersetzt. Sollte der String 12 mal hintereinander 'X' enthalten, so wird er (wieder als Ganzes) durch den String "12" ersetzt, usw. Nur bei einem Strike-losen Spiel wird der String am Ende durch den String "0" ersetzt.

Nunja, und anschließend muss der String (der ja nun die gesuchte Zahl enthält) nur noch in einen Integer umgewandelt werden…

Unser täglich MySQL- … ähm, moment … PostgreSQL-Rant gib uns heute? Tja, auch PostgreSQL enthält für den DB-Admin die eine oder andere Überraschung bereit… Wobei Rant übertrieben ist, schließlich ist alles Folgende nachvollziehbar und letztendlich auch sauber dokumentiert (ja, ich bleibe PostgreSQL-Fan).

Im Konkreten geht es um das Backup-Werkzeuggespann pg_dump/pg_restore und vor allem einigen Schaltern bzw. Schalterkombination von letztgenanntem Programm.

Angenommen, man möchte einen Datenbankdump der Datenbank “kochbuch” im Binärformat ziehen (was für ein abwegiger Beispieldatenbankname), dann sollte der Aufruf von pg_dump in etwa so aussehen:

pg_dump -Fc -w kochbuch > kochbuch.dump
# -Fc == Binärdumpformat
# -w == keine Passwortabfrage


So weit, so uninteressant. Wie bei jedem Backup ist die viel spannendere Frage, wie das Backup wieder zurück in die Datenbank kommt. An dieser Stelle kommt pg_restore ins Spiel, und zwar bei uns im Idealfall so:

pg_restore kochbuch.dump -v -1 -c -d kochbuch
# -v == verbose
# -1 == alles in einer Transaktion
# -c == löscht vorher alle anzulegenden Objekte (clean restore)
# -d == die Datenbank, mit der sich pg_restore zur Wiederherstellung verbindet


Die zu sehende Schalterkombi stellt eigentlich das Optimum dar, wenn man die Datenbank vollständig in den Zustand des damaligen Backups zurückversetzen möchte, was bei uns das Standardszenario ist.

Das -c sorgt insbesondere dafür, dass alle neueren Tabelleninhalte eliminiert werden; das Einspielen in einer Transaktion mittels -1 sorgt dafür, dass schon beim ersten Fehler das gesamte Einspielen vollständig revertiert wird, d.h. am Ende des Befehls hat man entweder eine sauber zurückgespielte Datenbank oder aber den unveränderten Zustand vor dem Einspielversuch.

Dummerweise kann man die Schalterkombi im realen Leben nicht verwenden. Zwei Negativbeispiele (es gibt sicher weitere) ergeben sich schon dann, wenn seit dem Backup Tabellen gelöscht oder hinzugefügt wurden. In ersterem Fall schlägt das Löschen der gelöschten Tabelle fehl, in zweiterem das Löschen des Schemas, da die neuen Tabellen noch drinliegen. Beide Fälle sorgen dafür, dass die Transaktion, in diesem Fall nur halbgewollt, abbricht.

Man muss also die -1 weglassen und die Warnungen ob der nicht löschbaren Objekte ignorieren (das Weglassen nur des -c unter Beibehaltung von -1 ist i.d.R. gar nicht möglich, da das Einfügen der bereits enthaltenen Elemente unweigerlich zum Abbruch der Transaktion führt) – unschön.

Also härtere Bandagen: vollständiges Löschen der existierenden Datenbank und Einspielen des Dumps in eine jungfräuliche, da neuangelegte Datenbank.

Erfreulicherweise kennt pg_restore den Schalter -C, der laut --help die zweite Hälfte leisten soll (“-C, create the target database“). Also frisch ans Werk – erst die Datenbank mittels dropdb löschen und dann neu Erstellen/Einspielen:

$ dropdb kochbuch
$ pg_restore /tmp/test.dump -v -C -d kochbuch
pg_restore: connecting to database for restore
pg_restore: [archiver (db)] connection to database "kochbuch" failed: FATAL: database "kochbuch" does not exist
pg_restore: *** aborted because of error


Dat war dann wohl nix! Die Datenbank kann nicht angelegt werden, da er sich mit der neu anzulegenden Datenbank nicht verbinden kann? Hmm, --help reicht offensichtlich nicht aus, also rein in die Doku (in meinem Fall von Version 9.1) und dort steht dann auch die zumindest für mich überraschende Zeile:

(When this option [-C] is used, the database named with -d is used only to issue the initial CREATE DATABASE command. All data is restored into the database name that appears in the archive.)

Ich muss mich also mit irgendeiner wildfremden Datenbank verbinden, damit PostgreSQL dann von dieser Datenbank aus die eigentlich wiederherzustellende Datenbank erzeugen kann – irgendwie nachvollziehbar, aber intuitiv geht anders.

Anyway, die Lösung lautet also (nur ein Beispiel, ich habe viele Datenbanken, die als Verbindungsdatenbank herhalten können)

$ dropdb kochbuch
$ pg_restore /tmp/test.dump -v -C -d postgres


…und schon macht pg_restore genau das Gewollte:

pg_restore: connecting to database for restore
pg_restore: creating DATABASE kochbuch
pg_restore: connecting to new database "kochbuch"
usw.


Noch ein Hinweis: ab Postgres 9.2 kann man (endlich) -c und -C auch kombinieren (vorher ergibt das ein “-C and -c are incompatible options“), so dass das Löschen und Neuanlegen der Datenbank in einem Befehl erfolgt. Aber auch hier gilt dann, dass man eine unbeteiligte Datenbank angeben muss, von der aus das DROP DATABASE und das CREATE DATABASE aus durchgeführt wird.

Wieder was gelernt, womit man vor Anfängern als PostgreSQL-Guru dastehen kann 🙂