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 🙂