pg_comparator - En ligne dans le Cloud

PROGRAMME:

Nom

pg_comparator - comparaison et synchronisation efficaces du contenu des tables

SYNOPSIS

pg_comparateur [options comme --Aidez-moi --option --homme] conn1 conn2

DESCRIPTION

Ce script effectue une comparaison ou une synchronisation efficace en termes de réseau et de temps de deux
éventuellement de grandes tables dans PostgreSQL, MySQL or SQLite bases de données, afin de détecter les insérés,
tuples mis à jour ou supprimés entre ces tables. L'algorithme est efficace surtout si
les différences attendues sont relativement faibles.

L'implémentation est assez générique : clés multi-colonnes (mais il doit y avoir une clé !), non
l'hypothèse de types de données autres qu'ils peuvent être convertis en texte, un sous-ensemble de colonnes peut être
utilisé pour la comparaison, la gestion des valeurs NULL...

Ce script se concentre sur l'algorithme de comparaison, d'où les nombreuses options. Le fait qu'il
peut faire quelque chose d'utile, comme vérifier qu'un outil de réplication se réplique bien
vos données, ou comme la synchronisation des tables, est un simple effet secondaire.

OPTIONS

Des options permettent de demander de l'aide ou d'ajuster certains paramètres internes. Une lettre courte
des options sont également disponibles, généralement avec la première lettre du nom de l'option.

"--aggregate=(somme|xor)" ou "-a (somme|xor)"
Fonction d'agrégation à utiliser pour les résumés, soit xor or somme. Il doit fonctionner sur
le résultat de la fonction de somme de contrôle. Pour PostgreSQL et SQLite, le xor agrégat
doit être chargé. Il y a un problème signé/non signé sur le hachage de clé lors de l'utilisation xor
pour comparer des tables sur MySQL ou SQLite vs PostgreSQL. Nous fournissons un nouveau "ISUM"
agrégé pour SQLite car "SUM" et "TOTAL" effectuent une gestion incompatible de
débordements d'entiers.

La valeur par défaut est somme car il est disponible par défaut et fonctionne en mode mixte.

"--demander-passer"
Demandez des mots de passe de manière interactive. Voir aussi l'option "--env-pass" ci-dessous.

La valeur par défaut est de ne pas demander de mots de passe.

"--asynchronous" ou "-A", "--no-asynchronous" ou "-X"
S'il faut exécuter des requêtes asynchrones. Cela fournit un certain parallélisme, mais les deux
les connexions sont plus ou moins synchronisées par requête.

La valeur par défaut consiste à utiliser des requêtes asynchrones pour activer un certain parallélisme.

"--checksum-computation=(create|insert)" ou "--cc=..."
Comment créer la table de somme de contrôle. Utilisation engendrent pour utiliser un "CREATE ... AS SELECT ..."
requête, ou insérer pour utiliser une requête "CREATE ...; INSERT ... SELECT ...". Le premier sera
nécessitent un comptage supplémentaire pour obtenir la taille du tableau, donc au final il y a deux
requêtes de toute façon. Il y a un problème de taille de type avec le insérer stratégie sur MySQL, le
la longueur cumulée de la chaîne de clé doit être inférieure à 64 octets.

La valeur par défaut est engendrent car cela fonctionne toujours pour les deux bases de données.

"--checksum-function=fun" ou "--cf=fun" ou "-c fun"
Fonction de somme de contrôle à utiliser, soit ck, fnv or md5. Pour PostgreSQL, MySQL et SQLite, le
à condition de ck et votre fnv les fonctions de somme de contrôle doivent être chargées dans les bases de données cibles.
Choisir md5 n'est pas gratuit non plus : les fonctions de cast fournies doivent être chargées
dans les bases de données cibles et le calcul est plus coûteux.

La valeur par défaut est ck, ce qui est rapide, surtout si l'opération est liée au processeur et que le
la bande passante est raisonnablement élevée.

"--checksum-size=n" ou "--check-size=n" ou "--cs=n" ou "-zn"
La taille de la somme de contrôle du tuple doit être 2, 4 or 8 octets. La taille de la somme de contrôle de la clé est toujours de 4 octets
longtemps.

La valeur par défaut est 8, de sorte que la probabilité de faux négatif est très faible. Il ne devrait pas y avoir
raison de changer cela.

"--nettoyer"
Supprimez la somme de contrôle et les tableaux récapitulatifs au préalable. Utile après une exécution avec "--no-temp" et
"--no-clear", généralement utilisé pour le débogage.

La valeur par défaut est de ne pas abandonner car ce n'est pas nécessaire.

"--dégager"
Supprimez la somme de contrôle et les tableaux récapitulatifs explicitement après le calcul. Notez qu'ils sont
abandonné implicitement par défaut lorsque la connexion est fermée car ils sont temporaires, voir
Option "-(-no)-temporaire". Cette option est utile pour le débogage.

