Exercice 06 : Base de données managée avec Cloud SQL

🎯 Objectifs

À la fin de cet exercice, vous serez capable de :

✅ Créer une instance Cloud SQL PostgreSQL sans IP publique
✅ Utiliser Cloud SQL Auth Proxy pour la connexion sécurisée
✅ Gérer les bases de données et les utilisateurs
✅ Activer les sauvegardes automatiques et lire les métriques

Durée estimée : 40 minutes

Difficulté : ⭐⭐⭐☆☆ (Intermédiaire)

Prérequis : Notions PostgreSQL, gcloud CLI configurée

📖 Contexte

Cloud SQL est le service de base de données relationnelle managée de GCP (PostgreSQL, MySQL, SQL Server). Comme AWS RDS, GCP gère les patches, les sauvegardes, la haute disponibilité et la réplication. Cloud SQL est accessible de manière sécurisée via le Cloud SQL Auth Proxy, qui chiffre la connexion et gère l'authentification IAM - sans avoir à ouvrir de ports dans le pare-feu.

📋 Énoncé

Créez une instance Cloud SQL PostgreSQL, connectez-vous via le proxy et gérez les données.

🧭 Déroulement de l'exercice

Tâche 1 : Créer l'instance Cloud SQL

bash

# Activer les APIs nécessaires
gcloud services enable sqladmin.googleapis.com

PROJECT_ID=$(gcloud config get-value project)

# Créer l'instance PostgreSQL (db-f1-micro = Free Tier)
gcloud sql instances create postgres-exercice \
  --database-version=POSTGRES_16 \
  --tier=db-f1-micro \              # 1 vCPU, 614 Mo RAM
  --region=europe-west9 \
  --storage-type=SSD \
  --storage-size=10GB \
  --backup-start-time=02:00 \       # Sauvegarde quotidienne à 2h
  --enable-bin-log \                # Binlog pour Point-in-Time Recovery
  --no-assign-ip                    # Pas d'IP publique (Private IP seulement)

# Cette commande prend 3-5 minutes
# Suivre la progression
gcloud sql instances describe postgres-exercice --format="value(state)"
# → RUNNABLE quand prêt

# Voir les détails
gcloud sql instances list

Indice : --no-assign-ip est la meilleure pratique de sécurité. L'instance Cloud SQL n'a pas d'IP publique et n'est accessible que via le Cloud SQL Auth Proxy ou depuis le VPC privé. C'est très différent d'une VM EC2 sur laquelle vous ouvririez manuellement le port 5432.

Tâche 2 : Créer une base et un utilisateur

bash

# Créer la base de données
gcloud sql databases create appdb --instance=postgres-exercice

# Définir le mot de passe du superutilisateur postgres
gcloud sql users set-password postgres \
  --instance=postgres-exercice \
  --password="MotDePasseSecurise123!"

# Créer un utilisateur applicatif (moindre privilège)
gcloud sql users create appuser \
  --instance=postgres-exercice \
  --password="AppMotDePasse456!"

# Lister les bases et les utilisateurs
gcloud sql databases list --instance=postgres-exercice
gcloud sql users list --instance=postgres-exercice

Tâche 3 : Se connecter via Cloud SQL Auth Proxy

Le Cloud SQL Auth Proxy crée un tunnel sécurisé entre votre machine et l'instance Cloud SQL.

bash

# Télécharger le Cloud SQL Auth Proxy
# Sur Linux x86_64
curl -o cloud-sql-proxy https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy/v2.6.0/cloud-sql-proxy.linux.amd64
chmod +x cloud-sql-proxy

# Récupérer le "Connection name" de l'instance
CONN_NAME=$(gcloud sql instances describe postgres-exercice \
  --format="value(connectionName)")
echo "Connection name : $CONN_NAME"
# Format : PROJECT_ID:REGION:INSTANCE_NAME

# Lancer le proxy (dans un terminal séparé ou en arrière-plan)
./cloud-sql-proxy $CONN_NAME --port=5432 &
PROXY_PID=$!

# Se connecter avec psql (le proxy écoute sur localhost:5432)
psql "host=127.0.0.1 port=5432 dbname=appdb user=appuser password=AppMotDePasse456!" << 'SQL'
-- Créer une table de test
CREATE TABLE IF NOT EXISTS produits (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(100) NOT NULL,
    prix DECIMAL(10,2),
    stock INTEGER DEFAULT 0
);

-- Insérer des données
INSERT INTO produits (nom, prix, stock) VALUES
    ('Laptop DevOps', 999.99, 10),
    ('Clé USB 256Go', 29.99, 50),
    ('Câble HDMI', 9.99, 100);

-- Requête
SELECT nom, prix FROM produits ORDER BY prix DESC;

