pg_dump Ausgabe ohne Escape-Sequenzen?

Hallo Forum,

nach meinem Kenntnisstand kann man pg_dump nicht dazu veranlassen, Binärdaten auszugeben. Stattdessen wird der Datenstrom mit Escape-Sequenzen gesendet (als Oktalzahlen?). Das sieht dann so aus: \220\200\260\313\316q\317:\242:\002B\370\266\244\030

Ich habe eine Datenbank (Postgres 8.3) mit Dokumenten (bytea), von der mit pg_dump Backups erstellt werden. Aus der Datenbank, die im Binärformat ca. 60 GB groß ist, werden durch das Escapen ca. 250 GB. Die nachträgliche Behandlung mit Kompression dauert sehr lange. Zwar wird der von pg_dump erzeugte Datenstrom bereits per Pipe abgegriffen und komprimiert, aber es laufen eben immer noch 250 GB in die Pipe, was seine Zeit kostet.

Welche Instanz verwandelt eigentlich die Binärdaten in Escape-Sequenzen um? Macht das pg_dump oder sendet bereits Postgres escaped an pg_dump? Dafür, daß bereits Postgres die Daten umwandelt, spricht, daß ich auch aus PHP oder Perl bei einem “SELECT (bytea) FROM table” Statement keine Binärdaten bekomme und die Daten erst (zeit)aufwendig mit pg_unescape_bytea zurückverwandeln muß.

Sind Euch irgendwelche Alternativen bekannt, mit denen man direkt einen binären Datenstrom aus Postgres holen kann?

Vielen Dank
ratio

ps: Bitte keine Ausführungen dazu, ob die Speicherung solcher Binärdatenmengen in Postgres sinnvoll ist. :slight_smile:

Wie wäre es, wenn du das Custom Format für das Backup einsetzt? Das ist bereits komprimiert.

Oh, und bezüglich der binären Daten in der DB: das ist nun mal der Overhead, den man sich erkauft, wenn man so etwas aufzieht :wink:

Schau Dir mal die man-page zu pg_dump an und achte auf die Option Fc.



Andreas

Hallo,

erstmal Danke für die schnellen Antworten.
Nach euren Tipps habe ich mal ein paar Dumps im custom Format erzeugt.
Kommandozeile:

pg_dump --blobs --create --disable-triggers -Fc --compress=0 data01 > db-test.dump

Leider musste ich feststellen das dies meine Probleme nicht so recht löst.
Der einzige Unterschied den ich feststellen konnte war, dass dieses Format standardmäßig eine Kompression besitzt. Wenn ich diese mit --compress=0 abschalte erhalte ich jedoch wieder den escapten Datenstrom den ich auch ohne die Option -Fc erhalte.

Desweiteren ist die Kompression nicht sonderlich effizient werder von der Geschwindiwgkeit noch von der Kompressionsrate, daher greife ich momentan auf einen externen Kompressor zurück (pigz pigz - Parallel gzip) dieser arbeitet parallel und so kann ich die 8 Prozessoren die ich in den DB Server habe voll ausnutzen und die Kompression entwickelt sich nicht zum Flaschenhals.

Mir ging es darum aus pg_dump ein Datenformat zu bekommen das nicht künstlich aufgeblasen ist, damit ich mir die nachträgliche Kompression ersparen kann. Zuerst bläst pg_dump meine Binärdaten mit viel Luft auf und ich muss dann zusehen wie ich die unter Einsatz von viel Rechenleisung wieder loswerde, damit meine Backups noch irgendwie eine vertretbare Grösse besitzen.

Vielleicht hat ja noch jemand einen Tipp…

Ja, schreib dir selber ein Werkzeug, das mit binnären Daten umgehen kann.

Das Problem ist, dass in biären Daten z. B. auch \000 vorkommen kann, was in C einen String beendet. Damit kannst du das Ende eines Strings nicht erkennen. Daher werden kritische Zeichen von PostgreSQL derart dargestellt, dass diese kein Problem ergeben.

Imho passiert das aber schon recht weit unten im Protokoll, so dass du die Daten selber umsetzen musst. pg_dump erzeugt ein Format, das sich ohne Probleme wieder einlesen lässt. Es ist nicht unbedingt dafür zuständig, dass dieses Format besonders klein wird.

