GeODin zu PostGIS, aber wie?

Probleme, Fragen die mit der Installation und Anwendung der PostGIS-Erweiterung zu tun haben.
schweitzerili
Benutzer
Beiträge: 59
Registriert: Di 13. Okt 2015, 09:35

Re: GeODin zu PostGIS, aber wie?

Beitrag von schweitzerili » Mi 21. Dez 2016, 09:19

Im Prinzip möchte ich nur eine Automatik die mir bestimmte Spalten aus der Urtabelle (im Schema public) in die neue Tabelle in meinem eigenen Schema "Kopie", welches den Postgis-Aufsatz hat, "rüber geschoben" haben. Dabei wird die Urtabelle durch ein externes Programm (GeODin) geändert. Die Änderungen können eben Inserts, Deletes oder Updates irgend einer Zeile sein. DAs Problem ist, dass die Urtabelle viel mehr SPalten hat als die 5 Spalten die ich ausschließlich haben will. Die anderen Spalten können ebenfalls gelöscht oder geupdatet werden ohne das sich was an den 5 Spalten ändert die mich interessieren. Somit soll der Trigger dann dort nicht auslösen obwohl etwas in der ROW bzw Spalte geändert wird. Ich weiß nicht mal ob das möglich ist... Deshalb dachtre ich die OLD/NEW GEschichte wäre eine Lösung.

Etwaige Feinheiten wie das ausschließliche Kopieren bzw Löschen von Zeilen in die neue Tabelle die Koordinaten haben wären ein Bonus.
Für mich stellt sich erstmal die Grundfrage wie ich die Daten in das andere Schema bekomme OHNE VIEW oder FDW (also ganz und gar andere Datenbank).

Ich möchte die neue Tabelle ungern im selben Schema anlegen wie die GeODin-Tabellen (das sind 97). GeODin hat die Struktur automatisch angelegt und ist relativ schmerzfrei. Dennoch wäre mir die neue Tabelle lieber in einem anderen Schema, da ich dort auch den PostGIS Aufsatz hinlegen kann und dort alle Berechnungen die ich mit den Koordinaten vor habe ausführen kann ohne die Struktur der originalen Tabellen zu nötigen.

Achso: Das Ziel der neuen Tabelle sollen PostGIS-DAten sein die ich aus den orignalen Daten zusammenbaue und berechne um diese über ein WebGIS einzubinden. Das wichtigste daran: Es muss up to DAte sein... Das heißt wenn ich was in GeODin eingebe oder lösche oder update, soll dieses dann automatisch in den DAten für das WebGIS widergespiegelt werden. Deshalb fällt ein View oder ein MV aus. Das FWD möchte ich nur ungern machen weil ich davon gleich mal null Ahnung habe. Es sei denn mir erklärt dass jemand, was ich nicht glaube.

So ich hoffe ich hab es halbwegs sinnvoll erklärt.

Gruß Stefan

PS.: Ich hatte schon eine Lösung über einen Insert-Trigger mit vorlaufendem Delete der neuen Tabelle. Jedoch gab es Probleme mit einer Serial-Spalte und dem delete...

So langsam bin ich etwas ratlos.

akretschmer
Erfahrener Benutzer
Beiträge: 7613
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: GeODin zu PostGIS, aber wie?

Beitrag von akretschmer » Mi 21. Dez 2016, 10:08

Du meinst sicher FDW, nicht FWD?

In der Quelldatenbank:

Ich erstelle eine Tabelle mit 4 Spalten und einen VIEW, der nur 2 dieser Spalten hat. Ungefähr das, was Du also auch hast.
Dieser View soll dann als externe Tabelle dienen.

Code: Alles auswählen

fdw_demo_source=# create table schweizerili(id serial primary key, col1 int, col2 int, col3 int, col4 int);
CREATE TABLE
fdw_demo_source=*# create view view_schweizerili as select id, col1, col4 from schweizerili;
CREATE VIEW
fdw_demo_source=*# commit;
COMMIT
In der Zieldatenbank:
Ich erstelle einen entfernten Server, eine entfernte Tabelle (die auf den oben erstellen View zeigt) und ein User-Mapping.

