Installation de l'extension lo Utilisation de la fonction de déclenchement lo_manage Suppression de gros objets orphelins à l'aide de vacuumlo Comprendre le fonctionnement d'vacuumlo Améliorer les vacuumlo performances Considérations relatives aux objets de grande taille

Gestion des objets volumineux avec le module lo

Le module lo (extension) est destiné aux utilisateurs de bases de données et aux développeurs travaillant avec des SQL bases de données Postgre via JDBC nos ODBC pilotes. Les deux JDBC et ODBC attendez-vous à ce que la base de données gère la suppression d'objets volumineux lorsque les références à ces objets changent. Cependant, Postgre SQL ne fonctionne pas de cette façon. Postgre SQL ne suppose pas qu'un objet doive être supprimé lorsque sa référence change. Les objets restent donc sur le disque, sans référence. L'extension lo inclut une fonction qui se déclenche en cas de changement de référence afin de supprimer des objets si nécessaire.

Astuce

Pour déterminer si votre base de données peut bénéficier de l'extension lo, utilisez l'vacuumloutilitaire pour vérifier la présence d'objets volumineux orphelins. Pour obtenir le nombre d'objets volumineux orphelins sans effectuer aucune action, exécutez l'utilitaire avec l'option -n (no-op). Pour savoir comment procéder, consultez vacuumlo utility.

Le module lo est disponible pour Aurora Postgre SQL 13.7, 12.11, 11.16, 10.21 et versions mineures supérieures.

Pour installer le module (extension), vous avez besoin de privilèges rds_superuser. L'installation de l'extension lo ajoute ce qui suit à votre base de données :

lo— Il s'agit d'un type de données d'objet volumineux (lo) que vous pouvez utiliser pour les grands objets binaires (BLOBs) et d'autres objets de grande taille. Le type de données lo est un domaine du type de données oid. En d'autres termes, il s'agit d'un identificateur d'objet avec des contraintes facultatives. Pour plus d'informations, consultez la section Identifiants d'objets dans la documentation PostgreSQL. En termes simples, vous pouvez utiliser le type de lo données pour distinguer les colonnes de votre base de données contenant de grandes références d'objets des autres identificateurs d'objets (OIDs).
lo_manage : fonction que vous pouvez utiliser dans les déclencheurs sur les colonnes de la table contenant des références d'objets volumineux. Lorsque vous supprimez ou modifiez une valeur qui fait référence à un objet volumineux, le déclencheur dissocie l'objet (lo_unlink) de sa référence. Utilisez le déclencheur sur une colonne uniquement si la colonne est la seule référence de base de données à l'objet volumineux.

Pour plus d'informations sur le module grands objets, consultez lo dans la SQL documentation de Postgre.

Installation de l'extension lo

Avant d'installer l'extension lo, assurez-vous que vous disposez de privilèges rds_superuser.

Pour installer l'extension Io

Utilisez-le psql pour vous connecter à l'instance de base de données principale de votre cluster de SQL base de données Aurora Postgre.
```
psql --host=your-cluster-instance-1.666666666666.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password
```
Lorsque vous y êtes invité, saisissez votre mot de passe. Le client psql se connecte à la base de données de connexions administratives par défaut, postgres=>, et l'affiche sous forme d'invite.

Installez l'extension comme suit.


postgres=> CREATE EXTENSION lo;
CREATE EXTENSION

Vous pouvez désormais utiliser le type de données lo pour définir des colonnes dans vos tables. Vous pouvez, par exemple, créer une table (images) qui contient des données d'image tramée. Vous pouvez utiliser le type de données lo pour une colonne raster, comme illustré dans l'exemple suivant, qui crée une table.


postgres=> CREATE TABLE images (image_name text, raster lo);

Utilisation de la fonction de déclenchement lo_manage pour supprimer des objets

Vous pouvez utiliser la fonction lo_manage dans un déclencheur sur une colonne lo ou d'autres colonnes d'objets volumineux pour les nettoyer (et éviter les objets orphelins) lorsque lo est mis à jour ou supprimé.

Pour configurer des déclencheurs sur les colonnes qui font référence à des objets volumineux

Effectuez l’une des actions suivantes :
- Créez un DELETE déclencheur BEFORE UPDATE OR sur chaque colonne pour contenir des références uniques à des objets de grande taille, en utilisant le nom de colonne pour l'argument.
```
postgres=> CREATE TRIGGER t_raster BEFORE UPDATE OR DELETE ON images
    FOR EACH ROW EXECUTE FUNCTION lo_manage(raster);
```
- Appliquez un déclencheur uniquement lorsque la colonne est en cours de mise à jour.
```
postgres=> CREATE TRIGGER t_raster BEFORE UPDATE OF images
    FOR EACH ROW EXECUTE FUNCTION lo_manage(raster);
```

