Modules d’extension officiels de PostgreSQL

ServBay, en tant qu’environnement local de développement Web puissant, intègre de nombreux outils essentiels, dont la base de données PostgreSQL riche en fonctionnalités. La force de PostgreSQL réside non seulement dans son noyau, mais aussi dans son vaste écosystème d’extensions officielles. ServBay inclut la plupart des modules d’extension officiels de PostgreSQL préinstallés, permettant aux développeurs de les activer et de les utiliser facilement dans leur environnement local, étendant considérablement les capacités de la base de données pour répondre à des besoins applicatifs complexes.

Cet article présente quelques modules d’extension officiels PostgreSQL couramment utilisés et vous guide sur leur installation et leur utilisation dans ServBay.

Présentation des extensions les plus courantes

PostgreSQL propose de nombreux modules d’extension couvrant la surveillance des performances, les types de données, la recherche plein texte, et bien plus. Voici une sélection d’extensions fréquemment utilisées par les développeurs au quotidien :

1. pg_stat_statements

• Fonctionnalités : Collecte et enregistre des statistiques sur toutes les instructions SQL exécutées par le serveur, telles que le nombre d’appels, la durée totale d’exécution, le nombre de lignes traitées, etc.
• Utilisation : Aide les administrateurs et développeurs à analyser les performances des requêtes, à identifier les requêtes lentes et à détecter les goulots d’étranglement.
• Commande d’installation :

  CREATE EXTENSION pg_stat_statements;

  Remarque : après l’installation, il est généralement nécessaire de configurer shared_preload_libraries = 'pg_stat_statements' dans le fichier postgresql.conf et de redémarrer le service PostgreSQL pour l’activer complètement et collecter les données. ServBay offre une méthode simple pour modifier cette configuration.

2. pg_trgm

• Fonctionnalités : Fournit des fonctions de mesure de similarité de texte basées sur les trigrammes ainsi que le support d’indexation.
• Utilisation : Permet la mise en place de recherches floues, de recherche de similarité et de recherche plein texte efficaces ; idéal pour gérer les fautes de frappe et les variantes orthographiques.
• Commande d’installation :

  CREATE EXTENSION pg_trgm;

3. hstore

• Fonctionnalités : Fournit un type de données permettant de stocker un ensemble de paires clé-valeur dans une seule valeur PostgreSQL.
• Utilisation : Adapté aux scénarios nécessitant de stocker des attributs dynamiques, des données non structurées ou semi-structurées, comme les préférences utilisateur, les métadonnées ou des données de configuration, sans structure de table rigide.
• Commande d’installation :

  CREATE EXTENSION hstore;

4. citext

• Fonctionnalités : Introduit un type de texte insensible à la casse.
• Utilisation : Utile lorsqu’on doit comparer des chaînes sans tenir compte de la casse, par exemple pour les noms d’utilisateur, adresses e-mail ou tags, en évitant le recours systématique à la fonction LOWER() dans les requêtes.
• Commande d’installation :

  CREATE EXTENSION citext;

5. uuid-ossp

• Fonctionnalités : Offre des fonctions pour générer des identifiants universellement uniques (UUID) selon plusieurs versions standards (1, 3, 4, 5).
• Utilisation : Utile pour générer des identifiants uniques globalement, notamment en tant que clé primaire, ID de session ou marqueur dans les systèmes distribués.
• Commande d’installation :

  CREATE EXTENSION "uuid-ossp";

  Remarque : le nom de l’extension contient un tiret, il doit donc être entouré de guillemets doubles dans la commande CREATE EXTENSION.

6. intarray

• Fonctionnalités : Propose des fonctions supplémentaires et des opérateurs pour manipuler les tableaux d’entiers.
• Utilisation : Idéal pour effectuer des opérations de groupe, des tests d’inclusion, du tri et d’autres opérations avancées sur des tableaux d’entiers directement dans la base de données.
• Commande d’installation :

  CREATE EXTENSION intarray;

7. btree_gin

• Fonctionnalités : Apporte le support des index GIN (Generalized Inverted Index) pour les types indexés habituellement en B-tree (entiers, texte, dates, etc.).
• Utilisation : Les index GIN sont souvent utilisés pour indexer des types contenant plusieurs valeurs (tableaux, hstore, tsvector), et btree_gin permet de bénéficier des GIN sur les éléments indexables en B-tree, améliorant ainsi les performances des requêtes pour ces types composites.
• Commande d’installation :

  CREATE EXTENSION btree_gin;

