Comparer des Dumps de BDD

ParPomeloVerbeux

Comparer des dumps est une méthode puissante pour assurer la qualité, la cohérence et l’intégrité des bases de données dans de nombreux contextes. Ici nous prendrons l’exemple d’une comparaison des dumps de BDD Postgresql de deux environnements (exemple recette et préprod) pour vérifier l’intégrité des données entre ces deux environnements qui sont censés être iso.

1 - Prérequis : Récupérer les dumps

Pour récupérer un dump d’une base de données PostgreSQL, on peut utiliser l’outil pg_dump, qui est inclus dans les installations PostgreSQL. Il faut donc nécessairement avoir le client psql installé.

# install de psql si on ne l'a pas

sudo apt-get install postgresql-client

# récuperation du dump compressé avec gzip

pg_dump -U nom_utilisateur -h hote -d nom_base_de_donnees | gzip > fichier_dump.sql.gz

# soit ici pour l'exemple (en local)

pg_dump -U mon_user -h localhost -p 5432 -d bdd_recette | gzip > bdd_recette_dump.sql

# Bonus
## spécifier un schémas

pg_dump -U nom_utilisateur -h hote -d nom_base_de_donnees -n nom_schema > fichier_dump.sql

## spécifier une table
pg_dump -U nom_utilisateur -h hote -d nom_base_de_donnees -t nom_table > fichier_table_dump.sql

Si l’utilisateur PostgreSQL est protégé par un mot de passe, on est invités à le saisir. Et voila, on a le dump.

2 - Créer son environnement conteneurisé

L’idée est la suivante : lancer une instance de postgres et un pgadmin dans des conteneurs pour un déploiement rapide de notre support de travail. Une fois en place on restaure nos dumps à comparer dans notre instance de postgres conteneurisé.

On pourra ensuite lancer nos scripts de comparaison.

Remarque : il faut donc avoir docker d’installé (ou Rancher Desktop).

Dans le dossier du projet créer un dossier dumps qui contient vos dumps à comparer.

Dans ce volume qui sera partagé avec le conteneur postgres on dé-compresse et dé-tar nos dumps :

tar -xzf backup.tar.gz -C /chemin/vers/dossier

# Ici on se place dans le bon dossier directement pour lancer le terminal

tar -xzf bdd_recette_dump.tar.gz
tar -xzf bdd_preprod_dump.tar.gz

# Rappels :
# -x : Extraire les fichiers de l'archive.
# -z : Filtrer l'archive via gzip (utilisé pour les fichiers .gz).
# -f : Spécifier le nom du fichier d'archive.

A la racine du projet placer le fichier docker-compose.yml suivant :

version: '3.8'

services:
  postgres:
    image: postgres:14.4
    container_name: postgres
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: password
      POSTGRES_DB: database1
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./dumps:/dumps # Assurez-vous que vos dumps sont placés dans un dossier nommé 'dumps' à côté de ce fichier docker-compose.yml
    ports:
      - "5432:5432"

  pgadmin:
    image: dpage/pgadmin4
    container_name: pgadmin
    environment:
      PGADMIN_DEFAULT_EMAIL: admin@admin.com
      PGADMIN_DEFAULT_PASSWORD: admin
    ports:
      - "8080:80"
    depends_on:
      - postgres

volumes:
  postgres_data:

Puis lancer les conteneurs :

# Mode détaché pour lancer les conteneurs en arrière plan
docker compose up -d

On rentre ensuite dans le conteneur postgres pour y ingérer le premier dump. La première BDD réceptrice (database1) a été initiée avec notre docker-compose.yml

# connexion au conteneur
docker exec -it postgres bash

# backup restore comme on dit
pg_restore -U nom_utilisateur -d nom_base -t nom_table chemin_vers_le_fichier_dump

#soit :
pg_restore -U postgres -d database1 bdd_recette_dump.sql

Une fois fait, on utilise l’IHM du conteneur pgadmin pour créer une deuxième BDD. 

Click droit (sur databases) > Create > Database

Celle-ci accueillera notre dump de préprod. On l’appellera database2. (owner postgres)
On peut ensuite ré-itérer l’opération précédente :

# connexion au conteneur postgres
docker exec -it postgres bash

pg_restore -U postgres -d database2 bdd_preprod_dump.sql

Nous sommes prêts pour la comparaison / analyse.

3 - Comparaison avec Pandas

