Kontinuierliche Übertragung von MSSQL zu PostgreSQL

[Dautzinator]

Hallo zusammen,

ich freue mich hier heute meinen ersten Beitrag im Datenbanken-Forum zu stellen

Für meine Studienarbeit muss ich Daten aus einer MSSQL-Datenbank in eine PostgreSQL Datenbank übertragen. Die Situation ist die folgende:
- Es werden kontinuierlich weiterhin Daten in die MSSQL-Datenbank geschrieben. Diese Daten sollten idealerweise nahezu in Echtzeit in die PostgreSQL Datenbank übertragen werden. Die MSSQL Datenbank soll weiterhin laufen, es geht hier nicht um eine einmalige Migration.
- Die Daten in der MSSQL Datenbank werden nach ca. einem Monat gelöscht, d.h. es werden kontinuierlich alle Einträge die älter sind entfernt
- Die Daten in der PostgreSQL Datenbank sollen letztendlich in zwei Tabllen landen: im "Archiv" sollen sie einfach abgespeichert werden und nicht nach einem Monat gelöscht werden (also im Rahmen der Synchronisierung sollen keine Werte gelöscht werden). Im "Arbeitsdatensatz" soll wie in der MSSQL Datenbank nur der letzte Monat verfügbar sein.
- Die beiden Datenbanken existieren auf zwei unterschiedlichen Rechnern im gleichen LAN Netzwerk, es kann bspw. über die IP aufeinander zugegriffen werden.
- Ob die MSSQL Datenbank die Daten auf Postgres "pusht" oder Postgres von MSSQL "pullt" ist unerheblich, auf beiden Rechnern kann ich Programme installieren oder Änderungen an den Datenbanken vornehmen. Falls beides möglich wäre, würde ich auf der Postgres-Seite die Änderungen vornehmen.

Ich hatte schon etwas geschaut, habe aber kein Programm finden können, mit dem ich diese Situation lösen könnte. Ich hatte auch über ODBC Treiber nachgelesen, bin mir aber nicht sicher, ob sie sich für den Zweck eignen würden oder wie sie einzurichten sind. Falls ich irgendwo eine offensichtliche Lösung übersehen habe oder es in diesem Forum bereits eine Antwort auf meine Frage habt, verweist mich gerne an die entsprechende Stelle.

Es würde mich sehr freuen, wenn ihr hierzu Ideen habt

Vielen Dank und viele Grüße
Alex

 

[akretschmer]

es gibt da im Prinzip 2 Wege:

* Replikation von M$SQL nach PG
* in PG die Tabellen via FDW (Foreign Data Wrapper) einzubinden

für beide Wege solltest Du via Google diverse Anleitungen finden.

 

[ukulele]

Es wäre gut zu wissen welche MSSQL Version / Edition im Einsatz ist.

Grundsätzlich würde ich push empfehlen denn PostgreSQL weiß nicht, wann Daten geändert wurden, MSSQL schon. In Frage käme ein Trigger der aus MSSQL in die Postgres DB schreibt, das müsste über einen Verbindungsserver mittlerweile gehen. Erfahrung habe ich damit aber noch nicht, lesen über den Verbindungsserver ging auf jeden Fall.

In Postgres würde ich keine Archivtabelle anlegen und Datensätze rum verschieben, schon gar nicht aus MSSQL heraus. Ich denke da gibt es bessere Wege als die Daten zu teilen, ich bin aber auch kein Postgres Profi.

PS: Replikation ist natürlich ideal wenn das geht, das wusste ich nicht.

 

[akretschmer]

PS: Replikation ist natürlich ideal wenn das geht, das wusste ich nicht.

Es gibt zumindest eine kommerzielle Lösung aus dem Hause EDB ;-)

 

[castorp]

Es gibt zumindest eine kommerzielle Lösung aus dem Hause EDB ;-)

Für eine Replikation von SQL Server nach Postgres?

 

[akretschmer]

Für eine Replikation von SQL Server nach Postgres?

ich denke ja, kenne sie aber auch nicht. Falls Du mehr Infos brauchst kontaktiere mich bitte direkt.

 

[MDDaniel]

Wenn ich das richtig verstanden habe geht es um die Übertragung aus einer oder mehreren MSSQL Tabellen nicht um die gesamte Datenbank, richtig?
Und die Daten sollen von 1 Tabelle im MSSQL in 2 Postgres Schemas landen (Arbeitsdatensatz + Archiv), richtig?

 

[ukulele]

Wenn ich das richtig verstanden habe geht es um die Übertragung aus einer oder mehreren MSSQL Tabellen nicht um die gesamte Datenbank, richtig?
Und die Daten sollen von 1 Tabelle im MSSQL in 2 Postgres Schemas landen (Arbeitsdatensatz + Archiv), richtig?

