Daten von einem Sever auf einem anderen Server integrieren

Konfiguration & Verwaltung
1

Guten Tag zusammen,

ich hoffe ich kann mein Problem richtig erläutern.

Ich habe an einem Standort meinen Master Server mit PostgreSQL 9.6 der hier lokal mit Daten befüllt wird. Nun habe ich an anderen Standorten lokale Server die auch mit Daten befüllt werden (auch 9.6). Nun möchte ich gerne regelmäßig die Daten von den anderen Standorten gerne auf meinen lokalen Master Server replizieren. Am liebsten immer nur die Änderungen die es seit der letzen Replikation gab.

Ist dies möglich und wie geht man da am besten vor? Am besten Multi Master Replikation?

Vielen Dank

2

Mit 9.6 schwer, logical replication mit pglogical von 2ndQuadrant, jetzt EDB, kann das, aber 9.6 ist out of support.

Prinzipell geht das, was Du willst, ab 10 aber via logical replication, aber NICHT als Primary-Primary. Wenn Du das brauchst, brauchst Du BDR (BiDirectional Replication). Das kostet allerdings etwas.
Wenn Du dazu mehr wissen willst, kontaktiere mich unter andreas . kretschmer @ enterprisedb . com

3

danke für die schnelle Antwort.
Besteht auch die Möglichkeit das alles über Dump zu machen? So dass ich von den anderen Servern immer einen Dump auf den Hauptserver einspiele?

4
  • wie willst Du nur neue Datensätze dumpen?
  • wie willst Du ID’s generieren?
  • was passiert mit Dubletten?
  • was passiert mit gelöschten / geänderten Datensätzen?

Multi-Master - Replikation ist sehr, sehr komplex …

5

ok ja habe schon festgestellt, dass das Ganze nicht so ohne ist. Vorallem wenn man noch neu in dem Thema ist wie ich.

Ich frage mich halt ob es eine “einfache” Art gibt wie ich Daten von einer Datenbank die auf einem Server 1 sind auf eine Datenbank schreiben kann die auf einem Server 2 sind.
Ich würde nie die ganze Datenbank von Server 1 nehmen sondern immer nur die neu dazugekommenen Daten.
Wollte da nicht zu viel Aufwand betreiben, da dies nur eine vorrübergehende Lösung sein wird und später ersetzt wird.

Die Frage die ich mir Stelle brauche ich dafür Replikation oder lässt sich dies mit Backup/Dump lösen.

6

wenn Du eine Netzwerkverbindung hast ist Replication mittel der Wahl.

  • streaming replication: Master & Standby, Standby ist read only, ALLES wird repliziert
  • logical replication: in eine Richtung, aber Du kannst definieren, was repliziert werden soll, beide Seiten schreibbar
  • BDR: wie logical, aber in alle Richtungen (Mesh), wahlweise synchron oder asynchron
1 „Gefällt mir“
7

ok super viele Dank.

Dann muss ich mir das nochmal genauer anschauen.

8

Guten Morgen,

ich habe mir nun nochmal Gedanken gemacht und mich gefragt ob ich eventuell mit Replikation auf dem falschen Weg bin.
Gibt es denn eine Möglichkeit Daten die sich in einem Schema auf Server 1 befinden in das gleiche Schema auf einem Server 2 zu integrieren ohne, dass da etwas gelöscht/überschrieben wird?

Danke

9

Was verstehst Du unter ‘integrieren’ ohne daß etwas gelöscht/überschrieben wird?

  • was soll bei Constraint-Verletzungen (duplicate key, unique) passieren?
  • was soll bei Integritätsverletzungen (referentielle Integrität) passieren?

Wie ich Dir schon schrieb: Du kannst logical replication nutzen.

10

aber logial replication gibt es erst ab Postgresql 10 oder existiert das auch bei 9.6 ?

