Daten transferieren: bcp, COPY, pgloader, ETL — welche Methode wann

Daten umziehen klingt nach der einfachsten Phase der Migration: Die Tabelle steht, die Daten rein, fertig. Bis die erste größere Tabelle einen ganzen Nachmittag braucht — weil sie Zeile für Zeile per INSERT läuft statt in einem Rutsch per COPY. Wer Daten nach PostgreSQL migrieren will, hat vier Wege zur Auswahl, und die Methodenwahl entscheidet über Stunden statt Minuten.

Anders als bei Datentypen und Schema gibt es hier keine Übersetzungstabelle, sondern eine Entscheidung — welches Werkzeug zu Datenmenge, Downtime-Toleranz und Transformationsbedarf passt.

Das Wichtigste vorab:

• Vier Methoden: bcp+COPY (die Flatfile-Brücke), pgloader (der Direkt-Automat aus MSSQL), ETL/SSIS (Transformation unterwegs) und die manuelle CSV-Brücke für kleine Tabellen.
• Die Wahl triffst du über Datenmenge × Downtime-Toleranz × Heterogenität — nicht über Geschmack.
• COPY schlägt INSERT um etwa eine Größenordnung: Indizes vor dem Load entfernen, danach neu bauen, ANALYZE nicht vergessen.
• Nach dem Load kommen die leicht vergessenen Schritte: Sequenz-Reset, Constraint-Revalidierung, Statistiken.

Voraussetzung: SQL Server als Quelle, PostgreSQL 14+ als Ziel. bcp, BULK INSERT und SSIS dürfen als SQL-Server-Wissen vorausgesetzt werden; die Postgres- und Tool-Pendants (COPY, \copy, pgloader) werden bei der ersten Nennung eingeordnet. Dass das Ziel-Schema bereits steht, ist hier Annahme — die DDL-Seite behandelt der Schwester-Artikel zur Schema-Migration, das reine Spalten-Typ-Mapping der Artikel zum Datentyp-Mapping.

Inhalt

• Daten nach PostgreSQL migrieren: die vier Wege
• Entscheidungsmatrix: Menge, Downtime, Heterogenität
• COPY schlägt INSERT — die Load-Performance-Hebel
• Was pgloader abnimmt — und wo es endet
• Nach dem Load: nicht vergessen
• FAQ
• Verwandte Artikel

Daten nach PostgreSQL migrieren: die vier Wege

Die Transfer-Methoden lassen sich auf vier Muster reduzieren. Sie unterscheiden sich nicht im Ergebnis, sondern im Aufwand, in der Kontrolle und darin, wie viel sie automatisch erledigen.

• bcp + COPY — die Flatfile-Brücke. SQL Server exportiert mit bcp … queryout in eine Textdatei, PostgreSQL liest sie mit COPY … FROM wieder ein. Maximale Kontrolle über Format, Delimiter und Encoding, dafür zwei Schritte und eine Zwischendatei. Der robuste Klassiker für homogene Bulk-Transfers, wenn man genau wissen will, was passiert.
• pgloader — der Direkt-Automat. Ein einziges Kommando liest direkt aus der MSSQL-Datenbank, legt die Zieltabellen an, castet die Typen und lädt die Daten — ohne Zwischendatei. Der 80-Prozent-Weg für Standard-Fälle; wo er an Grenzen stößt, steht weiter unten.
• ETL (SSIS oder Custom) — Transformation unterwegs. Wenn die Daten beim Umzug ihre Form ändern müssen (Spalten aufteilen, Werte normalisieren, mehrere Quellen zusammenführen), ist ein ETL-Prozess das richtige Werkzeug. Reiner Transfer ist das nicht mehr — es ist eine Verarbeitung, die zufällig ein Ziel-System wechselt.
• Manuelle CSV-Brücke — für kleine Tabellen. Export über das SSMS-GUI oder eine SELECT-Abfrage in eine CSV, Import über \copy in psql. Für Stammdaten, Lookup-Tabellen und Ad-hoc-Umzüge unter ein paar tausend Zeilen völlig ausreichend — und in fünf Minuten erledigt.

Die ersten beiden Methoden decken den Großteil echter Migrationen ab. ETL kommt ins Spiel, sobald „kopieren“ zu „umbauen“ wird; die CSV-Brücke ist die pragmatische Abkürzung für alles Kleine.

Entscheidungsmatrix: Menge, Downtime, Heterogenität