So habe ich es verstanden wobei es glaube ich nicht in Schemas unterteilt sondern in zwei Tabellen unterteilt werden soll. Ich denke aber das PG das mit zwei Schemas oder anderweitig besser gelöst kriegt als mit zwei Tabellen.

Erstmal ist die Frage wie die Daten übermittelt werden. Da es sich im Wesentlichen um eine Tabelle MSSQL in eine Tabelle Postgres handelt und das möglichst "live" passieren soll finde ich einen Trigger gut der aus MSSQL bei Änderung über Verbindungsserver nach Postgres schreibt.

 

[akretschmer]

So habe ich es verstanden wobei es glaube ich nicht in Schemas unterteilt sondern in zwei Tabellen unterteilt werden soll. Ich denke aber das PG das mit zwei Schemas oder anderweitig besser gelöst kriegt als mit zwei Tabellen.

ich denke da an PARTITION BY ...

 

[MDDaniel]

ich denke da an PARTITION BY ...

@akretschmer

Ich bin da nicht soweit drin
... Postgres kann...
A) nur Teile (Sprich Tabellen) eine Datenbank replizieren?
B) in eine andere Tabelle replizieren wie die Sourctabelle (Arbeitsdaten -> Archivdaten)?
C) die Replikation für im Ziel keine Löschungen durch (Archivdaten)?

 

[akretschmer]

Postgresql kann 2 Arten von Replikation: streamin replication und logical replication.

Streaming repliziert komplett alles, logical Replication sendet letztlich SQL-Befehle an das Ziel. Dabei kann man definieren, was die Quelltabelle ist, was die Zieltabelle ist. Man kann eine oder mehrere Tabellen replizieren, alles kein Problem. Man kann auch nur bestimmte Spalten und auch nur bestimmte Zeilen replizieren, alles möglich.

 

[ukulele]

Partitionierung hatte ich auch noch im Sinn, habe ich nur nie angewendet. Auch ist das ja unabhängig von der Art, wie es von MS nach PG kommt, einsetzbar.

 

[akretschmer]

@akretschmer

Ich bin da nicht soweit drin
... Postgres kann...
A) nur Teile (Sprich Tabellen) eine Datenbank replizieren?
B) in eine andere Tabelle replizieren wie die Sourctabelle (Arbeitsdaten -> Archivdaten)?
C) die Replikation für im Ziel keine Löschungen durch (Archivdaten)?

ja, es kann via logical replication eine ganze Menge ... Chapter 31. Logical Replication

 

[castorp]

Naja, die (standard) Postgres Replikation kann aber nicht Daten von SQL Server zu Postgres replizieren.

Wenn die Daten in Echtzeit in Postgres ankommen sollen, dann wird das vermutlich nur eine Replikationslösung von SQL Server erreichen können, z.B. über SQL Server Integration Services.

Alternativ könnte das vielleicht ein Tool wie Debezium schaffen - das ist aber schon recht kompliziert aufzusetzen.

Man könnte auch via Foreign Tables in Postgres auf die Daten in SQL Server zugreifen. Die Foreign Tables wirken wie "normale" Tabelle in Postgres holen die Daten aber direkt von SQL Server ab. Man kann dann auf der Postgres Seite die Archivierung machen (insert into archive (...) select ... from foreign_table where ...). D.h. "sehen" würde man die Daten wirklich in Echtzeit (weil sie ja direkt vom SQL Server gelesen werden). Die Archivierung müsste man dann über eine Cron Job oder ähnliches erledigen.

Falls Postgres allerdings nicht auf Linux läuft wird das Installieren des nötigen [foreign data wrappers](https://github.com/tds-fdw/tds_fdw) aber vermutlich ziemlich kompliziert.

 

[t-sql]

Es gibt zumindest eine kommerzielle Lösung aus dem Hause EDB ;-)

Nee, kostenlos ist viel besser. Kommerziell ist doch böse, wie du allen mit deiner kindlichen Schreibweise von MSSQL schön glauben machen willst.

@Dautzinator: Am einfachsten ist es natürlich das ganze über den SQL Server Agent mit einem Job abzubilden. Einfach einen Linked Server einrichten (SQL Server and PostgreSQL Linked Server Configuration - Part 2) und das entsprechende T-SQL Kommando absetzen um die Daten rüber zu bekommen. Oder Du machst es wie @ukulele schrieb über einen Trigger. Kommt aber natürlich auf die zu übertragenden Daten an. Um wieviele Daten handelt es sich denn? Was ich persönlich nicht glaube, ist das die Daten in "Echtzeit" übertragen werden müssen. Gerade bei Archiv Daten ist das eigentlich nicht notwendig.