Extensions PostgreSQL dans Azure Database pour PostgreSQL - Serveur unique

S’APPLIQUE À : Azure Database pour PostgreSQL – Serveur unique

PostgreSQL offre la possibilité d’étendre les fonctionnalités d’une base de données à l’aide des extensions. Les extensions regroupent plusieurs objets SQL associés au sein d’un package qui peut être chargé ou supprimé de votre base de données à l’aide d’une seule commande. Une fois chargées dans la base de données, les extensions fonctionnent comme des fonctionnalités intégrées.

Guide pratique pour utiliser les extensions PostgreSQL

Les extensions PostgreSQL doivent être installées dans votre base de données pour être utilisables. Pour installer une extension donnée, exécutez la commande CRÉER UNE EXTENSION dans l’outil psql pour charger les objets empaquetés dans votre base de données.

Azure Database pour PostgreSQL prend en charge un sous-ensemble d’extensions clés, comme indiqué ci-dessous. Ces informations sont également disponibles en exécutant SELECT * FROM pg_available_extensions;. Les extensions qui ne figurent pas dans la liste ne sont pas prises en charge. Vous ne pouvez pas créer votre propre extension dans Azure Database pour PostgreSQL.

Extensions Postgres 11

Les extensions suivantes sont disponibles dans les serveurs Azure Database pour PostgreSQL qui disposent de la version 11 de Postgres.

Extension Version d’extension Description
address_standardizer 2.5.1 Utilisé pour analyser une adresse et la décomposer en éléments constitutifs,
address_standardizer_data_us 2.5.1 Exemple de jeu de données de normalisation des adresses aux États-Unis
btree_gin 1.3 Prise en charge de l’indexation des types de données communs dans GIN
btree_gist 1.5 Prise en charge de l’indexation des types de données communs dans GiST
citext 1.5 Type de données des chaînes de caractères sans respect de la casse
cube 1.4 Type de données pour les cubes multidimensionnels
dblink 1.2 Se connecte à d’autres bases de données PostgreSQL à partir d’une base de données
dict_int 1.0 Modèle de dictionnaire de recherche de texte pour les entiers
earthdistance 1.1 Calcule les distances orthodromiques à la surface de la Terre
fuzzystrmatch 1.1 Détermine les ressemblances et la distance entre les chaînes
hstore 1.5 Type de données permettant de stocker des paires clé/valeur
hypopg 1.1.2 Index hypothétiques pour PostgreSQL
intarray 1.2 Fonctions, opérateurs et prise en charge d’index pour les tableaux d’entiers 1D
isn 1.2 Types de données pour les standards internationaux de numérotation de produits
ltree 1.1 Type de données pour les structures hiérarchiques de type arborescence
orafce 3.7 Fonctions et opérateurs qui émulent un sous-ensemble de fonctions et de packages à partir de systèmes SGBDR commerciaux
pgaudit 1.3.1 fournit des fonctionnalités d’audit
pgcrypto 1.3 Fonctions de chiffrement
pgrouting 2.6.2 Extension pgRouting
pgrowlocks 1.2 Affiche les informations de verrouillage au niveau des lignes
pgstattuple 1.5 Affiche les statistiques au niveau du tuple
pg_buffercache 1.3 Examine le cache des tampons partagé
pg_partman 4.0.0 Extension pour gérer les tables partitionnées par date et heure ou par ID
pg_prewarm 1.2 Préchauffe les données de relation
pg_stat_statements 1.6 Suit les statistiques d’exécution de toutes les instructions SQL exécutées
pg_trgm 1.4 Mesure de la similarité du texte et recherche d’index sur la base de trigrammes
plpgsql 1.0 Langage procédural PL/pgSQL
plv8 2.3.11 Langage procédural PL/JavaScript (v8) approuvé
postgis 2.5.1 Géométrie, géographie et types et fonctions spatiaux raster PostGIS
postgis_sfcgal 2.5.1 Fonctions SFCGAL PostGIS
postgis_tiger_geocoder 2.5.1 PostGIS tiger geocoder et geocoder inverse
postgis_topology 2.5.1 Types et fonctions spatiaux de topologie PostGIS
postgres_fdw 1.0 Wrapper de données externes pour les serveurs PostgreSQL distants
tablefunc 1.0 Fonctions qui manipulent des tables entières, y compris les tables d’analyse croisée
timescaledb 1.7.4 Permet des insertions scalables et des requêtes complexes pour les données de séries chronologiques
unaccent 1.1 Dictionnaire de recherche de texte qui supprime les accents
uuid-ossp 1.1 Génère des identificateurs uniques universels (UUID)