Code: Alles auswählen

fdw_demo_destination=# create server server_schweizerili foreign data wrapper postgres_fdw options(host '127.0.0.1', port '5432', dbname 'fdw_demo_source');
CREATE SERVER
fdw_demo_destination=*# create foreign table kopie_schweizerili (id int, col1 int, col4 int) server server_schweizerili options(table_name 'view_schweizerili');
CREATE FOREIGN TABLE
fdw_demo_destination=*# create user mapping for akretschmer server server_schweizerili options (user 'akretschmer', password 'akretschmer');
CREATE USER MAPPING

That's all. Du kannst nun in Deiner Quelltabelle "schweizerili" Daten eingeben, im View "view_schweizerili" siehst Du nur das, was exportiert werden soll, und in der anderen Datenbank, Tabelle "kopie_schweizerili" 1:1 das, was im View steht, und damit auch aktuell den Inhalt der Quelltabelle, begrenzt auf das, was Du sehen willst.


Das geht so nun auch über wirklich Remote Server, Du kannst das aber auch auf einem lokalen Server mit 2 DB's so machen (mache ich ja auch)

Andere Lösungen wären triggerbasierte Replikationen wie Slony oder Londiste oder ab PostgreSQL 9.4 logical Streaming Replication. (etwas, was Kollegen aus meiner Firma entwickelt haben)

Du vermischt einige Begriffe, daher ist nicht ganz klar, was Du genacht hast/willst. Ein Schema ist was anderes als eine Datenbank. Ein TRIGGER und ein VIEW kann schemaübergreifend arbeiten, nicht aber Datenbank- oder gar Serverübergreifend. Wenn Du innerhalb einer DB bist und 'nur' schemaübergreifend arbeiten willst wäre das allereinfachste für Dich ein VIEW.
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Gisman
Erfahrener Benutzer
Beiträge: 1122
Registriert: Fr 20. Jan 2006, 15:30
Wohnort: Pfalz
Kontaktdaten:

Re: GeODin zu PostGIS, aber wie?

Beitrag von Gisman » Mi 21. Dez 2016, 10:33

Ok, etwas konfus.
Du möchtest 5 Felder deiner GeoDIN-Tabelle in eine andere Tabelle in einem anderen Schema der gleichen Datenbank ablegen. Dazu verwendet man einen Trigger, der auf INSERT/UPDATE/DELETE reagiert. Ob an an dieser Stelle bereits nach den geänderten Felder filtern kann weiß ich nicht: bei Oracle geht das , Postgres bin ich mir nicht sicher. Du rufst den Trigger am Besten nach deiner Aktion auf als AFTER auf.
Innerhalb des Triggers kannst du mit der Systemvariablen (Kap. 40.9.1 in der Doku) unterscheiden, welche Operation deinen Trigger ausgelöst hat und nach Änderungen in den von dir ausgesuchten Felder suchen Hier kannst du mit einer einfachen IF-Abfrage entscheiden, welche Aktionen in der Kopie-Tabelle ausgeführt wird. Also z.B. Einfügen, wenn neue Daten hinzukommen, Ändern, wenn sich an deinen Feldern etwas geändert hat oder z.B. ignorieren, wenn ein Datensatz gelöscht wird (hier ist aber darauf zu achten, das im Original die ID nicht wieder vergeben wird).
Die suche nach Änderungen würde ich mit einer OR- Bedingung machen etwa so also:

Code: Alles auswählen

if old.Feld1 <> new.Feld1 or old.Feld2 <> new.Feld2 ... then update kopie.kopie_tabelle (Feld1, Feld2,...) set (Feld1, Feld2,...) = (new.Feld1, new.Feld2,...) where original.id = kopie.kopie_tabelle.id
Zusatz: Wenn du das in einer anderen Lokalen Datenbank haben möchtest, lege dir in der Kopiedatenbank eine Foreign-Table mit fdw an, akretscher hat das ja beschrieben. Ich mache das im Übrigen auch gerne so. Mit der Foreign-Table kannst du dann nach belieben andere Views etc. füttern und Berechnungen vornehmen. Das wäre IMHO das einfachste und ist ohne externe Tools und Konfiguration zu erledigen.