Btw, wenn es dir schon um Flaschenhals und Performance geht: was ist beim Restore? Das Custom Format kann ab PG 8.4 die Daten über mehrere Verbindungen wieder herstellen. Mir wäre die Backup Laufzeit nicht so wichtig wie das ggf. notwendige Restore. Das klappt aber nur, wenn pg_restore selbst das Restore managt.

ersmal noch eine Frage - welchem Zweck dient Deinem Anwendungsfall das die Lösung pg_dump, dass es sich als Flaschenhals darstellt?

Wirklich “nur” Backup?

Hallo,

@symbiont Ja mir geht es nur um Backup! Vielleicht gibt es da ja noch eine ganz andere Herangehensweise?

@ads
Die Backup-Performance ist uns tatächlich wichtiger, als die Restore-Zeit. Ein Datenverlust ist bei uns unwahrscheinlich, und in diesem Fall hätten wir auch genügend Zeit. Gleichwohl wird zur Beruhigung des Gewissens sehr häufig gesichert. :slight_smile:

Ja, ein Werkzeug das die esacpe sequenzen wieder in Binärdaten umsetzt, wäre eine Möglichkeit, den Overhead zu reduzieren. Kennst Du einen?

Ich hätte auch nichts dagegen, wenn nur die \000 bytes escaped würden…

Du schreibst:

Imho passiert das aber schon recht weit unten im Protokoll, so dass du die
Daten selber umsetzen musst. pg_dump erzeugt ein Format, das sich ohne
Probleme wieder einlesen lässt. Es ist nicht unbedingt dafür zuständig,
dass dieses Format besonders klein wird.

Heißt das, daß das escapen bereits im Kern von PostgreSQL stattfindet und ich keine Chance habe, von PostgreSQL jemals Binärdaten geliefert zu bekommen? Dann kann man natürlich an pg_dump nicht mehr viel drehen und ich würde meine Bemühungen tatsächlich auf den Umgang mit den escapten Daten reduzieren. Wie bereits gesagt, das eingebaute Kompressor komprimiert schlecht und läßt sich auch nicht auf mehrere Prozessoren skalieren.

Im Übrigen soll pg_restore für das Recovery benutzt werden, ich möchte so viel wie möglich standard Tools benutzen und so wenig wie möglich Eigenentwicklungen.

Über JDBC (Java) bekomme ich direkt ein byte array zurück.
Ich weiss allerdings nicht, ob der Treiber da irgendwas intern umwandelt, so dass die Anwendung das gar nicht merkt.

Na wenn du pg_restore nutzen willst, bleibt es bei pg_dump.

Habt ihr schon mal über ein Online Backup nachgedacht?


Vielleicht fallen mir im Wasser noch ein oder zwei Ideen ein, bis später.

nunja … wenns nur um backup geht, dann ist deine idee mit dem externen compress garnicht so falsch, weil du dadurch tatsaechlich den aufwand auf mehrere prozessoren verteilen kannst - natuerlich brauchst dann auch mehr bandbreite … musst du ausprobieren ob das was bringt …

also den output von pg_dump in einen compressor pipen … das sind dann 2 prozesse -> 2 kerne

an dem output-format von pg_dump wuerde ich nix aendern … backups sollen sicher sein - da will man keine spielereien …

Hmm, ich muss heute abend mal in meinen Text schauen, das habe ich irgendwo schon mal auseinander genommen.


Ev. ist das erst von pg_dump umgewandelt, aber dann braucht es das Format trotzdem, um pg_restore wieder nutzen zu können.

wenn ich mich recht erinnere decodiert der jdbc treiber die text-daten welche das protokoll zur verfuegung stellt …

ich erinnere mich falsch - hab gerade nachgesehen … scheint nicht so als wuerde da was codiert oder decodiert

PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: Message Formats

als beispiel

DataRow (B)



Byte(1)(‘D’)

Identifies the message as a data row.
Int32

Length of message contents in bytes, including self.
Int16

The number of column values that follow (possibly zero).

Next, the following pair of fields appear for each column:

Int32