Tout d’abord initialisons les connexion à nos BDD :

import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy import *

# Database connection strings
db_connection_str1 = 'postgresql://postgres:postgres@localhost:5432/database1'
db_connection_str2 = 'postgresql://postgres:postgres@localhost:5432/database2'
db_engine1 = create_engine(db_connection_str1)
db_engine2 = create_engine(db_connection_str2)

# init variables
total_elements = 0
total_differences = 0
table_names = ['Table1','Table2', 'Table3']
chunk_size = 2000

Dans l’exemple donné, nous comparons les dumps de deux bases de données provenant de deux environnements iso. Étant donné que les données dans ces deux environnements sont censées être identiques, nous pouvons émettre l’hypothèse que vérifier cette homogénéité en comparant les tailles des tables suffit, soit le nombre de lignes et/ou la taille des données stockées pour chaque table. Cette approche nous permet de détecter rapidement des anomalies éventuelles ou des différences dans le contenu, sans avoir à examiner chaque ligne de données individuellement.

def compare_size_in_chunks(db_engine1, db_engine2, table_name, chunk_size):
    df1_size = 0
    df2_size = 0
    diff_size = 0
    print('********************')
    print('TABLE : ', table_name)

    for chunk_df1, chunk_df2 in zip(
        pd.read_sql_table(table_name, con=db_engine1, chunksize=chunk_size),
        pd.read_sql_table(table_name, con=db_engine2, chunksize=chunk_size)):
        
        df1_size += chunk_df1.size
        df2_size += chunk_df2.size

    diff_size = max(df1_size, df2_size) - min(df1_size, df2_size)
    print('DF1 : ', df1_size)
    print('DF2 : ', df2_size)
    print('DIFF : ', diff_size)
    if diff_size>0:
        if df1_size>df2_size:
            print('% DIFF : ',(diff_size/df1_size)*100)
        else:
            print('% DIFF : ',(diff_size/df2_size)*100)

Pour aller plus loin, si la comparaison des tailles des tables révèle des incohérences ou si l’analyse de taille seule ne suffit pas à garantir l’intégrité des données, il est alors pertinent de procéder à une vérification plus approfondie en comparant les données elles-mêmes. Cette méthode consiste à s’assurer que chaque entrée présente dans une table d’une base de données se retrouve également dans son miroir dans l’autre base. On saura alors si toutes les lignes et les valeurs dans les tables des deux bases de données sont présentes et cohérentes, permettant ainsi de garantir non seulement que la structure des données est similaire, mais aussi que le contenu est complètement synchronisé entre les deux environnements (ou de mettre le doigt sur la source de l’erreur remontée à l’étape précédente).

Ici on suppose que les tables sont de taille raisonnables et peuvent être chargées d’un coup dans le dataframe.

for table_name in table_names:
    df1 = pd.read_sql_table(table_name, con=db_engine1.connect())
    df2 = pd.read_sql_table(table_name, con=db_engine2.connect())

    # Suppression de la colonne 'id' si elle existe
    df1 = df1.drop(columns=['id'], errors='ignore')
    df2 = df2.drop(columns=['id'], errors='ignore')

    # Tri par chacune des colonne pour avoir un ordre consistent
    df1_sorted = df1.sort_values(by=df1.columns.tolist()).reset_index(drop=True)
    df2_sorted = df2.sort_values(by=df2.columns.tolist()).reset_index(drop=True)

    # Comparaison
    if df1_sorted.equals(df2_sorted):
        print(f"Tables '{table_name}' are identical.")
    else:
        print(f"Tables '{table_name}' are different.")

        # Identification des lignes differentes
        diff1 = df1_sorted.merge(df2_sorted, how='outer', indicator=True).loc[lambda x: x['_merge'] == 'left_only']
        diff2 = df2_sorted.merge(df1_sorted, how='outer', indicator=True).loc[lambda x: x['_merge'] == 'right_only']
        
        # EXPLICATIONS : Si les tables sont différentes, 
        # le code identifie les lignes présentes dans df1 mais pas dans df2 et vice versa. 
        # Cela se fait en utilisant merge avec l'option how='outer' et indicator=True, 
        # ce qui permet de voir quelles lignes sont uniques à chaque table.

        # Calcul des differences
        num_diff_rows = len(diff1) + len(diff2)
        total_rows = len(df1_sorted) + len(df2_sorted) #- num_diff_rows
        percentage_difference = (num_diff_rows / total_rows) * 100

        # Output
        print(f"Number of different rows in env1': {len(diff1)}")
        print(f"Number of different rows in env2': {len(diff2)}")
        print(f"Number of different rows in '{table_name}': {num_diff_rows}")
        print(f"Total number of rows in '{table_name}': {total_rows}")
        print(f"Percentage of difference in '{table_name}': {percentage_difference:.6f}%")

