Configuration Multi-Database

Architecture Flexible Multi-SGBD

CSWeb Community Platform v2.0 introduit une architecture flexible multi-database permettant de choisir dynamiquement PostgreSQL, MySQL ou SQL Server pour le breakout, sans modifier le code.

Architecte & Développement :

• Lead Developer : Bouna DRAME
• Projet : CSWeb Community Platform v2.0
• Innovation : Support 3 SGBD avec changement à chaud (zero downtime)
• Drivers : pdo_pgsql, pdo_mysql, pdo_sqlsrv (tous installés)

Besoin d'expertise PostgreSQL/Multi-DB ? Consultez mon portfolio : KATS Analytics (Spring Boot + PostgreSQL), CSPro Webhooks, etc.

Table des Matières

Concept Clé : 2 Bases Distinctes

Base 1 : MySQL Métadonnées (FIXE)


 MySQL Metadata (OBLIGATOIRE - LOCAL) 

 Créé par : setup.php (installation) 
 Fichier config: src/AppBundle/config.php Port : 3306 
 Database : csweb Tables: 
 cspro_dictionaries cspro_users cspro_oauth_clients cspro_applications cspro_files 
 ... (métadonnées CSWeb) 

 NE JAMAIS MODIFIER

Responsabilité :

Synchronisation devices (tablets, smartphones)
Gestion utilisateurs et OAuth2
Métadonnées dictionnaires CSPro
Files upload/download

Base 2 : Breakout Database (CONFIGURABLE)


 PostgreSQL / MySQL / SQL Server 
 (CHOIX LIBRE - LOCAL ou REMOTE) 

 Configuré par: .env Service : BreakoutDatabaseConfig Type : BREAKOUT_DB_TYPE Tables (exemple EVAL_PRODUCTEURS): 
 eval_producteurs_cases eval_producteurs_notes eval_producteurs_producteurs (level 1) 
 eval_producteurs_observations (level 2) 
 eval_producteurs_photos (level 3) 
 ... (données analytics) 

 100% CONFIGURABLE

Responsabilité :

Données breakées (analytics)
Tables relationnelles structurées
Connexion Power BI / Tableau / Metabase
Queries SQL pour analyses

SGBD Supportés

1. PostgreSQL (Recommandé)

PostgreSQL - Meilleur Choix Analytics

Recommandé pour : Production, Analytics, Gros volumes (> 500k questionnaires)

Avantages

Performance : Excellent pour analytics
JSON natif : JSONB pour données semi-structurées
Window functions : Analyses complexes (OVER, PARTITION BY)
Full-text search : Recherche textuelle native
GIS : PostGIS pour données géographiques
Open source : Gratuit, aucune licence
ACID strict : Intégrité garantie

Inconvénients

Moins familier que MySQL
Courbe d'apprentissage (tuning)

BREAKOUT_DB_TYPE=postgresql
POSTGRES_HOST=localhost
POSTGRES_PORT=5432

Use Cases :

RGPH (Recensements nationaux > 3M questionnaires)
Enquêtes complexes avec analyses statistiques
Data Science / Machine Learning
Power BI / Tableau dashboards temps réel

2. MySQL

🟡 MySQL - Compatible & Familier

Recommandé pour : Compatibilité legacy, Infrastructure existante

Avantages

Familiarité : Très répandu, facile à trouver expertise
Performance : Bon pour OLTP (transactions)
Outils matures : phpMyAdmin, MySQL Workbench
Infrastructure : Souvent déjà en place
Compatible : CSWeb vanilla utilise MySQL

Inconvénients

Moins performant que PostgreSQL (analytics)
Pas de window functions (< MySQL 8.0)
JSON moins performant que JSONB PostgreSQL

BREAKOUT_DB_TYPE=mysql
MYSQL_BREAKOUT_HOST=localhost
MYSQL_BREAKOUT_PORT=3307

Use Cases :

Migration depuis CSWeb vanilla (déjà MySQL)
Infrastructure existante MySQL
Équipe familière avec MySQL uniquement
Volumes modérés (< 500k questionnaires)

3. SQL Server

SQL Server - Enterprise Microsoft

Recommandé pour : Écosystème Microsoft, Enterprise, RGPH gouvernementaux

Avantages

Enterprise : Robuste, haute disponibilité (Always On)
Microsoft Stack : Intégration parfaite (SSIS, SSRS, Azure)
Performance : Excellent sur serveurs de production
Outils : SQL Server Management Studio (SSMS)
Support : Microsoft support professionnel

Inconvénients

