🐘 PostgreSQL – Installation, SQL & Administration

Le SGBD Open-Source le plus avancé

PostgreSQL (souvent "Postgres") est un **SGBD Objet-Relationnel (SGBD-OR)**. Il est réputé pour sa robustesse, sa conformité stricte aux standards SQL, et son ensemble de fonctionnalités avancées.

Il est 100% open-source (licence PostgreSQL, similaire à BSD/MIT) et n'est contrôlé par aucune entreprise (contrairement à MySQL/MariaDB), mais par une communauté mondiale.

Caractéristiques Clés

• Conformité ACID : Garantie totale des propriétés (Atomicité, Cohérence, Isolation, Durabilité).
• Extensibilité : Vous pouvez créer vos propres types de données, fonctions, opérateurs et index.
• Types de données riches : Support natif pour JSONB (binaire), ARRAY (tableaux), UUID, types géospatiaux (PostGIS).
• Concurrence : Gérée via **MVCC** (Multi-Version Concurrency Control), permettant aux lecteurs de ne pas bloquer les scripteurs (et vice-versa).

Postgres vs. MySQL/MariaDB

Choix très courant, voici les différences philosophiques :

Analogie : MySQL/MariaDB est une voiture de sport rapide et simple. PostgreSQL est un "char d'assaut" : plus complexe, mais incroyablement robuste, fiable et capable de tout faire.

Option 1 : Installeur Windows (EDB)

L'installeur officiel pour Windows est fourni par EDB (EnterpriseDB).

1. Allez sur postgresql.org/download/windows/
2. Téléchargez l'installeur EDB.
3. Exécutez l'installeur.
4. Étape clé : Définissez un mot de passe pour le super-utilisateur postgres.
5. Laissez le port (5432).
6. Choisissez la "Locale" (ex: fr_FR).

L'installeur installe Postgres en tant que service Windows et ajoute psql à votre PATH.

Il installe aussi pgAdmin (un client GUI populaire).

(Dans cmd ou PowerShell)
psql -U postgres
Password for user postgres: [votre-mot-de-passe]

Option 2 : Docker (Recommandé pour le Dev)

La méthode la plus propre, la plus rapide et la plus isolée pour le développement.

# Lancer un conteneur Postgres
docker run -d \
  --name pg-local \
  -e POSTGRES_PASSWORD=mon_pass_secret \
  -p 5432:5432 \
  -v pg-data:/var/lib/postgresql/data \
  postgres:16-alpine

• -d : Détaché.
• -e POSTGRES_PASSWORD : Définit le mot de passe pour l'utilisateur postgres.
• -p 5432:5432 : Lie le port 5432 de votre PC au port 5432 du conteneur.
• -v pg-data... : Crée un volume Docker "pg-data" pour la persistance des données.
• postgres:16-alpine : Image officielle, version 16 (légère).

# Se connecter (depuis votre PC)
psql -h localhost -U postgres

Connexion psql

psql est le client interactif. Il est plus puissant que mysql car il possède des "méta-commandes" (commençant par \) qui ne sont pas du SQL.

# Syntaxe
psql -h [HOST] -U [USER] -p [PORT] -d [DATABASE]

# Connexion locale (compte 'postgres')
sudo -u postgres psql

# Connexion locale (votre compte) à une BDD
psql -U mon_user -d ideo_lab_crm

# Connexion distante
psql -h db.ideolab.com -U mon_user -d ideo_lab_prod

Le Prompt

Le prompt psql vous donne des informations :

ideo_lab_prod=#

