Chapter 3. PostGIS Verwaltung
Prev     Next

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.

PostgreSQL configuration can be set at the server level without touching postgresql.conf or postgresql.auto.conf by using the ALTER SYSTEM command.

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.22, “PostGIS Grand Unified Custom Variables (GUCs)” aufgeführt sind.

3.1.1. Startup

Diese Einstellungen werden in postgresql.conf konfiguriert:

constraint_exclusion

  • Standard: Partition

  • This is generally used for table partitioning. The default of "partition" forces the planner to only analyze tables for constraint consideration if they are in an inherited hierarchy and avoids paying the planner penalty otherwise.

shared_buffers

  • Default: ~128MB

  • 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 controls the maximum number of background processes available to the server and therefore also bounds how many workers can participate in parallel queries.

  • Standardwert: 8

  • Setzt die maximale Anzahl an Hintergrundprozessen, die von dem System unterstützt werden. Dieser Parameter kann nur durch einen Serverstart gesetzt werden.

3.1.2. Laufzeit

work_mem - setzt die Speichergröße für Sortiervorgänge und komplexe Abfragen

  • Standardwert: 1-4MB

  • Bitte passen Sie bei großen Datenbanken und für komplexe Abfragen mit entsprechend viel RAM an

  • Bitte verringern Sie den Wert bei vielen gleichzeitigen Benutzern oder wenn nur wenig RAM vorhanden ist.

  • Wenn Sie viel RAM und wenig Entwickler haben: 

    SET work_mem TO '256MB';

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

  • Standardwert: 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

Parallel query support in modern PostgreSQL and PostGIS uses this setting to decide how many workers a plan may request. If set to higher than 0, then some queries such as those involving relation functions like ST_Intersects can use multiple processes and can run more than twice as fast when doing so. If you have a lot of processors to spare, you should change the value of this to as many processors as you have. Also make sure to bump up max_worker_processes to at least as high as this number.

  • 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.

Out-of-db rasters and all raster drivers are disabled by default. In order to re-enable these, you need to set the following environment variables POSTGIS_GDAL_ENABLED_DRIVERS and POSTGIS_ENABLE_OUTDB_RASTERS in the server environment. You can also use the more cross-platform approach of setting the corresponding Section 7.22, “PostGIS Grand Unified Custom Variables (GUCs)”.

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

Setting environment variables varies depending on OS. For PostgreSQL installed on Ubuntu or Debian via apt-postgresql, the preferred way is to edit /etc/postgresql/MAJOR/CLUSTER/environment where the placeholders refer to the PostgreSQL major version and cluster name.

Wenn Sie PostgreSQL unter Windows als Dienst ausführen, können sie die Systemvariablen unter Windows 7 mit einem Rechtsklick auf Computer->roperties Advanced System Settings oder im Explorer unter Control Panel\All Control Panel Items\System. setzen. Klicken Sie anschließend auf Advanced System Settings ->Advanced->Environment Variables um neue Systemvariablen hinzuzufügen.

Nachdem die Umgebungsvariablen gesetzt wurden, muss der PostgreSQL-Dienst neu gestartet werden, damit die Änderungen wirksam werden.

3.3. Erstellung räumlicher Datenbanken

3.3.1. Die spatiale Datenbankerweiterung mittels EXTENSION aktivieren

If you have compiled and installed the extensions/postgis modules, you can turn a database into a spatial one using the EXTENSION mechanism.

Die Postgis-Erweiterung selbst enthält die Module geometry, geography, spatial_ref_sys inklusive aller Funktionen und Kommentare. Die Module raster und topology sind in separate Erweiterungen verpackt.

Um die spatiale Erweiterung in einer Datenbank zu aktivieren, wird der folgende SQL Befehl ausgeführt: 

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

3.3.2. Räumliche Aktivierung der Datenbank ohne Verwendung von EXTENSION (nicht empfohlen)

[Note]

Dies ist in der Regel nur dann notwendig, wenn PostGIS nicht in das Erweiterungsverzeichnis von PostgreSQL installiert werden kann oder soll (wie in einer Test-, Entwicklungs- oder eingeschränkten Umgebung).

