Differentielles Backup machbar?

Bitte von dem langen Text nicht abschrecken lassen. Leider kann ich es nicht kürzer beschreiben, ohne dass Verständnisschwierigkeiten auftreten. Vielen Dank für das Verständnis!

Hallo zusammen …

ich stehe vor einem großen Problem beim Einrichten des täglichen Backups von meiner Postgres-9 Datenbank. Bevor ich meine Frage vorstelle, möchte zuvor kurz den aktuellen Stand beschreiben:

Wir bauen gerade eine große Archivdatenbank auf. In dieser Datenbank sollen die Daten 10 Jahre lang vorgehalten werden. Da nur auf die letzten 12 Monate performant zugegriffen werden können soll, habe ich mich dazu entschieden, die entsprechenden Archivtabellen zu partitionieren und nur bei den entsprechenden Teiltabellen einen Index vorzusehen, die innerhalb dieser 12 Monate liegen. Mir ist bekannt, dass man auch einen partiellen Index hätte nutzen können - später dazu mehr.

Nun zum Backup: Da es sich um eine relativ große Datenbank handelt und ein Full-Backup momentan 8h dauert und in naher Zukunft voraussichtlich länger als 48h dauern wird, kommt m.E. nur ein differentielles Backup in Frage. Ein Full-Backup soll nur in Ausnahmefällen geschehen. Entsprechend pessimistisch ist meine Prognose zum Recovery, d.h. bei einem Recovery eines differentiellen Backups, muss mindestens 48h angesetzt werden + x Stunden, um das WAL nachzuspielen. Somit könnte m.E. schnell mehrere Tage zusammenkommen, um ein Recovery abzuschließen. Dieses ist nicht akzeptabel. Daher habe ich mir folgende Lösung einfallen lassen:

Ich baue eine WAL-Replikation der Datenbank auf. Mit Posgres-9 ist dieses möglich und hat m.E. den Vorteil, dass neue Tabellen und Tabellenänderungen nicht vorher aufwändig konfiguriert werden müssen. Vom Slave wird die Datensicherung gemacht. Dieses geschieht mit pg_dump. Dieser Ansatz muss allerdings noch verfeinert werden. Daher arbeite ich mit Triggern, d.h. zunächst hefte ich auf dem Slave an allen Tabellen (auch den partitionierten Tabellen) einen Trigger. Der Trigger reagiert auf create, update und delete. Wird der Trigger ausgelöst, löscht sich der Trigger von der entsprechenden Tabelle. Zum Zeitpunkt des Backups, stoppe ich die Replikation und schaue, welche Tabellen keinen Trigger besitzen und sichere diese. Danach installiere ich wieder an allen Tabellen den Trigger und starte die Replikation. Wo liegen die Vorteile dieses Verfahrens:

Wird eine neue Tabelle auf dem Master angelegt, wird diese ohne weiteres Zutun auf dem Slave automatisch gesichert, weil diese ja keinen Trigger besitzt. Desweiteren ist dieser Ansatz mit dem Trigger recht effizient, da dieser (idealerweise) nur einmal zwischen zwei Backups ausgelöst wird. Durch pg_dump werden die Tabellen voll gesichert. Die Partionierung reduziert das Datenvolumen aber erheblich. Spannend ist auch das Recovery, d.h. es können die aktuellen Teiltabellen der Partitionierung zuerst und diese innerhalb weniger Stunden wiederhergestellt werden, sodass die Benutzer mit dem wichtigsten Teil der Daten schon wieder arbeiten können. Die älteren Daten können im laufenden Betrieb “reingeschoben” werden.

Den Mechanismus mit den Trigger habe ich schon realisiert und erfolgreich getestet. Ich scheitere momentan daran, die WAL-Replikation einzurichten. Dieses liegt aber sicherlich daran, dass ich an irgendeiner Stelle die Anleitung nicht richtig befolge. Dieses soll auch hier nicht weiter relevant sein. Nach dem ersten Fehlversuch bin ich allerdings auch schon beim Lesen auf den Abschnitt mit den Einschränkungen der WAL-Replikation gestoßen. Da heißt es, dass nur lesender Zugriff auf dem Slave möglich ist.
Daher folgende Fragen:


  1. Ist die Annahme richtig, dass ich auf dem Slave keinen Trigger installieren kann aufgrund der Lesezugriffbeschränkung?
  2. Könnte ich diese Beschränkung umgehen, indem ich die DB herunterfahre, die Replikation deaktiviere z.B. durch umbenennen von “recovery.conf”, die DB starte, das Backup mache, die DB stoppe, die Replikation wieder reaktiviere und dann die DB starte?
  3. Wenn 1. oder 2. nicht funktionieren, was kann ich alternativ machen?