Gruß Uwe

akretschmer
Erfahrener Benutzer
Beiträge: 7613
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: GeODin zu PostGIS, aber wie?

Beitrag von akretschmer » Mi 21. Dez 2016, 10:39

Ja, PostgreSQL kennt conditional Trigger. Damit kannst Du erreichen, daß der Trigger nur ausgelöst wird, wenn sich z.B. Inhalte definierte Spalten ändern.

Siehe Doku zu TRIGGER, WHEN ( condition ).
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

schweitzerili
Benutzer
Beiträge: 59
Registriert: Di 13. Okt 2015, 09:35

Re: GeODin zu PostGIS, aber wie?

Beitrag von schweitzerili » Mi 21. Dez 2016, 15:38

Puh ok, euch beiden danke.

Diesmal etwas weniger konfus:

1. @ akretschmer: danke für das ding mit dem FDW. Hab ich sogar soweit verstanden. Nur dachte ich der View wird nicht automatisch geupdatet? Oder ist der immer 1:1 zur entsprechenden Quelle (also wenn ich was in die GeodinTAbelle eingebe ist es im View auch so?) Falls ja wäre das ein super Option.

Und ja ich hätte vor es mit 2 DB auf einem Server zu machen.

2. @ akretschmer: die anderen Lösungen habe ich auch schon mal "in den Händen gehabt". Wobei mich das logical Streaming Replication und Slony interessieren würden, falls du mir dazu mehr sagen kannst.

3. @ Gismen: wie du 2 Posts weiter vorne siehst habe ich das mit dem Insert Trigger schon mehrfach auf verschiedene Art und Weisen versucht, jedoch das Problem mit der GID(Serial)-Spalte gehabt und es dann mit OLD/NEW probiert, wo akretschmer aber meinte es würde noch was fehlen... habe ja eine entsprechende Fehlermeldung beim Insert von neuen Daten in der Urtabelle bekommen.

Zusammenfassung: Wenn der View wirklich immer sofort das Live-Abbild der Geodin-Tabelle ist (also bei jedem Insert oder ähnlichem in die Urtabelle das sofort so abbildet) und dies dann per FDW in eine andere DB-Tabelle übernommen wird, ist es wohl die sinnvollste Lösung.

Frage zu deinem FDW-Beispiel: Ich möchte auf dem selben Server eine zweite DB machen. Nehme ich dann anstatt create server, create db?

Danke euch schon mal. Ich verabschiede mich schon heute in die Ferien und melde mich somit erst Anfang des neuen Jahres wieder.

Schöne Weihnachten und bis nächstes Jahr.

Gruß Stefan

akretschmer
Erfahrener Benutzer
Beiträge: 7613
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: GeODin zu PostGIS, aber wie?

Beitrag von akretschmer » Mi 21. Dez 2016, 15:52

schweitzerili hat geschrieben:Puh ok, euch beiden danke.

Diesmal etwas weniger konfus:

1. @ akretschmer: danke für das ding mit dem FDW. Hab ich sogar soweit verstanden. Nur dachte ich der View wird nicht automatisch geupdatet? Oder ist der immer 1:1 zur entsprechenden Quelle (also wenn ich was in die GeodinTAbelle eingebe ist es im View auch so?) Falls ja wäre das ein super Option.

Und ja ich hätte vor es mit 2 DB auf einem Server zu machen.

2. @ akretschmer: die anderen Lösungen habe ich auch schon mal "in den Händen gehabt". Wobei mich das logical Streaming Replication und Slony interessieren würden, falls du mir dazu mehr sagen kannst.
Slony ist etwas, ähm, nicht mehr zeitgemäß. logical Streaming Replication ist eine sehr junge Entwicklung, die aber In-Code ist, und die Grundlage für BDR (Bi-Directional Replication) bildet. Das wird (hoffentlich) in 10 In-Core sein. Einige unserer Kunden nutzen das schon, produktiv, insbesondere in Anwendungen, wo man 2 oder mehr Rechenzentren geographisch verteilt hat, also z.B. New York, Singapure und Sydney. Die Architektur läßt (derzeit) bis zu 48 Master zu.

