Je vais balancer l’info de suite, c’est l’application de supervision Zabbix qui m’a posé ce problème. Après une mise à jour, le sacripant m’avait dupliqué une grande partie des données en base avec tout plein de problèmes de configurations, l’arrêt de la mise à jour des instances Zabbix Proxy du réseau, et toutes les conséquences qu’on peut imaginer. Et en effet, les tables de Zabbix n’ont pour la plupart pas de contraintes UNIQUE sur la colonne ID. Plus étonnant encore, les développeurs semblent ne pas s’en inquiéter après avoir relevé l’erreur, mais bon, j’ai fait ma part du boulot ;-)

Le script

Venons-en au fait, pour regler la situation, j’ai concocté ce petit script depuis diverses sources, et comme il m’a été difficile de trouver la solution, je vous le livre ici tout fait.

Voici le code SQL générique pour supprimer les lignes en double d’une table donnée :

--duplicate.sql

with d as
  ( select ctid, row_number() over (partition by t.*) as rn
    from <table> as t
  )
delete from <table> as t
using d
where d.rn > 1
  and d.ctid = t.ctid ;

Ne pas oublier d’adapter les deux références à <table> avec votre nom de table.

L’automatisation

Dans mon cas, j’avais une cinquantaine de tables à gérer, il n’a pas été long ni compliqué d’automatiser ces traitement à partir d’une liste. Pour ce faire:

• modifier le script ci dessus en remplaçant les références à <table par :tab (par exemple). Le sauver sous un fichier duplicate.sql
• extraire une liste des tables et la placer dans la liste en bash ci-dessous
• Mettre à jour les paramètres du script et l’exécuter

#!/bin/bash
ROLE="${ROLE:-postgres}"
DB="${DB:-zabbix}"
TABLES="table1
table2"

for t in $TABLES; do
  sudo -u $ROLE psql $DB -v tab=""$t"" -f duplicate.sql
done

Pour rendre son utilisation plus robuste, j’ai utilisé l’expansion de variable ${foo:-bar} ou $foo prend la valeur bar si elle est vide, ce qui permet d’appeler le script avec des variables d’environnement pré-remplies plutôt que de modifier celui-ci.

Enfin, l’argument -v tab= permet de spécifier un paramètre pouvant être retrouvé dans le fichier en argument, ici duplicate.sql.

En espérant que ce script sera utile à certains d’entre vous !