Jusqu’ici notre comparaison s’effectuait ligne à ligne. Une autre méthode consiste à vérifier si chaque ligne de l’une des tables est présente dans l’autre, sans tenir compte de l’ordre des lignes ou d’une comparaison stricte ligne à ligne.

for table_name in table_names:
    df1 = pd.read_sql_table(table_name, con=db_engine1.connect())
    df2 = pd.read_sql_table(table_name, con=db_engine2.connect())

    # Suppression de la colonne 'id' si elle existe
    df1 = df1.drop(columns=['id'], errors='ignore')
    df2 = df2.drop(columns=['id'], errors='ignore')

    # Trier les colonnes pour assurer une comparaison correcte
    df1 = df1[sorted(df1.columns)]
    df2 = df2[sorted(df2.columns)]

    # Comparaison
    df1_in_df2 = df1.isin(df2.to_dict(orient='list')).all(axis=1)
    # df1_in_df2: Vérifie pour chaque ligne de df1 si elle est présente dans df2. 
    # On utilise isin pour cela, qui vérifie si les valeurs de df1 existent dans df2
    # symétriquement :
    df2_in_df1 = df2.isin(df1.to_dict(orient='list')).all(axis=1)

    if df1_in_df2.all() and df2_in_df1.all():
        print(f"All rows in '{table_name}' from both DataFrames match.")
    else:
        # Identifier les lignes présentes dans df1 mais pas dans df2
        only_in_df1 = df1[~df1_in_df2]
        # Identifier les lignes présentes dans df2 mais pas dans df1
        only_in_df2 = df2[~df2_in_df1]

        # Output
        print(f"Rows in '{table_name}' present only in the first DataFrame (db1):")
        print(only_in_df1)

        print(f"Rows in '{table_name}' present only in the second DataFrame (db2):")
        print(only_in_df2)

        # Nombre total de lignes différentes
        num_diff_rows = len(only_in_df1) + len(only_in_df2)
        total_rows = len(df1) + len(df2)

        percentage_difference = (num_diff_rows / total_rows) * 100

        print(f"Number of different rows in '{table_name}': {num_diff_rows}")
        print(f"Total number of rows in '{table_name}': {total_rows}")
        print(f"Percentage of difference in '{table_name}': {percentage_difference:.6f}%")

Toutefois, cette dernière méthode, bien que flexible et pratique pour les tables non triées, est très coûteuse en mémoire et en temps de calcul car elle charge les DataFrames complets en mémoire et compare chaque ligne à toutes les autres du second dataframe. Il est important de noter que cette tâche peut devenir rapidement complexe, surtout lorsqu’il s’agit de grands volumes de données. Avec des outils comme Pandas, la gestion de telles données peut nécessiter l’utilisation de techniques avancées, telles que le traitement des données en chunks, pour éviter les problèmes de performance ou de mémoire. Mais cela rajoute de la complexité logique sur le code.

De plus les deux méthodes ci-dessus donnent des résultats sensiblement différents. En effet la méthode isin() de Pandas ne prend pas en compte les doublons dans sa comparaison. Pour obtenir des résultats plus précis, robustes et cohérents, il est recommandé d’utiliser la première méthode qui trie les lignes et utilise merge pour identifier les différences.

4 - Comparaison précise des données avec SQL

On retiendra que :

  • Pour des petits à moyens ensembles de données et des comparaisons spécifiques, Pandas peut être très pratique grâce à sa flexibilité et ses puissantes fonctionnalités d’analyse.
  • Pour des grands ensembles de données, des comparaisons basées directement sur SQL sont généralement plus efficaces. Elles utilisent les capacités optimisées du SGBD pour traiter les données et peuvent être moins gourmandes en ressources. 

Pour comparer nos deux BDD (sur le même serveur) il faut créer un dblink qui permet d’établir des connexions à d’autres bases de données et d’exécuter des requêtes sur celles-ci. On utilise ici la console (Query Tool) de l’IHM de notre conteneur pgadmin. On l’ouvre dans database1.

