Datenbackup mySQL vs. PG - Verlust?

Liebe Community

bin noch recht neu auf diesem Terrain, aber bisher überzeugt mich PG mehr als mySQL.
Ich hoffe, die Frage gehört hier im Forum an diese Stelle, wenn nicht bitte ich den Admin, diese zu verschieben.

Jetzt zu meiner Frage:
Mein Chef will unser BE von mySQL auf PG umstellen.
Soweit ich herausgefunden habe stellt PG keine zeitgesteuerte und intervallmässige Backupmöglichkeit zur Verfügung.
Daher helfe ich mir mit dem Windows-Taskmanager und folgender Eingabeaufforderung:

pg_dump -h localhost -p 5432 -U postgres -Fc -b -v -f > %pg_dump% “DATEINAME.backup”

Dieser Befehl wird mehrmals täglich ausgeführt.

Jetzt zu meiner eigentlichen Frage:

  1. Die von mySQL generierte SQL-Backupdatei ist circa 372 MB gross, das Update dauert ca. 15 Minuten. Die PG “.backup”-Datei ist lediglich 24 MB gross, scheint aber alle Daten zu beinhalten, sprich - ich vermute keinen Datenverlust. (372 vs. 24 MB = Riesen-Diskrepanz!) Das Backup dauert ebenso lediglich ca. 7 Sekunden. Kann das wirklich sein?

  2. Was geschieht mit Daten, die von Benutzern während des Backup-Prozesses eingegeben werden? Tauchen diese im nächsten Backup auf oder verschwinden die im Nirgendwo?

Ich bin auf eure Antworten und Erläuterungen gespannt. Vielen Dank!

Dr. Gonzo

re 1) Im SQL Script stehen z.B. nur die Definitionen der Indizes, nicht aber die tatsächlichen Index Daten, d.h wenn die Datenbank viele Indizes hat, kann es schon sein, dass ein Dump deutlich kleiner ist als die eigentliche Datenbank. Wie groß ist denn die Datenbank an sich? Das kannst Du Dir z.B. mit dem Befehl \l+ in psql oder mit select pg_database_size(current_database()); anzeigen lassen.

Warum das MySQL Script so groß ist, kann ich nicht sagen, ich verwende kein MySQL.

re 2). Der dump ist eine konsistente Sicht der Datenbank von dem Zeitpunkt an dem der Dump gestartet wurde. D.h. Daten die während dem Backup eingegeben werden, tauchen erst im nächsten Dump auf.

Ein echtes “Backup” ist das übrigens nicht - nur ein “Dump” von einem bestimmten Zeitpunkt.

Es gäbe in PostgreSQL die Möglichkeit, ein kontinuierliches Backup zu machen, welches runter bis auf die Transaktionsebene JEDE Transaktion sichert. Meine Kollegen in Prato haben dafür eine einfach zu verwaltende Oberfläche entwickelt, in der aktuellen Version sollte das auch mit einem Windows-Rechner als PG-Server funktionieren: Barman http://www.pgbarman.org/

Bei Fragen dazu kannst Dich an an mich direkt wenden.

Hallo castorp

Vielen Dank für die Antwort.

re 1)
Es wird mir eine Datenbankgrösse von 136534552 (pg_database_size bigint) angegeben.

Was genau ist denn der Unterschied zwischen einem Dump und einem Backup?
Ich habe die Datenbank nach dem Dump komplett gelöscht und mit pg_restore wiederhergestellt. Es wurden alle Tabellen und Indexe MIT Daten wiederhergestellt. Das ist doch das, was ein Backup so macht oder?..

re 2) Okay, danke.

Ein Dump mit 24MB für eine DB mit 130MB ist nicht ganz unrealistisch.

Ein Backup ist eine kontinuierliche Sicherung die den möglichen Datenverlust auf eine Minimum reduziert (typischerweise deutlich weniger als eine Sekunde). Wenn Du aber z.B. nur 4 mal täglich einen Dump ziehst, kannst Du bis zu 6 Stunden arbeitet verlieren, falls der Rechner kurz vor dem nächsten Backup ausfällt

Hallo castorp und akretschmer

  1. akretschmer, danke für den Link, werde mich bei Bedarf bei dir melden. Möchte das erst noch mit dem Chef bequasseln
  2. castorp, vielen Dank, du hast jetzt Licht ins Dunkel gebracht. Wenn ich dich richtig verstanden habe ist ein Dump einfach eine Datensicherung. Im PG Admin steht dann irreführenderweise auch “Backup” unter der Datenbank bei Rechtsklick. Bisher haben wir dann wohl eine Dump-Lösung gehabt, ohne es zu wissen, d. h. komplette Sicherung der DB 4x am Tag (mit einberechnetem Datenverlust).

Wenn es aber kontinuierlich ginge wäre das natürlich super. Fragt sich nur die Belastung des Netzwerkes: Geht es mir in die Knie?

Ein kontinuierliches Backup speichert nur Änderungen - nicht jedesmal die gesamte Datenbank.

https://www.postgresql.org/docs/current/static/continuous-archiving.html

Recht herzlichen Dank für den Link castorp.
Muss mir das in Ruhe mal anschauen, im Moment hab ich zu viele Projekte.
Mein Chef meinte, ein Dump im Moment genügt. Von daher…

URL zu diesem Thread hab ich aber und werde mich zu gegebener Zeit wieder melden.

Danke auch dir akretschmer.

Bei solch einer kleinen DB mag ein täglicher Dump absolut genügen. Bei größer werdenden Datenbanken ist das aber irgendwann nicht mehr praktikabel.

  • Dump und Restore dauern zu lange
  • das Fenster, in dem Daten verloren werden, ist zu groß
  • ein laufendes Dumpen hat negative Auswirkungungen auf die laufende DB (insbesondere Vacuum)

Behaltet das im Auge :wink: