Chapter 3. Administration de PostGIS

Table of Contents

3.1. Optimisation des performances

Le réglage des performances de PostGIS est similaire à celui de n'importe quelle charge de travail PostgreSQL. La seule considération supplémentaire est que les géométries et les rasters sont généralement de grande taille, donc les optimisations liées à la mémoire ont généralement plus d'impact sur PostGIS que sur d'autres types de requêtes PostgreSQL.

Pour plus de détails sur l'optimisation de PostgreSQL, reportez-vous à Tuning your PostgreSQL Server.

Pour PostgreSQL 9.4+, la configuration peut être définie au niveau du serveur sans toucher à postgresql.conf ou postgresql.auto.conf en utilisant la commande ALTER SYSTEM.

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;

En plus des paramètres de Postgres, PostGIS a quelques paramètres personnalisés qui sont listés dans Section 7.23, “Variables PostGIS GUC (Grand Unified Custom Variables)”.

3.1.1. Démarrage

Ces paramètres sont configurés dans postgresql.conf :

constraint_exclusion

  • Valeur par défaut : partition

  • Ceci est généralement utilisé pour le partitionnement des tables. La valeur par défaut est "partition", ce qui est idéal pour PostgreSQL 8.4 et plus car cela force le planificateur à n'analyser les tables pour la prise en compte des contraintes que si elles sont dans une hiérarchie héritée et à ne pas payer de pénalité au planificateur dans le cas contraire.

shared_buffers

  • Valeur par défaut : ~128MB dans PostgreSQL 9.6

  • Réglez-le à environ 25 à 40 % de la mémoire vive disponible. Sous Windows, il se peut que vous ne puissiez pas définir une valeur aussi élevée.

max_worker_processes Ce paramètre n'est disponible que pour PostgreSQL 9.4+. Pour PostgreSQL 9.6+, ce paramètre a une importance supplémentaire car il contrôle le nombre maximum de processus que vous pouvez avoir pour les requêtes parallèles.

  • Valeur par défaut : 8

  • Définit le nombre maximum de processus d'arrière-plan que le système peut prendre en charge. Ce paramètre ne peut être défini qu'au démarrage du serveur.

3.1.2. Temps d'exécution

work_mem - définit la taille de la mémoire utilisée pour les opérations de tri et les requêtes complexes

  • Valeur par défaut : 1-4MB

  • Ajustement pour les grandes bases de données, les requêtes complexes, beaucoup de RAM

  • Ajuster à la baisse pour de nombreux utilisateurs simultanés ou une faible mémoire vive.

  • Si vous avez beaucoup de mémoire vive et peu de développeurs :

    SET work_mem TO '256MB';

maintenance_work_mem - la taille de la mémoire utilisée pour VACUUM, CREATE INDEX, etc.

  • Valeur par défaut : 16-64MB

  • Généralement trop faible - immobilise les Entrées/Sorties, bloque les objets pendant l'échange de mémoire

  • Nous recommandons 32MB à 1GB sur les serveurs de production avec beaucoup de RAM, mais cela dépend du nombre d'utilisateurs simultanés. Si vous avez beaucoup de RAM et peu de développeurs :

    SET maintenance_work_mem TO '1GB';

max_parallel_workers_per_gather

Ce paramètre n'est disponible que pour PostgreSQL 9.6+ et n'affecte que PostGIS 2.3+, puisque seul PostGIS 2.3+ supporte les requêtes parallèles. Si ce paramètre est supérieur à 0, certaines requêtes telles que celles impliquant des fonctions de relation comme ST_Intersects peuvent utiliser plusieurs processus et s'exécuter plus de deux fois plus rapidement. Si vous avez beaucoup de processeurs à disposition, vous devriez changer la valeur de ce paramètre pour autant de processeurs que vous avez. Assurez-vous également d'augmenter max_worker_processes à une valeur au moins égale à ce nombre.

  • Valeur par défaut : 0

  • Définit le nombre maximum de workers qui peuvent être démarrés par un seul nœud Gather. Les workers parallèles sont pris dans le pool de processus établi par max_worker_processes. Notez que le nombre de workers demandé peut ne pas être disponible au moment de l'exécution. Dans ce cas, le plan s'exécutera avec moins de workers que prévu, ce qui peut s'avérer inefficace. La définition de cette valeur à 0, qui est la valeur par défaut, désactive l'exécution parallèle des requêtes.

3.2. Configurer la prise en charge du raster

Si vous activez la prise en charge du raster, vous devriez lire ce qui suit afin de bien la configurer.

À partir de PostGIS 2.1.3, tous les pilotes raster, et la prise en charge des rasters hors-connexion (out-of-db) est désactivé par défaut. Pour les activer, vous devez définir les variables d'environnement suivantes dans l'environnement du serveur : POSTGIS_GDAL_ENABLED_DRIVERS and POSTGIS_ENABLE_OUTDB_RASTERS. Depuis PostGIS 2.2, vous pouvez utiliser la méthode plus générique en définissant les Section 7.23, “Variables PostGIS GUC (Grand Unified Custom Variables)” correspondantes.

Si vous souhaitez activer le raster hors connexion :

POSTGIS_ENABLE_OUTDB_RASTERS=1

Si la variable a n'importe quelle autre valeur, ou si elle n'a pas de valeur, le support du raster hors-connexion sera désactivé.

Pour utiliser tous les pilotes GDAL disponibles dans votre installation GDAL, définissez la variable d'environnement via

POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL

Si vous souhaitez activer une liste de pilotes spécifiques, définissez la variable d'environnement via :

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

Si vous êtes sous Windows, ne pas mettre de guillemets autour de la liste des pilotes

La définition des variables d'environnement dépend de votre système d'exploitation. Sous Ubuntu ou Debian avec PostgreSQL installé via apt-postgresql, la méthode conseillée est d'éditer le fichier de configuration /etc/postgresql/10/main/environment où 10 est la version de PostgreSQL et main est le groupe de bases de données (cluster).

Sous Windows, si vous fonctionnez en tant que service, vous pouvez définir des variables système auxquelles vous pouvez accéder, sous Windows 7, en cliquant avec le bouton droit de la souris sur Ordinateur ->Propriétés - Paramètres système avancés ou, dans l'explorateur, en naviguant jusqu'à Panneau de configuration - Tous les éléments du panneau de configuration - Système. Cliquez ensuite sur Advanced System Settings ->Advanced->Environment Variables et ajoutez de nouvelles variables système.

Après avoir changé les variables d'environnement, vous devrez redémarrer le service PostgreSQL pour prendre en compte les changements.

3.3. Création de bases de données spatiales

3.3.1. Base de données spatiale en utilisant EXTENSION

Si vous utilisez PostgreSQL 9.1+ et avez compilé et installé les modules extensions/postgis, vous pouvez transformer une base de données en base de données spatiale en utilisant le mécanisme EXTENSION.

L'extension cœur postgis inclus le support des types geometry et geography, la table spatial_ref_sys ainsi que toutes les fonctions et commentaires. Les supports de raster et topologie sont fournis par des extensions dédiées.

Exécutez les requêtes SQL suivantes dans la base de données où vous souhaitez activer le support spatial :

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

3.3.2. Base de données spatiale sans utiliser EXTENSION (non recommandé)

[Note]

Cette méthode n'est en générale nécessaire que si vous ne pouvez pas ou ne voulez pas que PostGIS soit installé dans le répertoire des extensions PostgreSQL (par exemple pour des tests, du développement, ou dans un environnement restreint).

L'ajout des objets et définitions des fonctions PostGIS dans votre base de données se fait en chargeant plusieurs fichiers sql présents dans [prefix]/share/contrib, cet emplacement est celui qui a été défini durant la phase de compilation.

Les objets au cœur de PostGIS (types geometry et geography, et les fonctions associées) sont dans le script postgis.sql. Les objets raster sont dans le script rtpostgis.sql. Les objets de topologie sont dans le script topology.sql.

Pour avoir la liste complète des définitions des systèmes de coordonnées EPSG, vous pouvez aussi charger le script spatial_ref_sys.sql pour remplir la table spatial_ref_sys. Cela permettre d'utiliser la fonction ST_Transform() pour effectuer des reprojections sur les géométries.

Si vous souhaitez ajouter les commentaires sur les fonctions PostGIS, vous pouvez les trouver dans le script postgis_comments.sql. Vous pouvez accéder aux commentaires d'une fonction en tapant \dd [nom_de_la_fonction] depuis un terminal psql.

Exécutez les commandes Shell suivantes dans votre terminal :

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. Mise à jour des bases de données spatiales

La mise à jour des bases de données spatiales existantes peut s'avérer délicate car elle nécessite le remplacement ou l'introduction de nouvelles définitions d'objets PostGIS.

Malheureusement, toutes les définitions ne peuvent pas être facilement remplacées dans une base de données active, de sorte que la meilleure solution consiste parfois à effectuer un processus de dump/rechargement.

PostGIS propose une procédure SOFT UPGRADE pour les versions mineures ou les corrections de bugs, et une procédure HARD UPGRADE pour les versions majeures.

Avant d'essayer de mettre à jour PostGIS, il est toujours utile de sauvegarder vos données. Si vous utilisez l'option -Fc pour pg_dump, vous serez toujours en mesure de restaurer le dump lors d'un HARD UPGRADE.