La fonction de déclenchement lo_manage fonctionne uniquement dans le contexte de l'insertion ou de la suppression de données de colonne, en fonction de la façon dont vous définissez le déclencheur. Elle n'a aucun effet lorsque vous effectuez une opération DROP ou TRUNCATE sur une base de données. Cela signifie que vous devriez supprimer les colonnes d'objets de n'importe quelle table avant de procéder à la suppression de la base de données, pour éviter la création d'objets orphelins.

Par exemple, supposons que vous souhaitiez supprimer la base de données contenant la table images. Vous supprimez la colonne comme suit.


postgres=> DELETE FROM images COLUMN raster

En supposant que la fonction lo_manage est définie sur cette colonne pour gérer les suppressions, vous pouvez maintenant supprimer la table en toute sécurité.

Suppression de gros objets orphelins à l'aide de `vacuumlo`

L'vacuumloutilitaire identifie et supprime les objets volumineux orphelins des bases de données. Cet utilitaire est disponible depuis Postgre SQL 9.1.24. Si les utilisateurs de votre base de données travaillent régulièrement avec des objets volumineux, nous vous recommandons de les exécuter de vacuumlo temps en temps pour nettoyer les gros objets orphelins.

Avant d'installer l'extension lo, vous pouvez l'utiliser vacuumlo pour évaluer si votre cluster de SQL base de données Aurora Postgre peut en bénéficier. Pour ce faire, exécutez vacuumlo avec l'option -n (no-op) pour afficher les objets qui seraient supprimés, comme illustré dans l'exemple suivant :


$ vacuumlo -v -n -h your-cluster-instance-1.666666666666.aws-region.rds.amazonaws.com -p 5433 -U postgres docs-lab-spatial-db
Password:*****
Connected to database "docs-lab-spatial-db"
Test run: no large objects will be removed!
Would remove 0 large objects from database "docs-lab-spatial-db".

Comme indiqué dans la sortie, les objets volumineux orphelins ne posent aucun problème pour cette base de données.

Pour plus d'informations sur cet utilitaire, consultez vacuumlola SQL documentation Postgre.

Comprendre le fonctionnement d'`vacuumlo`

La vacuumlo commande supprime les grands objets orphelins (LOs) de votre SQL base de données Postgre sans affecter ni entrer en conflit avec vos tables utilisateur.

La commande fonctionne comme suit :

vacuumlocommence par créer une table temporaire contenant tous les Object IDs (OIDs) des grands objets de votre base de données.
vacuumloparcourt ensuite chaque colonne de la base de données qui utilise les types de données oid oulo. Si vacuumlo une correspondance est OID trouvée dans ces colonnes, elle est supprimée OID de la table temporaire. vacuumlovérifie uniquement les colonnes spécifiquement nommées oid ou lo ne vérifie pas les domaines basés sur ces types.
Les entrées restantes de la table temporaire représentent des entrées orphelinesLOs, qui sont vacuumlo ensuite supprimées en toute sécurité.

Améliorer les `vacuumlo` performances

Vous pouvez potentiellement améliorer les performances de vacuumlo en augmentant la taille du lot à l'aide de l'-loption. Cela permet d'vacuumloen traiter plusieurs LOs à la fois.

Si votre système dispose de suffisamment de mémoire et que vous pouvez placer la table temporaire entièrement en mémoire, l'augmentation des temp_buffers paramètres au niveau de la base de données peut améliorer les performances. Cela permet à la table de résider entièrement en mémoire, ce qui peut améliorer les performances globales.

La requête suivante estime la taille de la table temporaire :


SELECT
    pg_size_pretty(SUM(pg_column_size(oid))) estimated_lo_temp_table_size
FROM
    pg_largeobject_metadata;

Considérations relatives aux objets de grande taille

Vous trouverez ci-dessous quelques points importants à prendre en compte lorsque vous travaillez avec des objets de grande taille :

Vacuumloest la seule solution car il n'existe actuellement aucune autre méthode pour supprimer les orphelinsLOs.
Les outils tels que pglogical, la réplication logique native et AWS DMS qui utilisent des technologies de réplication ne prennent pas en charge la réplication d'objets de grande taille.
Lorsque vous concevez le schéma de votre base de données, évitez d'utiliser des objets volumineux dans la mesure du possible et envisagez d'utiliser d'autres types de données, bytea par exemple.
Exécutez vacuumlo régulièrement, au moins une fois par semaine, pour éviter les problèmes avec les orphelinsLOs.
Utilisez un déclencheur avec cette lo_manage fonction sur les tables qui stockent des objets de grande taille pour empêcher la création d'orphelinsLOs.

Avertissement JavaScript est désactivé ou n'est pas disponible dans votre navigateur.

Pour que vous puissiez utiliser la documentation AWS, Javascript doit être activé. Vous trouverez des instructions sur les pages d'aide de votre navigateur.

Conventions de rédaction

Utilisation de la prise en charge déléguée des extensions Amazon Aurora pour Postgre SQL

Gestion des données spatiales avec Post GIS