Migration de votre base de données PostgreSQL par vidage et restauration

  • Article

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

Vous pouvez utiliser pg_dump pour extraire une base de données PostgreSQL dans un fichier de vidage. La méthode de restauration de la base de données dépend du format du vidage que vous choisissez. Si votre vidage est pris avec le format brut (qui est la valeur par défaut -Fp, donc aucune option spécifique ne doit être spécifiée), la seule option à restaurer consiste à utiliser psql, car elle génère un fichier texte brut. Pour les trois autres méthodes de vidage : personnalisée, répertoire et tar, pg_restore doivent être utilisées.

Important

Les instructions et les commandes fournies dans cet article sont conçues pour être exécutées dans les terminaux bash. Cela inclut des environnements tels que Sous-système Windows pour Linux (WSL), Azure Cloud Shell et d’autres interfaces compatibles avec bash. Vérifiez que vous utilisez un terminal Bash pour suivre les étapes et exécuter les commandes détaillées dans ce guide. L’utilisation d’un autre type d’environnement de terminal ou d’interpréteur de commandes peut entraîner des différences de comportement de commande et ne pas produire les résultats prévus.

Dans cet article, nous nous concentrons sur les formats simples (par défaut) et de répertoire. Le format de répertoire est utile, car il vous permet d’utiliser plusieurs cœurs pour le traitement, ce qui peut améliorer considérablement l’efficacité, en particulier pour les bases de données volumineuses.

Le Portail Azure simplifie ce processus via le panneau Connecter en offrant des commandes préconfigurées adaptées à votre serveur, avec des valeurs remplacées par vos données utilisateur. Il est important de noter que le panneau Connecter est disponible uniquement pour Azure Database pour PostgreSQL - Serveur flexible et non pour le serveur unique. Voici comment utiliser cette fonctionnalité :

  1. Accéder Portail Azure : tout d’abord, accédez au Portail Azure et choisissez le panneau Connecter.

    Screenshot showing the placement of Connect blade in Azure portal.

  2. Sélectionnez votre base de données : dans le panneau Connecter, vous trouverez une liste déroulante de vos bases de données. Sélectionnez la base de données à partir de laquelle vous souhaitez effectuer un vidage.

    Screenshot showing the dropdown where specific database can be chosen.

  3. Choisissez la méthode appropriée : selon la taille de votre base de données, vous pouvez choisir entre deux méthodes :

    • pg_dump & psql - Utilisation d’un fichier texte singulier : idéale pour les bases de données plus petites, cette option utilise un seul fichier texte pour le processus de vidage et de restauration.
    • pg_dump &- Utilisation pg_restore de plusieurs cœurs : pour les bases de données plus volumineuses, cette méthode est plus efficace car elle utilise plusieurs cœurs pour gérer le processus de vidage et de restauration.

    Screenshot showing two possible dump methods.

  4. Copier et coller des commandes : le portail vous fournit des commandes prêtes à être utilisées pg_dump et psql ou pg_restore à des commandes. Ces commandes sont fournies avec des valeurs déjà remplacées en fonction du serveur et de la base de données que vous avez choisies. Copiez et collez ces commandes.

Prérequis

Si vous utilisez un serveur unique ou que vous n’avez pas accès au portail serveur flexible, lisez cette page de documentation. Il contient des informations similaires à ce qui est présenté dans le panneau Connecter pour le serveur flexible sur le portail.

Pour parcourir ce guide pratique, vous avez besoin des éléments suivants :

  • Un serveur Azure Database pour PostgreSQL avec des règles de pare-feu autorisant l’accès.
  • pg_dump, psql, pg_restore et pg_dumpall au cas où vous souhaitez migrer avec des rôles et des autorisations, des utilitaires de ligne de commande installés.
  • Choisissez l’emplacement du vidage : choisissez l’emplacement à partir duquel vous souhaitez effectuer le vidage. Il peut être effectué à partir de différents emplacements, tels qu’une machine virtuelle distincte, cloud Shell (où les utilitaires de ligne de commande sont déjà installés, mais ne se trouve peut-être pas dans la version appropriée, donc toujours case activée la version à l’aide, par exemple, psql --version) ou votre propre ordinateur portable. Gardez toujours à l’esprit la distance et la latence entre le serveur PostgreSQL et l’emplacement à partir duquel vous exécutez le vidage ou la restauration.

Important

Il est essentiel d’utiliser les utilitaires et psqlpg_restorepg_dumpall les pg_dumpservices qui sont de la même version principale ou d’une version majeure supérieure que le serveur de base de données vers lequel vous exportez des données ou vers lequel vous importez des données. L’échec de cette opération peut entraîner une migration de données infructueuse. Si votre serveur cible a une version majeure supérieure à celle du serveur source, utilisez des utilitaires qui sont soit la même version majeure, soit supérieure à celle du serveur cible.

Remarque