The length of the column value, in bytes (this count does not include itself). Can be zero. As a special case, -1 indicates a NULL column value. No value bytes follow in the NULL case.
Byte(n)

The value of the column, in the format indicated by the associated format code. n is the above length.

Du koenntes die schlimmen arrays bereits im modell als Text - Base64 codieren.

zunahme: “nur” 36 % … aus 60 MB werden ca 100 MB (im unguenstigen fall)

komprimieren kannst das bei pg_dump dann auch schoen.

beispiel aus der doku, welche die idee veranschaulicht

encode( ‘123\000\001’, ‘base64’) => ‘MTIzAAE=’

13 zeichen : 8 zeichen

am besten du baust dir dafuer nen custom typ base64 (wenns den noch nicht gibt) und nen cast nach text und wieder zurueck und auch einen fuer bytearray und wieder zurueck …
damit kannst dann wunderbar s ku ellen

Also, das Protokoll überträgt die Länge des Strings und dann den Inhalt selbst. Damit ist man nicht auf \000 als Trenner angewiesen.

Nur kann man das in der Form nicht exportieren, daher zeigen psql und pg_dump das encoded an. Das ist mir aufgefallen, als ich die C-Funktionen für mein Buch beschrieben habe.


Nun, das hilft aber alles nicht weiter, wenn du weiterhin pg_dump & pg_restore verwenden willst. Die sind nämlich auf dieses Format angewiesen. Das ist der Nachteil, wenn man binäre Daten in der DB speichert und sich kein externes Storage dafür überlegt.

Was könntest du jetzt machen? Du könntest eine eigene Backup-Routine schreiben, die die binären Daten exportiert und anders ablegt. Das Restore musst du dann natürlich auch selbst schreiben.

Also, nochmal vielen Dank für Eure Antworten. Im Ergebnis bleiben wir jetzt bei der bisher entwickelten Strategie. Das maßgebliche Argument dafür haben ads und symbiont schon gebracht: Wenn das recovery-Szenario pg_restore nutzen soll, muß mit pg_dump gesichert werden. Keine Experimente beim Backup!

