Chapter 3. PostGIS Verwaltung

Table of Contents

3.1. Leistungsoptimierung

Das Tuning für die PostGIS-Leistung ist ähnlich wie das Tuning für jede PostgreSQL-Arbeitslast. Die einzige zusätzliche Überlegung ist, dass Geometrien und Raster in der Regel groß sind, so dass speicherbezogene Optimierungen im Allgemeinen einen größeren Einfluss auf PostGIS haben als andere Arten von PostgreSQL-Abfragen.

Allgemeine Informationen zur Optimierung von PostgreSQL finden Sie unter Tuning your PostgreSQL Server.

Für PostgreSQL 9.4+ kann die Konfiguration auf Serverebene eingestellt werden, ohne postgresql.conf oder postgresql.auto.conf zu berühren, indem der Befehl ALTER SYSTEM verwendet wird.

ALTER SYSTEM SET work_mem = '256MB';
-- this forces non-startup configs to take effect for new connections
SELECT pg_reload_conf();
-- show current setting value
-- use SHOW ALL to see all settings
SHOW work_mem;

Zusätzlich zu den Postgres-Einstellungen verfügt PostGIS über einige benutzerdefinierte Einstellungen, die unter Section 7.23, “PostGIS Grand Unified Custom Variables (GUCs)” aufgeführt sind.

3.1.1. Startup

Diese Einstellungen werden in postgresql.conf konfiguriert:

constraint_exclusion

  • Standard: Partition

  • Dies wird im Allgemeinen für die Partitionierung von Tabellen verwendet. Die Voreinstellung hierfür ist "partition", was ideal für PostgreSQL 8.4 und höher ist, da es den Planer dazu zwingt, Tabellen nur dann für die Berücksichtigung von Einschränkungen zu analysieren, wenn sie sich in einer vererbten Hierarchie befinden, und den Planer ansonsten nicht zu bestrafen.

shared_buffers

  • Standard: ~128MB in PostgreSQL 9.6

  • Setzen Sie den Wert auf etwa 25 % bis 40 % des verfügbaren RAM. Unter Windows können Sie diesen Wert möglicherweise nicht so hoch einstellen.

max_worker_processes Diese Einstellung ist nur für PostgreSQL 9.4+ verfügbar. Für PostgreSQL 9.6+ hat diese Einstellung zusätzliche Bedeutung, da sie die maximale Anzahl von Prozessen steuert, die Sie für parallele Abfragen haben können.

  • Voreinstellung: 8

  • Legt die maximale Anzahl von Hintergrundprozessen fest, die das System unterstützen kann. Dieser Parameter kann nur beim Start des Servers gesetzt werden.

3.1.2. Laufzeit

work_mem - legt die Größe des für Sortiervorgänge und komplexe Abfragen verwendeten Speichers fest

  • Standard: 1-4MB

  • Anpassung für große Datenbanken, komplexe Abfragen, viel RAM

  • Verringern Sie den Wert bei vielen gleichzeitigen Benutzern oder geringem RAM.

  • Wenn Sie viel RAM und wenig Entwickler haben:

    SET work_mem TO '256MB';

maintenance_work_mem - die Speichergröße, die für VACUUM, CREATE INDEX, etc. verwendet wird.

  • Standard: 16-64MB

  • Im Allgemeinen zu niedrig - bindet E/A, sperrt Objekte beim Auslagern von Speicher

  • Wir empfehlen 32 MB bis 1 GB auf Produktionsservern mit viel RAM, aber das hängt von der Anzahl der gleichzeitigen Benutzer ab. Wenn Sie viel RAM und wenige Entwickler haben:

    SET maintenance_work_mem TO '1GB';

max_parallel_Arbeiter_pro_Gruppe

Diese Einstellung ist nur für PostgreSQL 9.6+ verfügbar und wirkt sich nur auf PostGIS 2.3+ aus, da nur PostGIS 2.3+ parallele Abfragen unterstützt. Wenn sie auf einen Wert größer als 0 gesetzt wird, können einige Abfragen, z.B. solche, die Beziehungsfunktionen wie ST_Intersects beinhalten, mehrere Prozesse verwenden und können dabei mehr als doppelt so schnell laufen. Wenn Sie viele Prozessoren zur Verfügung haben, sollten Sie den Wert auf so viele Prozessoren ändern, wie Sie haben. Stellen Sie außerdem sicher, dass max_worker_processes mindestens so hoch ist wie diese Zahl.

  • Voreinstellung: 0

  • Legt die maximale Anzahl von Workern fest, die von einem einzelnen Gather Knoten gestartet werden können. Parallele Worker werden aus dem durch max_worker_processes festgelegten Pool von Prozessen genommen. Beachten Sie, dass die angeforderte Anzahl von Workern zur Laufzeit möglicherweise nicht verfügbar ist. Wenn dies der Fall ist, wird der Plan mit weniger Arbeitern als erwartet ausgeführt, was ineffizient sein kann. Wenn Sie diesen Wert auf 0 setzen, was der Standardwert ist, wird die parallele Abfrageausführung deaktiviert.

3.2. Konfigurieren der Rasterunterstützung

Wenn die Rasterunterstützung aktiviert ist, sollte diese wie folgt konfiguriert werden.

Ab PostGIS 2.1.3 sind out-of-db-Raster und alle Rastertreiber von vornherein deaktiviert. Um diese zu aktivieren, müssen Sie die folgenden Umgebungsvariablen POSTGIS_GDAL_ENABLED_DRIVERS und POSTGIS_ENABLE_OUTDB_RASTERS in der Serverumgebung setzen. Ab PostGIS 2.2 können Sie einen plattformübergreifenderen Ansatz mit Hilfe der entsprechenden Section 7.23, “PostGIS Grand Unified Custom Variables (GUCs)” verwenden.

Wenn Sie Offline-Raster aktivieren möchten:

POSTGIS_ENABLE_OUTDB_RASTERS=1

Jede andere und auch keine Einstellung deaktiviert die out-of-db Raster.

Um alle installierten GDAL-Treiber zu aktivieren, muss diese Umgebungsvariable wie folgt gesetzt werden

POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL

Wenn nur bestimmte Treiber aktiviert werden sollen, muss diese Umgebungsvariable wie folgt gesetzt werden:

POSTGIS_GDAL_ENABLED_DRIVERS="GTiff PNG JPEG GIF XYZ"
[Note]

Unter Windows darf die Treiberliste nicht unter Hochkomma gestellt werden

Das Setzen von Umgebungsvariablen variiert je nach Betriebssystem. Für PostgreSQL, das unter Ubuntu oder Debian über apt-postgresql installiert wurde, ist der bevorzugte Weg, /etc/postgresql/10/main/environment zu editieren, wobei sich 10 auf die Version von PostgreSQL und main auf den Cluster bezieht.

Unter Windows können Sie, wenn Sie als Dienst ausgeführt werden, Systemvariablen festlegen, die Sie unter Windows 7 mit einem Rechtsklick auf Computer->Eigenschaften Erweiterte Systemeinstellungen oder im Explorer unter Systemsteuerung\Alle Systemsteuerungselemente\System erreichen. Klicken Sie dann auf Erweiterte Systemeinstellungen ->Erweitert->Umgebungsvariablen und fügen Sie neue Systemvariablen hinzu.

Nachdem Sie die Umgebungsvariablen gesetzt haben, müssen Sie Ihren PostgreSQL-Dienst neu starten, damit die Änderungen wirksam werden.

3.3. Erstellung räumlicher Datenbanken

3.3.1. Datenbank mit EXTENSION räumlich aktivieren

Wenn Sie PostgreSQL 9.1+ verwenden und die Extensions/Postgis-Module kompiliert und installiert haben, können Sie eine Datenbank mit Hilfe des EXTENSION-Mechanismus in eine räumliche Datenbank verwandeln.

Die Kern-Postgis-Erweiterung enthält Geometrie, Geographie, spatial_ref_sys und alle Funktionen und Kommentare. Raster und Topologie sind als separate Erweiterung verpackt.