La valeur par défaut est pas effacer explicitement la somme de contrôle et les tableaux récapitulatifs, car il n'est pas
nécessaire.

"--debug" ou "-d"
Définir le mode de débogage. Répétez l'opération pour les niveaux de débogage supérieurs. Voir aussi "--verbose". Méfiez-vous que
certaines précautions concernant les paramètres d'options sont ignorées sous débogage afin de permettre les tests
dans des conditions différentes.

La valeur par défaut est de ne pas s'exécuter en mode débogage.

"--env-pass='var'"
Prenez le mot de passe des variables d'environnement "var1", "var2" ou "var" pour la première connexion,
deux, ou les deux. Ceci est essayé avant de demander de manière interactive si "--ask-pass" est également défini.

La valeur par défaut est de ne pas rechercher les mots de passe à partir des variables d'environnement.

"--expect n" ou "-en"
Nombre total de différences à prévoir (mises à jour, suppressions et insertions). Cette option est
utilisé uniquement pour les tests de non régression. Voir la section ESSAIS.

"--folding-factor=7" ou "-f 7"
Facteur de pliage : log2 du nombre de lignes regroupées à chaque étape, en commençant
à partir des feuilles afin que le premier tour regroupe toujours le plus d'enregistrements possible. Les
la puissance de deux permet d'utiliser des calculs masqués. La valeur minimale de 1 crée un
arbre binaire.

Le facteur de pliage par défaut log2 est 7, soit taille 128 plis. Cette valeur par défaut a été choisie
après quelques tests de base sur des boîtiers de taille moyenne avec une bande passante moyenne ou faible. Valeurs de
4 à 8 devraient être un choix raisonnable pour la plupart des réglages.

"--help" ou "-h"
Afficher une brève aide.

"--key-checksum='kcs'" ou "--kcs=..."
Utiliser l'attribut de somme de contrôle de clé de ce nom, qui doit déjà être disponible dans les tables
comparer. Cette option nécessite également l'option "--tuple-checksum". Voir aussi le
EXEMPLES ci-dessous pour savoir comment définir un déclencheur de somme de contrôle. Considérez "--use-key"
à la place si vous avez déjà une clé primaire entière raisonnablement distribuée.

La valeur par défaut consiste à créer à la volée les sommes de contrôle des clés et des tuples.

"--lock", "--no-lock"
S'il faut verrouiller les tables. La définition de l'option remplace explicitement le sens unique par défaut
ou un autre. Pour PostgreSQL, cette option nécessite "--transaction", qui est activé par
défaut.

La valeur par défaut dépend de l'opération en cours : la table est pas fermé pour une comparaison,
mais il est fermé pour une synchronisation.

"--long-read-len=0" ou "-L 0"
Définir la taille maximale pour les gros objets binaires récupérés. Eh bien, il semble être ignoré au moins
par le pilote PostgreSQL.

La valeur par défaut consiste à conserver la valeur par défaut définie par le pilote.

"--man" ou "-m"
Afficher la page de manuel de manière interactive dans le terminal.

"--max-ratio=0.1"
Effort de recherche relatif maximum. La recherche est arrêtée si le nombre de résultats est
au-dessus de ce seuil exprimé par rapport à la taille de la table. Utilisez 2.0 pour aucune limite
(tous les tuples ont été supprimés et de nouveaux sont insérés).

La valeur par défaut est 0.1, c'est-à-dire qu'une différence globale de 10 % est autorisée avant d'abandonner.

"--max-rapport=n"
Effort de recherche absolu maximum. La recherche est arrêtée si le nombre de différences
dépasse ce seuil. Si elle est définie, l'option précédente "--max-ratio" est ignorée,
sinon l'effort est calculé avec le ratio une fois que la taille de la table est connue.

La valeur par défaut consiste à calculer le nombre maximum de différences signalées en fonction de la
Option "--max-ratio", avec un minimum de 100 différences autorisées.

"--niveaux-max=0"
Nombre maximum de niveaux utilisés. Permet de couper le pliage. 0 signifie pas de coupure.
La définition d'une valeur de 1 n'utiliserait que la table de somme de contrôle, sans résumés. Une valeur de
3 ou 4 serait raisonnable, car les derniers niveaux de l'arbre sont agréables pour le
formule de complexité théorique, mais n'améliore pas les performances en pratique.

La valeur par défaut est 0.

"--null='texte'"
Comment gérer les valeurs NULL. Soit hachage pour hacher toutes les valeurs, où NULL a un
valeur de hachage, ou texte où les valeurs NULL sont remplacées par la chaîne "NULL".

La valeur par défaut est texte car c'est plus rapide.

"--option" ou "-o"
Afficher le résumé des options.

"--pg-copie=128"
Option expérimentale pour utiliser COPY de PostgreSQL au lieu de INSERT/UPDATE lorsque
synchronisation, par morceaux de la taille spécifiée.