\q
SQL

# Arrêter le proxy
kill $PROXY_PID

Tâche 4 : Configurer les sauvegardes et les replicas

bash

# Vérifier la configuration de sauvegarde
gcloud sql instances describe postgres-exercice \
  --format="json(settings.backupConfiguration)"

# Créer un snapshot manuel
gcloud sql backups create \
  --instance=postgres-exercice \
  --description="Sauvegarde avant exercice"

# Lister les sauvegardes
gcloud sql backups list --instance=postgres-exercice

# Créer un replica de lecture (pour les requêtes analytiques)
gcloud sql instances create postgres-exercice-replica \
  --master-instance-name=postgres-exercice \
  --region=europe-west1 \  # Replica dans une autre région
  --tier=db-f1-micro

# Voir les instances (principale + replica)
gcloud sql instances list

Tâche 5 : Consulter les métriques et nettoyer

bash

# Voir les métriques récentes (via gcloud)
gcloud monitoring metrics list \
  --filter="metric.type:cloudsql" \
  --format="value(displayName)" | head -10

# Voir les connexions actives
gcloud sql operations list --instance=postgres-exercice

# Supprimer le replica puis l'instance principale
gcloud sql instances delete postgres-exercice-replica --quiet
gcloud sql instances delete postgres-exercice --quiet

✅ Vérification du résultat

gcloud sql instances list affiche l'instance en état RUNNABLE
La connexion psql via le proxy fonctionne sur localhost:5432
La table produits contient 3 lignes après les inserts
gcloud sql backups list montre au moins 1 sauvegarde

💡 À retenir

Cloud SQL vs Firebase vs Spanner :

Service	Type	Cas d'usage
Cloud SQL	Relationnel managé	Apps traditionnelles (PostgreSQL, MySQL)
Cloud Spanner	Relationnel distribué	Très haute dispo + échelle mondiale
Firestore	NoSQL document	Apps mobile/web, temps réel
Bigtable	NoSQL colonnes	Time series, IoT, analytics

Cloud SQL Auth Proxy vs connexion directe :

Auth Proxy : authentification IAM automatique, chiffrement TLS, pas de gestion des IPs
Connexion directe : nécessite une IP publique ou un VPC peering, gestion des certificats SSL manuelle

✨ Solution Complète

bash

# Connexion rapide via Cloud SQL Auth Proxy
CONN_NAME=$(gcloud sql instances describe mon-instance --format="value(connectionName)")
./cloud-sql-proxy $CONN_NAME &
psql "host=127.0.0.1 dbname=mabase user=monuser"

Exercice 06 : Base de données managée avec Cloud SQL

🎯 Objectifs

À la fin de cet exercice, vous serez capable de :

✅ Créer une instance Cloud SQL PostgreSQL sans IP publique
✅ Utiliser Cloud SQL Auth Proxy pour la connexion sécurisée
✅ Gérer les bases de données et les utilisateurs
✅ Activer les sauvegardes automatiques et lire les métriques

Durée estimée : 40 minutes

Difficulté : ⭐⭐⭐☆☆ (Intermédiaire)

Prérequis : Notions PostgreSQL, gcloud CLI configurée

📖 Contexte

📋 Énoncé

Créez une instance Cloud SQL PostgreSQL, connectez-vous via le proxy et gérez les données.

🧭 Déroulement de l'exercice

Tâche 1 : Créer l'instance Cloud SQL

bash

# Activer les APIs nécessaires
gcloud services enable sqladmin.googleapis.com

PROJECT_ID=$(gcloud config get-value project)

# Créer l'instance PostgreSQL (db-f1-micro = Free Tier)
gcloud sql instances create postgres-exercice \
  --database-version=POSTGRES_16 \
  --tier=db-f1-micro \              # 1 vCPU, 614 Mo RAM
  --region=europe-west9 \
  --storage-type=SSD \
  --storage-size=10GB \
  --backup-start-time=02:00 \       # Sauvegarde quotidienne à 2h
  --enable-bin-log \                # Binlog pour Point-in-Time Recovery
  --no-assign-ip                    # Pas d'IP publique (Private IP seulement)

# Cette commande prend 3-5 minutes
# Suivre la progression
gcloud sql instances describe postgres-exercice --format="value(state)"
# → RUNNABLE quand prêt

# Voir les détails
gcloud sql instances list

Indice : --no-assign-ip est la meilleure pratique de sécurité. L'instance Cloud SQL n'a pas d'IP publique et n'est accessible que via le Cloud SQL Auth Proxy ou depuis le VPC privé. C'est très différent d'une VM EC2 sur laquelle vous ouvririez manuellement le port 5432.

Tâche 2 : Créer une base et un utilisateur