-- on cree l'exetension dblink
CREATE EXTENSION dblink;

-- pour verifier que l'extension est bien crée
SELECT * FROM pg_available_extensions WHERE name = 'dblink';

--Comparaison des tables 2 à 2 
-- Rappel on a ouvert une console dans database-1
SELECT * 
FROM monSchema."Table1"
EXCEPT
SELECT *
FROM dblink('dbname=database-2', 'SELECT * FROM monSchema."Table1"') 
AS t1(id bigint, 
      col2 character varying, 
      col3 col_type, 
      ...);
      
--RAPPELS : Pour avoir les colonnes d'une table et leurs types 
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'monSchema'
AND table_name = 'Table1';

5 - Bonus

Choisir le bon outil dépend des besoins spécifiques et ici une solution parmi d’autres est présentée.

le top 10 des outils de comparaison généré par chatGPT

1. pgAdmin
  • Type : Interface graphique.
  • Fonctionnalités : Bien que pgAdmin ne soit pas un outil de comparaison de dumps à proprement parler, il offre des fonctionnalités pour comparer les schémas de bases de données directement au sein de l’application. Cela peut être utile pour identifier les différences entre deux bases de données (et donc indirectement entre deux dumps).
  • Utilisation : Importer les dumps dans deux bases de données distinctes et utiliser les outils intégrés pour comparer les schémas.
2. pg_diff
  • Type : Outil en ligne de commande.
  • Fonctionnalités : Comparaison des structures de schéma entre deux bases de données PostgreSQL. Il génère un fichier SQL de migration pour synchroniser les différences.
  • Utilisation : Utilisez pg_dump pour exporter les schémas des bases de données dans des fichiers SQL, puis utilisez pg_diff pour comparer les fichiers.
3. apgdiff (Another PostgreSQL Diff Tool)
  • Type : Outil en ligne de commande.
  • Fonctionnalités : Comparaison des schémas entre deux bases de données PostgreSQL, génère des scripts SQL pour synchroniser les différences.
  • Utilisation : Exporter les schémas des bases de données en fichiers SQL avec pg_dump, puis comparer les fichiers avec apgdiff.
4. dbForge Data Compare for PostgreSQL
  • Type : Outil GUI (propriétaire, payant).
  • Fonctionnalités : Comparaison de données entre deux bases de données PostgreSQL, synchronisation des données, génération de rapports de comparaison.
  • Utilisation : Connectez-vous à deux bases de données et utilisez l’interface graphique pour comparer les données et les structures.
5. Navicat for PostgreSQL
  • Type : Outil GUI (propriétaire, payant).
  • Fonctionnalités : Comparaison de schémas, comparaison de données, synchronisation de données et schémas entre deux bases de données PostgreSQL.
  • Utilisation : Connectez-vous à deux bases de données ou importez des dumps, puis utilisez les outils de comparaison intégrés.
6. Redgate’s Schema Compare for PostgreSQL
  • Type : Outil GUI (propriétaire, payant).
  • Fonctionnalités : Comparaison de schémas, synchronisation, génération de scripts de migration.
  • Utilisation : Comparer les schémas entre deux bases de données ou entre des dumps.
7. Liquibase
  • Type : Outil en ligne de commande et bibliothèque Java.
  • Fonctionnalités : Comparaison des schémas de base de données, génération de scripts de migration, versionnage des bases de données.
  • Utilisation : Utilisez Liquibase pour gérer les changements de schémas et pour comparer les structures de base de données.
8. Beyond Compare
  • Type : Outil GUI (propriétaire, payant).
  • Fonctionnalités : Comparaison de fichiers, y compris des fichiers SQL, avec des options pour comparer des dumps ligne par ligne.
  • Utilisation : Utilisez pg_dump pour exporter les bases de données en fichiers SQL, puis comparez ces fichiers avec Beyond Compare.
9. Meld
  • Type : Outil GUI (open source).
  • Fonctionnalités : Comparaison de fichiers, y compris des fichiers SQL.
  • Utilisation : Comparez manuellement les dumps SQL générés par pg_dump.
10. DiffDog
  • Type : Outil GUI (propriétaire, payant).
  • Fonctionnalités : Comparaison de schémas de bases de données et de données entre bases de données ou dumps.
  • Utilisation : Connectez-vous à deux bases de données ou chargez des dumps pour les comparer.

Publications similaires

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *