DPE — Rafraîchissement des statistiques¶

Les bases de statistiques ne contiennent aucune donnée propre : elles lisent les bases applicatives à travers postgres_fdw, en passant par le read replica de l'instance source, et agrègent ces lectures distantes dans 16 vues matérialisées. Deux CronJobs par environnement : install monte la liaison et crée les vues, refresh les reconstruit.

Où se trouve quoi¶

Élément	Emplacement
Scripts et image	repo `stat-refresh`, https://gitlab.ademe.fr/ademe-group/dpe/stat-refresh (branche `develop`, livraison par tag)
Configuration CI	repo `ademe_ci`, dossier `stat-refresh/` — le projet pointe dessus par son `ci_config_path`, aucun fichier de CI n'est dans `stat-refresh`
Image produite	`rg.fr-par.scw.cloud/namespace-scw-dpe/stat-refresh`, tag `<branche>-<sha court>`
Image de base	`ci-tooling-images/postgresql-16` (Alpine + client PostgreSQL 16), repo `docker-base-images`
Chart Helm	`app-template-v2-chart`, workload de type `cronjob`
Values ArgoCD	repo `argocd-ademe-appofapps`, `argocd-apps/dpe/helm-values/stat-refresh/<env>.yaml`
Comptes PostgreSQL	Vault `ademe/apps/dpe/<env>` — `dpestat_owner_` (propriétaire des vues) et `db_dpestat_ro_` (lecture distante)
Instances des statistiques	`horsprod-dpestat` et `prod-dpestat`, gérées par Terraform (`main-infra`, `040.rdb/dpestat.tf` et `041.rdb-account-creation/dpe_rdb_accounts.tf` de chaque environnement)

Architecture¶

Chaque environnement a deux bases de statistiques, <env>-dpe_stat et <env>-dpe_audit_stat, portées par l'instance dpestat — distincte de l'instance applicative. Chacune monte un serveur postgres_fdw (replica_dpe, replica_audit) vers le read replica de l'instance applicative, importe le schéma distant en tables étrangères, et définit par-dessus ses vues matérialisées.

instance dpe (applicative)          instance dpestat
  <env>-dpe        ──┐
  <env>-audit      ──┤  read replica  ──FDW──►  <env>-dpe_stat        8 vues matérialisées
                     ┘                          <env>-dpe_audit_stat  8 vues matérialisées

Le choix du read replica isole la charge de lecture : un REFRESH complet lit l'intégralité des tables sources.

Une seule table reste locale à la base de statistiques, application_property : elle est exclue de l'import du schéma distant (IMPORT FOREIGN SCHEMA … EXCEPT) puis recréée côté statistiques, le refresh devant y écrire. Elle porte la clé DATE_MAJ_STATISTIQUES affichée par l'application.

Les deux CronJobs¶

L'image n'a pas d'ENTRYPOINT : le CronJob choisit son script par sa command.

CronJob	Commande	Quand
`install`	`/app/bin/install.sh`	à la création d'un environnement, et après toute rotation du compte de lecture
`refresh`	`/app/bin/refresh.sh`	planifié (hebdomadaire)

Les deux sont suspendus (suspended: true) et se déclenchent à la main. Les schedule sont renseignés parce que le chart les exige ; celui d'install (0 1 1 1 *) n'est qu'un placeholder.

install¶

Enchaîne trois étapes, dans le seul ordre possible — les vues lisent les tables distantes, les droits portent sur les vues :

setup-fdw.sh — éprouve la connexion distante, crée ou met à jour le serveur FDW, pose le USER MAPPING du propriétaire, importe le schéma distant au premier passage, recrée application_property
create-views.sh — crée les 16 vues matérialisées en CREATE … IF NOT EXISTS ; la création les peuple au passage, d'où une durée du même ordre qu'un refresh
grant-app.sh — GRANT SELECT et default privileges au compte applicatif, GRANT USAGE sur le serveur FDW, et son propre USER MAPPING

Une étape en échec interrompt la suite, pour ne pas masquer la cause derrière une seconde erreur. Les trois scripts restent appelables séparément.

Le compte propriétaire suffit : il crée lui-même l'extension postgres_fdw et en obtient USAGE. Aucun compte admin d'instance n'est nécessaire.

Rejouer install est sans effet notable — le serveur existe déjà, les vues sont en IF NOT EXISTS, les droits se reposent. C'est la manière de rattraper une rotation du compte de lecture, les deux USER MAPPING étant recréés à chaque passage.

refresh¶

Rejoue les REFRESH MATERIALIZED VIEW dans l'ordre des dépendances, puis pose la date de fraîcheur.

Chaîne DPE : infos, logement, tertiaire, socle, puis les 4 vues dérivées de socle (sorties_par_energie, logements_visites, ventilations, climatisations). Chaîne audit : infos, principal, sorties_par_energie, logements_visites, ventilations, climatisations, travaux_resumes, travaux.

Les deux jeux de vues sont disjoints, aucune n'est commune.

Paramétrage¶

Les variables sont dans les values du chart, sous apps.statrefresh.workload.variables.