bash

# Créer la base de données
gcloud sql databases create appdb --instance=postgres-exercice

# Définir le mot de passe du superutilisateur postgres
gcloud sql users set-password postgres \
  --instance=postgres-exercice \
  --password="MotDePasseSecurise123!"

# Créer un utilisateur applicatif (moindre privilège)
gcloud sql users create appuser \
  --instance=postgres-exercice \
  --password="AppMotDePasse456!"

# Lister les bases et les utilisateurs
gcloud sql databases list --instance=postgres-exercice
gcloud sql users list --instance=postgres-exercice

Tâche 3 : Se connecter via Cloud SQL Auth Proxy

Le Cloud SQL Auth Proxy crée un tunnel sécurisé entre votre machine et l'instance Cloud SQL.

bash

# Télécharger le Cloud SQL Auth Proxy
# Sur Linux x86_64
curl -o cloud-sql-proxy https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy/v2.6.0/cloud-sql-proxy.linux.amd64
chmod +x cloud-sql-proxy

# Récupérer le "Connection name" de l'instance
CONN_NAME=$(gcloud sql instances describe postgres-exercice \
  --format="value(connectionName)")
echo "Connection name : $CONN_NAME"
# Format : PROJECT_ID:REGION:INSTANCE_NAME

# Lancer le proxy (dans un terminal séparé ou en arrière-plan)
./cloud-sql-proxy $CONN_NAME --port=5432 &
PROXY_PID=$!

# Se connecter avec psql (le proxy écoute sur localhost:5432)
psql "host=127.0.0.1 port=5432 dbname=appdb user=appuser password=AppMotDePasse456!" << 'SQL'
-- Créer une table de test
CREATE TABLE IF NOT EXISTS produits (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(100) NOT NULL,
    prix DECIMAL(10,2),
    stock INTEGER DEFAULT 0
);

-- Insérer des données
INSERT INTO produits (nom, prix, stock) VALUES
    ('Laptop DevOps', 999.99, 10),
    ('Clé USB 256Go', 29.99, 50),
    ('Câble HDMI', 9.99, 100);

-- Requête
SELECT nom, prix FROM produits ORDER BY prix DESC;

\q
SQL

# Arrêter le proxy
kill $PROXY_PID

Tâche 4 : Configurer les sauvegardes et les replicas

bash

# Vérifier la configuration de sauvegarde
gcloud sql instances describe postgres-exercice \
  --format="json(settings.backupConfiguration)"

# Créer un snapshot manuel
gcloud sql backups create \
  --instance=postgres-exercice \
  --description="Sauvegarde avant exercice"

# Lister les sauvegardes
gcloud sql backups list --instance=postgres-exercice

# Créer un replica de lecture (pour les requêtes analytiques)
gcloud sql instances create postgres-exercice-replica \
  --master-instance-name=postgres-exercice \
  --region=europe-west1 \  # Replica dans une autre région
  --tier=db-f1-micro

# Voir les instances (principale + replica)
gcloud sql instances list

Tâche 5 : Consulter les métriques et nettoyer

bash

# Voir les métriques récentes (via gcloud)
gcloud monitoring metrics list \
  --filter="metric.type:cloudsql" \
  --format="value(displayName)" | head -10

# Voir les connexions actives
gcloud sql operations list --instance=postgres-exercice

# Supprimer le replica puis l'instance principale
gcloud sql instances delete postgres-exercice-replica --quiet
gcloud sql instances delete postgres-exercice --quiet

✅ Vérification du résultat

gcloud sql instances list affiche l'instance en état RUNNABLE
La connexion psql via le proxy fonctionne sur localhost:5432
La table produits contient 3 lignes après les inserts
gcloud sql backups list montre au moins 1 sauvegarde

💡 À retenir

Cloud SQL vs Firebase vs Spanner :

Service	Type	Cas d'usage
Cloud SQL	Relationnel managé	Apps traditionnelles (PostgreSQL, MySQL)
Cloud Spanner	Relationnel distribué	Très haute dispo + échelle mondiale
Firestore	NoSQL document	Apps mobile/web, temps réel
Bigtable	NoSQL colonnes	Time series, IoT, analytics

Cloud SQL Auth Proxy vs connexion directe :

Auth Proxy : authentification IAM automatique, chiffrement TLS, pas de gestion des IPs
Connexion directe : nécessite une IP publique ou un VPC peering, gestion des certificats SSL manuelle

✨ Solution Complète

bash

# Connexion rapide via Cloud SQL Auth Proxy
CONN_NAME=$(gcloud sql instances describe mon-instance --format="value(connectionName)")
./cloud-sql-proxy $CONN_NAME &
psql "host=127.0.0.1 dbname=mabase user=monuser"