Archives par étiquette : PostgreSQL

Mise à jour majeure de notre serveur de base de données

Le mois d’août se prête au tâches de fond. La semaine dernière nous avons réalisé la migration de notre instance PostgreSQL/PostGIS de la version 13 à la version 17.

L’occasion de passer d’un service « dockerisé » sur un hôte à l’OS proche de la fin de vie à un service s’exécutant sur un OS tout frais sur une machine plus costaude.

L’occasion aussi d’installer quelques extensions que nous attendions impatiemment.

L’extension pg_cron pour faciliter la mise en œuvre de tâches cron par l’équipe, mais surtout  pl-python pour plus d’interactions avec notre serveur ODK Central grâce aux fonctions pl-pyodk. De quoi partager des référentiels d’objets entre nos applications métier (web, lizmap et QGIS) et nos applications mobiles (ODK Collect) et entre nos bases de données transversales et « projet ».
Nous avons aussi installé le connecter ogr-fdw permettant d’inerragir depuis nos bases de données avec divers sources de données géo (services web, fichiers…)

Les grandes étapes :

Création du nouveau serveur

  • installation et mise à jour de l’OS
  • configuration du pare-feu
  • création et montage des volumes (un pour les données de PostgreSQL, un provisoire pour accueillir les dumps de l’ancien serveur
  • Installation de PostgreSQL et des paquets utiles aux extensions
  • Paramétrage et Tuning de l’instance PostgreSQL avec pg_tune

Isolement de l’ancien serveur

  • Arrêt des connexions sur l’ancienne instance (pg_hba.conf restrictif)
  • Arrêt de l’ensemble des tâches cron

Sauvegarde des rôles et des bases de données

26 bases de données sont servies par notre instance, la plus ancienne date de 2006 et elle est le point d’entrée de nos données métier dans le SI.

Restauration sur le nouvel hôte (une à une)

On commence par les rôles puis on restaure chacune des bases en redirigeant les messages d’erreur vers un fichier de log consulté en fin de processus

Mise en ligne du nouveau serveur

  • récupération de l’adresse IP de l’ancienne instance, de manière à ne pas casser les nombreuses paramétrages d’outils connectés à ce serveur
  • Test de connexion
  • Ouverture des accès (restauration du fichier pg_hba.conf)

Correction des erreurs rencontrées

Ces erreurs non bloquantes étaient toutes des erreurs rencontrées lors du peuplement des vues matérialisées. Certaines de ces vues utilisent des serveurs de données externe (FDW vers d’autres bases de l’instance) dont le port à changer avec la migration de l’instance. La modification du port des serveurs externes a permis de résoudre l’ensemble des erreurs.

Relance des tâches cron.

Ces tâches cron interrogent régulièrement des serveurs distants, PostgreSQL ou autres (ODK Central) pour collecter ou publier des données.

Bilan

Migration sans encombre et sans impact sur le travail de l’équipe. L’opération a débuté à 18h, une bonne pizza et une bonne playlist plus tard et le nouveau serveur et les services associés étaient fonctionnels le lendemain matin à 5h30.

Cette nouvelle architecture, non « dockerisée » permettra une montée de version plus aisée avec l’outil pg_upgrade.

pl-pyDOK

https://github.com/mathieubossaert/pl-pyodk

Un nouvel ensemble de fonction pour récupérer automatiquement les données de vos formulaires ODK dans votre base de données #PostgreSQL.

Ce nouvel ensemble de fonctions utilise pyODK (https://github.com/getodk/pyodk) dans des fonctions pl/python et permet l’utilisation de filtres (ex les données envoyées il y a moins de 48h).
Le principe est le même qu’avec Central2PG, une tâche planifiée interroge à la fréquence voulue le serveur ODK Central.

Lors du premier appel les tables qui accueilleront les données sont créées dans la base de destination et sont remplie.
Lors des appels suivants, si de nouvelles questions ont été ajoutées au formulaire, les colonnes correspondantes sont ajoutées aux tables, et les nouvelles données intégrées.

Vous trouverez dans ce dépôt un exemple complet :
– une image docker (de test) d’un serveur PostgreSQL avec les fonctions
– un formulaire ODK vierge
– les instructions pour récupérer les données dans PostgreSQL
– les instructions pour créer une tâche planifiée
– les instructions pour afficher les afficher dans QGIS)

    ODK pour la collecte de données géo dans PostGIS (⅓)

    Premier article d’une série de 3 sur Geotribu, consacrée à l’utilisation d’ODK avec PostGIS

    Premier article d’une série de 3 sur Geotribu, consacrée à l’utilisation d’ODK avec PostGIS.

    Merci à l’équipe pour l’accueil, les conseils, la relecture et les outils mis en place pour la publication.

    https://static.geotribu.fr/articles/2021/2021-06-08_odk_postgis_1/

    Transformation des medias créés dans ODKCollect en fichiers sur le serveur

    Objectif

    Écrire dans le système de fichier du serveur, les fichiers stockés sous forme de binaire dans PotgreSQL. La méthode est générique et fonctionne si de nouveaux formulaires sont créés ou si certains sont supprimés.

    Write to the server’s file system, files stored as binary in PotgreSQL. The method is generic and works if new forms are created or if some are deleted.

    Ressources

    https://github.com/opendatakit/aggregate/issues/164#issuecomment-347390275

    https://stackoverflow.com/questions/38900823/postgres-find-all-rows-in-database-tables-matching-criteria-on-a-given-column

    Principe

    Une table liste les objets binaires stockés dans la base de données ODK (toutes les tables dont le nom se termine par « BLB » et ne commence par par un «  » (tables système d’Aggregate)

    Elle stocke aussi le nom que nous souhaitons donner au fichier (à partir de champs de métadonnées d’Aggregate « _LAST_UPDATE_DATE » et « UNROOTED_FILE_PATH » Et enfin un entier mis à 1 quand le fichier a été généré dans sur le disque

    Pour générer les fichiers nous faisons appel à la fonction lo_export

    Les fichiers sont créés dans un répertoire sur lequel postgres à le droit d’écrire. Ce répertoire est servi sur le web par un serveur de fichier (minio)

    Les téléphones actuels peuvent facilement créer des images de 15 Mo… L’option proposée dans xlsform permettant de réduire la talle des images en définissant un nombre maximum de pixel pour le plus grand côté de l’image sera intéressante : https://docs.opendatakit.org/form-question-types/?highlight=select%20one#scaling-down-images

    A table lists the binary objects stored in the ODK database (all tables whose name ends with « BLB » and does not begin with a «  » (Aggregate system tables) It also stores the name we want to give to the file (from Aggregate’s « _LAST_UPDATE_DATE » and « UNROOTED_FILE_PATH » metadata fields) And finally an integer set to 1 when the file was generated in on the disk

    To generate the files we use the lo_export function

    Files are created in a directory on which postgres have the right to write. This directory is served on the web by a file server (minio)

    Current phones can easily create 15MB images… The option offered in xlsform to reduce image’s size will be interesting : https://docs.opendatakit.org/form-question-types/?highlight=select%20one#scaling-down-images