Partage via

Vide complet à l'aide de pg_repack dans Azure Database pour PostgreSQL - Serveur flexible

  • Article

S’APPLIQUE À : Azure Database pour PostgreSQL : serveur flexible

Dans cet article, vous allez apprendre à utiliser pg_repack pour supprimer les encombrements et améliorer le niveau de performance de votre serveur flexible Azure Database pour PostgreSQL. Un ballonnement est une accumulation de données inutiles dans les tables et les index, en raison de mises à jour et de suppressions fréquentes. Un encombrement peut entraîner une croissance de la taille de la base de données plus importante que prévu, et cela peut affecter gravement les performances de certaines requêtes. Utilisez pg_repack pour récupérer l’espace perdu et réorganiser les données plus efficacement.

Qu’est-ce que pg_repack ?

pg_repack est une extension PostgreSQL qui supprime les encombrements des tables et des index et les réorganise plus efficacement. pg_repack crée une nouvelle copie de la table ou de l’index cible, en appliquant les modifications qui se sont produites pendant le processus, puis échange les anciennes et les nouvelles versions de manière atomique. pg_repack ne nécessite aucun temps d’arrêt ni verrou d’accès exclusifs sur la table ou l’index traité, à l’exception d’une courte période au début et à la fin de l’opération. Vous pouvez utiliser pg_repack pour optimiser n’importe quelle table ou index dans vos bases de données serveur flexible Azure Database pour PostgreSQL.

Comment utiliser pg_repack ?

Pour utiliser pg_repack, vous devez installer l’extension dans votre base de données de serveur flexible Azure Database pour PostgreSQL, puis exécuter la commande pg_repack, en spécifiant le nom de la table ou de l’index que vous souhaitez optimiser. L’extension acquiert des verrous sur la table ou l’index pour empêcher l’exécution d’autres opérations pendant que l’optimisation est en cours. Cela supprime l’encombrement et réorganise les données plus efficacement.

Fonctionnement de la recompression de table complète

Pour effectuer une recompression de table complète, l’extension suit les étapes suivantes :

  1. Permet de créer une table de journaux pour enregistrer les modifications apportées à la table d’origine.
  2. Permet d’ajouter un déclencheur à la table d’origine, journaliser les INSERT, les UPDATE et les DELETE dans la table du journal.
  3. Permet de créer une nouvelle table contenant toutes les lignes de la table d’origine.
  4. Permet de générer les index sur la nouvelle table.
  5. Permet d’appliquer toutes les modifications enregistrées dans la table du journal à la nouvelle table.
  6. Permet d’échanger les tables d’origine et nouvelles, y compris les index et les tables toast.
  7. Permet de supprimer la table d'origine.

Pendant ces étapes, pg_repack conserve uniquement un verrou d’accès exclusif pendant une courte période, pendant la configuration initiale (étapes 1 et 2) et à nouveau pendant la phase finale d’échange et de suppression (étapes 6 et 7). Le reste du temps, pg_repack a uniquement besoin de conserver un verrou d’accès partagé sur la table d’origine, ce qui permet aux INSERT, aux UPDATE et aux DELETE de continuer comme d’habitude.

Limites

pg_repack présente certaines limitations que vous devez connaître avant de l’utiliser :

  • La table cible doit avoir soit une clé PRIMARY soit un index UNIQUE sur une colonne NOT NULL pour que l’opération réussisse.
  • Pendant que pg_repack est en cours d’exécution, vous ne pouvez pas exécuter de commandes Langage de définition de données (DDL) sur les tables cibles, à l’exception de VACUUM ou ANALYZE. Pour vous assurer que ces restrictions sont appliquées, pg_repack contient un verrou d’accès partagé sur la table cible pendant une recompression de table complète.

Programme d’installation

Prérequis

  1. Configurez l’extension pg_repack en mettant sur liste d’autorisation et en créant l’extension.

Générer une application cliente pg_repack

