Partager via

Considérations relatives aux extensions spécifiques à Azure Database pour PostgreSQL – Serveur flexible

  • Article

Cet article décrit certaines considérations particulières à prendre en compte lors de l’utilisation de certaines extensions dans une instance de serveur flexible Azure Database pour PostgreSQL.

Prérequis

Lisez l’article Comment utiliser des extensions PostgreSQL pour Azure Database pour PostgreSQL pour découvrir comment :

  • Établir une liste d’autorisations d’extensions dans Azure Database pour PostgreSQL – Serveur flexible
  • Charger les bibliothèques d’extensions qui déploient des bibliothèques binaires, qui nécessitent l’allocation et l’accès à la mémoire partagée et qui doivent être chargées au démarrage du serveur.
  • Installer des extensions dans une base de données, afin que les objets SQL empaquetés dans cette extension soient déployés dans cette base de données et accessibles dans son contexte.
  • Supprimer des extensions de certaines bases de données afin que les objets SQL empaquetés dans cette extension soient supprimés de cette base de données.
  • Mettre à jour les artefacts SQL déployés par une extension déjà installée.
  • Afficher les extensions installées et leurs versions correspondantes.
  • Découvrir les erreurs possibles que vous pouvez recevoir lors de la gestion des extensions dans le serveur flexible Azure Database pour PostgreSQL, ainsi que la cause de chacune d’entre elles.

Extensions

La liste suivante énumère toutes les extensions prises en charge qui nécessitent des considérations spécifiques lorsqu’elles sont utilisées dans le service de serveur flexible Azure Database pour PostgreSQL :

  • dblink
  • pg_buffercache
  • pg_cron
  • pg_failover_slots
  • pg_hint_plan
  • pg_prewarm
  • pg_repack
  • pg_stat_statements
  • postgres_fdw
  • pgstattuple

L’extension dblink vous permet de vous connecter d’une instance de serveur flexible Azure Database pour PostgreSQL à une autre, ou à une autre base de données sur le même serveur. Le serveur flexible Azure Database pour PostgreSQL prend en charge les connexions entrantes et sortantes vers n’importe quel serveur PostgreSQL. Le serveur d’envoi doit autoriser les connexions sortantes vers le serveur de réception. De même, le serveur de réception doit autoriser les connexions à partir du serveur d’envoi.

Si vous envisagez d’utiliser cette extension, nous vous recommandons de déployer vos serveurs avec intégration du réseau virtuel. Par défaut, l’intégration du réseau virtuel autorise des connexions entre les serveurs du réseau virtuel. Vous pouvez également choisir d’utiliser des groupes de sécurité réseau de réseau virtuel pour personnaliser l’accès.

pg_buffercache

L’extension pg_buffercache peut être utilisée pour étudier le contenu de shared_buffers. À l’aide de cette extension, vous pouvez savoir si une relation particulière est mise en cache (dans shared_buffers). Cette extension peut vous aider à résoudre les problèmes de niveau de performance (problèmes de niveau de performance liés à la mise en cache).

Cette extension est intégrée à l’installation minimale de PostgreSQL et est facile à installer.

CREATE EXTENSION pg_buffercache;

pg_cron

L’extension pg_cron est un planificateur de travaux simple basé sur Cron pour PostgreSQL qui s’exécute dans la base de données en tant qu’extension. L’extension pg_cron peut exécuter des tâches de maintenance planifiées dans une base de données PostgreSQL. Par exemple, vous pouvez exécuter un vide périodique d’une table ou supprimer d’anciens travaux de données.

L’extension pg_cron peut exécuter plusieurs travaux en parallèle, mais elle exécute au plus une instance d’un travail à la fois. Si une deuxième exécution est censée démarrer avant la fin de la première, la deuxième exécution est mise en file d’attente et démarrée dès que la première exécution est terminée. Ainsi, les travaux s’exécutent précisément autant de fois que prévu et ne s’exécutent pas en concurrence avec eux-mêmes.