Variable	Format	Rôle
`DB_HOST`	`dpestat-horsprod.rdb.ademe-cloud.internal`	instance portant les bases de statistiques
`REPLICA_HOST`	adresse IP	read replica de l'instance applicative
`DATABASE_PAIRS`	`<base>:<jeu>`	jeu = dossier de `sql/refresh/` (`dpe` ou `audit`)
`SETUP_CHAINS`	`<base cible>:<serveur>:<base source>:<table témoin>`	montage FDW
`APP_GRANTS`	`<base>:<serveur>:<compte>`	droits et mapping d'un compte lecteur

Les comptes viennent de Vault par l'ExternalSecret du chart : DB_USER/DB_PASSWORD pour le propriétaire des vues, RO_USER/RO_PASSWORD pour la lecture distante.

Le propriétaire est dpestat_app, unique par instance : en hors-prod, il possède les vues des 14 bases de statistiques, tous environnements confondus. Il est déclaré dans 041.rdb-account-creation/dpe_rdb_accounts.tf, avec permission = "all" sur les bases de statistiques de l'instance. Le cloisonnement par environnement porte sur RO_USER et sur les comptes applicatifs d'APP_GRANTS.

L'ExternalSecret du chart ne lit que ademe/apps/dpe/<env> : les identifiants du propriétaire y sont donc recopiés depuis common. La recopie diffère selon l'environnement :

	Hors-prod	Production
Source du propriétaire	`ademe/apps/dpe/common/dpestat-horsprod`	`ademe/apps/dpe/common/dpestat-prod`
`dpestat_owner_*` dans le chemin de l'environnement	recopiés à la main (`vault kv patch`)	écrits par Terraform (`vault_generic_secret.dpestat_production`)
`db_dpe_stat_`, `db_dpe_audit_stat_`, `db_dpestat_ro_*`	présents avant Terraform, lus par lui	générés par Terraform s'ils sont absents, jamais régénérés ensuite

Aucun mot de passe ne transite par la ligne de commande : celui du propriétaire passe par un PGPASSFILE temporaire en 600 supprimé par un trap, celui du compte de lecture est lu par \getenv depuis l'environnement. Les pods ont shareProcessNamespace: true — un argument de psql serait lisible depuis les autres conteneurs du pod, ce que ps montre, l'environnement d'un processus ne le montre pas.

Monter un environnement¶