L’utilisation de cette extension nécessite une application cliente que vous pouvez générer et installer sur une instance d’Ubuntu.

Pour installer la version 1.4.7 de pg_repack, exécutez le script bash suivant sur une machine Ubuntu.

# Create the file repository configuration
sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# Import the repository signing key
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# Update the package lists
sudo apt-get update
# Install required packages to build the code
sudo apt-get install -y postgresql-server-dev-14 unzip make gcc libssl-dev liblz4-dev zlib1g-dev libreadline-dev libzstd-dev
# Download compressed version of build tree for version 1.4.7 of pg_repack
wget 'https://api.pgxn.org/dist/pg_repack/1.4.7/pg_repack-1.4.7.zip'
# Uncompress build tree
unzip pg_repack-1.4.7.zip
# Set current directory to where build tree was uncompressed
cd pg_repack-1.4.7
# Build code
sudo make
# Copy resulting binaries to /usr/local/bin
sudo cp bin/pg_repack /usr/local/bin
# Run pg_repack to check its version
pg_repack --version

Utilisez pg_repack

Exemple d’exécution de pg_repack sur une table nommée info, dans un schéma public au sein du serveur flexible Azure Database pour PostgreSQL, avec point de terminaison pgserver.postgres.database.azure.com, nom d’utilisateur azureuser et base de données foo, avec la commande suivante.

  1. À l’aide du client de votre préférence, connectez-vous à l’instance de serveur flexible Azure Database pour PostgreSQL. Nous utilisons psql dans cet exemple.

        psql "host=<server>.postgres.database.azure.com port=5432 dbname=<database> user=<user> password=<password> sslmode=require"

  2. Recherchez la version de l’extension pg_repack installée dans la base de données.

    SELECT installed_version FROM pg_available_extensions WHERE name = 'pg_repack';

  3. La version de l’extension doit correspondre à la version de l’application cliente, que vous pouvez vérifier en exécutant cette commande :

    azureuser@azureuser:~$ pg_repack --version

  4. Exécutez le client pg_repack sur une table appelée informations qui existe dans la base de données foo.

    pg_repack --host=<server>.postgres.database.azure.com --username=<user> --dbname=<database> --table=info --jobs=2 --no-kill-backend --no-superuser-check

Options de pg_repack

Options de pg_repack utiles pour les charges de travail de production :

  • -k, --no-superuser-check : ignorer les vérifications du superutilisateur dans le client. Ce paramètre est utile pour utiliser pg_repack sur des plateformes qui prennent en charge son exécution en tant que non-super-utilisateurs, comme les instances de serveur flexible Azure Database pour PostgreSQL.

  • -j, --jobs : créer le nombre spécifié de connexions supplémentaires au serveur flexible Azure Database pour PostgreSQL et utiliser ces connexions supplémentaires pour paralléliser la régénération des index sur chaque table. Les générations d’index parallèles ne sont prises en charge que pour les repacks de table complets.

  • Options d’index --index ou --only : si votre instance de serveur flexible Azure Database pour PostgreSQL dispose de cœurs supplémentaires et de disques E/S disponibles, l’utilisation de cette option peut être une façon pratique d’accélérer pg_repack.

  • -D, --no-kill-backend : au lieu de tuer les clients back-end qui exécutent des requêtes bloquantes, ignorez la recompression d’une table si le verrou ne peut pas être acquis après avoir attendu l’heure spécifiée dans --wait-timeout. Par défaut, --wait-timeout est défini sur 60 secondes. La valeur par défaut de ce paramètre est false.

  • -E LEVEL, --elevel=LEVEL : choisissez le niveau de message de sortie de DEBUG, INFO, NOTICE, WARNING, ERROR, LOG, FATAL et PANIC. Par défaut, il s’agit de INFO.

Pour comprendre toutes les options, consultez la documentation de pg_repack.

Forums Aux Questions (FAQ)

Est-ce que pg_repack est 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.

Contenu connexe