"--prefix='pgc_cmp'"
Préfixe de nom, éventuellement qualifié de schéma, utilisé pour les tableaux de comparaison générés par
en y ajoutant des numéros. Pensez à changer le préfixe si vous attendez plusieurs
comparaisons à exécuter simultanément avec la même base de données.

La valeur par défaut est "pgc_cmp". Les tables de Cheksum sont nommées "pgc_cmp_1_0" et "pgc_cmp_2_0", et
les tableaux récapitulatifs sont nommés en augmentant le dernier nombre.

"--report", "--no-report"
Signalez les différentes clés à stdout au fur et à mesure qu'elles sont trouvées.

La valeur par défaut est de signaler.

"--separator='|'" ou "-s '|'"
Chaîne ou caractère de séparation utilisé lors de la concaténation des colonnes clés pour le calcul
sommes de contrôle.

Par défaut le tube '|' personnage.

"--taille=n"
Supposons cette valeur comme taille de la table. Il suffit que l'algorithme exécute
bien que cette taille est de l'ordre de grandeur de la taille réelle de la table.

La valeur par défaut consiste à interroger les tailles de table, ce qui est ignoré si cette option est définie.

"--source-1='DBI:...'", "--source-2='...'" ou "-1 '...'", "-2 '...'"
Prenez le contrôle total de la spécification de la source de données DBI et ignorez la plupart du temps la comparaison
partie d'authentification des URL source ou cible. On peut se connecter avec
"DBI:Pg:service=backup", utilisez un autre pilote, définissez n'importe quelle option autorisée par le
driver... Voir les manuels "DBD::Pg" et "DBD:mysql" pour les différentes options qui peuvent être
défini via la spécification de source de données DBI. Cependant, le serveur de base de données spécifié
dans l'URL doit être cohérente avec cette spécification source afin que les requêtes
la syntaxe est la bonne.

La valeur par défaut consiste à s'appuyer sur les deux arguments d'URL.

"--skip-inserts", "--skip-updates", "--skip-deletes"
Lors de la synchronisation, n'effectuez pas ces opérations.

La valeur par défaut sous "--synchronize" consiste à effectuer toutes les opérations.

"--stats=(txt|csv)"
Afficher diverses statistiques sur la comparaison effectuée dans ce format. Aussi, possibilité
"--stats-name" donne un nom au test, utile pour générer des fichiers csv qui seront
traité automatiquement.

La valeur par défaut est pas pour afficher des statistiques, car cela nécessite des synchronisations supplémentaires et
n'est pas forcément intéressant pour l'utilisateur.

"--synchronise" ou "-S"
Effectuez en fait des opérations pour synchroniser la deuxième table par rapport à la première. Et bien non
vraiment, ce n'est qu'un essai. C'est effectivement fait si vous ajoutez "--do-it" ou "-D". sauvegarder
vos données avant d'essayer quelque chose comme ça !

La valeur par défaut est de ne pas synchroniser.

"--temporaire", "--non-temporaire"
S'il faut utiliser des tables temporaires. Si vous ne le faites pas, les tables sont conservées par défaut au
fin, ils devront donc être supprimés à la main. Voir l'option "--clear" pour demander un
nettoyer. Cette option est utile pour le débogage.

La valeur par défaut consiste à utiliser des tables temporaires qui sont automatiquement effacées lorsque le
la connexion est fermée.

"--unlogged", "--no-unlogged"
Utilisez des tables non enregistrées pour stocker les sommes de contrôle. Ces tables ne sont pas transactionnelles, il
peut accélérer un peu les choses. Cependant, ils ne sont pas automatiquement nettoyés au
finir. Voir l'option "--clear" pour demander un nettoyage.

La valeur par défaut consiste à ne pas utiliser de tables non consignées.

"--threads" ou "-T", "--no-threads" ou "-N"
Caractéristique hautement EXPÉRIMENTALE.

Essayez d'utiliser des threads pour effectuer des calculs en parallèle, avec quelques tours de passe-passe car
Le modèle de thread perl ne fonctionne pas vraiment bien avec DBI. Les fils Perl sont plutôt lourds
et lent, plus comme des processus de communication que comme des threads légers, vraiment.

Cela ne fonctionne PAS du tout avec PostgreSQL. Il fonctionne partiellement avec MySQL, au
prix de la désactivation de "--transaction".

La valeur par défaut est pas d'utiliser des threads, car cela ne fonctionne pas pour toutes les bases de données.

"--timeout n"
Comparaison du délai d'attente après "n" secondes.

La valeur par défaut n'est pas de délai d'attente. Sois patient.

"--transaction", "--pas-transaction"
S'il faut envelopper l'ensemble de l'algorithme en une seule transaction.

La valeur par défaut consiste à utiliser une transaction d'emballage, car il semble à la fois plus rapide et plus sûr de
fais-le.

