Backup générique multi-DB : Postgres et ClickHouse

En transformant un backup Plausible codé en dur en script de mirror générique capable de dumper tous les conteneurs DB d’un serveur (pour un standby de failover), deux hypothèses tranquilles ont sauté, et un troisième détail de quoting m’a coûté dix minutes.

Piège 1 — pg_dumpall -U postgres n’est pas universel

Le réflexe : docker exec <pg> pg_dumpall -U postgres. Marche pour Plausible… échoue pour le conteneur postgis voisin :

pg_dumpall: error: ... FATAL: role "postgres" does not exist

Les images Postgres respectent POSTGRES_USER : si le déploiement l’a fixé (ici appuser), le rôle postgres n’existe pas. Le superuser n’est pas une constante, c’est une variable du conteneur. On la lit dans son environnement :

pguser=$(docker exec "$c" printenv POSTGRES_USER | tr -d '\r'); pguser="${pguser:-postgres}"
docker exec "$c" pg_dumpall -U "$pguser"

Le superuser Postgres d’un conteneur, c’est $POSTGRES_USER, pas « postgres ». Codé en dur, ton backup générique rate une base sur deux.

Piège 2 — ClickHouse n’a pas de dump-all

Pas d’équivalent pg_dumpall : on dumpe table par table en FORMAT Native. Coder en dur events_v2, sessions_v2, schema_migrations (les tables « connues » de Plausible) marche… jusqu’à ce qu’une migration ajoute sessions, location_data, les imported_*, etc. La liste codée en dur dérive en silence et tu backupes une vue partielle sans le savoir.

La parade : découvrir les tables de données à dumper via system.tables, en se limitant aux moteurs MergeTree (on exclut vues et dictionnaires, qui sont dérivés) :

SELECT database||'.'||name FROM system.tables
WHERE database NOT IN ('system','INFORMATION_SCHEMA','information_schema')
  AND engine LIKE '%MergeTree%'

La découverte a trouvé 15 tables là où le code en dur en sauvegardait 3.

Une liste de tables codée en dur est une bombe à retardement : la prochaine migration la périme sans erreur. Découvre ce qui existe, ne présume pas ce que tu connais.

Piège 3 — le quoting ssh → docker exec → clickhouse-client --query

Trois niveaux de shell imbriqués, et la requête SQL contient des apostrophes ('system') et des guillemets. Le --query "..." se fait broyer. Deux sorties propres :

• Guillemets échappés dans la commande passée à ssh : ssh host "docker exec $c clickhouse-client --query \"SELECT ... WHERE database NOT IN ('system')\"" — les apostrophes SQL survivent dans les guillemets externes.
• Astuce sans aucun guillemet dans le SQL, via char() : char(46) = ., char(115,121,115,116,101,109) = system. Illisible mais increvable à travers n’importe quelle couche de shell — pratique pour tester la chaîne avant de fignoler l’échappement.

Lessons learned

• Un script de backup « générique » doit interroger l’environnement, pas présumer des constantes. Le superuser Postgres ($POSTGRES_USER), les tables ClickHouse (system.tables), les volumes attachés : tout se découvre à l’exécution.
• ClickHouse se dumpe table par table en FORMAT Native ; filtre sur engine LIKE '%MergeTree%' pour ne sauvegarder que les données réelles, pas les vues/dictionnaires dérivés.
• Une liste codée en dur de tables/bases est une dérive silencieuse. Elle ne lèvera jamais d’erreur en oubliant les nouvelles tables — juste un restore incomplet le jour J. Découvre dynamiquement.
• Et journalise ce que tu ne sais PAS sauvegarder. Un script qui dumpe les DB connues mais ignore en silence un volume de fichiers (une base H2 database.mv.db, des uploads) donne un faux sentiment de complétude. Logue chaque conteneur/volume non couvert : le trou découvert vaut mieux que le trou ignoré.

#sysadmin #docker #backup #postgres #clickhouse #bash