Re: Postgresql/Postgis : Trigger pour historisation/versionnement

Oui PostgreSQL propose les solutions internes notamment le couple fonction/trigger pour "versionner" votre table de maniere a conserver des archives des enregistrements et permettre une restauration si necessaire.

Vous souhaitez mettre en place une solution de versionnement dans le cas ou vous puissiez restaurer

L'archivage/versionnement surtout avec les objets spatiaux et notamment leurs index peut lourdement impacter les performances de votre bases
L'idee serait d'isoler ce schema ARCHIVE  soit directement sur un disque dedie pour eviter les engorgements sur disque I/O car cela se limite a de l'ecriture. Cela serait tout a fait possible grace a des tablespace et de modier le schema. Ce serait un tablespace sur un disque physique/virtuel et le schema ARCHIVE serait dans ce tablespace isole.
Ainsi ca permettrait "d'aerer" la base. Quand aux index spatiaux il est toujours recommand de les isoler sur un autre disque. Ce n'est pas la meme chose que les index classiques.
Tout cela serait sur du court terme.
Sur du long terme, la mise en place d'une solution de replication serait aussi recommande avec un delai de quelques heures en retard. Ca necessite de bien parametrer les 3 ou 4 parametres pour l'archivage des WAL.
S'il n'y a qu'une seule base repliquee pg_archivecleanup et restore_command peuvent etre finement parametre pour que le nettoyage des WAL archives se fasse sans trop de souci
Des idees en vrac comme ca

Le lundi 19 septembre 2022 à 07:11:07 UTC+2, celati Laurent <laurent(dot)celati(at)gmail(dot)com> a écrit :

Bonjour,
Je travaille avec Postgresql 13, Postgis et Qgis 3.22.
Mon besoin est de mettre en place au sein de ma base de données utilisée pour de la production de cartes geographiques, des triggers pour l'historisation, le versionnement de données.
L'idée : Au sein d'une table "OUVRAGE' (géométrie : points), différentes mises à jour de la table sont effectuées au fil du temps.

Le changement de version se produit dans le cas où :
•      de nouveaux objets sont ajoutés ou supprimés dans la table (INSERT / DELETE)
•      des objets sont mis à jour par de nouvelles versions d’objets annulant et remplaçant les objets existants (UPDATE).
Parfois, ces UPDATE concernent uniquement le champ geometrie  (changement de la localisation). Parfois d'autres champs/attributs (type d'ouvrage, referent technique, nom de la campagne de l'ouvrage, date de debut de construction, nom de la commune de localisation de l'ouvrage,etc.).

=> Ces INSERT/DELETE/UPDATE devront avoir pour effet, au sein du schéma "archive" (schéma dédié au stockage des tables/objets historisés/versionnés), la création d'une nouvelle table "OUVRAGE_V1", "OUVRAGE_V2", "OUVRAGE_V3",etc.

Exigences
•      L’objectif n'est PAS d'effectuer une montée en version à chaque modification (UPDATE) dans la table, mais d’identifier des phases structurantes.
Autrement dit, l'idée est que l'administrateur puisse décider, déclencher quand il juge pertinent, le processus de trigger/d'historisation-versionnement.

• Le versionnement à la table est préconisé" : les tables de versionnement (schema ARCHIVE) seront constituées d’éléments modifiés ET également des objets non modifiés de la table "OUVRAGE".
Ce choix a été retenue notamment pour faciliter la restauration des objets versionnés au niveau des projets qgis (client SIG) archivés.
Avantage : possibilité de se référer à une phase précise du projet Instant T.
Inconvénient : duplication des objets  même non modifiées au sein de la table archive.

L’administrateur pourrait activer une action d’historisation lorsque cela lui semble pertinent. Par exemple à la fin d’une session de mise à jour. Le mécanisme d’historisation est automatique.

Une personne saurait elle me guider, m'orienter sur les méthodes offertes par PostgreSQL/Postgis susceptibles de satisfaire mon besoin ?
Un grand merci. ;-) En espérant avoir été suffisamment clair.