Vérifiez que la valeur définie pour shared_preload_libraries inclut pg_cron. Cette extension ne prend pas en charge le chargement de la bibliothèque comme effet de l’exécution de CREATE EXTENSION. Toute tentative d’exécution de CREATE EXTENSION si l’extension n’a pas été ajoutée à shared_preload_libraries, ou si le serveur n’a pas été redémarré après son ajout, génère une erreur dont le texte indique pg_cron can only be loaded via shared_preload_libraries et dont l’indicateur est Add pg_cron to the shared_preload_libraries configuration variable in postgresql.conf.

Pour utiliser pg_cron, assurez-vous que sa bibliothèque est ajoutée pour être chargée au démarrage du serveur, qu’il est sur la liste d’autorisation, et qu’il est installé dans une base de données à partir de laquelle vous souhaitez interagir avec ses fonctionnalités, à l’aide des artefacts SQL qu’il crée.

Exemples

  1. Pour supprimer d’anciennes données le samedi à 3h30 (GMT).

    SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);

  2. Pour exécuter le vide tous les jours à 10h00 (GMT) dans la base de données par défaut postgres.

    SELECT cron.schedule('0 10 * * *', 'VACUUM');

  3. Pour annuler la planification de toutes les tâches de pg_cron.

    SELECT cron.unschedule(jobid) FROM cron.job;

  4. Pour afficher tous les travaux actuellement planifiés avec pg_cron.

    SELECT * FROM cron.job;

  5. Pour exécuter le vide tous les jours à 10h00 (GMT) dans la base de données test cron sous le compte du rôle azure_pg_admin.

    SELECT cron.schedule_in_database('VACUUM',' 0 10 * * * ', 'VACUUM', 'testcron',null,TRUE);

Autres exemples

À partir de pg_cron version 1.4, vous pouvez utiliser les fonctions cron.schedule_in_database et cron.alter_job pour planifier votre travail dans une base de données spécifique et mettre à jour une planification existante, respectivement.