(Connecté à 'ideo_lab_prod' en tant que super-utilisateur #)

ideo_lab_prod=>

(Connecté à 'ideo_lab_prod' en tant qu'utilisateur normal >)

ideo_lab_prod-(

(Dans une transaction BEGIN; non terminée)

Méta-commandes (Cheat-sheet)

C'est ce qui rend psql génial. (Pas besoin de ;)

Isolation

Dans Postgres, les BDD sont des conteneurs totalement isolés. Une connexion est liée à UNE seule BDD. Vous ne pouvez pas faire de JOIN entre BDD (contrairement à MySQL).

(Pour cela, on utilise les Schemas, voir 3.3).

Créer une BDD (SQL)

-- Syntaxe
CREATE DATABASE nom_db
    WITH
    OWNER = role_proprietaire
    ENCODING = 'UTF8'
    LC_COLLATE = 'fr_FR.UTF-8'
    LC_CTYPE = 'fr_FR.UTF-8'
    TEMPLATE = template0;

-- Exemple simple (suffisant 99% du temps)
CREATE DATABASE ideo_lab_crm OWNER ideo_app;

Créer une BDD (CLI)

(Exécuté depuis le shell bash, pas psql)

sudo -u postgres createdb -O ideo_app ideo_lab_crm

Lister & Gérer (psql)

postgres=# \l
-- (ou \list) Liste toutes les BDD

                               List of databases
       Name       |  Owner   | Encoding |   Collate   |    Ctype    | ...
------------------+----------+----------+-------------+-------------+-----
 ideo_lab_crm   | ideo_app | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8 |
 postgres         | postgres | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8 |
 template0        | postgres | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8 |
 template1        | postgres | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8 |

template0 et template1 sont les modèles utilisés par CREATE DATABASE. Ne pas y toucher.

Se connecter (psql)

postgres=# \c ideo_lab_crm
You are now connected to database "ideo_lab_crm" as user "postgres".
ideo_lab_crm=#

Supprimer une BDD

-- SQL
DROP DATABASE ideo_lab_crm;

-- CLI (bash)
sudo -u postgres dropdb ideo_lab_crm

Schema : Un "dossier" dans votre BDD

Si une BDD est un "Projet", un Schema est un "dossier" dans ce projet. Il permet d'organiser les tables logiquement.

C'est ce qui permet à Postgres de gérer des applications multi-tenant ou complexes dans une seule BDD.

BDD "ideo_lab_main"
├── Schema: public (Défaut)
│   ├── Table: utilisateurs
│   └── Table: entreprises
│
├── Schema: crm
│   ├── Table: clients (public.entreprises)
│   └── Table: contacts
│
└── Schema: compta
    ├── Table: factures
    └── Table: lignes_facture

Le schema public

Toute nouvelle BDD contient un schema public. Si vous faites CREATE TABLE users, Postgres crée en fait public.users.

search_path (Le "PATH" de Postgres)

Comment Postgres sait-il où chercher (public, crm...) quand vous écrivez SELECT * FROM clients ?

Il utilise le search_path.

-- Voir le search_path actuel
SHOW search_path;
-- Résultat: "$user", public

-- (Il cherche d'abord un schema à votre nom, puis 'public')

-- Changer le search_path pour la session
SET search_path TO crm, public;

-- Maintenant, 'SELECT * FROM clients' va chercher 'crm.clients'
-- 'SELECT * FROM utilisateurs' va chercher 'public.utilisateurs'

Utilisation

-- Créer un schema
CREATE SCHEMA crm;

-- Créer une table dans un schema
CREATE TABLE crm.contacts (
    id INT PRIMARY KEY,
    nom VARCHAR(100)
);

-- Lister les schemas (psql)
\dn

Contraintes de Colonne

CREATE TABLE utilisateurs (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    
    email TEXT NOT NULL
        CONSTRAINT email_unique UNIQUE, -- Contrainte nommée
        
    nom TEXT NOT NULL,
    
    date_naissance DATE
        CHECK (date_naissance > '1900-01-01'), -- Contrainte CHECK
        
    statut TEXT DEFAULT 'actif'
        CHECK (statut IN ('actif', 'inactif', 'archive')),
        
    cree_le TIMESTAMPTZ DEFAULT NOW()
);

Contraintes de Table (Clés Étrangères)

Définit la relation entre commandes et utilisateurs.

CREATE TABLE commandes (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    utilisateur_id INT NOT NULL,
    montant NUMERIC(10, 2) NOT NULL CHECK (montant > 0),
    
    -- Contrainte de Clé Étrangère
    CONSTRAINT fk_utilisateur
        FOREIGN KEY(utilisateur_id) 
        REFERENCES utilisateurs(id)
        ON DELETE RESTRICT -- (Quoi faire si l'utilisateur est supprimé)
);

Comportement ON DELETE

• ON DELETE RESTRICT (Défaut) : Refuse de supprimer un utilisateur s'il a encore des commandes. (Sûr).
• ON DELETE CASCADE : Si on supprime un utilisateur, supprime automatiquement toutes ses commandes. (Dangereux, mais utile).
• ON DELETE SET NULL : Si on supprime un utilisateur, met utilisateur_id à NULL dans ses commandes.

INSERT ... RETURNING

La clause RETURNING est une fonctionnalité très puissante de Postgres. Elle permet de retourner des valeurs (comme l'ID) juste après l'insertion.

-- (Table 'utilisateurs' de l'exemple 4.2)

INSERT INTO utilisateurs (nom, email)
VALUES ('Jean Dupont', 'jean.dupont@mail.com')
RETURNING id, cree_le;

-- Résultat (très utile pour les ORMs):
-- id | cree_le
-- ---|-------------------------------
-- 5  | 2025-11-01 22:30:00.12345+01

INSERT (JSONB & ARRAY)

INSERT INTO produits (nom, tags, specs)
VALUES (
    'Clavier Pro',
    ARRAY['mecanique', 'rgb', 'usb-c'],
    '{"poids": 500, "switch": "blue"}'::jsonb
);

UPDATE

(WHERE est obligatoire !)

UPDATE utilisateurs
SET
    nom = 'Jean Martin',
    statut = 'inactif'
WHERE email = 'jean.dupont@mail.com'
RETURNING id; -- (RETURNING marche aussi ici)

DELETE

(WHERE est obligatoire !)

DELETE FROM utilisateurs
WHERE statut = 'archive'
RETURNING email; -- (Retourne les emails supprimés)

TRUNCATE

-- Vider une table (rapide)
TRUNCATE TABLE logs;

-- Vider plusieurs tables (ex: dépendantes)
TRUNCATE TABLE utilisateurs, commandes RESTART IDENTITY CASCADE;
-- RESTART IDENTITY: Réinitialise les 'SERIAL'
-- CASCADE: Vide aussi les tables liées (commandes)

Garantie ACID

Postgres est transactionnel par défaut. Chaque requête SQL est exécutée dans sa propre transaction (auto-commit).

Pour des opérations complexes (ex: virement bancaire), vous devez les envelopper dans une transaction manuelle (BEGIN) pour garantir l'Atomicité (Tout ou Rien).

Scénario : Virement bancaire

-- Client 1 (Alice) a 100€
-- Client 2 (Bob) a 50€

-- Démarrer la transaction
BEGIN;

-- 1. Débiter Alice de 30€
UPDATE comptes SET solde = solde - 30 WHERE client_id = 1;

-- 2. Créditer Bob de 30€
UPDATE comptes SET solde = solde + 30 WHERE client_id = 2;

-- (Si le serveur crash ici, rien n'est sauvegardé
-- au redémarrage, car le COMMIT n'a pas eu lieu)

-- 3. Valider (Appliquer les 2 changements d'un coup)
COMMIT;

ROLLBACK (Annulation)

Si une erreur survient (ex: le solde d'Alice devient négatif), l'application doit annuler toute la transaction.

BEGIN;

-- 1. Débiter Alice (Solde: 100€) de 500€
UPDATE comptes SET solde = solde - 500 WHERE client_id = 1;
-- (Le solde est maintenant -400€)

-- (L'application (ou un CHECK) détecte l'erreur)

-- 2. Annuler toute la transaction
ROLLBACK;

-- (Le solde d'Alice revient à 100€, comme si
-- rien ne s'était passé.)

SAVEPOINT (Points de sauvegarde)

BEGIN;
INSERT INTO users (nom) VALUES ('A');
SAVEPOINT point_a;
INSERT INTO users (nom) VALUES ('B');
-- Annule juste B, garde A
ROLLBACK TO SAVEPOINT point_a;
COMMIT; -- (Valide A)

Fonctions (pl/pgsql)

Postgres permet d'écrire une logique complexe (procédures stockées) directement dans la BDD. Le langage par défaut est pl/pgsql (similaire au PL/SQL d'Oracle).

CREATE FUNCTION get_total_ca(date_debut DATE, date_fin DATE)
RETURNS NUMERIC AS $$
DECLARE
    total_ca NUMERIC := 0;
BEGIN
    SELECT SUM(montant)
    INTO total_ca
    FROM commandes
    WHERE date_commande BETWEEN date_debut AND date_fin;
    
    RETURN total_ca;
END;
$$ LANGUAGE plpgsql;

-- Utilisation:
SELECT get_total_ca('2025-01-01', '2025-01-31');

Triggers

Un Trigger exécute une Fonction automatiquement avant ou après un événement (INSERT, UPDATE, DELETE).

Ex: Mettre à jour modifie_le à chaque UPDATE.

-- 1. La Fonction (qui retourne un TRIGGER)
CREATE FUNCTION update_modifie_le_col()
RETURNS TRIGGER AS $$
BEGIN
    NEW.modifie_le = NOW(); -- 'NEW' est la ligne en cours d'insertion/maj
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 2. Le Trigger (lié à la table)
CREATE TRIGGER trg_update_modifie_le
BEFORE UPDATE ON utilisateurs -- 'BEFORE' ou 'AFTER'
FOR EACH ROW -- (Pour chaque ligne modifiée)
EXECUTE FUNCTION update_modifie_le_col();

Vues (VIEW)

Une Vue est une "requête SELECT stockée" qui agit comme une table virtuelle. Elle n'existe pas physiquement et est ré-exécutée à chaque appel.

Utile pour simplifier des requêtes complexes ou restreindre l'accès aux colonnes.

-- Vue qui joint utilisateurs et commandes
CREATE VIEW vue_commandes_clients AS
    SELECT
        u.email,
        u.nom,
        c.id AS commande_id,
        c.montant
    FROM utilisateurs u
    JOIN commandes c ON u.id = c.utilisateur_id;

-- Utilisation (comme une table)
SELECT * FROM vue_commandes_clients
WHERE montant > 100;

Vues Matérialisées (MATERIALIZED VIEW)

Une Vue Matérialisée **exécute** la requête et stocke le résultat **physiquement** sur le disque (un "snapshot").

Avantage : La lecture est instantanée (c'est une table).
Inconvénient : Les données ne sont pas à jour. Elles doivent être rafraîchies manuellement.

Idéal pour les rapports (BI) lourds qui n'ont pas besoin d'être en temps réel.

-- Créer la vue (coûteux)
CREATE MATERIALIZED VIEW mv_rapport_ca_par_jour AS
    SELECT
        date_commande::DATE AS jour,
        SUM(montant) AS ca_total,
        COUNT(id) AS nb_commandes
    FROM commandes
    GROUP BY jour;
    
-- Lire la vue (instantané)
SELECT * FROM mv_rapport_ca_par_jour;

-- Rafraîchir les données (coûteux)
REFRESH MATERIALIZED VIEW mv_rapport_ca_par_jour;

pg_dump (Backup Logique)

pg_dump est l'outil CLI pour sauvegarder **UNE** base de données. Il se connecte à la BDD (comme psql) et génère un fichier de dump. Il ne bloque pas les lectures/écritures (grâce à MVCC).

Option 1: Format SQL (Texte)

Génère un gros fichier .sql lisible. Simple, portable, mais lent à restaurer.

pg_dump -U [USER] -h [HOST] -W [NOM_DB] > backup.sql
# -W force la demande de mot de passe

Option 2: Format "Custom" (Binaire)

Recommandé. Génère un fichier binaire .dump, compressé, qui peut être restauré en parallèle (multi-thread) avec pg_restore.

pg_dump -U [USER] -h [HOST] -W -F c [NOM_DB] > backup.dump
# -F c = Format Custom

pg_dumpall (Roles & Global)

pg_dump ne sauvegarde **PAS** les Rôles (utilisateurs) ni les Tablespaces (objets globaux).

pg_dumpall sauvegarde **toutes** les BDD *ET* les objets globaux (roles, etc.) dans un seul fichier SQL.

# Sauvegarde globale (structure + rôles)
pg_dumpall -U postgres --globals-only > roles_et_globaux.sql

# Sauvegarde globale (tout)
pg_dumpall -U postgres > full_cluster_dump.sql

Stratégie de Backup

1. Faire un pg_dumpall --globals-only (une fois par jour).
2. Faire un pg_dump -F c sur chaque BDD applicative (toutes les heures).

Restaurer un Dump SQL (Texte)

On utilise psql pour "exécuter" le fichier SQL.

# 1. Créer la BDD vide
# (Le dump SQL ne contient pas CREATE DATABASE)
createdb -U postgres -O ideo_app ideo_lab_crm_restored

# 2. Importer le dump
psql -U postgres -d ideo_lab_crm_restored < backup.sql

Restaurer un Dump "Custom" (Binaire)

On doit utiliser pg_restore. C'est plus puissant et permet la restauration parallèle (-j).

# 1. Créer la BDD vide
createdb -U postgres ideo_lab_crm_restored

# 2. Importer avec pg_restore
# -d (database), -j (jobs/threads)
pg_restore -U postgres -d ideo_lab_crm_restored -j 4 backup.dump

Restauration (pg_dumpall)

Un dump de pg_dumpall (SQL) se restaure simplement avec psql. (Il contient les CREATE DATABASE et CREATE ROLE).

psql -U postgres -f full_cluster_dump.sql