Die derzeitige Sicherungsstrategie sieht wie folgt aus:
Die Binärdaten liegen in nur einer Tabelle (60 GB). Die “normalen” Daten werden getrennt von dieser Tabelle gesichert. Das Backup läuft nicht über Netzwerk, sondern über ein direkt am Server angeschlossenes eSATA-Dock. Nun hat man die Wahl, den von pg_dump erzeugten Datenstrom

  1. unbehandelt (unkomprimiert) auf der Platte abzulegen => 247 GB / 115 Minuten,
  2. in einen Komprimierer mit schlechter Kompressionsrate, aber hoher Geschwindigkeit zu pipen, z.B. pigz (parallele Implementation von gzip) => 76 GB / 128 Minuten, oder
  3. in einen Komprimierer mit hoher Kompressionsrate, aber geringer Geschwindigkeit zu pipen, z.B. pbzip2 (parallele Implementation von bzip2 => 47 GB / 4,5 Stunden oder grzip (single-thread) => 42 GB / 6 Stunden

Wir haben uns für Variante 2 entschieden, weil der Postgres-Server während des Dumps mit einem Prozess auf einem Kern fast ausgelastet ist und wir das schnell beenden wollen. Im Anschluß wird die .gz-Datei von einem separaten Prozess wieder entpackt und als .grz gepackt, was aber nicht den Postgres-Server belastet. Der Server hat übrigens 8 Kerne.

Die Kommandos:

pg_dump --create --table=dokumente.doc --disable-triggers dbname | pigz -2 -p 6 -c > pgsql_docs_dump_$DATUM.sql.gz

pigz -d -c pgsql_docs_dump_$DATUM.sql.gz | grzip -c -b8m -m4 > pgsql_docs_dump_$DATUM.sql.grz

rm pgsql_docs_dump_$DATUM.sql.gz

Das Thema mit den Binärdaten ist wirklich ärgerlich. Daß pg_dump ein “sicheres” Format benötigt, kann ich verstehen. Aber wieso stellen die Clients (in meinem Fall PHP oder Perl) keine Binärdaten zur Verfügung? (Und jetzt kommt der Satz, den hier keiner hören will… :slight_smile: In MySQL ist das kein Problem.

Also noch mal, damit ich das richtig verstehe - ihr schreibt das Backup auf die Platte, leicht komprimiert. Dann unkomprimiert ihr das wieder und komprimiert das erneut? Und welche Maschine führt diesen zweiten Kompressionsschritt durch?

Wenn das eine andere Maschine ist, könnt ihr ja gleich die Daten über das Netzwerk ziehen. Wenn das die gleiche Maschine ist, dann ist das doch Overhead.



Zu den Clients: wer sagt, dass das nicht geht? Das hängt rein vom Client ab. Allerdings - da die Daten binär sind, benötigt man dafür spezielle Funktionen. Ist in MySQL wohl auch nicht anders.

Die eingebauten PHP Funktionen nutzen z. B. pg_escape_bytea. PDO liefert das als Stream, wenn ich das richtig in Erinnerung habe. Perl nutzt sowieso durchgehend prepared Queries, da werden die Daten einfach beim Aufruf samt Länge übermittelt.

Ja, richtig. Erst leicht komprimiert. Dann nochmal dekomprimieren und wieder stark komprimieren. Alles auf derselben Maschine (8 Kerne, 32 GB RAM), die sonst nie ausgelastet ist und mal was für ihr Geld tun soll. :slight_smile:

Der Grund für diese Vorgehensweise liegt darin, daß wir den Postgres-Prozess keine 6 Stunden mit dem Dump beschäftigen wollen. Im Grunde hängt die Problematik daran, daß es noch kein paralleles Komprimierungsprogramm gibt, das die Datenmenge in einer vernünftigen Zeit auf ein vernünftiges Maß komprimiert. Am aussichtsreichsten war in unseren Tests pbzip2 auf 6 Kernen (ca. 560 % CPU-Auslastung). Wenn man die anfängliche Geschwindigkeit hochrechnete, kam man auch auf ca. 2 Stunden. pbzip2 bricht aber wegen des eingebauten Sortieralgorithmus bei stark repetetiven Daten dramatisch ein und schrieb nur noch 2 MB / Minute. Inzwischen haben wir unsere Daten mit den verschiedensten Komprimierern getesten und heraus kam eben diese Lösung. Aber wir hoffen auf weitere Verbesserungen in diesem Segment.

Zu PHP:

Wir haben ein Applikation, die beispielsweise 20 JPEG-Bilder von jeweils 3 MB aus der DB holt und mit ImageMagick via Skalierung eine Vorschau berechnet.

Typischerweise sieht das Holen der Daten so aus:

$result = pg_query($db_conn, "SELECT bytecode(bytea) FROM doc WHERE id='$doc_id'");
$bytecode_escaped = pg_fetch_result($result, 0, 0);
$bytecode = pg_unescape_bytea($bytecode_escaped);

Wir haben mit microtime() gemessen, wo die Zeit verloren geht. Das unescapen benötigt bei 3 MB ca 0,1 Sekunde. Bei 20 Bildern sind das schon 2 Sekunden. Wenn Du also eine Idee hast, wie ich mir das unescapen ersparen kann, wäre ich sehr dankbar.

Das ist einfach: Bilder als Dateien im Filesystem speichern. Aber das willst Du ja nicht hören…

Andreas

Nun, der Datenbank ist es doch egal, ob sie 2 oder 5 Stunden für ein Backup braucht. Der entsprechende Prozess nimmt halt einen Kern deiner vielen CPUs in Anspruch und gut ist. Ich verstehe nicht, warum ihr hier soviel Aufstand darum macht.


Zur PHP Geschichte: nein, keine Ahnung. Aber offensichtlich dauert es einige Zeit, die ganzen Daten durch den Speicher zu bewegen. Und PHP ist da nicht das eleganteste, was man nutzen kann … Dazu kommt halt die Tatsache, dass man sich genau solche Probleme einhandelt, wenn man Dateien binär in einer Datenbank speichert. Dafür sind relationale Datenbanken schlicht nicht gedacht.


Schon mal überlegt, auf etwas wie CouchDB oder so umzusteigen, wenigstens für die Dateien? Wenn ihr schon die Dateien unbedingt in einer Datenbank und nicht im Filesystem haben müsst?