Coût : Licences payantes (sauf Express)
Complexité : Installation/configuration plus lourde
Windows : Historiquement Windows-only (Linux depuis 2017)

BREAKOUT_DB_TYPE=sqlserver
SQLSERVER_HOST=172.16.0.50
SQLSERVER_PORT=1433

Use Cases :

RGPH5 Sénégal (ANSD - SQL Server 2022 Enterprise)
Gouvernements avec infrastructure Microsoft
Projets avec budget enterprise
Azure cloud (SQL Database)

Tableau Comparatif

Critère	PostgreSQL	MySQL	SQL Server
Licence	🟢 Open Source (gratuit)	🟢 Open Source (gratuit)	Payant (Express gratuit)
Performance Analytics	🟢 Excellente	🟡 Bonne	🟢 Excellente
JSON Support	🟢 JSONB natif	🟡 JSON basique	🟡 JSON basique
Window Functions	🟢 Oui	🟡 MySQL 8.0+	🟢 Oui
Full-Text Search	🟢 Natif	🟡 Basique	🟢 Avancé
GIS	🟢 PostGIS	🟡 Spatial	🟢 Geography
Familiarité	🟡 Moyenne	🟢 Très haute	🟡 Moyenne
Outils Graphiques	🟢 pgAdmin, DBeaver	🟢 phpMyAdmin, Workbench	🟢 SSMS
Cloud	🟢 AWS RDS, Azure, GCP	🟢 AWS RDS, Azure, GCP	🟢 Azure SQL Database
Driver PHP	`pdo_pgsql`	`pdo_mysql`	`pdo_sqlsrv`
Port Défaut	5432	3306	1433
Recommandé Pour	Analytics, RGPH, ML	Legacy, Familiarité	Microsoft Stack, Enterprise

Quand Choisir Quel SGBD

Matrice de Décision


 CHOISIR LE BON SGBD Volume < 50k questionnaires MySQL (simple) 
 Volume 50k - 500k PostgreSQL Volume > 500k (RGPH) PostgreSQL / SQL Srv Budget limité PostgreSQL / MySQL Budget enterprise SQL Server Expertise équipe = MySQL MySQL Expertise équipe = Microsoft SQL Server Expertise équipe = Polyvalente PostgreSQL Analytics complexes PostgreSQL Transactions simples MySQL Cloud = Azure SQL Server Cloud = AWS / GCP PostgreSQL / MySQL Infrastructure existante = MySQL MySQL Infrastructure existante = SQL Srv SQL Server Nouvelle infrastructure PostgreSQL

Exemples Concrets

Cas 1 : Développeur Local (Test)

BREAKOUT_DB_TYPE=postgresql # Recommandé
Raison: Gratuit, performant, learn best practices

Cas 2 : INS avec Infrastructure MySQL Existante

BREAKOUT_DB_TYPE=mysql
Raison: DBA MySQL déjà en place, pas besoin de former

Cas 3 : RGPH National (3M+ questionnaires)

BREAKOUT_DB_TYPE=postgresql # ou sqlserver
Raison: Performance analytics, window functions, JSONB

Cas 4 : Gouvernement Microsoft-Only

BREAKOUT_DB_TYPE=sqlserver
Raison: Politique IT, licences déjà achetées, support Microsoft

Configuration

Variables .env par SGBD

#####################################
# POSTGRESQL (Recommandé)
#####################################
 
# Type de DB breakout
BREAKOUT_DB_TYPE=postgresql
 
# Connexion PostgreSQL
POSTGRES_HOST=localhost # ou IP remote: 10.0.1.100
POSTGRES_PORT=5432
POSTGRES_DATABASE=csweb_analytics
POSTGRES_USER=csweb_user
POSTGRES_PASSWORD=SecurePassword123!
 
# Optionnel: Schema PostgreSQL
POSTGRES_SCHEMA=public # ou nom custom

Services Docker démarrés :

# Mode LOCAL
docker compose --profile local-postgres up -d
 
# Mode REMOTE (ne démarre PAS postgres local)
docker compose up -d csweb mysql

Migration Entre SGBD

Scénario 1 : MySQL PostgreSQL

Raison : Améliorer performance analytics, utiliser JSON natif.

Étapes :

# 1. Export données MySQL
docker compose exec mysql-breakout mysqldump \
 -u breakout_user -p csweb_breakout > mysql_backup.sql
 
# 2. Installer pgloader (outil conversion)
sudo apt-get install pgloader
 
