Migration von MariaDB zu PG: Problem mit Datentyp serial

[Parkside]

Ich möchte ein kleines Projekt mit 14 Tabellen von MariaDB zu PG migrieren. Nicht alle 14 Tabellen haben zum Glück Referenzen untereinander, aber mindestens eine wird in einer zweiten Tabelle über ihre id, die gleichzeitig primary key ist, per foreign key referenziert. Das bedeutet, daß die Spalte id nach der Migration dieselben Werte enthalten muß wie vorher.

Jetzt verstehe ich den Umgang mit dem Datentyp serial bei PG so, daß man ihn nur angeben kann, wenn die Spalte angelegt wird, da in diesem Moment die Sequenz kreiert wird. Wenn die Spalte erstmal existiert, kann ich nicht nachträglich z. B. von int auf serial wechseln. (Ist doch richtig soweit, oder?)

Ich habe jetzt also das Problem, die Tabelle so zu migrieren, daß alle Spalten und Zeilen hinterher die gleichen Werte haben, und insbesondere id beim Anlegen neuer Datensätze korrekt hochgezählt wird (auto-increment).

Meine Versuche fußen bislang auf Erfahrungen mit DBeaver und ein bißchen SQL-Arbeit in psql. So habe ich z. B. die Tabelle ex- und importiert (ein Schritt mit DBeaver), habe dann eine neue Spalte id_pq angelegt mit Datentyp serial, und dann füllt PG id_pq schön mit aufeinanderfolgenden Werten, die aber leider von der originalen id-Spalte abweichen, weil da mal Datensätze gelöscht worden sind und die Abfolge der id-Werte Löcher hat und nicht einmal bei 1 beginnt.

Habt Ihr Tips, wie ich das lösen kann? Ich bin noch ganz am Anfang, was das Kennenlernen von PG angeht, daher vielen Dank vorab für die Unterstützung!

 

[castorp]

serial sollte man nicht mehr verwenden, wegen einiger Problem mit den Berechtigungen der dahinter liegenden Sequence.

Definiere die Spalte als integer generated by default as identity.

Dann importiere die Daten und übernimm die "alten" ID Werte.

Danach musst Du die im Hintergrund verwendete Sequence auf den "aktuellen" Wert bringen. Das kann man mit der `setval()` Methode machen:

select setval(pg_get_serial_sequence('tabelle', 'id'), max(id))
from tabelle;

Das kann man auch automatisieren für alle Tabellen auf einmal

with sequences as (
  select *
  from (
    select table_schema,
           table_name,
           column_name,
           pg_get_serial_sequence(format('%I.%I', table_schema, table_name), column_name) as col_sequence
    from information_schema.columns
    where table_schema not in ('pg_catalog', 'information_schema')
  ) t
  where col_sequence is not null
), maxvals as (
  select table_schema, table_name, column_name, col_sequence,
          (xpath('/row/max/text()',
             query_to_xml(format('select max(%I) from %I.%I', column_name, table_schema, table_name), true, true, ''))
          )[1]::text::bigint as max_val
  from sequences
)
select table_schema,
       table_name,
       column_name,
       col_sequence,
       coalesce(max_val, 0) as max_val,
       -- das hier ist die Zeile die dafür sorgt, dass die Sequence aktualisiert wird
       setval(col_sequence, coalesce(max_val, 1)) as new_sequence_value
from maxvals;

 

[castorp]

Vielleicht eine kleine Erklärung zu dem langen Query:

Im ersten Teil (mit dem Namen "sequences") werden alle Tabelle geliefert die eine Spalte haben die über eine Sequence befüllt wird (egal ob serial oder identity).

Der Teil maxvals erzeugt für jede dieser Tabellen ein select max(..) from .... Die "Magie" hier, ist die Funktion query_to_xml() die eine SQL Abfrage ausführt und das Ergebnis als XML zurückgibt. So bekommt man für jede aus dem ersten Schritt gefundene Tabelle den höchsten Wert der entsprechenden Spalte.

Im abschließenden SELECT wird dann für jede dieser Tabellen und Spalten einmal das setval() aufgerufen um die Sequence anzugleichen.

Bei der ersten Ausführung würde ich empfehlen, das setval() erstmal wegzulassen und sich nur anzusehen, was die Abfrage zurückliefert

 

[Parkside]

Super! Hat auf Anhieb geklappt! Mit diesem Material muß ich jetzt mal weiterarbeiten, damit ich das vollständig verstehe und in Form eines Kochrezepts aufschreiben kann. Ich hatte schon an die Möglichkeit einer Interimstabelle gedacht, aber so wie von Dir vorgeschlagen ist das wirklich hochelegant. Danke!

 

[Parkside]

Vielleicht eine kleine Erklärung zu dem langen Query:

...

Bei der ersten Ausführung würde ich empfehlen, das setval() erstmal wegzulassen und sich nur anzusehen, was die Abfrage zurückliefert

Danke, das ist auch sehr hilfreich! Ich hatte jetzt im ersten Anlauf erstmal die kleine Lösung getestet, die umfassende Lösung muß ich in Ruhe studieren, da steckt ja eine Menge Know-How drin