8. btree_gist

• Fonctionnalités : Apporte le support des index GiST (Generalized Search Tree) aux types de données indexés en B-tree.
• Utilisation : GiST est une structure d’indexation générique. btree_gist autorise l’utilisation d’index GiST sur des types de données standards, pratique pour combiner plusieurs types de requêtes (recherche de plage, inclusion, etc.) et pour la création d’index multi-colonnes.
• Commande d’installation :

  CREATE EXTENSION btree_gist;

9. cube

• Fonctionnalités : Introduit un type pour représenter des cubes multidimensionnels ou des points, avec les opérateurs et fonctions associés.
• Utilisation : Convenable pour la manipulation de données multidimensionnelles, comme certaines données géospatiales simples (points 2D/3D), l’analyse multidimensionnelle dans un data warehouse ou pour certains calculs scientifiques.
• Commande d’installation :

  CREATE EXTENSION cube;

10. fuzzystrmatch

• Fonctionnalités : Met à disposition diverses fonctions pour mesurer la similarité ou la distance entre des chaînes (distance de Levenshtein, Soundex, Metaphone, etc.).
• Utilisation : Pour mettre en place la recherche floue de chaînes, la correction orthographique, le nettoyage et la déduplication de données.
• Commande d’installation :

  CREATE EXTENSION fuzzystrmatch;

11. plpgsql

• Fonctionnalités : Langage procédural de PostgreSQL, permettant d’écrire des procédures stockées, des fonctions déclencheurs et des blocs anonymes.
• Utilisation : Pour réaliser des logiques métiers complexes côté serveur, automatiser des tâches et traiter des flux de données directement dans la base. plpgsql est installé par défaut, mais l’utilisation de CREATE EXTENSION permet de vérifier ou de recréer explicitement ce langage dans la base.
• Commande d’installation :

  CREATE EXTENSION plpgsql;

12. tablefunc

• Fonctionnalités : Fournit des fonctions pour manipuler et transformer des tables complètes, notamment la célèbre fonction crosstab.
• Utilisation : Permet de réaliser des tableaux croisés (pivot), c’est-à-dire de transformer les valeurs uniques d’une colonne en plusieurs colonnes, utile pour des rapports ou de l’analyse de données.
• Commande d’installation :

  CREATE EXTENSION tablefunc;

13. dblink

• Fonctionnalités : Permet de se connecter et d’exécuter des requêtes sur une autre base de données PostgreSQL depuis la base en cours.
• Utilisation : Idéal pour des cas simples de requêtes interbases ou de transfert de données sans avoir à gérer la logique de connexion dans l’application.
• Commande d’installation :

  CREATE EXTENSION dblink;

14. postgres_fdw

• Fonctionnalités : Fournit un wrapper de données externes (Foreign Data Wrapper) pour accéder à des tables d’une autre base PostgreSQL comme si elles étaient locales.
• Utilisation : Outil puissant pour l’intégration de données et les requêtes fédérées entre plusieurs bases PostgreSQL, offrant un niveau d’optimisation et de souplesse supérieur à dblink.
• Commande d’installation :

  CREATE EXTENSION postgres_fdw;

15. pgcrypto

• Fonctionnalités : Met à disposition diverses fonctions de chiffrement et de déchiffrement : hachage, chiffrement symétrique, chiffrement à clé publique, etc.
• Utilisation : Pour chiffrer les données, stocker des mots de passe hachés, générer ou vérifier des signatures numériques directement au niveau de la base de données.
• Commande d’installation :

  CREATE EXTENSION pgcrypto;

Cette liste n’est qu’un aperçu des modules d’extension officiels de PostgreSQL disponibles dans ServBay. ServBay s’efforce d’inclure la grande majorité des extensions communautaires officielles afin de répondre à tous les besoins des développeurs.

Installer et utiliser les extensions PostgreSQL dans ServBay

Installer et utiliser les modules d’extension PostgreSQL dans l’environnement ServBay est simple et intuitif. Les extensions sont installées par base de données, ce qui signifie qu’il faut les activer séparément dans chaque base nécessitant une fonctionnalité donnée.

Prérequis :

• Vérifiez que ServBay est en cours d’exécution et que le service PostgreSQL est bien démarré.

Étapes d’utilisation :