# 3. Convertir MySQL PostgreSQL
pgloader mysql://breakout_user:password@localhost/csweb_breakout \
 postgresql://csweb_user:password@localhost/csweb_analytics
 
# 4. Modifier .env
# AVANT
# BREAKOUT_DB_TYPE=mysql
 
# APRÈS
BREAKOUT_DB_TYPE=postgresql
POSTGRES_HOST=localhost
POSTGRES_DATABASE=csweb_analytics
 
# 5. Redémarrer services
docker compose down
docker compose --profile local-postgres up -d
 
# 6. Vérifier données
psql -h localhost -U csweb_user -d csweb_analytics
\dt
SELECT COUNT(*) FROM eval_producteurs_cases;

Scénario 2 : PostgreSQL SQL Server

Raison : Politique IT gouvernementale impose Microsoft.

Étapes :

# 1. Export PostgreSQL
docker compose exec postgres pg_dump \
 -U csweb_user -F c csweb_analytics > postgres_backup.dump
 
# 2. Conversion manuelle (ou outil tiers)
# Installer SQL Server Migration Assistant (SSMA)
# https://learn.microsoft.com/en-us/sql/ssma/
 
# 3. Créer DB sur SQL Server
sqlcmd -S localhost -U sa -P 'YourStrong!Passw0rd'
CREATE DATABASE CSWeb_Analytics;
GO
 
# 4. Importer via SSMA ou scripts T-SQL convertis
 
# 5. Modifier .env
BREAKOUT_DB_TYPE=sqlserver
SQLSERVER_HOST=localhost
SQLSERVER_DATABASE=CSWeb_Analytics
 
# 6. Redémarrer
docker compose down
docker compose --profile local-sqlserver up -d

Drivers PHP

Vérifier Drivers Disponibles

# Commande CSWeb intégrée
docker compose exec csweb php bin/console csweb:check-database-drivers
 
# Sortie attendue:
# pdo_mysql: Available (MySQL metadata + breakout)
# pdo_pgsql: Available (PostgreSQL breakout)
# pdo_sqlsrv: Available (SQL Server breakout)
 
# Commande PHP standard
docker compose exec csweb php -m | grep pdo
 
# Sortie:
# pdo_mysql
# pdo_pgsql
# pdo_sqlsrv

Installation Drivers (si manquants)

# PostgreSQL
sudo apt-get update
sudo apt-get install -y php-pgsql php-pdo
 
# MySQL
sudo apt-get install -y php-mysql php-pdo
 
# SQL Server (via PECL)
sudo apt-get install -y php-dev php-pear unixodbc-dev
sudo pecl install sqlsrv pdo_sqlsrv
 
# Activer extensions
echo "extension=sqlsrv.so" | sudo tee /etc/php/8.1/mods-available/sqlsrv.ini
echo "extension=pdo_sqlsrv.so" | sudo tee /etc/php/8.1/mods-available/pdo_sqlsrv.ini
sudo phpenmod sqlsrv pdo_sqlsrv
 
# Redémarrer Apache/PHP-FPM
sudo systemctl restart apache2

Troubleshooting

Erreur : Driver Not Found

Symptôme :

[PDOException] could not find driver

Solution :

# Vérifier driver installé
php -m | grep pdo_pgsql
 
# Si absent, installer
sudo apt-get install php-pgsql
sudo systemctl restart apache2
 
# Vérifier à nouveau
docker compose exec csweb php bin/console csweb:check-database-drivers

Erreur : Connection Refused (Remote)

Symptôme :

SQLSTATE[HY000] [2002] Connection refused

Solutions :

# 1. Vérifier serveur distant accessible
ping 10.0.1.100
telnet 10.0.1.100 5432
 
# 2. Vérifier firewall
sudo ufw allow from 10.0.1.10 to any port 5432
 
# 3. Vérifier pg_hba.conf (PostgreSQL)
sudo vim /etc/postgresql/16/main/pg_hba.conf
# Ajouter:
# host csweb_analytics csweb_user 10.0.1.10/32 md5
 
sudo systemctl restart postgresql

Conclusion

Récapitulatif Multi-Database

3 SGBD supportés : PostgreSQL, MySQL, SQL Server
Changement à chaud : Modifier .env sans code
Drivers inclus : pdo_pgsql, pdo_mysql, pdo_sqlsrv
Migration possible : MySQL PostgreSQL SQL Server
Recommandation : PostgreSQL pour analytics

Architecture par :

Bouna DRAME - Lead Developer CSWeb Community Platform

Prochaines Étapes :

Breakout Distant (Docker)PostgreSQL