"--tuple-checksum='tcs'" ou "--tcs=..."
Utilisez l'attribut de somme de contrôle de tuple de ce nom, qui doit déjà être disponible dans le
tableaux à comparer. Cette option nécessite de définir également "--use-key" ou
"--key-checksum=..." ci-dessus. Les attributs de somme de contrôle fournis ne doivent pas apparaître dans le
listes de colonnes de clé et de valeur. Voir aussi la section EXEMPLES ci-dessous pour savoir comment définir un
déclencheur de somme de contrôle.

La valeur par défaut consiste à créer à la volée les sommes de contrôle des clés et des tuples.

"--use-key" ou "-u"
S'il faut utiliser directement la valeur de la clé pour distribuer les tuples entre les branches. Les
la clé doit être simple, entière, non NULL, et uniformément répartie. Si tu as un
clé primaire entière raisonnablement répartie, envisagez d'utiliser cette option pour éviter la moitié de la
calculs de hachage de table de somme de contrôle.

La valeur par défaut consiste à hacher la clé, de manière à gérer n'importe quel type, composition et distribution.

"--use-null", "--no-use-null"
S'il faut utiliser les informations selon lesquelles une colonne est déclarée NON NULL pour simplifier
calculs en évitant les appels à COALESCE pour gérer les valeurs NULL.

La valeur par défaut est d'utiliser ces informations, au prix de l'interrogation des métadonnées de la table.

"--verbose" ou "-v"
Soyez bavard sur ce qui se passe. Plus vous demandez, plus bavard.

La valeur par défaut est d'être silencieux, afin que d'éventuels avertissements ou erreurs ressortent.

"--version" ou "-V"
Afficher les informations sur la version et quitter.

"--où=..."
Condition booléenne SQL sur les tuples de table pour une comparaison partielle. Utile pour réduire le
chargez si vous savez que des différences attendues existent dans certaines parties de vos données, disons celles
horodaté aujourd'hui... La même condition est passée des deux côtés, donc les deux tables doivent
être assez similaire pour que cela fonctionne. C'est généralement le cas.

La valeur par défaut consiste à comparer des tables entières.

ARGUMENTS

Les deux arguments décrivent les connexions à la base de données avec la syntaxe de type URL suivante, où
les crochets indiquent les parties facultatives. De nombreuses pièces sont facultatives avec une valeur par défaut. Le minimum
la spécification syntaxiquement correcte est "/", mais cela ne veut pas nécessairement dire quoi que ce soit
utile.

