Push data to PostGreSQL

[mattcln]

Nous voulons pousser certaines données dans PostGre pour des objectifs de performances et d'accessibilité des données.

Voici une liste non exhaustive des tables à créer :

1. Eco2mix, données raw :

• date_heure : object
• consommation : float
• eolien : float
• solaire : float

=> Il nous faut au moins l'année précédente jusqu'au 1er septembre, pour calculer les moyennes glissantes nécessaire à la prédiction des jours tempos

2. Prédiction de la consommation sur l'API RTE

• time : object
• consommation : float

3. Forecast flux solaire
   => Une colonne date / heure puis une colonne par département
   => Les prédictions sont sur 3/4 jours, donc prévoir une nouvelle colonne "delta_hours_pred" ou autre, pour donner l'information du nombre d'heure d'avance sur la prédiction. On aura plusieurs prédictions pour un même département / même heure

4. Forecast vent
   => Une colonne date / heure puis une colonne par département
   => Les prédictions sont sur 3/4 jours, donc prévoir une nouvelle colonne "delta_hours_pred" ou autre, pour donner l'information du nombre d'heure d'avance sur la prédiction. On aura plusieurs prédictions pour un même département / même heure

5. Forecast ENR

• time : object
• eolien : float
• solaire : float
  => Les prédictions sont sur 3/4 jours, donc prévoir une nouvelle colonne "delta_hours_pred" ou autre, pour donner l'information du nombre d'heure d'avance sur la prédiction. On aura plusieurs prédictions pour un même département / même heure

6. Température

• time : object
• temperature : float
  => Moyenne de la température quotidienne nationale, très simpliste donc pas forcément nécessaire ?

7. Données préparées pour la prédiction tempo

• consommation : float
• eolien : float
• solaire : float
• Type_de_jour_TEMPO : str
• temperature : float
• production_nette : float
• production_nette_q40 : float
• production_nette_q80 : float
• mean_temp_q30 : float

8. Prédiction tempo

• date : object
• Type_de_jour_TEMPO : str
• our_tempo_J-1 : str
• our_tempo_J-2 : str
• our_tempo_J-3 : str

[mattcln]

J'ai fait quelques tests en local pour voir comment mettre en place le postgres sur le VPS, avec l'aide de @guillaumepot :

• J'utilise un docker compose pour déployer postgres:17-alpine et pgadmin4
• Quelques opérations basiques (création d'une table, d'un superuser et d'un user read-only)
• Volume créé

J'ai poussé mon code sur le VPS mais j'ai quelques questions :

1. @antoinetavant Je vois qu'il y a déjà un conteneur postgres:16-alpine qui tourne sur le VPS, tu l'utilises pour quoi ?
2. Comment veut-on gérer les passwords ? Comment faire pour ne pas les écrire en dur, on les créé manuellement une fois et on se communique les passwords via un autre canal ?
3. J'ai un petit problème avec mon volume, j'arrive jamais à avoir la permission d'y accéder en local, je dois supprimer le volume pour relancer le docker-compose à chaque fois (pas très pratique pour un volume...)

[antoinetavant]

Trop bien !
Ça doit être outline ou un autre service du type. Il vaut mieux lancer un 2 ème conteneur pour avoir une deuxième base de données dédiée.

Pour les mots de passe tu peux utiliser un .env dans le VPS, ça devrait être suffisamment sécurisé.

Quel ORM utilises tu ? Parce que si on passe sur Django, je me demande si on doit nécessairement utiliser l'orm Django ou non.

[guillaumepot]

Secrets & .env

Pour les mots de passe, au delà du .env tu peux utiliser un docker secret (attention le fonctionnement n'est pas le même selon l'engine Docker (Docker ou Docker Swarm).

Dans le cas de Docker, le secret sera stockée dans un dossier qu'il faudra sécuriser (avec un chmod 400 par exemple en donnant les droits uniquement sur l'utilisateur qui lance les containers).

ORM

Par ORM tu entends le moyen de communication entre Django et Postgres ?

Django est basé sur le principe d'API (et je crois qu'il est sourcé sur Flask server), je ne suis pas sûr de saisir l'acronyme ORM mais s'il s'agit de moyen de connexion, le soutils généraux sont psycopg2 et asyncpg.