Il est important de savoir qu’il pg_dump n’est possible d’exporter qu’une seule base de données à la fois. Cette limitation s’applique quelle que soit la méthode que vous avez choisie, qu’elle utilise un fichier singulier ou plusieurs cœurs.

Vidage des utilisateurs et des rôles avec pg_dumpall -r

pg_dump est utilisé pour extraire une base de données PostgreSQL dans un fichier de vidage. Toutefois, il est essentiel de comprendre qu’il pg_dump n’y a pas de rôles de vidage ou de définitions d’utilisateurs, car ils sont considérés comme des objets globaux dans l’environnement PostgreSQL. Pour une migration complète, y compris les utilisateurs et les rôles, vous devez utiliser pg_dumpall -r. Cette commande vous permet de capturer toutes les informations de rôle et d’utilisateur à partir de votre environnement PostgreSQL. Si vous migrez dans des bases de données sur le même serveur, n’hésitez pas à ignorer cette étape et à passer à la section Créer une base de données .

pg_dumpall -r -h <server name> -U <user name> > roles.sql

Par exemple, si vous avez un serveur nommé mydemoserver et qu’un utilisateur nommé myuser exécutez la commande suivante :

pg_dumpall -r -h mydemoserver.postgres.database.azure.com -U myuser > roles.sql

Si vous utilisez un serveur unique, votre nom d’utilisateur inclut le composant de nom de serveur. Par conséquent, au lieu de myuser, utilisez myuser@mydemoserver.

Vider des rôles à partir d’un serveur flexible

Dans un environnement serveur flexible, les mesures de sécurité améliorées signifient que les utilisateurs n’ont pas accès à la table pg_authid, c’est-à-dire où les mots de passe de rôle sont stockés. Cette restriction affecte la façon dont vous effectuez un vidage de rôles, car la commande standard pg_dumpall -r tente d’accéder à cette table pour les mots de passe et échoue en raison d’un manque d’autorisation.

Lorsque vous videz des rôles à partir d’un serveur flexible, il est essentiel d’inclure l’option --no-role-passwords dans votre pg_dumpall commande. Cette option empêche pg_dumpall toute tentative d’accès à la pg_authid table, qu’elle ne peut pas lire en raison de restrictions de sécurité.

Pour vider les rôles à partir d’un serveur flexible, utilisez la commande suivante :

pg_dumpall -r --no-role-passwords -h <server name> -U <user name> > roles.sql

Par exemple, si vous avez un serveur nommé mydemoserver, un utilisateur nommé myuser, exécutez la commande suivante :

pg_dumpall -r --no-role-passwords -h mydemoserver.postgres.database.azure.com -U myuser > roles.sql

Nettoyage du vidage des rôles

Lors de la migration du fichier roles.sql de sortie, vous pouvez inclure certains rôles et attributs qui ne sont pas applicables ou autorisés dans le nouvel environnement. Voici ce que vous devez prendre en compte :

  • Suppression d’attributs qui ne peuvent être définis que par des superutilisateurs : si vous migrez vers un environnement où vous n’avez pas de privilèges de superutilisateur, supprimez les attributs comme NOSUPERUSER et NOBYPASSRLS dans le vidage des rôles.

  • Exclusion des utilisateurs spécifiques au service : exclure des utilisateurs de service à serveur unique, tels que azure_superuser ou azure_pg_admin. Celles-ci sont spécifiques au service et seront créées automatiquement dans le nouvel environnement.

Utilisez la commande suivante sed pour propre le vidage de vos rôles :

sed -i '/azure_superuser/d; /azure_pg_admin/d; /azuresu/d; /^CREATE ROLE replication/d; /^ALTER ROLE replication/d; /^ALTER ROLE/ {s/NOSUPERUSER//; s/NOBYPASSRLS//;}' roles.sql

Cette commande supprime les lignes contenant , , les lignes commençant CREATE ROLE replication par et ALTER ROLE replication, et supprime les NOSUPERUSER attributs et NOBYPASSRLS les attributs des ALTER ROLE instructions. azuresuazure_pg_adminazure_superuser

Création d’un fichier de vidage qui contient les données à charger

Pour exporter votre base de données PostgreSQL existante localement ou dans une machine virtuelle vers un fichier de script SQL, exécutez la commande suivante dans votre environnement existant :

pg_dump <database name> -h <server name> -U <user name> > <database name>_dump.sql

Par exemple, si vous avez un serveur nommé mydemoserver, un utilisateur nommé myuser et une base de données appelée testdb, exécutez la commande suivante :

pg_dump testdb -h mydemoserver.postgres.database.azure.com -U myuser > testdb_dump.sql

Si vous utilisez un serveur unique, votre nom d’utilisateur inclut le composant de nom de serveur. Par conséquent, au lieu de myuser, utilisez myuser@mydemoserver.

Restaurer les données dans la base de données cible

Restaurer des rôles et des utilisateurs

