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 :sqlRemarque : après l’installation, il est généralement nécessaire de configurer
CREATE EXTENSION pg_stat_statements;
1shared_preload_libraries = 'pg_stat_statements'
dans le fichierpostgresql.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 :sql
CREATE EXTENSION pg_trgm;
1
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 :sql
CREATE EXTENSION hstore;
1
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 :sql
CREATE EXTENSION citext;
1
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 :sqlRemarque : le nom de l’extension contient un tiret, il doit donc être entouré de guillemets doubles dans la commande
CREATE EXTENSION "uuid-ossp";
1CREATE 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 :sql
CREATE EXTENSION intarray;
1
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
), etbtree_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 :sql
CREATE EXTENSION btree_gin;
1
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 :sql
CREATE EXTENSION btree_gist;
1
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 :sql
CREATE EXTENSION cube;
1
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 :sql
CREATE EXTENSION fuzzystrmatch;
1
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 deCREATE EXTENSION
permet de vérifier ou de recréer explicitement ce langage dans la base. - Commande d’installation :sql
CREATE EXTENSION plpgsql;
1
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 :sql
CREATE EXTENSION tablefunc;
1
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 :sql
CREATE EXTENSION dblink;
1
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 :sql
CREATE EXTENSION postgres_fdw;
1
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 :sql
CREATE EXTENSION pgcrypto;
1
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 :
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 estpostgres
, tout comme le nom de la base de données.bashpsql -U your_username -d your_database
1Exemple pour se connecter à la base
postgres
par défaut :bashpsql -U postgres -d postgres
1Si un mot de passe est demandé, saisissez le mot de passe défini dans ServBay pour l’utilisateur root/postgres de PostgreSQL.
Installer le module d’extension : Une fois connecté, vous êtes dans l’interface en ligne de commande
psql
. Lancez la commandeCREATE EXTENSION
pour activer l’extension souhaitée, remplacezmodule_name
par le nom de l’extension désirée.sqlCREATE EXTENSION module_name;
1Exemple pour installer l’extension
pg_trgm
:sqlCREATE EXTENSION pg_trgm;
1Vé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.sql\dx
1
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
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.