Die Methodenwahl hängt an drei Achsen: wie viele Daten, wie viel Stillstand verträgst du, und müssen die Daten unterwegs umgeformt werden. Daraus ergibt sich fast immer eine klare Empfehlung.

Zwei Lesehilfen zur Tabelle. Erstens: Die Downtime-Achse ist hier nur grob abgebildet. Alle vier Methoden setzen ein Stillstands-Fenster voraus, in dem die Quelle nicht weiter beschrieben wird — sonst migrierst du einen inkonsistenten Stand. Echtes Migrieren bei laufendem Betrieb (logische Replikation, CDC, Dual-Write) ist ein eigenes, größeres Thema und bekommt einen separaten Artikel. Zweitens: Die Zeilen schließen sich nicht aus. Eine reale Migration nutzt oft pgloader für die große Masse und die CSV-Brücke für drei Sondertabellen, die der Automat nicht sauber trifft.

COPY schlägt INSERT — die Load-Performance-Hebel

Egal welche Methode: Beim Laden entscheidet ein einziger Mechanismus über Minuten gegen Stunden. Ein einzelner INSERT ist eine eigene kleine Transaktion mit Parsing, Planung und WAL-Eintrag. Eine Million davon ist eine Million Mal dieser Overhead. COPY dagegen streamt die Zeilen in einem Rutsch in die Tabelle — dieselben Daten landen oft um eine Größenordnung schneller im Ziel. Wer Daten nach PostgreSQL migrieren will, lädt also per COPY, nicht per generierten INSERT-Statements.

Mit COPY allein ist es aber nicht getan. Drei weitere Hebel beschleunigen den Load spürbar:

• Indizes und Fremdschlüssel vor dem Load entfernen, danach neu bauen. Jeder Index muss pro eingefügter Zeile gepflegt, jede Fremdschlüssel-Prüfung pro Zeile ausgeführt werden — bei Millionen Zeilen ist beides teurer als ein einziger Aufbau am Ende über den fertigen Datenbestand. NOT NULL– und CHECK-Constraints lässt man dagegen meist stehen; sie kosten beim Load kaum etwas und fangen kaputte Zeilen früh ab. Vorsicht beim inkrementellen Load: Dort lohnt das Drop-and-Recreate nicht, weil die Recreate-Zeit über den ganzen Bestand läuft, nicht nur über die neuen Zeilen.
• UNLOGGED-Zwischentabelle. Eine UNLOGGED-Tabelle schreibt kein WAL und lädt damit deutlich schneller — der Preis ist, dass ihr Inhalt einen Crash nicht übersteht. Als Staging-Tabelle für den Import ist das ideal: erst per COPY befüllen, dann nach Prüfung in die echte (geloggte) Zieltabelle übernehmen. Dass dieser zweite Schritt wieder WAL schreibt, ist eingepreist — der teure Roh-Load lief WAL-frei, und das Umkopieren fällt einmalig und gebündelt an.
• maintenance_work_mem hochsetzen, ANALYZE nicht vergessen. Ein großzügiges maintenance_work_mem (etwa 512MB, je nach verfügbarem RAM) beschleunigt den nachgelagerten Index-Aufbau erheblich. Und nach dem Load braucht der Planner frische Statistiken — ohne ANALYZE plant PostgreSQL die ersten Abfragen auf Basis einer leeren Tabelle und wählt katastrophale Pläne.

Als Muster zusammengesetzt sieht der schnelle Load so aus:

  1: -- 1. Zwischentabelle ohne WAL-Overhead (UNLOGGED) anlegen.
  2: CREATE UNLOGGED TABLE staging_sales (LIKE sales INCLUDING DEFAULTS);
  3:
  4: -- 2. Bulk-Load per COPY statt vieler INSERTs.
  5: COPY staging_sales FROM '/var/lib/postgresql/import/sales.csv'
  6:    WITH (FORMAT csv, HEADER);
  7:
  8: -- 3. Erst nach dem Load die Indizes bauen — einmal statt pro Zeile.
  9: CREATE INDEX ix_sales_customer ON staging_sales (customer_id);
 10:
 11: -- 4. Planner-Statistiken aktualisieren, sonst drohen schlechte Pläne.
 12: ANALYZE staging_sales;

  1: -- 1. Zwischentabelle ohne WAL-Overhead (UNLOGGED) anlegen.
  2: CREATE UNLOGGED TABLE staging_sales (LIKE sales INCLUDING DEFAULTS);
  3:
  4: -- 2. Bulk-Load per COPY statt vieler INSERTs.
  5: COPY staging_sales FROM '/var/lib/postgresql/import/sales.csv'
  6:    WITH (FORMAT csv, HEADER);
  7:
  8: -- 3. Erst nach dem Load die Indizes bauen — einmal statt pro Zeile.
  9: CREATE INDEX ix_sales_customer ON staging_sales (customer_id);
 10:
 11: -- 4. Planner-Statistiken aktualisieren, sonst drohen schlechte Pläne.
 12: ANALYZE staging_sales;