Avant de restaurer vos objets de base de données, vérifiez que vous avez correctement vidé et propre les rôles. Si vous migrez au sein de bases de données sur le même serveur, il se peut que les rôles soient vides et restaurés. Toutefois, pour les migrations entre différents serveurs ou environnements, cette étape est cruciale.

Pour restaurer les rôles et les utilisateurs dans la base de données cible, utilisez la commande suivante :

psql -f roles.sql -h <server_name> -U <user_name>

Remplacez <server_name> par le nom de votre serveur cible et <user_name> par votre nom d’utilisateur. Cette commande utilise l’utilitaire psql pour exécuter les commandes SQL contenues dans le roles.sql fichier, en restaurant efficacement les rôles et les utilisateurs dans votre base de données cible.

Par exemple, si vous avez un serveur nommé mydemoserver, un utilisateur nommé myuser, exécutez la commande suivante :

psql -f roles.sql -h mydemoserver.postgres.database.azure.com -U myuser

Si vous utilisez un serveur unique, votre nom d’utilisateur inclut le composant de nom de serveur. Par conséquent, au lieu de myuser, utilisez myuser@mydemoserver.

Remarque

Si vous avez déjà des utilisateurs portant les mêmes noms sur votre serveur unique ou sur votre serveur local à partir duquel vous migrez, et que votre serveur cible, sachez que ce processus de restauration peut modifier les mots de passe de ces rôles. Par conséquent, toutes les commandes suivantes que vous devez exécuter peuvent nécessiter les mots de passe mis à jour. Cela ne s’applique pas si votre serveur source est un serveur flexible, car le serveur flexible n’autorise pas le dumping des mots de passe pour les utilisateurs en raison de mesures de sécurité améliorées.

Créer une base de données

Avant de restaurer votre base de données, vous devrez peut-être créer une base de données vide. Pour ce faire, l’utilisateur que vous utilisez doit disposer de l’autorisation CREATEDB . Voici deux méthodes couramment utilisées :

  1. L’utilisation de createdb l’utilitaire Permet createdb la création de bases de données directement à partir de la ligne de commande bash, sans avoir à se connecter à PostgreSQL ou à quitter l’environnement du système d’exploitation. Exemple :

    createdb <new database name> -h <server name> -U <user name>

    Par exemple, si vous avez un serveur nommé mydemoserver, un utilisateur nommé myuser et la nouvelle base de données que vous souhaitez créer est testdb_copy, exécutez la commande suivante :

    createdb testdb_copy -h mydemoserver.postgres.database.azure.com -U myuser

    Si vous utilisez un serveur unique, votre nom d’utilisateur inclut le composant de nom de serveur. Par conséquent, au lieu de myuser, utilisez myuser@mydemoserver.

  2. À l’aide de la commande SQL Pour créer une base de données à l’aide d’une commande SQL, vous devez vous connecter à votre serveur PostgreSQL via une interface de ligne de commande ou un outil de gestion de base de données. Une fois connecté, vous pouvez utiliser la commande SQL suivante pour créer une base de données :

CREATE DATABASE <new database name>;

Remplacez <new database name> par le nom que vous souhaitez donner à votre nouvelle base de données. Par exemple, pour créer une base de données nommée testdb_copy, la commande serait :

CREATE DATABASE testdb_copy;

Restauration du vidage

Une fois que vous avez créé la base de données cible, vous pouvez restaurer les données dans cette base de données à partir du fichier de vidage. Pendant la restauration, consignez toutes les erreurs dans un errors.log fichier et case activée son contenu pour toutes les erreurs après la restauration.

psql -f <database name>_dump.sql <new database name> -h <server name> -U <user name> 2> errors.log

Par exemple, si vous avez un serveur nommé mydemoserver, un utilisateur nommé myuser et une nouvelle base de données appelée testdb_copy, exécutez la commande suivante :

psql -f testdb_dump.sql testdb_copy -h mydemoserver.postgres.database.azure.com -U myuser 2> errors.log

Après restauration case activée

Une fois le processus de restauration terminé, il est important de passer en revue le errors.log fichier pour connaître les erreurs qui se sont produites. Cette étape est essentielle pour garantir l’intégrité et l’exhaustivité des données restaurées. Résolvez les problèmes détectés dans le fichier journal pour maintenir la fiabilité de votre base de données.

Optimisation du processus de migration

Lorsque vous travaillez avec des bases de données volumineuses, le processus de vidage et de restauration peut être long et peut nécessiter une optimisation pour garantir l’efficacité et la fiabilité. Il est important de connaître les différents facteurs qui peuvent avoir un impact sur les performances de ces opérations et de prendre des mesures pour les optimiser.

Pour obtenir des instructions détaillées sur l’optimisation du processus de vidage et de restauration, reportez-vous aux meilleures pratiques pour pg_dump et l’article pg_restore . Cette ressource fournit des informations et des stratégies complètes qui peuvent être utiles pour la gestion des bases de données volumineuses.

Étapes suivantes