1. Se connecter à la base PostgreSQL cible : Ouvrez votre terminal (par exemple, Terminal ou iTerm2 sous macOS) et connectez-vous à la base souhaitée à l’aide de l’outil en ligne de commande psql. Par défaut, l’utilisateur PostgreSQL créé par ServBay est postgres, tout comme le nom de la base de données.

   psql -U your_username -d your_database

   Exemple pour se connecter à la base postgres par défaut :

   psql -U postgres -d postgres

   Si un mot de passe est demandé, saisissez le mot de passe défini dans ServBay pour l’utilisateur root/postgres de PostgreSQL.

2. Installer le module d’extension : Une fois connecté, vous êtes dans l’interface en ligne de commande psql. Lancez la commande CREATE EXTENSION pour activer l’extension souhaitée, remplacez module_name par le nom de l’extension désirée.

   CREATE EXTENSION module_name;

   Exemple pour installer l’extension pg_trgm :

   CREATE EXTENSION pg_trgm;

3. Vérifier l’installation : Après installation, affichez la liste des extensions actives via la commande \dx afin de vérifier que votre extension a bien été ajoutée.

   \dx

Exemple : Activation de l’extension pg_trgm

Voici un exemple complet d’une session psql où l’on se connecte à la base de données postgres et où l’on active l’extension pg_trgm :

# Connectez-vous à la base de données via le terminal
psql -U postgres -d postgres

# Une fois connecté, le prompt psql s’affiche
psql (16.3 (ServBay))
Type "help" for help.

postgres=# -- Afficher les extensions installées (par défaut, vous trouverez plpgsql et pgcrypto)
postgres=# \dx
                  List of installed extensions
   Name   | Version |   Schema   |         Description          
----------+---------+------------+------------------------------
 pgcrypto | 1.3     | public     | cryptographic functions
 plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

postgres=# -- Installer l’extension pg_trgm
postgres=# CREATE EXTENSION pg_trgm;
CREATE EXTENSION

postgres=# -- Vérifier à nouveau les extensions installées, pg_trgm doit maintenant figurer dans la liste
postgres=# \dx
                                    List of installed extensions
   Name   | Version |   Schema   |                            Description                            
----------+---------+------------+-------------------------------------------------------------------
 pg_trgm  | 1.6     | public     | text similarity measurement and index searching based on trigrams
 pgcrypto | 1.3     | public     | cryptographic functions
 plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

postgres=# -- L’installation est un succès, vous pouvez maintenant profiter des fonctions et opérateurs de pg_trgm dans cette base

Lister les extensions PostgreSQL disponibles dans ServBay

Avant d’installer une extension, il peut être utile de découvrir toutes celles que votre version ServBay de PostgreSQL met à disposition. Ceci vous permet d’explorer de nouvelles fonctionnalités potentielles.

Une fois connecté à n’importe quelle base PostgreSQL, exécutez la requête SQL suivante pour obtenir la liste complète des extensions disponibles :

SELECT * FROM pg_available_extensions ORDER BY name;

Cette commande retourne un tableau listant toutes les extensions supportées par votre version de PostgreSQL intégrée à ServBay, en indiquant leur nom, la version par défaut, s’il y a une version installée et leur description.

Voici un exemple de résultat (la liste est généralement longue, le contenu et les versions varient selon la version de ServBay et de PostgreSQL) :

             name             | default_version | installed_version |                                                       comment                                                       
