Sichere Datenübertragung über unzuverlässige Verbindung

Hallo PG-Forum,

das ist mein erster Post also ein bisschen über mich: Vor 13 Jahren hab ich meine Anwendung mit MySQL angefangen. Als kurz darauf Oracle Sun und damit auch MySQL übernommen hatte, hab ich dann meine Anwendung auf Postgresql umgestellt. Ich glaube das war damals 7 (Debian Version). In der Zwischenzeit hab ich immer wieder etwas gemacht mit PG aber nicht viel. Upgrade war immer ein pg_dumpall, upgrade und psql restore. Ich habe nur einfache SQL Befehle verwendet und meine erste komplexere Abfrage mit Stackoverflow [1] gelöst. Dazu später mehr.

Meine Anwendung erfasst Messwerte und trägt diese in eine Tabelle ein. Anfangs habe ich das direkt in meine DB eingetragen. Als ich dann feststellte das die Verbindung instabil ist und es anfangs gar nicht gemerkt habe, habe ich direkt vor Ort einen Raspi mit einer DB hingestellt. Dieser trägt die Daten in eine Tabelle Namens solarcache ein. Nachdem die Daten eingetragen wurden, versucht die Anwendung sich mit der Haupt DB zu verbinden und die Daten dort abzuliefern. Seither habe ich auch keine Daten mehr verloren aber teils doppelte Einträge. Damit habe ich lange gelebt.

Letztens haben wir in der Firma eine Schulung über eine Anwendung gehabt welche mit MSSQL gearbeitet hat und das hat mich angespornt doch meine Anwendung zu verbessern. Was mich immer gestört hat war das die Abfrage [1] sehr lange gebraucht hat. 30+ sec bei zur Zeit 2.4 Mio Einträgen in der Tabelle. Ich hab das immer auf die Leistung des Raspis geschoben. Mit der Hilfe von ein paar Büchern (PostgreSQL 14 Administration Cookbook + Mastering PostgreSQL 13) und der Dokumentation hab ich das mit LEFT JOIN gelöst und auf 7 Sekunden gedrückt. Mit Materialized Views und pg_cron dann auf ein paar Millisekunden. Soweit so gut zur Vorgeschichte.

Meine Ideen:

  1. Logical Replication
    Jetzt hab ich von Logical Replication gelesen. Mit dem Publish/Subscribe. Wie ist das wenn die Verbindung abbrechen kann? Wie verhält sich das wenn die Verbindung länger nicht mehr da ist? Wie verhält sich das wenn ich ein Upgrade von PSQL auf der einen Seite mache und die andere noch eine ältere Version nutzt?
    Es geht nur darum die solarcache Tabelle von A nach B zu bekommen und wenn B die Einträge dann in die Haupttabelle eingetragen hat auf A zu löschen.

  2. Fremdtabellen + pg_cron
    Hier würde der Hauptserver die Daten vom Messserver abholen und per Transaktion sicher übertragen. (Denke ich zumindest)

  3. Bisheriges Verhalten weiter nutzen

Ok, Das wars erst mal! Wünsche euch ein schönes Wochenende!

Georg

[1] postgresql - sum up graphs from different streams but slightly different timestamps - Stack Overflow

logical replication sollte gut funktionieren, auch wenn die Verbindung mal weg ist baut sich das wieder auf, wenn die Verbindung steht (replication slots). Das geht auch über verschiedene Versionen.

31.2. Subscription
A subscription is the downstream side of logical replication. The node where a subscription is defined
is referred to as the subscriber. A subscription defines the connection to another database and set of
publications (one or more) to which it wants to subscribe.
The subscriber database behaves in the same way as any other PostgreSQL instance and can be used
as a publisher for other databases by defining its own publications.

Was ich noch nicht verstanden habe, wenn ich dann Werte aus der Subscription Tabelle lösche, werden die dann auch aus der Publisher Seite gelöscht oder brauch ich dann eine Publish/Subscribe in beide Richtungen?

Nein, das passiert nicht, und das ist auch nicht trivial. Wenn Du das willst: dazu gibt es BDR (BiDirectional Replication) bzw. neuerdings PGD genannt (Postgresql Distrubuted), ist aber keine OpenSource.

Ok Danke! Dann werde ich den Foreign Table Ansatz nehmen :slight_smile:
Danke für die Infos!

Dieses Thema wurde automatisch 60 Tage nach der letzten Antwort geschlossen. Es sind keine neuen Antworten mehr erlaubt.