Kann DB-Dump nicht importieren

Ich habe einen Dump einer DB mittels pg_dump im Plain-SQL-Format erstellt (enthält Blobs). Ich versuche diesen Dump in eine DB auf einer anderen Maschine einzuspielen. Der Dump wurde mit --clean --blobs … erstellt.

Beim Einspielen auf der Zielmaschine erhalte ich die Fehlermeldung:
:5471459: FEHLER: doppelter Schlüsselwert verletzt Unique-Constraint »pg_largeobject_metadata_oid_index«
DETAIL: Schlüssel »(oid)=(999995)« existiert bereits.
ALTER LARGE OBJECT

Beide Tabellen pg_largeobject bzw. pg_largeobject_metadata_oid_index sind leer. Schaut man sich das aktuelle Statment mit pg_stat_activity an, so fällt auf, dass nach einem COPY stehts ein ROLLBACK erfolgt. Es wird keine Tabelle angelegt bzw. irgendwelche Daten importiert.

Ich habe die DB gelöscht und neu erzeugt. Leider ändert sich beim Importversuch nichts.

Ich erhalte seltsame Fehlermeldungen beim Import in eine leere! Datenbank:
11037278: FEHLER: Wert zu lang für Typ character varying(100)
10988273: FEHLER: doppelter Schlüsselwert verletzt Unique-Constraint beim Index

Pg_dump sollte doch die notwendigen Infos ins SQL-File (wenn mit --clean Option ausgeführt) schreiben, damit die SQL-Statements ohne Probleme ausgeführt werden können. Beide DB sind Version 16.1.

Gibt es eine Möglichkeit so was zu reparieren oder hilft hier nur eine komplette Neuinstallation der DB/des DBServers/des Datenbereichs ?

Schau Dir mal dieses Tool an, vielleicht hilft das bei der Klärung / Bereinigung.
Du kannst auf beiden Systemen, also Quelle und Ziel mal schauen, was dort ausgegeben wird, ohne dass Du etwas änderst mit dem Schalter:

-n
–dry-run
Don’t remove anything, just show what would be done.

Die --clean Option löscht (DROP) nur Benutzertabellen, nicht aber Systemtabelle (wie pg_large_object). Offensichtlich waren vorher large objects drin, aber die werden nicht automatisch aufgeräumt, wenn Du die referenzierenden Datensätze löscht (mit einer der Gründe warum man eigentlich niemals large objects verwenden sollte).

Du musst also vor dem Import die noch existierenden large objects manuell löschen, das von pogomips erwähnte Tool kann dabei helfen.

Wenn Du Dir 100% sicher bist, dass wirklich keines der noch vorhandenen large objects benötigt wird, kannst Du auch alle direkt löschen, z.B. so:

select lo_unlink(oid)
from pg_largeobject_metadata;

(Achtung: ungetestet)

Mittel- bis langfristig solltest Du Dir überlegen, wie Du die Verwendung der large objects durch eine andere Lösung ersetzen kannst.

Den Verdacht mit kollidierenden oids der Large Objects, den hatte ich auch.

Aber wie vermeidet man die Nutzung von large objects wenn das Datenmodell clobs und blobs in der Datenbank vorsieht ? Die werden doch automatisch zu large objects. Oder gibt es hier einen anderen Weg diese in der DB zu speichern ohne sie jeweils als Datei auszulagern und nur die Referenz darauf in der DB aufzunehmen ?

Wenn die nicht größer als 1GB werden, nimm text und bytea

Das ist in meinem Fall der Fall. Es wird text für clob und bytea für blob verwendet. Jede darin gespeicherte Datenmenge ist kleiner 10 MByte.
Wenn ich eine Tabelle mit pg_dump im Plain-SQL dumpe und wieder einlese erscheinen Meldungen lo_unlink(xxxxx) und dann create_lo(xxxx) oder so ähnlich.

Wie verwende ich bytea und text ohne dass daraus ein large object zu erzeugen ? Was macht der Schalter --blobs bei pg_dump genau ? Ist das der Grund dass daraus beim Dump ein large object erzeugt wird ? Ich habe das immer so interpretiert, dass damit die Daten in den Dump übernommen werden.

Was für Werte stehen denn in den Spalten die als text oder bytea definiert wurden drin? Wenn z.B. in der Textspalte nur eine Zahl drin steht, wurde sie zwar als text angelegt, aber verwendet wird sie wie ein large object (dem large object is es egal wie oder ob die ID noch irgendwo anders abgespeichert wurde).

Verwendest Du Hibernate? Das macht so einen Blödsinn wenn man die @Lob Annotation verwendet.

In bytea stehen binäre Daten z.B. Bilder(JPG,PNG), PDFs, XLSX usw. In text stehen lange Texte (z.B. XML, JSON Dateien).

Ich verwende Hibernate ja und nein. Es geht hier primär um eine Datenbank, die nicht durch Hibernate erzeugt wurde.

Also wenn Du gar keine large objects verwendest, wo kommen die da her?

Hast Du mal wirklich mit SQL reingesehen was wirklich in den Spalten steht? Nicht, dass was die Anwendung anzeigt?