Das Hinzufügen von PostGIS-Objekten und Funktionsdefinitionen in Ihre Datenbank erfolgt durch das Laden unterschiedlicher SQL-Dateien unter [prefix]/share/contrib, wie während der Erstellungsphase angezeigt.

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

Für einen vollständigen Satz an EPSG-Koordinatensystemdefinitionen können Sie auch die Definitionsdatei spatial_ref_sys.sql laden um die Tabelle spatial_ref_sys zu befüllen. Dies ermöglicht Ihnen ST_Transform()-Operationen an Geometrien durchzuführen.

Wenn Sie Kommentare zu den PostGIS-Funktionen hinzufügen möchten, finden Sie diese im Skript postgis_comments.sql. Kommentare können durch die 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.6/

    # 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.

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

PostGIS bietet ein SOFT UPGRADE-Verfahren für kleinere oder Bugfix-Releases sowie ein HARD-UPGRADE-Verfahren für Haupt-Releases.

Bevor PostGIS aktualisiert wird, sollten die Daten unbedingt gesichert werden. Wenn die Option -Fc beim Befehl pg_dump verwendet wird, kann der Dump bei einem HARD UPGRADE immer wieder hergestellt werden.

3.4.1. Sanfte Aktualisierung

Wenn Sie Ihre Datenbank mithilfe von EXTENSION installiert haben, müssen Sie das Upgrade ebenfalls über das EXTENSION Modell durchführen. Falls Sie die Installation über die alte SQL-Skript-Methode vorgenommen haben, wird empfohlen, auf die Nutzung von EXTENSION umzusteigen, da die Skript-Methode nicht länger unterstützt wird.

3.4.1.1. Soft upgrade using extensions

If you originally installed PostGIS with extensions, then you need to upgrade using extensions as well. Doing a minor upgrade with extensions is fairly painless.

Use the PostGIS_Extensions_Upgrade function to upgrade to the latest version you have installed.

SELECT postgis_extensions_upgrade();

If you are upgrading a database that still runs PostGIS 2.5, issue an ALTER EXTENSION followed by postgis_extensions_upgrade() to make sure the legacy postgis_raster objects are folded back into the main extension.

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 gewünschte Version wie folgt explizit angeben:

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

Falls Sie eine Fehlermeldung erhalten, die in etwa wie folgt aussieht:

No migration path defined for … to 3.7.0dev

Dann müssen Sie eine Sicherungskopie Ihrer Datenbank anlegen, eine neue Datenbank erstellen, wie unter Section 3.3.1, “Die spatiale Datenbankerweiterung mittels EXTENSION aktivieren” beschrieben, und dann Ihre Sicherungskopie in dieser neuen Datenbank wiederherstellen.

Falls Sie eine Hinweisnachricht erhalten, die in etwa wie folgt aussieht:

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

Dann ist bereits alles auf dem neuesten Stand und Sie können das 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.7.0devnext";
ALTER EXTENSION postgis_topology UPDATE TO "3.7.0devnext";
[Note]

Wenn Sie PostGIS ursprünglich installiert haben ohne eine Version anzugeben , können Sie die Neuinstallation der postgis-Erweiterung vor der Wiederherstellung oftmals überspringen, da im Backup nur CREATE EXTENSION postgis steht und daher die aktuellste Version für die Wiederherstellung herangezogen wird.
[Note]

Wenn Sie die PostGIS-Erweiterung vor Version 3.0.0 aktualisieren, erhalten Sie die neue Erweiterung postgis_raster, welche Sie getrost löschen können, wenn Sie keine Rasterunterstützung benötigen. Sie können diese wie folgt entfernen:

DROP EXTENSION postgis_raster;

3.4.1.2. Soft upgrade without extensions

Extensions have been the supported installation method for many releases. If you still rely on an unpackaged installation, perform a HARD UPGRADE into a database created with extensions and migrate your data there.

3.4.1.3. 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, “Räumliche Aktivierung der Datenbank ohne Verwendung von EXTENSION (nicht empfohlen)” und Section 3.3.1, “Die spatiale Datenbankerweiterung mittels EXTENSION 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