La fonction cron_schedule_in_database autorise le nom d’utilisateur comme paramètre facultatif. La définition du nom d’utilisateur sur une valeur non nulle requiert le privilège de superutilisateur PostgreSQL et n’est pas prise en charge dans le serveur flexible Azure Database pour PostgreSQL. Les exemples précédents montrent l’exécution de cette fonction avec un paramètre nom d’utilisateur facultatif omis ou défini sur nul, qui exécute le travail dans le contexte de la planification du travail par l’utilisateur, lequel doit avoir les privilèges du rôle azure_pg_admin.

  1. Pour supprimer d’anciennes données le samedi à 3h30 (GMT) sur la base de données DBName.

    SELECT cron.schedule_in_database('JobName', '30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$,'DBName');

  2. Pour mettre à jour ou modifier le nom de la base de données pour la planification existante

    SELECT cron.alter_job(job_id:=MyJobID,database:='NewDBName');

pg_failover_slots

L’extension pg_failover_slots améliore le serveur flexible Azure Database pour PostgreSQL quand il fonctionne à la fois avec une réplication logique et des serveurs à haute disponibilité. Elle résout efficacement le défi au sein du moteur PostgreSQL standard qui ne conserve pas les emplacements de réplication logique après un basculement. La maintenance de ces emplacements est essentielle pour empêcher les interruptions de réplication ou les incompatibilités de données pendant les modifications de rôle serveur principal, ce qui garantit la continuité opérationnelle et l’intégrité des données.

L’extension simplifie le processus de basculement en gérant le transfert, le nettoyage et la synchronisation nécessaires des emplacements de réplication, ce qui offre une transition transparente pendant les modifications de rôle serveur.

Vous trouverez plus d’informations et des instructions sur l’utilisation de l’extension pg_failover_slots sur sa page GitHub.

Pour utiliser l’extension pg_failover_slots, assurez-vous que sa bibliothèque a été chargée au démarrage du serveur.

pg_hint_plan

L’extension pg_hint_plan permet d’adapter les plans d’exécution PostgreSQL à l’aide de ce que l’on appelle des « conseils » dans les commentaires SQL, comme :

/*+ SeqScan(a) */

L’extension pg_hint_plan lit les expressions de conseil dans un commentaire de la forme spéciale donnée avec l’instruction SQL cible. Le formulaire particulier commence par la séquence de caractères « /*+ » et se termine par « */ ». Les phrases de conseil se composent du nom du conseil et des paramètres suivants entre parenthèses et délimités par des espaces. De nouvelles lignes peuvent délimiter chaque expression d’indicateur, pour plus de lisibilité.

Exemple :

/*+
 HashJoin(a b)
 SeqScan(a)
 */
    SELECT *
    FROM pgbench_branches b
    JOIN pgbench_accounts an ON b.bid = a.bid
    ORDER BY a.aid;

L’exemple précédent oblige le planificateur à utiliser les résultats d’un seqscan sur la table a à combiner avec la table b en tant que hashjoin.

Pour utiliser l’extension pg_hint_plan, assurez-vous d’ajouter l’extension à la liste d’autorisation, de charger sa bibliothèque et d’installer l’extension dans la base de données sur laquelle vous envisagez d’utiliser ses fonctionnalités.

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. La fonctionnalité de préchauffage automatique du serveur flexible PostgreSQL n’est actuellement pas disponible dans la base de données Azure.

pg_repack

Ceux qui utilisent pour la première fois l’extension pg_repack se posent généralement la question : pg_repack est-il une extension ou un exécutable côté client comme psql ou pg_dump ?

pg_repack est les deux. pg_repack/lib a le code de l’extension, y compris le schéma et les artefacts SQL qu’il crée, et la bibliothèque C implémentant le code de plusieurs de ces fonctions.

D’un autre côté, pg_repack/bin a le code de l’application cliente, qui sait comment interagir avec les éléments de programmabilité implémentés dans l’extension. Cette application cliente vise à simplifier la complexité de l’interaction avec les différentes interfaces exposées par l’extension côté serveur. Il offre à l’utilisateur certaines options de ligne de commande qui sont plus faciles à comprendre. L’application cliente est inutile sans que l’extension créée sur la base de données vers laquelle elle pointe. L’extension côté serveur est entièrement fonctionnelle, mais nécessite que l’utilisateur comprenne un modèle d’interaction complexe. Ce modèle consiste à exécuter des requêtes pour récupérer des données utilisées comme entrée pour les fonctions implémentées par l’extension, etc.

Autorisation refusée pour le schéma repack

Actuellement, étant donné que nous octroyons des autorisations au schéma repack créé par cette extension, nous ne prenons en charge que l’exécution de la fonctionnalité pg_repack à partir du contexte de azure_pg_admin.

Vous remarquerez peut-être que si le propriétaire d’une table, qui n’est pas azure_pg_admin, tente d’exécuter pg_repack, il finit par recevoir une erreur semblable à la suivante :

NOTICE: Setting up workers.conns
ERROR: pg_repack failed with error: ERROR:  permission denied for schema repack
LINE 1: select repack.version(), repack.version_sql()

Pour éviter cette erreur, exécutez pg_repack à partir du contexte de azure_pg_admin.

pg_stat_statements

L’extension pg_stat_statements vous donne une vue de toutes les requêtes exécutées sur votre base de données. Cela est utile pour comprendre le niveau de performance de votre charge de travail de requête sur un système de production.

L’extension pg_stat_statements est préchargée dans shared_preload_libraries sur chaque instance de serveur flexible Azure Database pour PostgreSQL pour fournir un moyen de suivre les statistiques d’exécution des instructions SQL.

Pour des raisons de sécurité, vous devez établir une liste d’autorisation pour l’extension pg_stat_statements et l’installer à l’aide de la commande CREATE EXTENSION.

Le paramètre pg_stat_statements.track, qui contrôle quelles instructions sont suivies 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é en tant que paramètre de serveur.

Il y a un compromis entre les informations d’exécution des requêtes que l’extension pg_stat_statements fournit sur le niveau de performance du serveur en raison de la journalisation de chaque instruction SQL. Si vous n’utilisez pas activement l’extension pg_stat_statements, nous vous recommandons de définir pg_stat_statements.track sur none. Certains services de supervision tiers peuvent s’appuyer sur pg_stat_statements pour fournir des aperçus du niveau de performance des requêtes. Vérifiez si c’est le cas pour vous.

postgres_fdw

L’extension postgres_fdw vous permet de vous connecter d’une instance de serveur flexible Azure Database pour PostgreSQL à une autre, ou à une autre base de données sur le même serveur. Le serveur flexible Azure Database pour PostgreSQL prend en charge les connexions entrantes et sortantes vers n’importe quel serveur PostgreSQL. Le serveur d’envoi doit autoriser les connexions sortantes vers le serveur de réception. De même, le serveur de réception doit autoriser les connexions à partir du serveur d’envoi.

Si vous envisagez d’utiliser cette extension, nous vous recommandons de déployer vos serveurs avec intégration du réseau virtuel. Par défaut, l’intégration du réseau virtuel autorise des connexions entre les serveurs du réseau virtuel. Vous pouvez également choisir d’utiliser des groupes de sécurité réseau de réseau virtuel pour personnaliser l’accès.

pgstattuple

Quand vous utilisez l’extension pgstattuple pour essayer d’obtenir des statistiques de tuples à partir d’objets conservés dans le schéma pg_toast dans les versions de PostgreSQL 11 à 13, vous recevez une erreur « autorisation refusée pour le schéma pg_toast ».

Autorisation refusée pour le schéma pg_toast

Les clients utilisant PostgreSQL versions 11 à 13 sur Azure Database pour serveur flexible ne peuvent pas utiliser l’extension pgstattuple sur des objets du schéma pg_toast.

Dans PostgreSQL 16 et 17, le rôle pg_read_all_data est automatiquement accordé à azure_pg_admin, ce qui permet à pgstattuple de fonctionner correctement. Dans PostgreSQL 14 et 15, les clients peuvent accorder manuellement le rôle pg_read_all_data à azure_pg_admin pour obtenir le même résultat. Toutefois, dans PostgreSQL 11 à 13, le rôle pg_read_all_data n’existe pas.

Les clients ne peuvent pas octroyer directement les autorisations nécessaires. Si vous avez besoin d’exécuter pgstattuple pour accéder aux objets sous le schéma pg_toast, procédez à la création d’une demande de support Azure.

timescaleDB

L’extension timescaleDB est une base de données de série chronologique emballée en tant qu’extension de PostgreSQL. Elle 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. En savoir plus sur TimescaleDB, marque déposée de Timescale, Inc. Le serveur flexible Azure Database pour PostgreSQL fournit l’édition Apache-2 de TimescaleDB.

Installer TimescaleDB

Pour utiliser timescaleDB, assurez-vous d’ajouter l’extension à la liste d’autorisation, de charger sa bibliothèque et d’installer l’extension dans la base de données sur laquelle vous envisagez d’utiliser ses fonctionnalités.

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

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

Pour restaurer une base de données Timescale à 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 want to 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 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 d’une base de données Timescale, consultez la Documentation Timescale.

Restaurer 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(), vous pourriez obtenir des autorisations refusées 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 utiliser une autre méthode à l’aide de l’outil timescaledb-backup pour sauvegarder et restaurer la base de données Timescale. 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, procédez comme suit :

  1. Installer les outils comme détaillé ici.

  2. Créer une instance de serveur flexible Azure Database pour PostgreSQL cible et une base de données.

  3. Activer l’extension Timescale.

  4. Octroyer le rôle azure_pg_admin à l’utilisateur 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.

Extensions et mise à niveau d’une version majeure

Le serveur flexible Azure Database pour PostgreSQL offre une fonctionnalité de mise à niveau d’une version majeure sur place qui effectue une mise à niveau sur place de l’instance de serveur flexible Azure Database pour PostgreSQL en une simple interaction de l’utilisateur. La mise à niveau de version principale sur place simplifie le processus de mise à niveau de serveur flexible Azure Database pour PostgreSQL, ce qui réduit les interruptions pour les utilisateurs et les applications accédant au serveur. Les mises à niveau d’une version majeure sur place ne prennent pas en charge les extensions spécifiques et il existe certaines limites à la mise à niveau de certaines extensions.

Les extensions anon, Apache AGE, dblink, orafce, pgaudit, postgres_fdw et timescaledb ne sont prises en charge pour toutes les versions de serveur flexible Azure Database pour PostgreSQL lors de l’utilisation de la fonctionnalité de mise à jour d’une version majeure sur place.

Contenu connexe