Wenn der Fehler mit den doppelten Schlösseln auch beim Import in eine leere Datenbank kommt ist das oberfaul. Aber es sollte auch bei einer Datenbank, in der schon Daten sind, nicht passieren.

So ein Dump schaut so aus:

...
SELECT pg_catalog.lo_unlink('45565');
SELECT pg_catalog.lo_unlink('45379');
...
SELECT pg_catalog.lo_create('45379');
ALTER LARGE OBJECT 45379 OWNER TO ...;

SELECT pg_catalog.lo_create('45565');
ALTER LARGE OBJECT 45565 OWNER TO ...;
...
SELECT pg_catalog.lo_open('45379', 131072);
SELECT pg_catalog.lowrite(0, '\x...');
SELECT pg_catalog.lo_close(0);

SELECT pg_catalog.lo_open('45565', 131072);
SELECT pg_catalog.lowrite(0, '\x...');
SELECT pg_catalog.lo_close(0);

Die einzige Art, wie ich mir vorstellen könnte, daß es zu dieser Fehlermeldung kommt, wären zwei Large Objects in der Originaldatenbank mit der gleichen oid. Ist das im gegebenen Fall so? Wenn ja, dann bedeutet das Datenkorruption auf der Originaldatenbank. Man muß eines der beiden Objekte löschen und ein REINDEX TABLE pg_largeobject; durchführen. Dann wäre ein Dump und Restore empfehlenswert.

Ich konnte das Problem lösen. Grund war ein korruptes Dumfile. Nachdem ich die DB mit vacuumdb und vacuumlo bereinigt hatte konte ich diese exportieren und in eine neue “leere” ohne Fehler importieren.

Grund war die Mißinterpretation des Schalters --large-objects mit pg_dump. Ich bin davon ausgeganen, dass damit diese objects (Spalteninhalte vom type bytea bzw. text) in den Dump übernommen werden. Scheinbar bewirkt der Schalter aber, dass diese Inhalte in large-objects umgewandelt und in den Dump geschrieben werden. Expotiert man mit dem Schalter -B bzw. --no-large-objects, dann tauchen solche Fehler beim Import nicht auf.

Bei einem Versionswechsel habe ich alle Datenbanken in Unwissenheit mit dem Schalter --large-objects exportiert und dann wieder importiert. Das hat alles super funktioniert bis ich eine Tabelle aus einer anderen DB (enthielt text und bytea Spalten) importieren wollte. Das geht scheinbar nicht, weil oid(s) für die large objects teilweise doppelt vergeben sind (in der Orgainaldatenbank und in dem Tabellenimport). Irgendwie hat es mir dann die DB und den Dump daraus “korrumpiert”.

Wie schon erwähnt export mit Schalter --no-large-objects und anschließendem Import sowie der Import einer einzelen Tabelle aus einer anderen Datenbank hat funktioniert. Keine Konflikte und alle Daten sind wie gewünscht in der Datenbank.

Vielleicht sollte man mal an der Stelle die Dokumentation von pg_dump etwas genauer fassen. Oder bin ich den einzige Anwender der das falsch verstanden hat ?

Gruß
Thomas

Die Option --large-objects (oder gleichbedeutent --blobs) wandelt nicht bytea order text in Large Objects um. Sie bewirkt nur, daß auch Large Objects exportiert werden. Large Objects werden automatisch exportiert, wenn man die ganze Datenbank exportiert, aber wenn man nur Teile einer Datenbank exportiert (etwa mit der --schema=schemaname oder --table=tabellenname), muß man explizit --large-objects sagen, damit auch die Large Objects exportiert werden.

Insofern denke ich, daß du die Dokumentation richtig verstanden hast, und daß es sehr wohl Large Objects in der Datenbank gibt, und daß mit denen etwas nicht stimmt. Dann vermeidet die Nichtverwendung von --large-objects den Fehler, aber Teile der Datenbank werden nicht exportiert. Wenn die Large Objects niemandem fehlen, dann ist es ja gut, aber ich würde das genau überprüfen, denn vielleicht werden sie ja irgendwo benötigt…

Ich würde dem auf den Grund gehen wollen und zum Beispiel einmal nur die Large Objects exportieren:

pg_dump --schema=pg_toast --large-objects --user=postgres --file=lobdump datenbank

Dann würde ich schauen, ob in dem Ergebnis die Zahl 999995 mehrmals vorkommt, wie ich es mir erwarte.

Vielleicht ist es auch interessant, sich an die Originaldatenbank mit psql zu verbinden und eines der Large Objects als Datei zu exportieren:

\lo_export 999995 dateiname

Dann kann man untersuchen, was für eine Art Datei da herauskommt (etwa mit file dateiname auf Linux) und was da drinnensteht. Das erleichtert vielleicht die Überlegung, ob diese Large Objects irgendwo benötigt werden oder nicht.

Ich habe im Rahmen meiner Tests die importierten BInärdateien stichpunktartig geprüft. Zudem habe ich MD5Hash über die Inhalte ermittelt und mit denen einer alten Sicherung verglichen. Alle Hashwerte waren identisch.

Laut den Tabellen pg_largeobject bzw. pg_largeobject_metadata habe ich keine solche Objekte in der Datenbank (keine Einträge).

Ich habe eine Lösung gefunden die funktioniert. Ich denke man kann das Thema hier schließen.