Les deux bases et les comptes existent (Terraform, couches 002, 040 et 041), les secrets sont dans Vault
Poser les droits par défaut du compte de lecture sur les deux bases sources, avec le compte applicatif propriétaire des tables (voir Points d'attention)
Créer helm-values/stat-refresh/<env>.yaml — le bloc common est à copier tel quel depuis les values de batch du même environnement
Ajouter la source <env>-stat-refresh dans argocd-apps/dpe/<env>.yaml. L'application parente dpe peut tarder à relire le dépôt : la rafraîchir, puis <prefixe>-dpe, si la source n'apparaît pas
Déclencher install à la main (kubectl create job --from=cronjob/cronjob-dpe-statrefresh-install …), vérifier le log
Déclencher refresh à la main : install peuple les vues mais ne pose pas la date de fraîcheur, que l'application affiche

Qui lit les vues¶

L'application statistiques lit les bases de statistiques en préproduction et en production.

Environnement	Datasources de l'application `statistiques`
préproduction	`preprod-dpe_stat` / `preprod-dpe_audit_stat` sur `horsprod-dpestat`, comptes `preprod-dpe-stat` et `preprod-dpe-audit-stat`
production	`prod-dpe_stat` / `prod-dpe_audit_stat` sur `prod-dpestat`, comptes `prod-dpe-stat` et `prod-dpe-audit-stat` ; déploiement `dpe-statistiques` à 2 réplicas
6 autres hors-prod	`<env>-dpe` / `<env>-audit` sur l'instance applicative `dpe`, avec le compte applicatif

Les bases applicatives ne contiennent aucune relation mv_. Sur les 6 autres environnements hors-prod, les vues sont peuplées et à jour mais personne ne les lit.

Points d'attention¶

REPLICA_HOST doit être une adresse IP

C'est l'instance dpestat qui résout ce host, pas le pod : postgres_fdw ouvre la connexion depuis le serveur. Une instance RDB n'interroge ni le CoreDNS du cluster ni le DNS privé du VPC, les deux seuls endroits où le replica porte un nom. Elle résout en revanche le DNS public. L'IP est réservée par IPAM, elle ne change que si le replica est recréé.

Les vues sont inaccessibles pendant leur reconstruction

Un REFRESH sans CONCURRENTLY prend un AccessExclusiveLock. CONCURRENTLY exigerait un index unique sur chaque vue, qu'aucune ne porte aujourd'hui. REFRESH n'est pas incrémental : compter la durée d'une construction complète à chaque passage.

Une vue existante n'est jamais redéfinie

CREATE MATERIALIZED VIEW IF NOT EXISTS ignore silencieusement une vue déjà présente : modifier sa définition dans sql/schema/ et rejouer install ne change rien. Il faut la supprimer explicitement avant.

Le readonly Terraform ne couvre pas les tables créées ensuite

Un GRANT ne couvre que les objets existant au moment où il est posé. Toute table créée ou recréée sur la base source — migration applicative ou restauration — est illisible par le compte de lecture distante, et install comme refresh échouent en permission denied for table ….

Parade : des droits par défaut, posés par le compte applicatif lui-même, propriétaire des tables (prod-dpe/prod-audit en production, <env>-dpe/<env>-dpe-audit en hors-prod) :

GRANT SELECT ON ALL TABLES IN SCHEMA public TO "<env>-dpestat-ro";
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "<env>-dpestat-ro";

Posés sur les deux bases sources des 8 environnements. En préproduction, le postRestoreScripts de la restauration depuis la production les rejoue après chaque restauration. Lancé par un autre rôle que le propriétaire, le GRANT ne donne rien sans échouer : vérifier que le compte possède bien les tables.

Une base source recréée perd aussi le droit CONNECT

Après la recréation d'une base source, setup-fdw échoue en permission denied for database "<base>" — User does not have CONNECT privilege. Seul le readonly de Scaleway donne CONNECT ; le GRANT du compte applicatif ne peut pas le rendre.

Réparation, dans cet ordre :

terraform apply ciblé sur scaleway_rdb_privilege.dpestat_ro (041.rdb-account-creation) : le plan affiche permission = "custom" -> "readonly", l'apply repose le readonly complet. Il n'efface pas les droits par défaut posés en SQL
Rejouer le GRANT et les droits par défaut ci-dessus, avec le compte applicatif

L'API Scaleway affiche custom dès que les droits réels s'écartent de son modèle : un custom dans le plan de 041 signale des droits incomplets. Ne pas le masquer par un ignore_changes, qui empêcherait Terraform de réparer.

Arrêter un job : le suspendre d'abord

Le Job hérite d'un backoffLimit: 1 : annuler la requête en cours (pg_cancel_backend) fait échouer le pod, et Kubernetes en relance un second qui reprend install depuis le début. Suspendre le Job d'abord (kubectl patch job <job> --type=merge -p '{"spec":{"suspend":true}}'), puis annuler la requête côté instance — supprimer le pod seul ne l'arrête pas forcément.

hot_standby_feedback doit être actif sur l'instance source

Sans lui, le replica annule toute requête qui bloque la réplication plus de 30 s (canceling statement due to conflict with recovery) dès que le primaire écrit. Il est actif sur horsprod-dpe et prod-dpe (rdb_settings_hot_standby_feedback dans 002.config_applications). En contrepartie, le primaire retarde son VACUUM pendant les refresh.

Sans grant-app, tout paraît vert et l'application est pourtant en permission denied : le readonly de Scaleway ne crée aucun USER MAPPING, sans lequel toute lecture d'une table distante échoue.
Un USER MAPPING au mot de passe erroné se crée sans erreur — seule une lecture distante réelle le révèle. D'où la table témoin de SETUP_CHAINS et le contrôle de connexion en tête de setup-fdw.sh, mené sur un serveur jetable dans une transaction annulée pour ne pas casser un montage qui fonctionne.
La date de fraîcheur est écrite en UTC (NOW() AT TIME ZONE 'UTC'). L'application force sa JVM en UTC (DpeApplication.java, TimeZone.setDefault), lit la valeur comme de l'UTC puis la convertit en Europe/Paris : une valeur écrite en heure de Paris s'affiche avec deux heures d'avance. Contrôler une date par l'API (/pub/statistiques/etat, /pub/statistiques-audits/etat), pas en base.
La date de fraîcheur n'est posée qu'à la toute fin du script, donc uniquement si tous les REFRESH de la base ont réussi. Une base en échec garde sa date précédente pendant que l'autre avance.
Les scripts accumulent les échecs au lieu de s'arrêter au premier : une base en erreur n'empêche pas l'autre d'être traitée, et le code de sortie vaut 1 dès qu'une étape a échoué.
activeDeadlineSeconds est à 21600 (6 h) en hors-prod et 43200 (12 h) en production, contre 3600 par défaut dans le chart.

Durées mesurées¶

Les deux chaînes d'un environnement cumulées. Production relevée le 2026-10-13, hors-prod le 2026-10-08.

Environnement	`install`	`refresh`
production	—	40 min 17 (DPE 30 min 20, audit 9 min 57)
préproduction	—	2 h 24
intégration	4 min	3 min 56
receditprod	5 min 50	5 min 02
recedit	5 min 48	4 min 57
recette	1 s	3 min 58
recrefonte	4 min 13	3 min 52
recprod	4 min 05	3 min 48

Les écarts suivent le volume des bases sources : 325 Go en préproduction contre 2,5 Go sur les environnements de recette. L'install de production et de préproduction n'est pas mesuré ; celui de recette est à 1 s, les vues y préexistant.

Le facteur limitant mesuré est le transfert FDW, pas le CPU : pg_stat_activity reste en wait_event_type = Extension pendant le refresh. Mesures de production avec prod-dpe en db-pop2-4c-16g et prod-dpestat en db-pop2-2c-8g.