3.4.1. Mise à niveau progressive (Soft upgrade)

Si vous avez installé votre base de données en utilisant des extensions, vous devrez également mettre à jour en utilisant le modèle d'extension. Si vous avez installé votre base de données à l'aide de l'ancien script SQL, il vous est conseillé de passer à l'extension car le script n'est plus pris en charge.

3.4.1.1. Mise à niveau progressive (Soft upgrade) 9.1+ utilisant des extensions

Si vous avez installé PostGIS à l'origine avec des extensions, vous devez également effectuer une mise à jour en utilisant des extensions. Faire une mise à jour mineure avec les extensions est assez facile.

Si vous utilisez PostGIS 3 ou une version supérieure, vous devez utiliser la fonction PostGIS_Extensions_Upgrade pour passer à la dernière version que vous avez installée.

SELECT postgis_extensions_upgrade();

Si vous utilisez PostGIS 2.5 ou une version inférieure, procédez comme suit :

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

Si plusieurs versions de PostGIS sont installées et que vous ne souhaitez pas mettre à niveau vers la dernière version, vous pouvez spécifier explicitement la version comme suit :

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

Si vous obtenez un message d'erreur du type :

No migration path defined for … to 3.5.0dev

Vous devrez alors sauvegarder votre base de données, en créer une nouvelle comme décrit dans Section 3.3.1, “Base de données spatiale en utilisant EXTENSION”, puis restaurer votre sauvegarde sur cette nouvelle base de données.

Si vous obtenez un message du type :

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

Dans ce cas, tout est déjà à jour et vous pouvez l'ignorer en toute sécurité. Sauf vous essayez de passer d'une version de développement à la suivante (qui ne reçoit pas de nouveau numéro de version) ; dans ce cas, vous pouvez ajouter "next" à la chaîne de caractères de la version, et la prochaine fois, vous devrez à nouveau supprimer le suffixe "next" :

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

Si vous avez installé PostGIS à l'origine sans spécifier de version, vous pouvez souvent ignorer la réinstallation de l'extension postgis avant la restauration puisque la sauvegarde a juste exécuté CREATE EXTENSION postgis et récupère donc la dernière version la plus récente lors de la restauration.

[Note]

Si vous mettez à jour l'extension PostGIS à partir d'une version antérieure à 3.0.0, vous aurez une nouvelle extension postgis_raster que vous pouvez abandonner en toute sécurité, si vous n'avez pas besoin du support raster. Vous pouvez abandonner l'extension comme suit :

DROP EXTENSION postgis_raster;

3.4.1.2. Mise à niveau progressive (Soft Upgrade) Pre 9.1+ ou sans extensions

Cette section ne s'applique qu'à ceux qui ont installé PostGIS sans utiliser d'extensions. Si vous avez des extensions et que vous essayez de faire une mise à jour avec cette approche, vous obtiendrez des messages comme :

can't drop … because postgis extension depends on it

NOTE : si vous passez de PostGIS 1.* à PostGIS 2.* ou de PostGIS 2.* antérieur à r7409, vous ne pouvez pas utiliser cette procédure mais devrez plutôt faire une HARD UPGRADE.

Après la compilation et l'installation (make install), vous devriez trouver un ensemble de fichiers *_upgrade.sql dans les dossiers d'installation. Vous pouvez les lister avec :

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

Chargez-les tous à tour de rôle, en commençant par postgis_upgrade.sql.

psql -f postgis_upgrade.sql -d your_spatial_database

La même procédure s'applique aux extensions raster, topologie et sfcgal, avec des fichiers de mise à niveau nommés respectivement rtpostgis_upgrade.sql, topology_upgrade.sql et sfcgal_upgrade.sql. Si vous en avez besoin :

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

Il est conseillé de passer à une installation basée sur l'extension en lançant

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

Si vous ne trouvez pas le postgis_upgrade.sql spécifique à la mise à niveau de votre version, vous utilisez une version trop ancienne pour une mise à niveau progressive (Soft Upgrade) et vous devez faire une HARD UPGRADE.

La fonction PostGIS_Full_Version devrait vous informer de la nécessité d'exécuter ce type de mise à niveau à l'aide d'un message "procs need upgrade".

3.4.2. Mise à niveau complète (Hard upgrade)

Par HARD UPGRADE, nous entendons un dump/recharge complet des bases de données compatibles avec PostGIS. Vous avez besoin d'une mise à jour complète lorsque le stockage interne des objets PostGIS change ou lorsque la mise à jour progressive (Soft upgrade) n'est pas possible. L'annexe Release Notes indique pour chaque version si vous avez besoin d'un dump/recharge (HARD UPGRADE) pour effectuer la mise à jour.