Extensions Postgres 10

Les extensions suivantes sont disponibles dans les serveurs Azure Database pour PostgreSQL qui disposent de la version 10 de Postgres.

Extension Version d’extension Description
address_standardizer 2.5.1 Utilisé pour analyser une adresse et la décomposer en éléments constitutifs,
address_standardizer_data_us 2.5.1 Exemple de jeu de données de normalisation des adresses aux États-Unis
btree_gin 1.3 Prise en charge de l’indexation des types de données communs dans GIN
btree_gist 1.5 Prise en charge de l’indexation des types de données communs dans GiST
chkpass 1.0 Type de données pour les mots de passe chiffrés automatiquement
citext 1.4 Type de données des chaînes de caractères sans respect de la casse
cube 1.2 Type de données pour les cubes multidimensionnels
dblink 1.2 Se connecte à d’autres bases de données PostgreSQL à partir d’une base de données
dict_int 1.0 Modèle de dictionnaire de recherche de texte pour les entiers
earthdistance 1.1 Calcule les distances orthodromiques à la surface de la Terre
fuzzystrmatch 1.1 Détermine les ressemblances et la distance entre les chaînes
hstore 1.4 Type de données permettant de stocker des paires clé/valeur
hypopg 1.1.1 Index hypothétiques pour PostgreSQL
intarray 1.2 Fonctions, opérateurs et prise en charge d’index pour les tableaux d’entiers 1D
isn 1.1 Types de données pour les standards internationaux de numérotation de produits
ltree 1.1 Type de données pour les structures hiérarchiques de type arborescence
orafce 3.7 Fonctions et opérateurs qui émulent un sous-ensemble de fonctions et de packages à partir de systèmes SGBDR commerciaux
pgaudit 1.2 fournit des fonctionnalités d’audit
pgcrypto 1.3 Fonctions de chiffrement
pgrouting 2.5.2 Extension pgRouting
pgrowlocks 1.2 Affiche les informations de verrouillage au niveau des lignes
pgstattuple 1.5 Affiche les statistiques au niveau du tuple
pg_buffercache 1.3 Examine le cache des tampons partagé
pg_partman 2.6.3 Extension pour gérer les tables partitionnées par date et heure ou par ID
pg_prewarm 1.1 Préchauffe les données de relation
pg_stat_statements 1.6 Suit les statistiques d’exécution de toutes les instructions SQL exécutées
pg_trgm 1.3 Mesure de la similarité du texte et recherche d’index sur la base de trigrammes
plpgsql 1.0 Langage procédural PL/pgSQL
plv8 2.1.0 Langage procédural PL/JavaScript (v8) approuvé
postgis 2.4.3 Géométrie, géographie et types et fonctions spatiaux raster PostGIS
postgis_sfcgal 2.4.3 Fonctions SFCGAL PostGIS
postgis_tiger_geocoder 2.4.3 PostGIS tiger geocoder et geocoder inverse
postgis_topology 2.4.3 Types et fonctions spatiaux de topologie PostGIS
postgres_fdw 1.0 Wrapper de données externes pour les serveurs PostgreSQL distants
tablefunc 1.0 Fonctions qui manipulent des tables entières, y compris les tables d’analyse croisée
timescaledb 1.7.4 Permet des insertions scalables et des requêtes complexes pour les données de séries chronologiques
unaccent 1.1 Dictionnaire de recherche de texte qui supprime les accents
uuid-ossp 1.1 Génère des identificateurs uniques universels (UUID)

Extensions Postgres 9.6

Les extensions suivantes sont disponibles dans les serveurs Azure Database pour PostgreSQL qui disposent de la version 9.6 de Postgres.