ich denke ich steh da noch etwas auf dem Schlauch.
Replikation heißt ja es entsteht eine Kopie von einem Server.
Ich will ja nun aber keine Kopie sondern nur die Daten die ich auf vielen anderen Server habe auf einen Hauptserver. Und das regelmäßig automatisch anstoßen. Wobei auf den anderen Servern immer manuell nur das rein kopiert wird was ich dann auf den Hauptserver schicken will.

ich hoffe ich habe das verständlich erklärt. Sollte halt mit 9.6 laufen.

11

es gibt pglogical für 9.6, aber mal sollte kein 9.6 mehr für was neues verwenden.

Es gibt andere Implementationen für logical replication, Slony funktionierte schon für 7er Versionen. Ist TRIGGER-based und sowas von alt, …

12

ok vielen dank ja im Moment können wir nur 9.6 verwenden da unser Klient nur mit 9.6 klar kommt.

13

halte ich für relativ unwahrscheinlich, daß ein Client mit 9.6 kann und mit 10 oder höher nicht mehr. In 11 sind OIDs entfernt worden, aber wenn das ein Problem ist hast Du noch ganz andere Probleme …

14

ok danke.

Was muss man beachten wenn man nun von einem Schema A mit Insert Daten in das Schema B “kopiert”?

15

deine Frage ist zu nebulös …

16

Bei physischer Replikation ist das auch wirklich so: man erstellt eine exakte 1:1 Kopie der Quelle. Unter Strich werden dabei - sehr vereinfacht ausgedrückt - die Blöcke aus dem Dateisystem des einen Servers zu dem anderen kopiert, dabei kann aber nicht gefiltert werden.-

Bei logischer Replikation werden aber einzelne Datensätze (bzw. Änderungen an diesen) repliziert. Im Grunde wir der SQL Befehl (UPDATE, DELETE usw) einfach auf dem anderen Server nochmal ausgeführt. Damit kann man nur bestimmte Tabelle replizieren. Und die Zieldatenbank darf zum Schreiben anderer Daten verwendet werden.

Grundsätzlich ist ein Setup wie Du es Dir vorstellst, am einfachsten zu lösen, wenn für alle Tabelle Primärschlüssel verwendet werden, die “global” eindeutig sind - z.B. UUIDs

Wenn man Daten in zwei Richtungen replizieren will, bekommt man ja ganz schnell das Problem, dass potentiell der gleiche Datensatz auf beiden Seiten angelegt wird. Wenn dieser dann repliziert wird, dann knallt es halt. Dann muss man sich überlegen wie man mit diesen Konflikten umgeht.

Ein global eindeutiger Schlüssel vermeidet erstmal diesen technischen Fehler. Dann hat man allerdings immer noch das Problem, dass logisch/fachlich der gleichen Datensatz zweimal existiert - welcher ist denn nur “der richtige”?

