Chapter 3. Administration de PostGIS
Prev     Next

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.

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;

En plus des paramètres de Postgres, PostGIS a quelques paramètres personnalisés qui sont listés dans Section 7.22, “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

  • 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

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

  • 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

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.

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

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, “Variables PostGIS GUC (Grand Unified Custom Variables)”.

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

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.

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

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

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

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.7.0dev";
ALTER EXTENSION postgis_topology UPDATE TO "3.7.0dev";

Si vous obtenez un message d'erreur du type :

No migration path defined for … to 3.7.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.7.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.7.0devnext";
ALTER EXTENSION postgis_topology UPDATE TO "3.7.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. 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. 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