Extension Version d’extension Description
address_standardizer 2.3.2 Utilisé pour analyser une adresse et la décomposer en éléments constitutifs,
address_standardizer_data_us 2.3.2 Exemple de jeu de données de normalisation des adresses aux États-Unis
btree_gin 1.0 Prise en charge de l’indexation des types de données communs dans GIN
btree_gist 1.2 Prise en charge de l’indexation des types de données communs dans GiST
chkpass 1.0 Type de données pour les mots de passe chiffrés automatiquement
citext 1.3 Type de données des chaînes de caractères sans respect de la casse
cube 1.2 Type de données pour les cubes multidimensionnels
dblink 1.2 Se connecte à d’autres bases de données PostgreSQL à partir d’une base de données
dict_int 1.0 Modèle de dictionnaire de recherche de texte pour les entiers
earthdistance 1.1 Calcule les distances orthodromiques à la surface de la Terre
fuzzystrmatch 1.1 Détermine les ressemblances et la distance entre les chaînes
hstore 1.4 Type de données permettant de stocker des paires clé/valeur
hypopg 1.1.1 Index hypothétiques pour PostgreSQL
intarray 1.2 Fonctions, opérateurs et prise en charge d’index pour les tableaux d’entiers 1D
isn 1.1 Types de données pour les standards internationaux de numérotation de produits
ltree 1.1 Type de données pour les structures hiérarchiques de type arborescence
orafce 3.7 Fonctions et opérateurs qui émulent un sous-ensemble de fonctions et de packages à partir de systèmes SGBDR commerciaux
pgaudit 1.1.2 fournit des fonctionnalités d’audit
pgcrypto 1.3 Fonctions de chiffrement
pgrouting 2.3.2 Extension pgRouting
pgrowlocks 1.2 Affiche les informations de verrouillage au niveau des lignes
pgstattuple 1.4 Affiche les statistiques au niveau du tuple
pg_buffercache 1.2 Examine le cache des tampons partagé
pg_partman 2.6.3 Extension pour gérer les tables partitionnées par date et heure ou par ID
pg_prewarm 1.1 Préchauffe les données de relation
pg_stat_statements 1.4 Suit les statistiques d’exécution de toutes les instructions SQL exécutées
pg_trgm 1.3 Mesure de la similarité du texte et recherche d’index sur la base de trigrammes
plpgsql 1.0 Langage procédural PL/pgSQL
plv8 2.1.0 Langage procédural PL/JavaScript (v8) approuvé
postgis 2.3.2 Géométrie, géographie et types et fonctions spatiaux raster PostGIS
postgis_sfcgal 2.3.2 Fonctions SFCGAL PostGIS
postgis_tiger_geocoder 2.3.2 PostGIS tiger geocoder et geocoder inverse
postgis_topology 2.3.2 Types et fonctions spatiaux de topologie PostGIS
postgres_fdw 1.0 Wrapper de données externes pour les serveurs PostgreSQL distants
tablefunc 1.0 Fonctions qui manipulent des tables entières, y compris les tables d’analyse croisée
timescaledb 1.7.4 Permet des insertions scalables et des requêtes complexes pour les données de séries chronologiques
unaccent 1.1 Dictionnaire de recherche de texte qui supprime les accents
uuid-ossp 1.1 Génère des identificateurs uniques universels (UUID)

Extensions Postgres 9.5

Notes

La version 9.5 de PostgreSQL a été retirée.

Les extensions suivantes sont disponibles dans les serveurs Azure Database pour PostgreSQL qui disposent de la version 9.5 de Postgres.

Extension Version d’extension Description
address_standardizer 2.3.0 Utilisé pour analyser une adresse et la décomposer en éléments constitutifs,
address_standardizer_data_us 2.3.0 Exemple de jeu de données de normalisation des adresses aux États-Unis
btree_gin 1.0 Prise en charge de l’indexation des types de données communs dans GIN
btree_gist 1.1 Prise en charge de l’indexation des types de données communs dans GiST
chkpass 1.0 Type de données pour les mots de passe chiffrés automatiquement
citext 1.1 Type de données des chaînes de caractères sans respect de la casse
cube 1.0 Type de données pour les cubes multidimensionnels
dblink 1.1 Se connecte à d’autres bases de données PostgreSQL à partir d’une base de données
dict_int 1.0 Modèle de dictionnaire de recherche de texte pour les entiers
earthdistance 1.0 Calcule les distances orthodromiques à la surface de la Terre
fuzzystrmatch 1.0 Détermine les ressemblances et la distance entre les chaînes
hstore 1.3 Type de données permettant de stocker des paires clé/valeur
hypopg 1.1.1 Index hypothétiques pour PostgreSQL
intarray 1.0 Fonctions, opérateurs et prise en charge d’index pour les tableaux d’entiers 1D
isn 1.0 Types de données pour les standards internationaux de numérotation de produits
ltree 1.0 Type de données pour les structures hiérarchiques de type arborescence
orafce 3.7 Fonctions et opérateurs qui émulent un sous-ensemble de fonctions et de packages à partir de systèmes SGBDR commerciaux
pgaudit 1.0.7 fournit des fonctionnalités d’audit
pgcrypto 1.2 Fonctions de chiffrement
pgrouting 2.3.0 Extension pgRouting
pgrowlocks 1.1 Affiche les informations de verrouillage au niveau des lignes
pgstattuple 1.3 Affiche les statistiques au niveau du tuple
pg_buffercache 1.1 Examine le cache des tampons partagé
pg_partman 2.6.3 Extension pour gérer les tables partitionnées par date et heure ou par ID
pg_prewarm 1.0 Préchauffe les données de relation
pg_stat_statements 1.3 Suit les statistiques d’exécution de toutes les instructions SQL exécutées
pg_trgm 1.1 Mesure de la similarité du texte et recherche d’index sur la base de trigrammes
plpgsql 1.0 Langage procédural PL/pgSQL
postgis 2.3.0 Géométrie, géographie et types et fonctions spatiaux raster PostGIS
postgis_sfcgal 2.3.0 Fonctions SFCGAL PostGIS
postgis_tiger_geocoder 2.3.0 PostGIS tiger geocoder et geocoder inverse
postgis_topology 2.3.0 Types et fonctions spatiaux de topologie PostGIS
postgres_fdw 1.0 Wrapper de données externes pour les serveurs PostgreSQL distants
tablefunc 1.0 Fonctions qui manipulent des tables entières, y compris les tables d’analyse croisée
unaccent 1.0 Dictionnaire de recherche de texte qui supprime les accents
uuid-ossp 1.0 Génère des identificateurs uniques universels (UUID)

pg_stat_statements

L’extension pg_stat_statements est préchargée sur chaque serveur Azure Database pour PostgreSQL afin de vous fournir un moyen de suivre les statistiques d’exécution des instructions SQL. Le paramètre pg_stat_statements.track, qui contrôle quelles instructions sont comptées par l’extension, a la valeur par défaut top, ce qui signifie que toutes les instructions exécutées directement par les clients sont suivies. Les deux autres niveaux de suivi sont none et all. Ce paramètre peut être configuré comme paramètre de serveur via le portail Azure ou Azure CLI.

Un compromis existe entre les informations d’exécution des requêtes fournies par pg_stat_statements et l’impact sur les performances du serveur en raison de la journalisation de chaque instruction SQL. Si vous n’utilisez pas l’extension pg_stat_statements de façon active, nous vous recommandons de définir pg_stat_statements.track sur none. Notez que certains services de surveillance tiers peuvent s’appuyer sur pg_stat_statements pour fournir des insights sur les performances des requêtes : vérifiez donc si c’est ou non le cas pour vous.

dblink et postgres_fdw vous permettent de vous connecter d’un serveur PostgreSQL à un autre ou à une autre base de données dans le même serveur. Le serveur de réception doit autoriser les connexions à partir du serveur d’envoi via son pare-feu. Quand vous utilisez ces extensions pour établir une connexion entre des serveurs Azure Database pour PostgreSQL, vous pouvez effectuer cette opération en définissant « Autoriser l’accès aux services Azure » sur Activé. Vous devez faire de même si vous souhaitez utiliser les extensions pour établir la connexion vers le même serveur. Le paramètre « Autoriser l’accès aux services Azure » se trouve dans la page du portail Azure dédiée au serveur Postgres, sous Sécurité de la connexion. L’activation du paramètre « Autoriser l’accès aux services Azure » place toutes les adresses IP Azure sur liste verte.

Notes

Actuellement, les connexions sortantes d’Azure Database pour PostgreSQL via des extensions de wrapper de données externes telles que postgres_fdw ne sont pas prises en charge, à l’exception des connexions vers d’autres serveurs Azure Database pour PostgreSQL au sein de la même région Azure.

uuid

Si vous pensez utiliser uuid_generate_v4() à partir de l’extension uuid-ossp, envisagez la comparaison avec gen_random_uuid() à partir de l’extension pgcrypto pour optimiser les performances.

pgAudit

L’extension pgAudit permet la journalisation d’audit des sessions et des objets. Pour savoir comment utiliser cette extension dans Azure Database pour PostgreSQL, consultez l’article sur les concepts d’audit.

pg_prewarm

L’extension pg_prewarm charge des données relationnelles dans le cache. Le préchauffage de vos caches signifie que vos requêtes ont de meilleurs temps de réponse lors de leur première exécution après un redémarrage. Dans Postgres 10 et versions antérieures, le préchauffage est effectué manuellement à l’aide de la fonction de préchauffage.

Dans Postgres 11 et versions ultérieures, vous pouvez configurer le préchauffage pour qu’il se produise automatiquement. Vous devez inclure pg_prewarm dans la liste de votre paramètre shared_preload_libraries et redémarrer le serveur pour appliquer la modification. Les paramètres peuvent être définis à partir du portail Azure, de CLI, de l’API REST ou du modèle ARM.