Es gibt einige Anleitungen im Internet, wie man das nutzt, z.B. bei uns:

http://blog.2ndquadrant.com/why-logical-replication/
https://2ndquadrant.com/de/resources/pglogical/

Du kannst dafür auch professionellen Support bekommen. Z.B. bei uns ;-). Ist nicht ganz trivial alles, z.B. mußt Du aufpassen bei DDL-Befehlen, also z.B. ALTER TABLE etc., und natürlich muß man sich ausreichend Gedanken machen, wie man Konflikte möglichst vermeided. (also z.B. globale Sequencen)


Zusammenfassung: Wenn der View wirklich immer sofort das Live-Abbild der Geodin-Tabelle ist (also bei jedem Insert oder ähnlichem in die Urtabelle das sofort so abbildet) und dies dann per FDW in eine andere DB-Tabelle übernommen wird, ist es wohl die sinnvollste Lösung.
Ja. Ein VIEW ist nix weiter als ein Alias auf das Select, auf dem der View basiert. Es wird also in Wahrheit nur das Select ausgeführt. Etwas anders ist das bei materialized Views, die benötigen ein Refresh.
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

schweitzerili
Benutzer
Beiträge: 59
Registriert: Di 13. Okt 2015, 09:35

Re: GeODin zu PostGIS, aber wie?

Beitrag von schweitzerili » Mi 21. Dez 2016, 16:16

Ok cool, danke.

Und anstatt Server kann ich auch einfach eine DB im FDW createn oder?

akretschmer
Erfahrener Benutzer
Beiträge: 7613
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: GeODin zu PostGIS, aber wie?

Beitrag von akretschmer » Mi 21. Dez 2016, 16:26

jupp. Siehe die Options im Befehl.
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

schweitzerili
Benutzer
Beiträge: 59
Registriert: Di 13. Okt 2015, 09:35

Re: GeODin zu PostGIS, aber wie?

Beitrag von schweitzerili » Mi 21. Dez 2016, 16:45

Ok.

Super, dann nun endgültig Danke und bis zum nächsten Jahr.

Gruß

schweitzerili
Benutzer
Beiträge: 59
Registriert: Di 13. Okt 2015, 09:35

Re: GeODin zu PostGIS, aber wie?

Beitrag von schweitzerili » Mo 16. Jan 2017, 16:33

Hallo im Neuen Jahr,

ich habe gerade die GEschichte mit dem FDW versucht... Alle Sachen wurden successfully ausgeführt... SOweit so gut... Nun finde ich aber diese foreign Tabelle nirgends ^^ auch der Foreign Server ist nicht auf zu finden... ich bin dabei im PG_Admin... Wo finde ich denn nun diese ZielTabelle?

Ich habe folgende Schritte ausgeführt:

In der Quelldatenbank "Testobjekt"

Code: Alles auswählen

create view view_select as select prj_id, xcoord, ycoord, zcoordb, longname from geodin_loc_ssgkrzt1;
commit;
In der Zieldatenbank "FDW-Server" habe ich dann folgende Schritte ausgeführt:

Code: Alles auswählen

CREATE SERVER "FDW-Server" FOREIGN DATA WRAPPER postgres_fdw 
OPTIONS (host '192.168.31.239', dbname 'Testobjekt', port '5432');

create user mapping for postgres server "FDW-Server" options 
(user 'postgres', password '********');

create foreign table kopie_stammdaten 
	(prj_id character varying(6), 
	xcoord double precision, 
	ycoord double precision, 
	zcoordb double precision, 
	longname character varying(40)) 
server "FDW-Server" options(table_name 'view_select');
All diese Schritte wurden ohne Probleme ausgeführt.
Aber wo finde ich jetzt diese foreign table kopie_stammdaten?.
In der ZielDB "FDW-Server" ist nix.

Oder hab ich was falsch verstanden?

Antworten