Führen Sie das folgende SQL-Snippet in der Datenbank aus, die Sie räumlich aktivieren möchten:

CREATE EXTENSION IF NOT EXISTS plpgsql;
      CREATE EXTENSION postgis;
      CREATE EXTENSION postgis_raster; -- OPTIONAL
      CREATE EXTENSION postgis_topology; -- OPTIONAL

3.3.2. Datenbank räumlich aktivieren, ohne EXTENSION zu verwenden (nicht empfohlen)

[Note]

Dies ist in der Regel nur notwendig, wenn Sie PostGIS nicht in das PostgreSQL-Erweiterungsverzeichnis installieren können oder wollen (z.B. beim Testen, bei der Entwicklung oder in einer eingeschränkten Umgebung).

Das Hinzufügen von PostGIS-Objekten und Funktionsdefinitionen in Ihre Datenbank erfolgt durch das Laden der verschiedenen sql-Dateien, die sich in [prefix]/share/contrib befinden, wie während der Erstellungsphase angegeben.

Die zentralen PostGIS-Objekte (Geometrie- und Geografietypen sowie deren Unterstützungsfunktionen) befinden sich im Skript postgis.sql. Rasterobjekte befinden sich im Skript rtpostgis.sql. Topologieobjekte befinden sich im Skript topology.sql.

Für einen vollständigen Satz von EPSG-Koordinatensystem-Definitionsbezeichnern können Sie auch die Definitionsdatei spatial_ref_sys.sql laden und die Tabelle spatial_ref_sys befüllen. Damit können Sie ST_Transform()-Operationen an Geometrien durchführen.

Wenn Sie Kommentare zu den PostGIS-Funktionen hinzufügen möchten, finden Sie diese im Skript postgis_comments.sql. Die Kommentare können einfach durch Eingabe von \dd [function_name] in einem psql Terminalfenster angezeigt werden.

Führen Sie die folgenden Shell-Befehle in Ihrem Terminal aus:

DB=[yourdatabase]
    SCRIPTSDIR=`pg_config --sharedir`/contrib/postgis-3.4/

    # Core objects
    psql -d ${DB} -f ${SCRIPTSDIR}/postgis.sql
    psql -d ${DB} -f ${SCRIPTSDIR}/spatial_ref_sys.sql
    psql -d ${DB} -f ${SCRIPTSDIR}/postgis_comments.sql # OPTIONAL

    # Raster support (OPTIONAL)
    psql -d ${DB} -f ${SCRIPTSDIR}/rtpostgis.sql
    psql -d ${DB} -f ${SCRIPTSDIR}/raster_comments.sql # OPTIONAL

    # Topology support (OPTIONAL)
    psql -d ${DB} -f ${SCRIPTSDIR}/topology.sql
    psql -d ${DB} -f ${SCRIPTSDIR}/topology_comments.sql # OPTIONAL

3.4. Aktualisierung von Geodatenbanken

Die Aktualisierung bestehender räumlicher Datenbanken kann schwierig sein, da sie den Austausch oder die Einführung neuer PostGIS-Objektdefinitionen erfordert.

Leider können nicht alle Definitionen in einer aktiven Datenbank einfach ersetzt werden, so dass manchmal ein Dump/Reload-Prozess die beste Lösung ist.

PostGIS bietet ein SOFT UPGRADE-Verfahren für Minor- oder Bugfix-Versionen und ein HARD UPGRADE-Verfahren für Major-Versionen.

Bevor Sie versuchen, PostGIS zu aktualisieren, lohnt es sich immer, Ihre Daten zu sichern. Wenn Sie das Flag -Fc für pg_dump verwenden, können Sie den Dump bei einem HARD UPGRADE immer wiederherstellen.

3.4.1. Sanftes Upgrade

Wenn Sie Ihre Datenbank mithilfe von Erweiterungen installiert haben, müssen Sie das Upgrade auch mithilfe des Erweiterungsmodells durchführen. Wenn Sie mit dem alten SQL-Skript installiert haben, sollten Sie Ihre Installation auf Erweiterungen umstellen, da das Skript nicht mehr unterstützt wird.