Vielen Dank für eure Ausdauer und Beteiligung zur Klärung meiner Fragen im Voraus!

  • Dieter.
  1. ja, das stimmt
  2. nein, dann hättest Du keine gültige Replikation der DB mehr. Du kannst die DB zwar runterfahren und dann ein FS-Backup machen, aber bitte nicht als intakten Master hochfahren. Das knallt dann.
  3. die Doku enthält ein eigenes Kapitel zu Backup/Restore.


    Andreas

Deine Fragen hat dir akretschmer schon beantwortet.

Alternativ kannst du mal einen Blick auf Greenplum werfen. Das löst deine Partitionierungs- und Backupprobleme, außerdem skaliert das linear nach oben.
Du hast also auch zukünftig keine Performanceprobleme.

Was nicht bedeuted, daß PostgreSQL nicht in der Lage wäre, dies auch zu können…

Wenn man z.B. gescheit partitioniert, dann sollte ein tägliches Base-Backup auch nicht zum Flachenhals werden, weil ein Teil der Tabellen, und zwar die Masse der Daten, sich ja nicht ändert und man mit inkrementellen Backupstrategien für das FS arbeiten kann.


Andreas

Nun ja, du weißt nicht, ob sich die Daten nicht ändern - siehe Vacuum.
Also entweder nutzt du pg_dump - und hast immer alle Daten zu exportieren.
Oder du schaust, wie du das auf Filesystemebene hinbekommst und hast trotzdem mit den Problemen zu kämpfen.

So viele Antworten habe ich nicht erwartet. Danke!

Leider habe ich die Hot Standby-Replikation bisher noch nicht feherfrei eingerichtet bekommen. Dank akretschmers Antwort kann ich darauf verzichten und meine Zeit besser nutzen.

Alternativ kannst du mal einen Blick auf > Greenplum > werfen. Das löst deine Partitionierungs- und Backupprobleme, außerdem skaliert das linear nach oben.
Du hast also auch zukünftig keine Performanceprobleme.

Greenplum ist mir bis dato unbekannt gewesen. Klingt interessant, was die auf Ihrer Homepage schreiben. Die Postgres-Lösung kurzfristig zu migrieren, halte aufgrund meiner mangelnden Erfahrung mit Greenplum zu riskant.

Wenn man z.B. gescheit partitioniert, dann sollte ein tägliches Base-Backup auch nicht zum Flachenhals werden, weil ein Teil der Tabellen, und zwar die Masse der Daten, sich ja nicht ändert und man mit inkrementellen Backupstrategien für das FS arbeiten kann.

Ich stimme Dir da vollkommen zu. Das FS-Backup wäre meine Fallback-Lösung auch mit den von Ads genannten Nachteil bei einem Vacuum.

Allerdings möchte ich mir die Möglichkeit erhalten, die differentiellen Backups in der Zeit absteigend zu restoren, um die gefühlte Offline-Zeit für den Benutzer so kurz wie möglich zu halten. Desweiteren kann ich dann auch leichter Teile der Daten aus dem Live-System in das Testsystem überführen (ohne die Live-DB zu belasten). Bei einem FS-Backup sehe ich diese Möglichkeit nicht bzw. ist mir unbekannt. Bin aber lernfähig :wink:

Von einem Kollegen kam die Idee, DRBD einzusetzen. Das Grobkonzept lautet

  1. Fahre den Master herunter, um die Dateien zu flushen (ist bei uns nachts kurzzeitig möglich, geht das aber noch eleganter?)
  2. Trenne die DRBD-Verbindung zum Master
  3. Starte Master nur mit lokaler Zugriffsmöglichkeit und setze die Triggermarkierung (wie oben beschrieben) zurück
  4. Erlaube den Vollzugriff auf den Master
  5. Starte Postgres auf dem Slave
  6. Ermittle anhand der Triggermarkierungen auf dem Slave, welche Tabellen zu sichern sind und ziehe die Dumps
  7. Beende Postgres auf dem Slave
  8. Verbinde den Slave mit dem Master wieder über das DRBD. Die Synchronizierung wird automatisch in Gang gesetzt.

Spricht aus Eurer Sicht irgendetwas gegen diese Lösung? Wie ist eure Meinung?

-Dieter.

Zum einen: Greenplum ist ein PostgreSQL Fork und funktioniert (fast) genauso wie PostgreSQL.

Zu deinem Versuch einer Lösung:
Das sieht mir sehr buggy aus. Wenn du das DRBD wieder verbindest: wer garantiert dir, das der die Änderungen in die richtige Richtung nimmt?
Wenn das falsch herum repliziert, zerlegst du dir deine Master Datenbank. Dann lieber rsync.
Außerdem brauchst du einen Trigger auf alle Tabellen, was dir die Performance in den Keller zieht.