S’il vous est déjà arrivé par le passé de faire du run avec deux bases différentes (une de production et une de qualification par exemple) qui ne sont pas mises à jour automatiquement par un outil de publication.
Comparer deux bases de données relève alors du domaine du parcours du combattant. Comment être sûr que tous nos objets de bases de données sont exactement identiques ?
Et s’ils ne le sont pas, comment facilement remettre d’équerre le schéma pour qu’il soit identique sur les deux environnements ?
Il existe une extension de Postgresql pour ça. Elle s’appelle Pyrseas. Cette extension permet de générer le schéma de votre base de données au format YAML et se charge lui-même de le comparer avec un autre et de générer des scripts de mise à jour pour naviguer d’un schéma à l’autre.
Pour prouver son efficacité, nous allons réaliser un test avec deux bases de données que nous allons comparer.
Première étape : Installer un PostgreSQL 14 sur notre machine
root:~#sudo apt update && sudo apt upgrade root:~#sudo apt -y install gnupg2 wget vim root:~#sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' root:~#wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - root:~#sudo apt -y update root:~#sudo apt -y install postgresql-14
Une fois que notre instance est installée et fonctionnelle, on va créer dessus deux bases de données. Le but ici est de générer deux bases de données quasiment identiques à l’exception d’une petite chose (par exemple un type de donnée différent entre les deux bases sur la même table).
On crée notre première base de données :
root@ip-172-44-2-57:~# su - postgres postgres@ip-172-44-2-57:~$ psql psql (14.6 (Ubuntu 14.6-1.pgdg22.04+1)) Type "help" for help.
postgres=# create database test; CREATE DATABASE postgres=# \c test You are now connected to database "test" as user "postgres". test=# create table client ( id integer primary key, name varchar(20), phone varchar(20)); CREATE TABLE
Puis la deuxième :
postgres=# create database test2; CREATE DATABASE postgres=# \c test2 You are now connected to database "test2" as user "postgres". test2=# create table client ( id integer primary key, name varchar(20), phone integer); CREATE TABLE
Deuxième partie : Installer notre extension
La documentation de l’extension est disponible à cette adresse : documentation
On commence par télécharger les sources :
root:~$ git clone https://github.com/perseas/Pyrseas.git Cloning into 'Pyrseas'... remote: Enumerating objects: 6590, done. remote: Counting objects: 100% (271/271), done. remote: Compressing objects: 100% (174/174), done. remote: Total 6590 (delta 140), reused 175 (delta 88), pack-reused 6319 Receiving objects: 100% (6590/6590), 3.10 MiB | 13.02 MiB/s, done. Resolving deltas: 100% (4707/4707), done.
On se positionne ensuite dans le dossier et on installe l’extension :
root:~$ cd Pyrseas root:~/Pyrseas# sudo python3 setup.py install ... Installed /usr/local/lib/python3.10/dist-packages/typing_extensions-4.4.0-py3.10.egg Searching for PyYAML==5.4.1 Best match: PyYAML 5.4.1 Adding PyYAML 5.4.1 to easy-install.pth file Using /usr/lib/python3/dist-packages Finished processing dependencies for Pyrseas==0.10.0
Troisième étape : Faire des tests !
Une fois que notre extension est installée, il ne nous reste plus qu’à l’utiliser.
Pour faire ceci, il faut appeler les différentes fonctions de l’extension.
On commence par sortir un fichier YAML de notre base test, pour cela on utilise la fonction dbtoyaml fournie par pyrseas :
postgres:/etc/Pyrseas/pyrseas$ python3 dbtoyaml.py test > $HOME/test.yaml postgres:/etc/Pyrseas/pyrseas$ cd $HOME postgres:~$ ls -l total 12 -rw-rw-r-- 1 postgres postgres 518 Dec 9 09:27 test.yaml postgres:~$ vi test.yaml
Voici le résultat obtenu quand on visualise le fichier yaml généré :
Une fois que nous avons notre fichier qui va servir de base de comparaison, on peut lancer le compare avec notre autre base de données, et lui demander de générer un fichier de différence. Je précise ici qu’il s’agit de générer un fichier de modification dans le sens schéma vers l’extraction. Notre extension génèrera donc un script pour que notre base corresponde à notre fichier yaml :
postgres:/etc/Pyrseas/pyrseas$ python3 yamltodb.py test2 $HOME/test.yaml ALTER TABLE public.client ALTER COLUMN phone TYPE character varying(20);
Les ordres SQL sont générés en brut dans la réponse de la commande, on peut si on veut les envoyer dans un fichier SQL ce qui nous permet de les relire, puis de les exécuter sur notre base de données quand on est sûr qu’ils font bien ce que l’on désire.
postgres@ip-172-44-2-57:/etc/Pyrseas/pyrseas$ python3 yamltodb.py test2 $HOME/test.yaml > $HOME/update.sql postgres:/etc/Pyrseas/pyrseas$ cd $HOME postgres:~$ vi update.sql
ALTER TABLE public.client ALTER COLUMN phone TYPE character varying(20); "update.sql" 3L, 78B
Pyrseas vient aussi avec une autre option, l’augmenter de base de données. Cette option sert à améliorer facilement votre base de données à l’aide d’une liste d’augmentation prédéfinie. Elles servent globalement à gagner du temps puisqu’elles mettent en place des mécanismes comme l’inscription automatique de date de mise à jour dans une colonne sans que vous n’ayez besoin d’écrire les triggers.
Conclusion :
Il y a beaucoup de situations dans la vie de quelqu’un qui se sert régulièrement d’une base de données dans laquelle on aimerait avoir un outil comme Pyrseas.
Comparer deux bases de données c’est fastidieux, encore plus quand ce sont des bases de données volumineuses avec de nombreux objets différents.
Le fait que cela soit vite fait, qu’il génère par lui-même un script d’évolution qu’on peut exécuter directement ou vérifier avant de le faire fonctionner est une feature pratique pour rattraper un écart de dll entre une production et un autre environnement sans avoir besoin de réimporter une sauvegarde.
Aussi pratique soit-il, cependant, il s’agit d’un projet réalisé par des utilisateurs du moteur, et il n’est pas forcément parfait.
Comme la majorité des extensions pour PostgreSQL, il continue cependant à être maintenu et à être corrigé au fil du temps.
Continuez votre lecture sur le blog :
- PostgreSQL Anonymizer (Sarah FAVEERE) [PostgreSQL]
- pg_recursively_delete : Simplifier les suppressions récursives (Sarah FAVEERE) [PostgreSQL]
- pg_dirtyread où comment réparer facilement un delete sauvage (Sarah FAVEERE) [PostgreSQL]
- PostgreSQL Benchmarking (Capdata team) [AWSAzureNon classéPostgreSQL]
- PostgreSQL : planifier une tâche avec pg_cron (Emmanuel RAMI) [Non classéPostgreSQL]