338 lines
10 KiB
Markdown
338 lines
10 KiB
Markdown
# 07 — Base de données PostgreSQL
|
||
|
||
Deux options selon votre palier et votre tolérance aux opérations.
|
||
|
||
---
|
||
|
||
## Option A — Neon.tech (recommandé pour MVP)
|
||
|
||
### Pourquoi Neon.tech
|
||
|
||
- PostgreSQL 15 managé, compatible TypeORM
|
||
- Extensions `uuid-ossp` et `pg_trgm` **disponibles** (requises par Xpeditis)
|
||
- Connection pooling intégré (PgBouncer) → critique pour NestJS multi-pods
|
||
- Backups automatiques + point-in-time recovery
|
||
- Free tier pour le développement
|
||
- **$19/mois** pour le plan Pro (production)
|
||
- Pas de gestion de HA, de réplication, ni de backups à faire
|
||
|
||
### Setup Neon.tech
|
||
|
||
1. Créez un compte sur https://neon.tech
|
||
2. "New Project" → Nom: `xpeditis-prod` → Region: `AWS eu-central-1 (Frankfurt)` (le plus proche de Hetzner FSN1)
|
||
3. Sélectionnez **Plan Pro** ($19/mois)
|
||
4. PostgreSQL version: **15**
|
||
|
||
### Créer la base de données
|
||
|
||
```bash
|
||
# Dans l'interface Neon → SQL Editor, ou via CLI neon
|
||
# Installer la CLI Neon
|
||
npm install -g neonctl
|
||
neonctl auth
|
||
|
||
# Créer les extensions requises par Xpeditis
|
||
neonctl sql --project-id <project_id> << 'EOF'
|
||
-- Extensions requises par Xpeditis
|
||
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
||
CREATE EXTENSION IF NOT EXISTS "pg_trgm";
|
||
|
||
-- Vérification
|
||
SELECT extname, extversion FROM pg_extension
|
||
WHERE extname IN ('uuid-ossp', 'pg_trgm');
|
||
EOF
|
||
```
|
||
|
||
### Connection string
|
||
|
||
Dans l'interface Neon → Connection Details → choisissez **Pooled connection** :
|
||
|
||
```bash
|
||
# Connection string avec pooling (via PgBouncer) — pour la prod
|
||
postgresql://xpeditis:<password>@ep-xxx-xxx.eu-central-1.aws.neon.tech/xpeditis?pgbouncer=true&connection_limit=1&sslmode=require
|
||
|
||
# Connection string directe — pour les migrations TypeORM
|
||
postgresql://xpeditis:<password>@ep-xxx-xxx.eu-central-1.aws.neon.tech/xpeditis?sslmode=require
|
||
```
|
||
|
||
> **Important :** TypeORM migrations doivent utiliser la **connexion directe** (sans pgbouncer). Pour le runtime NestJS, utilisez la **connexion poolée**.
|
||
|
||
### Configuration TypeORM pour Neon
|
||
|
||
L'app utilise des variables séparées pour l'hôte/port. Modifiez pour utiliser `DATABASE_URL` :
|
||
|
||
Vérifiez le fichier `apps/backend/src/app.module.ts`. Si TypeORM est configuré avec des variables séparées (`DATABASE_HOST`, `DATABASE_PORT`, etc.), vous avez deux options :
|
||
|
||
**Option 1 (recommandée) — URL complète :**
|
||
|
||
Dans le `app.module.ts`, TypeOrmModule accepte une `url` :
|
||
```typescript
|
||
TypeOrmModule.forRootAsync({
|
||
useFactory: (configService: ConfigService) => ({
|
||
type: 'postgres',
|
||
url: configService.get('DATABASE_URL'), // ← Utiliser si disponible
|
||
ssl: { rejectUnauthorized: false }, // ← Requis pour Neon
|
||
// ... reste de la config
|
||
}),
|
||
})
|
||
```
|
||
|
||
**Option 2 — Variables séparées (configuration actuelle) :**
|
||
|
||
Décomposez l'URL Neon en variables séparées dans le `.env` :
|
||
```bash
|
||
DATABASE_HOST=ep-xxx-xxx.eu-central-1.aws.neon.tech
|
||
DATABASE_PORT=5432
|
||
DATABASE_USER=xpeditis
|
||
DATABASE_PASSWORD=<neon_password>
|
||
DATABASE_NAME=xpeditis
|
||
DATABASE_SSL=true
|
||
```
|
||
|
||
Et ajoutez `ssl: { rejectUnauthorized: false }` dans la config TypeORM.
|
||
|
||
### Lancer les migrations
|
||
|
||
```bash
|
||
# Se placer dans le répertoire backend
|
||
cd apps/backend
|
||
|
||
# Copier l'env de prod
|
||
cp .env.example .env.production
|
||
|
||
# Éditer .env.production avec les vraies valeurs Neon
|
||
# DATABASE_HOST=ep-xxx-xxx.eu-central-1.aws.neon.tech
|
||
# DATABASE_USER=xpeditis
|
||
# DATABASE_PASSWORD=<password>
|
||
# DATABASE_NAME=xpeditis
|
||
# DATABASE_SSL=true
|
||
|
||
# Lancer les migrations (connexion directe, pas poolée)
|
||
NODE_ENV=production npm run migration:run
|
||
|
||
# Vérifier les migrations appliquées
|
||
NODE_ENV=production npm run typeorm query "SELECT version, name FROM typeorm_migrations ORDER BY id"
|
||
```
|
||
|
||
---
|
||
|
||
## Option B — PostgreSQL self-hosted sur Hetzner (1 000+ users)
|
||
|
||
### Architecture recommandée
|
||
|
||
```
|
||
CX22/CCX13 ─── PostgreSQL primary (lecture + écriture)
|
||
│
|
||
CCX13 ─── PostgreSQL replica (lecture seule + failover)
|
||
│
|
||
Volume Hetzner ─── /var/lib/postgresql/data (persistant)
|
||
```
|
||
|
||
### 1. Créer le serveur PostgreSQL dédié
|
||
|
||
```bash
|
||
# Créer un serveur CCX13 dédié pour PostgreSQL
|
||
hcloud server create \
|
||
--name xpeditis-postgres \
|
||
--type ccx13 \
|
||
--image ubuntu-24.04 \
|
||
--location fsn1 \
|
||
--ssh-key xpeditis-deploy \
|
||
--network xpeditis-network \
|
||
--firewall xpeditis-firewall
|
||
|
||
# Attacher le volume de données
|
||
hcloud volume attach xpeditis-postgres-data \
|
||
--server xpeditis-postgres \
|
||
--automount
|
||
|
||
# Récupérer l'IP privée
|
||
POSTGRES_PRIVATE_IP=$(hcloud server ip xpeditis-postgres --private-ip)
|
||
echo "PostgreSQL IP privée: $POSTGRES_PRIVATE_IP"
|
||
```
|
||
|
||
### 2. Installer et configurer PostgreSQL
|
||
|
||
```bash
|
||
# Se connecter au serveur
|
||
ssh -i ~/.ssh/xpeditis_hetzner root@<IP_PUBLIQUE>
|
||
|
||
# Installer PostgreSQL 15
|
||
apt-get update
|
||
apt-get install -y postgresql-15 postgresql-client-15
|
||
|
||
# Monter le volume de données
|
||
DEVICE_NAME=$(lsblk -o NAME,SERIAL | grep HC | head -1 | awk '{print $1}')
|
||
mkfs.ext4 /dev/$DEVICE_NAME
|
||
mkdir -p /mnt/postgres-data
|
||
mount /dev/$DEVICE_NAME /mnt/postgres-data
|
||
echo "/dev/$DEVICE_NAME /mnt/postgres-data ext4 defaults 0 2" >> /etc/fstab
|
||
|
||
# Déplacer les données PostgreSQL vers le volume
|
||
systemctl stop postgresql
|
||
rsync -av /var/lib/postgresql /mnt/postgres-data/
|
||
rm -rf /var/lib/postgresql/15/main
|
||
ln -s /mnt/postgres-data/postgresql/15/main /var/lib/postgresql/15/main
|
||
systemctl start postgresql
|
||
|
||
# Créer la base de données et l'utilisateur
|
||
sudo -u postgres psql << 'PGSQL'
|
||
CREATE USER xpeditis WITH PASSWORD '<MOT_DE_PASSE_FORT>';
|
||
CREATE DATABASE xpeditis_prod OWNER xpeditis;
|
||
GRANT ALL PRIVILEGES ON DATABASE xpeditis_prod TO xpeditis;
|
||
|
||
-- Connecter à la base
|
||
\c xpeditis_prod
|
||
|
||
-- Extensions requises
|
||
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
||
CREATE EXTENSION IF NOT EXISTS "pg_trgm";
|
||
|
||
-- Vérification
|
||
SELECT extname FROM pg_extension;
|
||
PGSQL
|
||
```
|
||
|
||
### 3. Configuration PostgreSQL pour la production
|
||
|
||
```bash
|
||
# Éditer /etc/postgresql/15/main/postgresql.conf
|
||
cat >> /etc/postgresql/15/main/postgresql.conf << 'EOF'
|
||
|
||
# Performance tuning (pour CCX13 : 4 vCPU dédiés, 8 GB RAM)
|
||
shared_buffers = 2GB # 25% de la RAM
|
||
effective_cache_size = 6GB # 75% de la RAM
|
||
maintenance_work_mem = 512MB
|
||
checkpoint_completion_target = 0.9
|
||
wal_buffers = 16MB
|
||
default_statistics_target = 100
|
||
random_page_cost = 1.1 # SSD NVMe
|
||
effective_io_concurrency = 200 # SSD
|
||
work_mem = 64MB
|
||
min_wal_size = 1GB
|
||
max_wal_size = 4GB
|
||
|
||
# Connexions
|
||
max_connections = 100
|
||
# Avec RDS Proxy / PgBouncer en front, 100 suffisent
|
||
|
||
# Logging
|
||
log_destination = 'stderr'
|
||
logging_collector = on
|
||
log_directory = '/var/log/postgresql'
|
||
log_filename = 'postgresql-%Y-%m-%d.log'
|
||
log_rotation_age = 1d
|
||
log_min_duration_statement = 1000 # Log les queries > 1s
|
||
log_checkpoints = on
|
||
log_connections = on
|
||
log_disconnections = on
|
||
log_lock_waits = on
|
||
|
||
# Réplication (pour replica future)
|
||
wal_level = replica
|
||
max_wal_senders = 3
|
||
max_replication_slots = 3
|
||
EOF
|
||
|
||
# Autoriser les connexions depuis le réseau privé Hetzner
|
||
cat >> /etc/postgresql/15/main/pg_hba.conf << 'EOF'
|
||
|
||
# Connexions depuis le réseau privé Hetzner (pods k3s)
|
||
host xpeditis_prod xpeditis 10.0.0.0/16 md5
|
||
EOF
|
||
|
||
# Écouter sur toutes les interfaces (nécessaire pour le réseau privé)
|
||
sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '10.0.0.0\/16,localhost'/" \
|
||
/etc/postgresql/15/main/postgresql.conf
|
||
|
||
systemctl restart postgresql
|
||
systemctl enable postgresql
|
||
|
||
# Test de connexion depuis le réseau privé
|
||
psql -h $POSTGRES_PRIVATE_IP -U xpeditis -d xpeditis_prod -c "SELECT version();"
|
||
```
|
||
|
||
### 4. Installer PgBouncer (connection pooling)
|
||
|
||
NestJS crée une connexion par pod. Sans pooler, 10 pods × 10 connexions = 100 connexions constantes. PgBouncer réduit ça drastiquement.
|
||
|
||
```bash
|
||
apt-get install -y pgbouncer
|
||
|
||
cat > /etc/pgbouncer/pgbouncer.ini << 'EOF'
|
||
[databases]
|
||
xpeditis_prod = host=localhost port=5432 dbname=xpeditis_prod
|
||
|
||
[pgbouncer]
|
||
listen_addr = 0.0.0.0
|
||
listen_port = 6432
|
||
auth_type = md5
|
||
auth_file = /etc/pgbouncer/userlist.txt
|
||
pool_mode = transaction # Mode le plus efficace pour NestJS
|
||
max_client_conn = 500 # Connexions clients max
|
||
default_pool_size = 20 # Connexions vers PostgreSQL par pool
|
||
reserve_pool_size = 5
|
||
server_reset_query = DISCARD ALL
|
||
log_connections = 1
|
||
log_disconnections = 1
|
||
logfile = /var/log/pgbouncer/pgbouncer.log
|
||
pidfile = /var/run/pgbouncer/pgbouncer.pid
|
||
EOF
|
||
|
||
# Créer le fichier d'authentification
|
||
echo '"xpeditis" "md5<hash>"' > /etc/pgbouncer/userlist.txt
|
||
# Pour générer le hash md5 :
|
||
echo -n "md5$(echo -n '<MOT_DE_PASSE>xpeditis' | md5sum | awk '{print $1}')"
|
||
|
||
systemctl enable pgbouncer
|
||
systemctl start pgbouncer
|
||
```
|
||
|
||
Avec PgBouncer, les pods NestJS se connectent sur le port `6432` :
|
||
```bash
|
||
# Variables d'environnement pour PgBouncer
|
||
DATABASE_HOST=<POSTGRES_PRIVATE_IP>
|
||
DATABASE_PORT=6432 # PgBouncer au lieu de 5432
|
||
```
|
||
|
||
### 5. Lancer les migrations TypeORM
|
||
|
||
```bash
|
||
# Depuis votre machine locale (ou depuis un pod de migration)
|
||
cd apps/backend
|
||
DATABASE_HOST=<POSTGRES_PRIVATE_IP> \
|
||
DATABASE_PORT=5432 \ # Direct PostgreSQL pour les migrations (pas PgBouncer)
|
||
DATABASE_USER=xpeditis \
|
||
DATABASE_PASSWORD=<password> \
|
||
DATABASE_NAME=xpeditis_prod \
|
||
npm run migration:run
|
||
|
||
# Vérifier
|
||
DATABASE_HOST=<POSTGRES_PRIVATE_IP> \
|
||
DATABASE_PORT=5432 \
|
||
DATABASE_USER=xpeditis \
|
||
DATABASE_PASSWORD=<password> \
|
||
DATABASE_NAME=xpeditis_prod \
|
||
npm run typeorm query "SELECT COUNT(*) as migrations FROM typeorm_migrations"
|
||
```
|
||
|
||
---
|
||
|
||
## Comparaison des options
|
||
|
||
| Critère | Neon.tech (Option A) | Self-hosted (Option B) |
|
||
|---|---|---|
|
||
| **Coût (1 000 users)** | $19/mois | ~€30/mois (CCX13 + volume) |
|
||
| **HA** | Automatique | Manuel (Patroni) |
|
||
| **Backups** | Automatique (7 jours PITR) | Script cron (doc 13) |
|
||
| **Extensions** | uuid-ossp + pg_trgm ✅ | Toutes |
|
||
| **Migrations** | Simple | Simple |
|
||
| **Ops requis** | Aucun | Maintenance mensuelle |
|
||
| **Scale** | Jusqu'à $69/mois (Pro) | Changement de serveur |
|
||
| **Limite connexions** | PgBouncer inclus | PgBouncer à installer |
|
||
|
||
**Recommandation :**
|
||
- < 500 users → **Neon.tech** (aucun ops, $19/mois)
|
||
- 500–5 000 users → **Self-hosted CCX23** (plus économique à ce niveau)
|
||
- > 5 000 users → **Self-hosted CCX33 + replica** (contrôle total)
|