Le processus de dump/rechargement est assisté par le script postgis_restore.pl qui se charge de sauter du dump toutes les définitions qui appartiennent à PostGIS (y compris les anciennes), ce qui vous permet de restaurer vos schémas et vos données dans une base de données où PostGIS est installé sans obtenir d'erreurs de symboles dupliqués ou d'avancer des objets obsolètes.

Des instructions supplémentaires pour les utilisateurs de Windows sont disponibles sur Windows Hard upgrade.

La procédure est la suivante :

  1. Créer un dump au format personnalisé de la base de données que vous souhaitez mettre à jour (appelons-le olddb) et inclure des blobs binaires (-b) et une sortie verbose (-v). L'utilisateur peut être le propriétaire de la base de données, il n'a pas besoin d'être le super utilisateur postgres.

    pg_dump -h localhost -p 5432 -U postgres -Fc -b -v -f "/somepath/olddb.backup" olddb
  2. Effectuez une nouvelle installation de PostGIS dans une nouvelle base de données - nous appellerons cette base de données newdb. Veuillez vous référer à Section 3.3.2, “Base de données spatiale sans utiliser EXTENSION (non recommandé)” et Section 3.3.1, “Base de données spatiale en utilisant EXTENSION” pour les instructions sur la façon de procéder.

    Les entrées spatial_ref_sys trouvées dans votre dump seront restaurées, mais elles ne remplaceront pas les entrées existantes dans spatial_ref_sys. Cela permet de s'assurer que les corrections du jeu officiel seront correctement propagées dans les bases de données restaurées. Si, pour une raison quelconque, vous souhaitez vraiment que vos propres entrées standard soient remplacées, ne chargez pas le fichier spatial_ref_sys.sql lors de la création de la nouvelle base de données.

    Si votre base de données est très ancienne ou si vous savez que vous avez utilisé des fonctions dépréciées depuis longtemps dans vos vues et fonctions, vous devrez peut-être charger legacy.sql pour que toutes vos fonctions, vues, etc. reviennent correctement. Ne le faites que si c'est _vraiment_ nécessaire. Envisagez plutôt de mettre à jour vos vues et fonctions avant de faire le dumping, si possible. Les fonctions dépréciées peuvent être supprimées plus tard en chargeant uninstall_legacy.sql.

  3. Restaurez votre sauvegarde dans votre nouvelle base de données newdb à l'aide de postgis_restore. Les erreurs inattendues, s'il y en a, seront imprimées par psql dans le flux d'erreurs standard. Conservez un journal de ces erreurs.

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

Des erreurs peuvent survenir dans les cas suivants :

  1. Certaines de vos vues ou fonctions utilisent des objets PostGIS obsolètes. Pour y remédier, vous pouvez essayer de charger le script legacy.sql avant la restauration ou vous devrez restaurer vers une version de PostGIS qui contient encore ces objets et réessayer une migration après le portage de votre code. Si la méthode legacy.sql fonctionne pour vous, n'oubliez pas de corriger votre code pour arrêter d'utiliser les fonctions dépréciées et de les supprimer en chargeant uninstall_legacy.sql.

  2. Certains enregistrements personnalisés de spatial_ref_sys dans le fichier dump ont une valeur SRID invalide. Les valeurs SRID valides sont supérieures à 0 et inférieures à 999000. Les valeurs comprises entre 999000 et 999999 sont réservées à un usage interne, tandis que les valeurs > 999999 ne peuvent pas être utilisées du tout. Tous vos enregistrements personnalisés avec des SRID invalides seront conservés, avec les valeurs > 999999 déplacées dans la plage réservée, mais la table spatial_ref_sys perdrait une contrainte de contrôle qui protège cet invariant et peut-être aussi sa clé primaire (lorsque plusieurs SRIDS invalides sont convertis en la même valeur SRID réservée).

    Pour résoudre ce problème, vous devez copier votre SRS personnalisé vers un SRID avec une valeur valide (peut-être dans la plage 910000..910999), convertir toutes vos tables vers le nouveau SRID (voir UpdateGeometrySRID), supprimer l'entrée invalide de spatial_ref_sys et reconstruire le(s) contrôle(s) avec :

    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));
        

    Si vous mettez à jour une ancienne base de données contenant une cartographie française IGN , vous aurez probablement des SRID hors limites et vous verrez, lors de l'importation de votre base de données, des problèmes comme celui-ci :

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

    Dans ce cas, vous pouvez essayer les étapes suivantes : d'abord supprimer complètement l'IGN du sql qui résulte de postgis_restore. Ainsi, après avoir exécuté :

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

    Exécutez cette commande :

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

    Créez ensuite votre newdb, activez les extensions Postgis nécessaires, et insérez correctement l'IGN du système français avec : ce script Après ces opérations, importez vos données :

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