• psycopg2 sera plus simple à mettre en place mais ne supportera pas les connexions asynchrones
• asyncpg est légèrement plus complexe (fonctions async) mais rend l'interface plus rapide car elle ne bloquera pas sur une seule connexion Postgres en cas de mutli requêtage.

Volume

idem pas sûr d'avoir cerné où était le problème, c'est le container qui n'arrive pas à écrire dans le volume ?

[mattcln]

Merci pour vos commentaires !

Secrets

J'ai déployé les secrets via Docker Swarm. Il y a quatre mots de passe.
Pour le postgres directement :
POSTGRES_PASSWORD
POSTGRES_SUPER_USER_PASSWORD
POSTGRES_READONLY_USER_PASSWORD

Pour les accès à pgadmin :
PGADMIN_DEFAULT_PASSWORD

Je les ai noté en perso (conservé sur Dashlane), a voir comment on veut gérer ça pour la suite !

ORM

J'ai pas fais beaucoup de recherche dans ce sens. J'ai plus entendu parlé de psycopg2, et j'ai eu tendance à dire jusqu'à aujourd'hui que la quantité de données et nos utilisations ne nous poussait pas à chercher des optimisations de performances très poussées (async & co). Cependant, on pourrait le regretter d'ici quelques mois.

Volume

Je me plaignais de ne pas avoir accès à mon dir "volume", mais aucune importance !

Pour la suite

Le pgadmin tourne sur le VPS. J'ai quasiment jamais fait de réseau et je ne sais pas comment y accéder ni comment le déployer sur une adresse publique. @antoinetavant, j'imagine que tu as dû faire ça à plusieurs reprises pour les services que tu as poussé ?

N'hésitez pas à me demander les passwords si vous voulez tester bien sûr (en attendant qu'on décide d'un moyen pour se les partager).

[antoinetavant]

Trop bien !

J'ai jamais utilisé les secrets de docker swarm c'est cool !

Pour le rendre public il "suffit" d'ouvrir le port fourni par le docker. Ça doit être 54321 par défaut, mais comme j'ai déjà une image docker suis tourné tu l'as peut-être changé. Après ça veut dire que le service doit être super sécurisé, sinon c'est la porte ouverte pour n'importe qui.

J'ai installé fail2ban et j'utilise un part feu qui bloque tous les ports sauf les basiques (ssh, http et https).

Pour un peu plus de sécurité (mais pas tellement plus), j'utilise Nginx pour faire un revers proxy. C'est pas très compliqué d'ajouter un nouveau service, je peux essayer de le faire sous peu. Mais il me faut un nom de domaine, tu en as un en tête ?

[antoinetavant]

Concernant l'ORM, c'est pas une question de performance, mais d'utilisabilité.

Psycopg oblige à écrire les requêtes SQL sous forme de strings, qui est un peu embêtant. (btw maintenant psycopg3 est recommandé.

Je vous invite à aller voir sqlalchemy par exemple pour plus de détails, mais l'idée est d'écrire du code python, et que l'ORM transcrit le python en SQL a votre place.

Il faut faire le choix assez tôt dans le projet, car c'est une dette technique qui sera difficile d'adresser plus tard.

Django propose son propre ORM, c'est une feature assez centrale de Django et je ne sais pas si on peut faire sans.
Flask ne propose pas d'ORM, mais s'intègre bien avec sqlachemy, ou bien directement avec une base de donnée.

[mattcln]

J'ai pas touché au port par défaut, je découvre aussi Swarm !

On peut aussi en discuter lundi midi avec Hugo par rapport à Django alors ?

[guillaumepot]

Tu peux modifier le port exposé de la machine hôte qui fait tunnel avec ton container.
Ensuite effectivement installer un certificat TLS est de rigueur pour sécuriser la connexion. Si il y a déjà un reserve proxy NGINX qui tourne, ça va aller très vite à faire;