Wenn das System aber so gestaltet ist, dass dieser logische Fehler nicht auftreten kann, dann kann man mit UUIDs den technischen Fehler vermeiden. Nimmt man hingegen “normale” Integer Werte die über eine Sequenz erstellt werden, wird es mit Sicherheit über kurz oder lang ein Problem geben. Hauptsächlich weil das Ändern einer Sequenz auf keinen Fall repliziert wird. Häufig behilft man sich hier mit Nummernkreisen, das eine System nimmt nur gerade Zahlen, das andere nur ungerade (die entsprechende Sequenz wird dann mit increment by 2 definiert.

Bi-direktionale Replikation ist immer kompliziert und auch die Anwendung sollte darauf vorbereitet sein.

Wenn es nur darum geht, dass das eine System auf die Daten des anderen zugreifen kann, sind vielleicht “foreign tables” eine Alternative. Dabei werden gar keine Daten repliziert, sondern Tabellen angelegt, die (unsichtbar für den Aufrufer) die Daten halt nicht von dem “aktuellen” Server holen sondern von dem anderen.

Ihr solltet aber trotzdem mal mit eurem Kunden ein Wörtchen reden: Software die keine Sicherheitsupdates mehr bekommt sollte man einfach nicht produktiv einsetzen. Ein Upgrade auf eine aktuelle Version ist dringend angeraten (und dann auch gleich auf die 14, sonst habt ihr in 10 Monaten wieder das gleiche Problem - dann wird nämlich die Unterstützung für die 10er Version eingestellt).

2 „Gefällt mir“
17

ja, die Idee mit fdw zu arbeiten ist auch gut, also, guter Hinweis von @castorp !

1 „Gefällt mir“
18

Vielen Dank für die ausführliche Antwort.
Ich bin in dem Thema Datenbanken komplett neu und daher ist es etwas durcheinander.

Das Anfangs Problem war, dass es mehrere Server an verschiedenen Standorten gibt. Jeder Server hat das gleiche Schema, User usw.
Im Moment wird über einen Klient ein Export der Daten gemacht und diese dann am anderen Standort über Import eingespielt. Nun ist dies jedoch sehr langsam und aufwending.
Daher war die Idee eben, die Daten die benötigt werden auf einen anderen Server zu kopieren und dann über Replikation auf den “Hauptserver” zu bringe. Was natürlich nicht geht da wir mit der Replikationd dann alles anderen auf dem “Hauptserver” löschen würden.

Die Methode mit dem Insert haben wir nun auch aufgegeben. Führt wohl nur zu Problemen.

Es geht mir darum, dass die Daten die ich auf einem Server 1 habe am Ende auch auf dem Server 2 sind.
Da im Moment alle Server das gleiche Schema haben kann ich dies aber ja nicht einfach replizieren.

Die einfachste Lösung wäre wohl die Schemas der anderen Server umzubenennen und neue Schmas auf dem Hauptserver erstellen und dies dann immer zu replizieren ?
Alle anderen Möglichkeiten scheinen mir zu anfällig zu sein, dass irgendwelche Daten verloren gehen oder durcheinander geraten.

Wenn die Replikation einmal komplett durchgelaufen ist, wird am Ende dann nur noch das geschickt was sich geändert hat?

19

Vielleicht ist es jetzt mal ein guter Zeitpunkt, um in die Doku zu schauen:

Es gibt neben dieser offiziellen Doku Tonnen von Blogs etc. zu diesem Thema.

20

Für das, was Du beschreibst, ist logische Replikation vermutlich schon die beste Wahl.

Aber jede Replikationslösung setzt voraus, dass zwischen den beteiligten Servern eine (ständige und stabile) Netzwerkverbindung besteht. Und ihr müsst auf eine aktuelle Version von Postgres wechseln - und zwar auf allen beteiligten Servern!

Auch das Replizieren von bestimmten Schemata geht nur über logische Replikation.

Und ja, wenn die logische Replikation mal initialisiert ist (und damit alle Daten übertragen sind), werden danach nur noch Änderungen übertragen.

Bei der logischen Replikation werden aber Änderungen an den Tabellenstrukturen etwas komplizierter. Da muss man Änderungen genau planen und das Ausrollen der Schema-Migrationen auch gut synchronisieren weil DDL Änderungen leider nicht repliziert werden. Ich bin mir nicht ganz sicher ob pglogical da helfen könnte, ich meine da sind Funktionen drin, die das etwas einfacher machen.

Mit physischer Replikation ist das einfacher zu organisieren (weil ja eine identische 1:1 Kopie gemacht wird) - da muss man eigentlich auf gar nichts aufpassen.

Wenn auf dem “zentralen” Server genug Platz ist, könntet ihr noch überlegen dort für jeden “externen” Server, eine weitere Postgres Instanz (nicht “Datenbank”!) zu starten (die laufen dann halt auf anderen Ports, 5433, 5435 usw). Die könnten dann via physischer Replikation ständig auf dem Stand des externen Servers gehalten werden (ist aber read-only weil ja repliziert wird). Wenn ihr alle Daten dann in eine einzige Datenbank haben wollt, könnte ihr das lokal viel einfacher organisieren und müsst keine Dumps in der Weltgeschichte rumschicken.

Das erhöht natürlich die Last auf dem zentralen Server. Wenn das zu viel wäre, müsst ihr vermutlich über zusätzliche Hardware nachdenken.