[driver://][login[:pass]@][host][:port]/[base/[[schema.]table[?key[:cols]]]]

Voir la section EXEMPLES ci-dessous, ainsi que les options "--source-*" ci-dessus.

Notez que certaines valeurs par défaut utilisées par les pilotes DBI peuvent être modifiées par des pilotes spécifiques
variables d'environnement, et que DBI fournit également ses propres valeurs par défaut et remplacements, alors quoi
se passe réellement peut ne pas toujours être clair. Les valeurs par défaut pour la deuxième URL sont principalement
extrait de la première URL.

driver
Pilote de base de données à utiliser. Utilisation pgsql pour PostgreSQL, mysql pour MySQL, sqlite pour SQLite.
Des bases de données hétérogènes peuvent être comparées et synchronisées, mais attention aux subtilités
les problèmes de typage, d'encodage et de transtypage peuvent empêcher des comparaisons hétérogènes ou
synchronisations pour réussir. La valeur par défaut est pgsql pour la première connexion, et comme
premier pour deuxième.

Pour SQLite, la partie authentification de l'URL (login, pass, host, port) est attendue
être vide, donc l'URL complète devrait ressembler à :

sqlite:///base.db/table?key,col:other,colonnes

De plus, définir la variable d'environnement PGC_SQLITE_LOAD_EXTENSION avec
Les fichiers d'objets partagés séparés par ":" les chargent dans SQLite.

vous connecter
Login à utiliser lors de la connexion à la base de données. La valeur par défaut est le nom d'utilisateur pour la première connexion,
et même que la première connexion pour la seconde.

pass
Mot de passe à utiliser lors de la connexion à la base de données. Notez que c'est une mauvaise idée de mettre un
mot de passe comme argument de commande. La valeur par défaut est aucun pour la première connexion, et le
même mot de passe que la première connexion pour la seconde if la connexion cible le
même hôte, port et utilise le même login. Voir aussi "--ask-pass" et "--env-pass"
options.

hôte
Nom d'hôte ou IP auquel se connecter. La valeur par défaut est la chaîne vide, ce qui signifie se connecter à
la base de données sur localhost avec un socket UNIX.

port
Port TCP-IP auquel se connecter. La valeur par défaut est 5432 pour PostgreSQL et 3306 pour MySQL.

base
Catalogue de base de données auquel se connecter. La valeur par défaut est le nom d'utilisateur pour la première connexion. La valeur par défaut est
Identique à la première connexion pour la deuxième connexion. Pour SQLite, fournissez le fichier de base de données
Nom. Le chemin est relatif par défaut, mais peut être rendu absolu en ajoutant un
Additionnel '/':

sqlite:////var/cache/sqlite/base.db/table?...

schéma.table
La table éventuellement qualifiée de schéma à utiliser pour la comparaison. Pas de valeur par défaut pour le premier
lien. La valeur par défaut est identique à la première connexion pour la deuxième connexion.

Notez que MySQL n'a pas schémas, mais assez étrangement leur base de données le concept est
comme un schéma, donc MySQL n'a vraiment pas bases de données, bien qu'il y ait
quelque chose de ce nom. Suis-je clair?

clés
Liste des colonnes clés séparées par des virgules. La valeur par défaut est la clé primaire de la table pour la première
lien. La valeur par défaut est identique à la première connexion pour la deuxième connexion. La clé
ne peut pas être vide. Si vous n'avez pas de moyen d'identifier vos tuples, alors il n'y a pas
point à chercher des différences.

cols
Liste de colonnes séparées par des virgules à comparer. Peut être vide. La valeur par défaut est toutes les colonnes mais
clés pour la première connexion. La valeur par défaut est identique à la première connexion pour la deuxième connexion.
Attention, "...?key:" signifie une colonne vide, tandis que "...?key" définit la valeur par défaut en
interroger les métadonnées de la table.

EXEMPLES

Comparer les tables calvin et hobbes dans la famille de base de données sur localhost, avec clé id et colonnes
c1 et votre c2:

./pg_comparator /famille/calvin?id:c1,c2 /famille/hobbes

Comparer les tables calvin dans la base de données par défaut sur localhost et la même table par défaut
base de données sur les sablons, avec clé id et colonne données,:

./pg_comparator localhost/family/calvin?id:data sablons/

Synchroniser la table "user" dans la base de données "wikipedia" de MySQL sur "server1" vers PostgreSQL sur
"serveur2".

./pg_comparator -S -D --ask-pass \
mysql://calvin@server1/wikipedia/user pgsql://hobbes@server2/

Pour PostgreSQL, vous pouvez ajouter des sommes de contrôle de clé et de tuple maintenues par le déclencheur comme :

-- TABLE Foo (id CLÉ PRIMAIRE SÉRIE, données ... NON NULL);
-- ajouter une clé et des attributs de somme de contrôle de tuple
-- la somme de contrôle de la clé peut être ignorée si vous utilisez --use-key,
-- pour lequel la clé doit être un simple entier NOT NULL.
ALTER TABLE
AJOUTER COLONNE key_cs INT4 NON NULL DEFAUT 0,
AJOUTER LA COLONNE tup_cs INT8 NON NULL DEFAUT 0 ;
-- fonction pour mettre à jour la somme de contrôle du tuple
-- si certains attributs peuvent être NULL, ils doivent être fusionnés
CRÉER UNE FONCTION foo_cs() RETOURNE LE DÉCLENCHEUR COMME $$
COMMENCER
-- calcule la somme de contrôle de la clé
NOUVEAU.key_cs = cksum4(NEW.id);
-- calcule la somme de contrôle des tuples
NEW.tup_cs = cksum8(NEW.id || '|' || NEW.data);
RETOUR NEUF ;
FINIR; $$ LANGUE plpgsql;
-- définir le déclencheur pour appeler la fonction de mise à jour de la somme de contrôle
CRÉER UN DÉCLENCHEUR foo_cs_trigger
AVANT LA MISE À JOUR OU L'INSERTION SUR Foo
POUR CHAQUE LIGNE EXÉCUTER LA PROCÉDURE foo_cs();
-- si la table Foo n'est pas initialement vide,
-- mettre à jour son contenu pour déclencher des calculs de somme de contrôle
MISE À JOUR Foo SET id=id;

Ensuite, une comparaison rapide, qui n'a pas besoin de calculer la table de somme de contrôle initiale, peut être
demandé avec :

./pg_comparator --tcs=tup_cs --kcs=key_cs \
admin@server1/app/Foo?id:data hobbes@server2/

Comme la clé primaire est un simple entier, le clé_cs pourrait être omis et la comparaison
pourrait être lancé avec :

./pg_comparator --tcs=tup_cs --use-key \
admin@server1/app/Foo?id:data hobbes@server2/

SORTIE

La sortie de la commande se compose de lignes décrivant les différences trouvées entre les
deux tables. Ils sont exprimés en terme d'insertions, de mises à jour ou de suppressions et de tuple
clés.

MISE À JOUR k
ACTIVITES k tuple est mis à jour du tableau 1 au tableau 2. Il existe dans les deux tableaux avec
des valeurs différentes.

INSERT k
ACTIVITES k le tuple n'apparaît pas dans le tableau 2, mais seulement dans le tableau 1. Il doit être inséré dans
table 2 pour la synchroniser avec la table 1.

EFFACER k
ACTIVITES k tuple apparaît dans le tableau 2, mais pas dans le tableau 1. Il doit être supprimé de 2 à
synchronisez-le avec la table 1.

En cas de collisions de somme de contrôle de tuple, des résultats faussement négatifs peuvent se produire. Changer le
la fonction de somme de contrôle serait utile dans de tels cas. Voir la sous-section ANALYSE.

DÉPENDANCES

Trois fonctions de support sont nécessaires sur la base de données :

1.
La fonction "COALESCE" prend en charge les valeurs NULL dans les colonnes.

2.
Une fonction de somme de contrôle doit être utilisée pour réduire et distribuer les valeurs des clés et des colonnes. Cela pourrait
être modifié avec l'option "--checksum". Sa taille peut être sélectionnée avec le
Option "--checksize" (actuellement 2, 4 ou 8 octets). Les sommes de contrôle exigent également que les moulages soient
convertis en nombres entiers de différentes tailles.

Des implémentations appropriées sont disponibles pour PostgreSQL et peuvent être chargées sur le serveur
en traitant "share/contrib/pgc_checksum.sql" et "share/contrib/pgc_casts.sql". Nouveau
les sommes de contrôle et les conversions sont également disponibles pour MySQL, voir "mysql_*.sql". Un chargeable
l'implémentation de fonctions de somme de contrôle appropriées est également disponible pour SQLite, voir
"sqlite_checksum.*".

La somme de contrôle "ck" est basée sur le hachage Jenkins ,
qui repose sur de simples opérations d'addition, de décalage et de xor entier. La somme de contrôle "fnv" est
inspiré du hachage FNV (64 bits 1a
version) qui utilise des opérations d'entier xor et mult, bien que j'ai également ajouté un décalage
et ajouter pour aider à peaufiner les bits élevés.

3.
Une fonction d'agrégat est utilisée pour résumer les sommes de contrôle pour une plage de lignes. Il doit
opérer sur le résultat de la fonction de somme de contrôle. Il peut être modifié avec le
Option "--agrégat".

Des implémentations appropriées d'un agrégat exclusif ou "xor" sont disponibles pour PostgreSQL
et peut être chargé sur le serveur en traitant "share/contrib/xor_aggregate.sql".

Le fichier "sqlite_checksum.*" fournit également des agrégats "xor" et "sum" pour SQLite qui
sont compatibles avec d'autres bases de données.

De plus plusieurs modules perl sont utiles pour exécuter ce script :

· "Getopt::Long" pour la gestion des options.

· "DBI", "DBD::Pg" pour se connecter à PostgreSQL, "DBD::mysql" pour se connecter à MySQL, et
"DBD::SQLite" pour se connecter à SQLite.

· "Term::ReadPassword" pour l'option "--ask-pass".

· "Pod::Usage" pour l'auto-extraction de doc ("--man" "--opt" "--help").

· "threads" pour la version expérimentale filetée avec l'option "--threads".

· « Digest : : MD5 » pour la somme de contrôle md5 avec SQLite.

Les modules ne sont chargés par le script que s'ils sont réellement requis.

ALGORITHME

Le but de l'algorithme est de comparer le contenu de deux tables, éventuellement sur des
serveurs distants, avec un trafic réseau minimum. Elle se déroule en trois phases.

1.
Une table de somme de contrôle est calculée de chaque côté pour la table cible.

2.
Un premier tableau récapitulatif de niveau est calculé de chaque côté en agrégeant des morceaux du
tableau de somme de contrôle. D'autres niveaux d'agrégations récapitulatives sont ensuite effectués jusqu'à ce qu'il y ait
une seule ligne dans la dernière table, qui stocke ensuite une somme de contrôle globale pour l'ensemble
tables cibles initiales.

3.
À partir des tableaux récapitulatifs supérieurs, les sommes de contrôle agrégées sont comparées à partir des deux
côtés pour rechercher des différences, jusqu'à la table de somme de contrôle initiale. Clés de différent
les tuples sont affichés.

CHECKSUM TABLE
La première phase calcule la table de somme de contrôle initiale T(0) de chaque côté. En admettant que clé
est les colonnes clés de la table, et cols est les colonnes de données de la table qui doivent être vérifiées
différences, puis il est effectué en interrogeant la table cible T comme suit:

CREER LA TABLE T(0) COMME
SELECT clé AS pk, -- clé primaire
somme de contrôle (clé) AS kcs, -- somme de contrôle de clé
somme de contrôle (clé || cols) AS tcs -- somme de contrôle de tuple
DE t;

La clé initiale est conservée, car elle sera utilisée pour afficher des clés différentes à la fin. Les
rationnel pour le KCS colonne est de randomiser la distribution des valeurs-clés afin d'équilibrer
agrégats dans la phase suivante. La clé doit également apparaître dans la somme de contrôle, sinon le contenu
échangés entre deux clés ne seraient pas détectés dans certains cas.

RÉSUMÉ TABLES
Nous calculons maintenant un ensemble de tableaux récapitulatifs en cascade en regroupant f (facteur de pliage) sommes de contrôle
ensemble à chaque étape. Le regroupement est basé sur un masque sur le KCS colonne à prendre
avantage de la randomisation de la somme de contrôle. Partant de p = 0 nous bâtissons:

CRÉER TABLE T(p+1) COMME
SELECT kcs & mask(p+1) AS kcs, -- key checksum subset
XOR(tcs) AS tcs -- résumé de la somme de contrôle des tuples
DE T(p)
GROUP BY kcs & mask(p+1);

Le masque(p) est défini pour qu'il regroupe en moyenne f sommes de contrôle ensemble : masque(0)
= plafond2(taille); masque(p) = masque(p-1)/f; Cela conduit à une hiérarchie de tables, chacune étant
un petit résumé du précédent :

niveau 0
tableau de somme de contrôle, Taille lignes, c'est-à-dire autant de lignes que la table cible.

niveau 1
premier tableau récapitulatif, (taille/f) lignes.

niveau p
tableau récapitulatif intermédiaire, (taille/f**p) lignes.

niveau n-1
un avant dernier tableau récapitulatif, moins de f lignes.

niveau n
dernier tableau récapitulatif, le masque est 0, 1 ligne.

Il est important que les mêmes masques soient utilisés des deux côtés afin que les agrégations soient
le même, permettant de comparer les contenus correspondants des deux côtés.

Rechercher POUR DIFFÉRENCES
Après que toutes ces tables de support soient construites des deux côtés vient la recherche des différences.
Lors de la vérification du récapitulatif de la somme de contrôle des dernières tables (niveau n) avec une seule ligne, c'est
essentiellement une comparaison de la somme de contrôle de l'ensemble du contenu de la table. S'ils correspondent, alors
les deux tables sont égales, et nous avons terminé. Sinon, si ces sommes de contrôle diffèrent, certains
une enquête est nécessaire pour détecter les clés incriminées.

L'enquête est effectuée en descendant la hiérarchie des tables et en recherchant tous KCS
pour lequel il y avait une différence dans la somme de contrôle par rapport au niveau précédent. La même requête est
réalisé des deux côtés à chaque étape :

SÉLECTIONNER kcs, tcs
DE T(p)
O kcs & mask(p+1) IN (kcs-with-diff-checksums-from-level-p+1)
ORDER BY kcs [et au niveau 0 : , id] ;

Et les résultats des deux côtés sont fusionnés. Lors de la procédure de fusion, quatre
des cas peuvent survenir :

1.
Le KCS et votre tcs rencontre. Ensuite, il n'y a pas de différence.

2.
Bien que KCS correspond, tcs ne fait pas. Ensuite ceci KCS doit faire l'objet d'une enquête à la prochaine
niveau, car le résumé de la somme de contrôle diffère. Si nous sommes déjà au dernier niveau, alors le
la clé incriminée peut être affichée.

3.
Non KCS match, un supplémentaire KCS dans le premier côté. Ensuite ceci KCS correspondre à
clé(s) qui doivent être insérées pour synchroniser la deuxième table par rapport à la première.

4.
Non KCS match, un supplémentaire KCS dans le deuxième côté. Ensuite ceci KCS correspondre à
clé(s) qui doivent être supprimées pour synchroniser la deuxième table par rapport à la première.

Les cas 3 et 4 sont simplement symétriques, et ce n'est qu'une interprétation pour décider si
il s'agit d'une insertion ou d'une suppression, prenant le premier côté comme référence.

Analyse De Défaillance
Laisser nous n être le nombre de lignes, r la taille de la ligne, f le facteur de pliage, k le nombre de
différences à détecter, c la taille de la somme de contrôle en bits, puis les coûts d'identification
différences et le taux d'erreur est :

réseau le volume
est mieux que k*f*plafond(log(n)/log(f))*(c+log(n)). le contenu de k blocs de taille f
est transféré sur la profondeur de l'arbre, et chaque identifiant de bloc est de taille log (n) et votre
contient une somme de contrôle c. Il est indépendant de r, et tu veux k<<n. Le volume du
Les requêtes SQL concernent k*log(n)*plafond(log(n)/log(f)), comme la liste des non-correspondants
checksums k*log(n) peut être traîné sur la profondeur de l'arbre.

nombre of demandes (sur chacun côté, le algorithme is symétrique)
le minimum est 6+plafond(log(n)/log(f)) pour des tables égales, le maximum est 6+2*plafond(log(n)/log(f)).

disque I / O circulation
est d'environ n*r+n*ln(n)*(f/(f-1)).

non négatif probabilité
à partie des tableaux sont considérés comme égaux bien qu'ils soient différents. Avec un
fonction de somme de contrôle parfaite, c'est la probabilité d'une collision de somme de contrôle à tout moment
où ils sont calculés et auraient dû être différents : environ
k*plafond(log(n)/log(f))*2**-c. Pour une table d'un million de lignes, s'attendre à 1000 changements avec le
valeurs des paramètres d'algorithme par défaut, il s'agit de 2 ** 10 *3/2**64, c'est à peu près un
chance dans 2 ** 52 fusionner les exécutions.

Plus le facteur de pliage est bas f mieux c'est pour le volume du réseau, mais plus le
mieux pour le nombre de requêtes et d'E/S disque : le choix de f est un compromis.

Plus la taille de la somme de contrôle est faible c, le meilleur pour le volume du réseau, mais le pire pour le
probabilité faussement négative.

Si la bande passante disponible est raisonnable, la comparaison sera très probablement liée au processeur :
le temps est principalement consacré au calcul de la table de somme de contrôle initiale. Ainsi si vous êtes
prévoyez de vérifier les différences assez souvent, envisagez de maintenir une somme de contrôle de tuple avec
un déclencheur, et éventuellement une somme de contrôle de clé également, et invoquez avec "--tuple-checksum" et
soit "--key-checksum" ou "--use-key".

Implémentation QUESTIONS
L'implémentation de la somme de contrôle donne des entiers, qui sont de longueur constante et faciles à
manipuler ensuite.

Notre xor l'agrégat est un bon choix car il n'y a pas de problème de débordement, il faut
en compte tous les bits de l'entrée, et il peut facilement être défini sur n'importe quelle donnée binaire. Les
somme L'agrégat est également correct, mais il nécessite une sorte de type entier sous-jacent.

Les valeurs NULL doivent être prises en compte de manière appropriée.

Le facteur de pliage et tous les modules sont pris en puissance de deux afin d'utiliser un masque.

Il y a une gestion spéciale des gros morceaux de suppressions ou d'insertions qui est mise en œuvre
bien que non détaillé dans l'aperçu algorithmique et l'analyse de complexité.

Il y a des efforts pour construire une implémentation compatible PostgreSQL/MySQL du
algorithme, qui a ajouté des hacks pour gérer les conversions de type et d'autres choses.

Ce script est raisonnablement testé, mais en raison de sa nature de preuve de concept, il y a beaucoup de
options dont la combinaison ne peut pas toutes être testée.

REMARQUE
Si les tables à comparer sont dans la même base de données, une simple requête SQL peut extraire les
différences. Tables d'hypothèse T1 et votre T2 avec clé primaire id et contenus non nuls données,,
puis leurs différences, c'est ainsi T2 diffère de la référence T1, est résumé par le
requête suivante :

SELECT COALESCE(T1.id, T2.id) AS clé,
CAS QUAND T1.id EST NULL ALORS 'DELETE'
QUAND T2.id EST NULL ALORS 'INSERT'
AUTREMENT 'MISE À JOUR'
Opération FIN COMME
À PARTIR DU T1 JOINDRE LE T2 AU COMPLET EN UTILISANT (id)
O T1.id EST NULL -- SUPPRIMER
OU T2.id EST NULL -- INSÉRER
OU T1.data <> T2.data -- MISE À JOUR

Références
Un article a été présenté lors d'une conférence sur cet outil et son algorithme : Remote Comparaison
of Base de données Tables by Fabien Coelho, Dans Troisième Conférence internationale sur les progrès en
Bases de données, connaissances et applications de données (DBKDA), pp 23-28, St Marteen, Pays-Bas
Antilles, janvier 2011. ISBN : 978-1-61208-002-4. Copyright IARIA 2011. En ligne sur Think
Dérangehttp://www.thinkmind.org/index.php?view=article&articleid=dbkda_2011_2_10_30021>.

L'algorithme et le script ont été inspirés par Apprivoisement le Distribué Base de données Problème: A Témoignage client
Étude En utilisant MySQL by Giuseppe la magie in Sys Administrateur vol 13 num 8, août 2004, pp 29-40. Voir
Moines Perlhttp://www.perlmonks.org/index.pl?node_id=381053> pour plus de détails. Dans ce document,
trois algorithmes sont présentés. Le premier compare deux tables avec une somme de contrôle
technique. Le second trouve les différences UPDATE ou INSERT basées sur un niveau 2 (somme de contrôle
et résumé) hiérarchie des tableaux. L'algorithme est asymétrique, car différentes requêtes sont
effectué sur les deux tableaux à comparer. Il semble que le volume de trafic réseau soit en
k*(f+(n/f)+r), qu'il a une procédure de fusion probabiliste boguée, et qu'il fait
hypothèses sur la distribution des valeurs clés. Le troisième algorithme recherche DELETE
différences basées sur le comptage, avec l'hypothèse implicite qu'il n'y a que de tels
différences.

Contrairement à cette approche, notre algorithme entièrement symétrique implémente les trois tâches
à la fois, pour trouver UPDATE, DELETE et INSERT entre les deux tables. La somme de contrôle et
l'idée de niveau hiérarchique sommaire est réutilisée et généralisée de manière à réduire l'algorithmique
complexité.

Du point de vue de la mise en œuvre, le script est aussi paramétrique que possible avec de nombreux
options et fait peu d'hypothèses sur les structures, les types et les valeurs des tables.

Utilisez pg_comparator en ligne en utilisant les services onworks.net