Ein Wort zum Datei-Pfad, das sonst eine Stunde Fehlersuche kostet: COPY … FROM 'datei' läuft server-seitig — die Datei muss auf dem Datenbank-Server liegen und für den Postgres-Prozess lesbar sein. Wer von seinem Arbeitsplatz aus lädt, nimmt stattdessen \copy in psql; das liest die Datei client-seitig und schickt sie über die Verbindung. Gleiche Syntax, aber \copy braucht keinen Server-Zugriff auf das Dateisystem. Die Verwechslung ist der häufigste Stolperstein beim ersten Flatfile-Load.

Die Flatfile-Brücke selbst besteht aus zwei Kommandos. Auf der SQL-Server-Seite exportiert bcp:

bcp "SELECT customer_id, region_id, email, credit_limit, created_at FROM dbo.customer" ^
    queryout customer.csv -c -t"|" -C 65001 -S localhost -d SourceDb -T

bcp "SELECT customer_id, region_id, email, credit_limit, created_at FROM dbo.customer" ^
    queryout customer.csv -c -t"|" -C 65001 -S localhost -d SourceDb -T

Auf der PostgreSQL-Seite liest COPY wieder ein:

  1: -- Server-seitig: die Datei muss auf dem DB-Server liegen.
  2: COPY customer (customer_id, region_id, email, credit_limit, created_at)
  3: FROM '/var/lib/postgresql/import/customer.csv'
  4:    WITH (FORMAT csv, NULL '', ENCODING 'UTF8', DELIMITER '|');

  1: -- Server-seitig: die Datei muss auf dem DB-Server liegen.
  2: COPY customer (customer_id, region_id, email, credit_limit, created_at)
  3: FROM '/var/lib/postgresql/import/customer.csv'
  4:    WITH (FORMAT csv, NULL '', ENCODING 'UTF8', DELIMITER '|');

An drei Stellen kippt die Flatfile-Brücke immer wieder:

• Encoding — bcp -w schreibt UTF-16, das COPY mit ENCODING 'UTF8' nicht lesen kann; deshalb oben -c -C 65001 für UTF-8.
• NULL-Marker — bcp schreibt für NULL einen leeren String, den COPY über NULL '' wieder als NULL interpretieren muss.
• Delimiter in Textfeldern — ein Komma als Trenner kippt, sobald eine Adresse selbst ein Komma enthält; deshalb oben | statt ,.

Was pgloader abnimmt — und wo es endet

pgloader ist das Werkzeug, das die meiste Handarbeit abnimmt. Ein einziges Kommando — oder eine kleine Konfigurationsdatei — verbindet sich mit der MSSQL-Quelle und der PostgreSQL-Zieldatenbank und erledigt in einem Lauf — je nach aktivierten WITH-Optionen — Zieltabellen anlegen, Datentypen casten, Daten per COPY laden, Indizes und Fremdschlüssel aufbauen und — der Bonus gegenüber der Handarbeit — die Sequenzen zurücksetzen.

Eine typische Konfigurationsdatei sieht so aus:

LOAD DATABASE
     FROM     mssql://migrator@mssql-host/SourceDb
     INTO postgresql://migrator@pg-host/targetdb

 WITH include drop,
      create tables,
      create indexes,
      reset sequences,
      foreign keys,
      downcase identifiers,
      workers = 8, concurrency = 1

  SET maintenance_work_mem to '512MB',
      work_mem to '64MB'

 CAST type datetime to timestamptz,
      type money    to numeric;

LOAD DATABASE
     FROM     mssql://migrator@mssql-host/SourceDb
     INTO postgresql://migrator@pg-host/targetdb

 WITH include drop,
      create tables,
      create indexes,
      reset sequences,
      foreign keys,
      downcase identifiers,
      workers = 8, concurrency = 1

  SET maintenance_work_mem to '512MB',
      work_mem to '64MB'

 CAST type datetime to timestamptz,
      type money    to numeric;

Die WITH-Optionen sind die Stärke: reset sequences erledigt automatisch den Sequenz-Reset, den man bei der Handarbeit so leicht vergisst; downcase identifiers setzt die Bezeichner auf Kleinschreibung (aus CustomerOrders wird customerorders — kein automatisches snake_case, aber genau das quoting-freie Kleinschreiben, das Postgres idiomatisch erwartet); include drop macht den Lauf wiederholbar. Die CAST-Regeln greifen dort ein, wo die Default-Konvertierung nicht passt.

So weit der Happy Path. Es gibt allerdings vier Stellen, an denen pgloader an seine Grenzen stößt — Dinge, für die es schlicht nicht gemacht ist:

• Prozedur-Logik bleibt liegen. Stored Procedures, Funktionen und Trigger überträgt pgloader nicht. Das ist eine eigene Disziplin und Thema des Artikels zur Code-Portierung.
• Typ-Edge-Cases brauchen eigene CAST-Regeln. Die heiklen Konvertierungen — money, datetime2, uniqueidentifier, bit — trifft die Automatik nicht immer so, wie man es will. Welche Typen sauber konvertieren und welche kippen, behandelt der Artikel zum Datentyp-Mapping.
• Performance-Tuning bleibt manuell. pgloader lädt brauchbar, aber das Feintuning sehr großer Tabellen (Partitionierung, parallele Worker, Storage-Parameter) nimmt es einem nicht ab.
• Komplexe Schema-Entscheidungen ignoriert es. Den IDENTITY-Mechanismus, benannte Constraints oder den Wegfall des Clustered Index entscheidet pgloader nach Schema F — die bewussten Entscheidungen dazu trifft die Schema-Migration.

Kurz: pgloader ist ein exzellenter Startpunkt, keine Komplettlösung. Für die große Masse homogener Tabellen spart es Stunden; die anspruchsvollen 20 Prozent bleiben Handarbeit.

Nach dem Load: nicht vergessen

Der Load ist durch, die Zeilen sind drin — und hier hören viele Migrationen einen Schritt zu früh auf. Vier Nacharbeiten gehören dazu:

• Sequenz-Reset. Wer die alten Schlüsselwerte mitgeladen hat, muss den Identitäts-Zähler auf den höchsten vergebenen Wert heben, sonst kollidiert der erste produktive INSERT. pgloader erledigt das mit reset sequences automatisch; bei bcp+COPY und der CSV-Brücke ist es Handarbeit. Den Mechanismus (setval(pg_get_serial_sequence(...))) und die Falle dahinter beschreibt der Artikel zur Schema-Migration im Detail.
• Constraints revalidieren. Wurden Fremdschlüssel für den schnellen Load deaktiviert oder NOT VALID angelegt, müssen sie danach mit ALTER TABLE … VALIDATE CONSTRAINT … geprüft werden — sonst trägt die Datenbank ungeprüfte Referenzen.
• ANALYZE auf Datenbank-Ebene. Über den ganzen geladenen Bestand einmal ANALYZE (oder vacuumdb --analyze) laufen lassen, damit der Planner für alle Tabellen frische Statistiken hat.
• Erste Plausibilität. Ein schneller Zeilen-Abgleich (count(*) Quelle gegen Ziel) fängt grobe Lade-Fehler sofort ab. Die richtige, belastbare Verifikation — Prüfsummen, Stichproben, Datenqualität — ist ein eigener Schritt und bekommt einen eigenen Artikel.

FAQ

Verwandte Artikel

Dieser Artikel ist Teil einer Serie zur Migration von SQL Server nach PostgreSQL. Die übrigen Teile:

• Überblick: Datenmigration SQL Server nach PostgreSQL — der vollständige Leitfaden (folgt)
• Datentypen: Datentyp-Mapping SQL Server → PostgreSQL — was sauber konvertiert und was kippt
• Schema: Schema-Migration SQL Server → PostgreSQL — Identity, Constraints, Defaults, Sequenzen
• Code-Portierung: T-SQL nach PL/pgSQL portieren — Prozeduren und Funktionen
• Verifikation: Migration verifizieren — Datenqualität und Zeilen-Abgleich (folgt)
• Downtime-arm: Strategien für den Cutover bei laufendem Betrieb (folgt)

Zum Transfer als ETL-Schritt:

• ETL vs. ELT — woran du erkennst, welches Muster du wirklich gebaut hast
• Design Pattern // Architektur eines ETL-Prozesses — wie sich schlechte Daten sauber isolieren lassen
• SSIS vs. SQL — wann SSIS, wann reines T-SQL, wann beides kombinieren?