TimescaleDB

TimescaleDB est une base de données de séries chronologiques empaquetée en tant qu’extension pour PostgreSQL. TimescaleDB fournit des fonctions analytiques axées sur le temps et des optimisations et met à l’échelle PostgreSQL pour les charges de travail de série chronologique.

Apprenez-en davantage sur TimescaleDB, marque déposée de Timescale, Inc.. Azure Database pour PostgreSQL fournit la TimescaleDB Apache-2.

Installation de TimescaleDB

Pour installer TimescaleDB, vous devez l’inclure dans les bibliothèques de préchargement partagées du serveur. Une modification apportée au paramètre shared_preload_libraries de Postgres nécessite un shared_preload_libraries pour prendre effet. Vous pouvez modifier les paramètres à l’aide du portail Azure ou d’Azure CLI.

À l’aide du Portail Azure :

  1. Sélectionnez votre serveur Azure Database pour PostgreSQL.

  2. Dans la barre latérale, sélectionnez Paramètres du serveur.

  3. Recherchez le paramètre shared_preload_libraries.

  4. Sélectionnez TimescaleDB.

  5. Sélectionnez Enregistrer pour conserver vos modifications. Vous recevez une notification une fois que la modification est enregistrée.

  6. Après avoir reçu la notification, redémarrez le serveur pour appliquer ces modifications. Pour apprendre à redémarrer un serveur, consultez Redémarrer un serveur Azure Database pour PostgreSQL.

Vous pouvez maintenant activer TimescaleDB dans votre base de données Postgres. Connectez-vous à la base de données et exécutez la commande suivante :

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

Conseil

Si vous voyez une erreur, vérifiez que vous avez redémarré le serveur après l’enregistrement de shared_preload_libraries.

Vous pouvez maintenant créer une hypertable TimescaleDB à partir de zéro ou migrer des données de série chronologiques existantes dans PostgreSQL.

Restauration d’une base de données d’échelle de temps à l’aide de pg_dump et pg_restore

Pour restaurer une base de données d’échelle de temps à l’aide de pg_dump et pg_restore, vous devez exécuter deux procédures d’assistance dans la base de données de destination : timescaledb_pre_restore() et timescaledb_post restore().

Préparez d’abord la base de données de destination :

--create the new database where you'll perform the restore
CREATE DATABASE tutorial;
\c tutorial --connect to the database 
CREATE EXTENSION timescaledb;

SELECT timescaledb_pre_restore();

Vous pouvez maintenant exécuter pg_dump sur la base de données d’origine, puis effectuer pg_restore. Après la restauration, veillez à exécuter la commande suivante dans la base de données restaurée :

SELECT timescaledb_post_restore();

Pour plus d’informations sur la méthode de restauration avec une base de données avec une échelle de temps, consultez la documentation relative à l’échelle de temps

Restauration d’une base de données d’échelle de données à l’aide de timescaledb-backup

Lors de l’exécution de la procédure SELECT timescaledb_post_restore() indiquée ci-dessus, vous pouvez recevoir des erreurs d’autorisation refusée lors de la mise à jour de l’indicateur timescaledb.restoring. Cela est dû à l’autorisation ALTER DATABASE limitée dans les services de base de données cloud PaaS. Dans ce cas, vous pouvez effectuer une autre méthode à l’aide de l'outil timescaledb-backup pour sauvegarder et restaurer une base de données d’échelle de temps. Timescaledb-backup est un programme qui simplifie le vidage et la restauration d’une base de données TimescaleDB, moins susceptible d’engendrer des erreurs et plus performant. Pour ce faire, vous devez procéder comme suit

  1. Installer les outils comme détaillé ici
  2. Créer un serveur et une base de données Azure Database pour PostgreSQL cibles
  3. Activer l’extension d’échelle de temps comme indiqué ci-dessus
  4. Accorder le rôle azure_pg_admin à l’utilisateur qui sera utilisé par ts-restore
  5. Exécuter ts-restore pour restaurer la base de données

Des informations supplémentaires sur ces utilitaires sont disponibles ici.

Notes

Lorsque vous utilisez des utilitaires timescale-backup pour effectuer une restauration vers Azure, comme les noms d’utilisateur de base de données pour Azure Database pour PostgresQL non flexible doivent utiliser le format <user@db-name>, vous devez remplacer @ par l'encodage de caractères %40.

Étapes suivantes

Si vous ne voyez pas une extension que vous souhaitez utiliser, faites-le-nous savoir. Votez pour les demandes existantes ou créez de nouvelles demandes dans notre Forum de commentaires.