------------------------------+-----------------+-------------------+---------------------------------------------------------------------------------------------------------------------
 address_standardizer         | 3.4.2           |                   | Used to parse an address into constituent elements. Generally used to support geocoding address normalization step.
 address_standardizer_data_us | 3.4.2           |                   | Address Standardizer US dataset example
 adminpack                    | 2.1             |                   | administrative functions for PostgreSQL
 amcheck                      | 1.3             |                   | functions for verifying relation integrity
 autoinc                      | 1.0             |                   | functions for autoincrementing fields
 bloom                        | 1.0             |                   | bloom access method - signature file based index
 btree_gin                    | 1.3             |                   | support for indexing common datatypes in GIN
 btree_gist                   | 1.7             |                   | support for indexing common datatypes in GiST
 citext                       | 1.6             |                   | data type for case-insensitive character strings
 cube                         | 1.5             |                   | data type for multidimensional cubes
 dblink                       | 1.2             |                   | connect to other PostgreSQL databases from within a database
 dict_int                     | 1.0             |                   | text search dictionary template for integers
 dict_xsyn                    | 1.0             |                   | text search dictionary template for extended synonym processing
 earthdistance                | 1.1             |                   | calculate great-circle distances on the surface of the Earth
 file_fdw                     | 1.0             |                   | foreign-data wrapper for flat file access
 fuzzystrmatch                | 1.2             |                   | determine similarities and distance between strings
 hstore                       | 1.8             |                   | data type for storing sets of (key, value) pairs
 insert_username              | 1.0             |                   | functions for tracking who changed a table
 intagg                       | 1.1             |                   | integer aggregator and enumerator (obsolete)
 intarray                     | 1.5             |                   | functions, operators, and index support for 1-D arrays of integers
 isn                          | 1.2             |                   | data types for international product numbering standards
 lo                           | 1.1             |                   | Large Object maintenance
 ltree                        | 1.2             |                   | data type for hierarchical tree-like structures
 moddatetime                  | 1.0             |                   | functions for tracking last modification time
 old_snapshot                 | 1.0             |                   | utilities in support of old_snapshot_threshold
 pageinspect                  | 1.12            |                   | inspect the contents of database pages at a low level
 pg_buffercache               | 1.4             |                   | examine the shared buffer cache
 pg_freespacemap              | 1.2             |                   | examine the free space map (FSM)
 pg_jieba                     | 1.1.1           |                   | a parser for full-text search of Chinese
 pg_prewarm                   | 1.2             |                   | prewarm relation data
 pg_stat_statements           | 1.10            |                   | track planning and execution statistics of all SQL statements executed
 pg_surgery                   | 1.0             |                   | extension to perform surgery on a damaged relation
 pg_trgm                      | 1.6             | 1.6               | text similarity measurement and index searching based on trigrams
 pg_visibility                | 1.2             |                   | examine the visibility map (VM) and page-level visibility info
 pg_walinspect                | 1.1             |                   | functions to inspect contents of PostgreSQL Write-Ahead Log
 pgcrypto                     | 1.3             | 1.3               | cryptographic functions
 pgrouting                    | 3.6.2           |                   | pgRouting Extension
 pgrowlocks                   | 1.2             |                   | show row-level locking information
 pgstattuple                  | 1.5             |                   | show tuple-level statistics
 plpgsql                      | 1.0             | 1.0               | PL/pgSQL procedural language
 postgis                      | 3.4.2           |                   | PostGIS geometry and geography spatial types and functions
 postgis_sfcgal               | 3.4.2           |                   | PostGIS SFCGAL functions
 postgis_tiger_geocoder       | 3.4.2           |                   | PostGIS tiger geocoder and reverse geocoder
 postgres_fdw                 | 1.1             |                   | foreign-data wrapper for remote PostgreSQL servers
 refint                       | 1.0             |                   | functions for implementing referential integrity (obsolete)
 seg                          | 1.4             |                   | data type for representing line segments or floating-point intervals
 sslinfo                      | 1.2             |                   | information about SSL certificates
 tablefunc                    | 1.0             |                   | functions that manipulate whole tables, including crosstab
 tcn                          | 1.0             |                   | Triggered change notifications
 tsm_system_rows              | 1.0             |                   | TABLESAMPLE method which accepts number of rows as a limit
 tsm_system_time              | 1.0             |                   | TABLESAMPLE method which accepts time in milliseconds as a limit
 unaccent                     | 1.1             |                   | text search dictionary that removes accents
 vector                       | 0.7.0           |                   | vector data type and ivfflat and hnsw access methods
 xml2                         | 1.1             |                   | XPath querying and XSLT
 zhparser                     | 2.2             |                   | a parser for full-text search of Chinese
(55 rows)

Attention : le contenu de la liste et les numéros de version peuvent varier selon les versions de ServBay ou de PostgreSQL installées.

Conclusion

Grâce à la préinstallation d’un large éventail d’extensions officielles PostgreSQL, ServBay simplifie grandement l’activation et l’exploitation de leurs fonctionnalités avancées dans votre environnement local. Les développeurs n’ont pas besoin de compiler ou d’installer manuellement quoi que ce soit : il suffit d’exécuter une simple commande SQL pour activer l’extension nécessaire et se consacrer pleinement au développement de leur application, en gagnant en productivité. En tirant parti de ces extensions, vous pouvez enrichir vos projets de fonctionnalités avancées telles que l’analyse des performances, la recherche pleine texte, la sécurité des données, etc.