3.4.1.1. Soft Upgrade 9.1+ mit Erweiterungen

Wenn Sie PostGIS ursprünglich mit Erweiterungen installiert haben, dann müssen Sie das Upgrade auch mit Erweiterungen durchführen. Ein kleines Upgrade mit Erweiterungen ist ziemlich schmerzlos.

Wenn Sie mit PostGIS 3 oder höher arbeiten, sollten Sie die Funktion PostGIS_Extensions_Upgrade verwenden, um auf die neueste Version zu aktualisieren, die Sie installiert haben.

SELECT postgis_extensions_upgrade();

Wenn Sie PostGIS 2.5 oder niedriger verwenden, gehen Sie wie folgt vor:

ALTER EXTENSION postgis UPDATE;
SELECT postgis_extensions_upgrade();
-- This second call is needed to rebundle postgis_raster extension
SELECT postgis_extensions_upgrade();

Wenn Sie mehrere Versionen von PostGIS installiert haben und nicht auf die neueste Version aktualisieren möchten, können Sie die Version wie folgt explizit angeben:

ALTER EXTENSION postgis UPDATE TO "3.5.0dev";
ALTER EXTENSION postgis_topology UPDATE TO "3.5.0dev";

Wenn Sie eine Fehlermeldung wie diese erhalten:

No migration path defined for … to 3.5.0dev

Dann müssen Sie eine Sicherungskopie Ihrer Datenbank anlegen, eine neue Datenbank erstellen, wie unter Section 3.3.1, “Datenbank mit EXTENSION räumlich aktivieren” beschrieben, und dann Ihre Sicherungskopie auf dieser neuen Datenbank wiederherstellen.

Wenn Sie eine Meldung wie diese erhalten:

Version "3.5.0dev" of extension "postgis" is already installed

Dann ist bereits alles auf dem neuesten Stand und Sie können es getrost ignorieren. Es sei denn, Sie versuchen, von einer Entwicklungsversion auf die nächste zu aktualisieren (die keine neue Versionsnummer erhält); in diesem Fall können Sie "next" an die Versionszeichenfolge anhängen, und beim nächsten Mal müssen Sie das Suffix "next" wieder weglassen:

ALTER EXTENSION postgis UPDATE TO "3.5.0devnext";
ALTER EXTENSION postgis_topology UPDATE TO "3.5.0devnext";
[Note]

Wenn Sie PostGIS ursprünglich ohne Versionsangabe installiert haben, können Sie die Neuinstallation der postgis-Erweiterung vor der Wiederherstellung oft überspringen, da das Backup nur CREATE EXTENSION postgis hat und somit die neueste Version bei der Wiederherstellung übernimmt.

[Note]

Wenn Sie die PostGIS-Erweiterung von einer Version vor 3.0.0 aktualisieren, erhalten Sie eine neue Erweiterung postgis_raster, die Sie getrost löschen können, wenn Sie die Rasterunterstützung nicht benötigen. Sie können sie wie folgt entfernen:

DROP EXTENSION postgis_raster;

3.4.1.2. Soft Upgrade Pre 9.1+ oder ohne Erweiterungen

Dieser Abschnitt gilt nur für diejenigen, die PostGIS ohne Erweiterungen installiert haben. Wenn Sie Erweiterungen haben und versuchen, mit diesem Ansatz zu aktualisieren, erhalten Sie Meldungen wie:

can't drop … because postgis extension depends on it

HINWEIS: Wenn Sie von PostGIS 1.* zu PostGIS 2.* oder von PostGIS 2.* vor r7409 wechseln, können Sie dieses Verfahren nicht verwenden, sondern müssen ein HARD UPGRADE durchführen.

Nach dem Kompilieren und Installieren (make install) sollten Sie eine Reihe von *_upgrade.sql Dateien in den Installationsordnern finden. Sie können sie alle mit auflisten:

ls `pg_config --sharedir`/contrib/postgis-3.5.0dev/*_upgrade.sql

Laden Sie sie alle nacheinander, beginnend mit postgis_upgrade.sql.

psql -f postgis_upgrade.sql -d your_spatial_database

Das gleiche Verfahren gilt für Raster-, Topologie- und sfcgal-Erweiterungen, mit Upgrade-Dateien namens rtpostgis_upgrade.sql, topology_upgrade.sql und sfcgal_upgrade.sql. Falls Sie sie benötigen:

psql -f rtpostgis_upgrade.sql -d your_spatial_database
psql -f topology_upgrade.sql -d your_spatial_database
psql -f sfcgal_upgrade.sql -d your_spatial_database

Wir empfehlen Ihnen, zu einer erweiterungsbasierten Installation zu wechseln, indem Sie

psql -c "SELECT postgis_extensions_upgrade();"
[Note]

Wenn Sie die postgis_upgrade.sql speziell für das Upgrade Ihrer Version nicht finden können, verwenden Sie eine Version, die zu früh für ein Soft-Upgrade ist und müssen ein HARD UPGRADE durchführen.

Die Funktion PostGIS_Full_Version sollte Sie mit der Meldung "procs need upgrade" über die Notwendigkeit eines solchen Upgrades informieren.

3.4.2. Hartes Upgrade

Unter HARD UPGRADE verstehen wir ein vollständiges Dump/Reload von PostGIS-aktivierten Datenbanken. Ein HARD UPGRADE ist erforderlich, wenn sich der interne Speicher von PostGIS-Objekten ändert oder wenn ein SOFT UPGRADE nicht möglich ist. Der Anhang Release Notes gibt für jede Version an, ob ein Dump/Reload (HARD UPGRADE) für ein Upgrade erforderlich ist.

Der Dump/Reload-Prozess wird durch das postgis_restore-Skript unterstützt, das dafür sorgt, dass alle Definitionen, die zu PostGIS gehören (einschließlich der alten), aus dem Dump ausgelassen werden. So können Sie Ihre Schemata und Daten in einer Datenbank mit installiertem PostGIS wiederherstellen, ohne dass es zu Fehlern bei doppelten Symbolen kommt oder veraltete Objekte mitgenommen werden.

Ergänzende Anleitungen für Windows-Benutzer finden Sie unter Windows Hard Upgrade.

Das Verfahren ist wie folgt:

  1. Erstellen Sie einen Dump im "benutzerdefinierten Format" der Datenbank, die Sie aktualisieren möchten (nennen wir ihn olddb), einschließlich binärer Blobs (-b) und ausführlicher (-v) Ausgabe. Der Benutzer kann der Eigentümer der Datenbank sein, er muss nicht das Postgres-Superkonto sein.

    pg_dump -h localhost -p 5432 -U postgres -Fc -b -v -f "/somepath/olddb.backup" olddb
  2. Führen Sie eine Neuinstallation von PostGIS in einer neuen Datenbank durch - wir bezeichnen diese Datenbank als newdb. Eine Anleitung dazu finden Sie unter Section 3.3.2, “Datenbank räumlich aktivieren, ohne EXTENSION zu verwenden (nicht empfohlen)” und Section 3.3.1, “Datenbank mit EXTENSION räumlich aktivieren”.

    Die spatial_ref_sys-Einträge, die Sie in Ihrem Dump gefunden haben, werden wiederhergestellt, überschreiben aber nicht die vorhandenen Einträge in spatial_ref_sys. Damit soll sichergestellt werden, dass die Korrekturen im offiziellen Satz ordnungsgemäß in die wiederhergestellten Datenbanken übertragen werden. Wenn Sie aus irgendeinem Grund eigene Überschreibungen von Standardeinträgen wünschen, laden Sie die Datei spatial_ref_sys.sql beim Erstellen der neuen Datenbank einfach nicht.

    Wenn Ihre Datenbank wirklich alt ist oder Sie wissen, dass Sie lange veraltete Funktionen in Ihren Ansichten und Funktionen verwendet haben, müssen Sie möglicherweise legacy.sql laden, damit alle Ihre Funktionen und Ansichten usw. wieder richtig funktionieren. Tun Sie dies nur, wenn es _wirklich_ notwendig ist. Erwägen Sie stattdessen, Ihre Ansichten und Funktionen vor dem Dumping zu aktualisieren, falls möglich. Die veralteten Funktionen können später durch Laden von uninstall_legacy.sql entfernt werden.

  3. Stellen Sie Ihre Sicherung mit postgis_restore in Ihre neue Datenbank newdb wieder her. Unerwartete Fehler werden, falls vorhanden, von psql in den Standardfehlerstrom ausgegeben. Führen Sie ein Protokoll über diese Fehler.

    postgis_restore "/somepath/olddb.backup" | psql -h localhost -p 5432 -U postgres newdb 2> errors.txt

In den folgenden Fällen kann es zu Fehlern kommen:

  1. Einige Ihrer Ansichten oder Funktionen verwenden veraltete PostGIS-Objekte. Um dies zu beheben, können Sie versuchen, das Skript legacy.sql vor der Wiederherstellung zu laden, oder Sie müssen eine Version von PostGIS wiederherstellen, die diese Objekte noch enthält, und nach der Portierung Ihres Codes erneut eine Migration versuchen. Wenn der legacy.sql Weg für Sie funktioniert, vergessen Sie nicht, Ihren Code so zu korrigieren, dass er keine veralteten Funktionen mehr verwendet und sie durch Laden von uninstall_legacy.sql fallen lässt.

  2. Einige benutzerdefinierte Datensätze von spatial_ref_sys in der Dump-Datei haben einen ungültigen SRID-Wert. Gültige SRID-Werte sind größer als 0 und kleiner als 999000. Werte im Bereich 999000.999999 sind für den internen Gebrauch reserviert, während Werte > 999999 überhaupt nicht verwendet werden können. Alle benutzerdefinierten Datensätze mit ungültigen SRIDs werden beibehalten, wobei die Werte > 999999 in den reservierten Bereich verschoben werden, aber die Tabelle spatial_ref_sys würde eine Prüfbeschränkung verlieren, die dafür sorgt, dass diese Invariante beibehalten wird, und möglicherweise auch ihren Primärschlüssel (wenn mehrere ungültige SRIDS in denselben reservierten SRID-Wert umgewandelt werden).

    Um das Problem zu beheben, sollten Sie Ihre benutzerdefinierte SRS auf eine SRID mit einem gültigen Wert (vielleicht im Bereich 910000..910999) kopieren, alle Ihre Tabellen in die neue SRID konvertieren (siehe UpdateGeometrySRID), den ungültigen Eintrag aus spatial_ref_sys löschen und die Prüfung(en) mit neu konstruieren:

    ALTER TABLE spatial_ref_sys ADD CONSTRAINT spatial_ref_sys_srid_check check (srid 
    > 0 AND srid < 999000 );
    
    ALTER TABLE spatial_ref_sys ADD PRIMARY KEY(srid));
        

    Wenn Sie eine alte Datenbank aktualisieren, die französische IGN -Kartografie enthält, werden Sie wahrscheinlich SRIDs außerhalb des Bereichs haben und beim Importieren Ihrer Datenbank Probleme wie diese sehen:

    WARNING: SRID 310642222 converted to 999175 (in reserved zone)

    In diesem Fall können Sie die folgenden Schritte versuchen: Zuerst werfen Sie die IGN komplett aus der Sql, die aus postgis_restore resultiert. Also, nachdem Sie :

    postgis_restore "/somepath/olddb.backup" > olddb.sql

    führen Sie diesen Befehl aus:

    grep -v IGNF olddb.sql > olddb-without-IGN.sql

    Erstellen Sie dann Ihre neue Datenbank, aktivieren Sie die erforderlichen Postgis-Erweiterungen und fügen Sie die französische System-IGN mit diesem Skript ein, um Ihre Daten zu importieren:

    psql -h localhost -p 5432 -U postgres -d newdb -f olddb-without-IGN.sql  2> errors.txt