Cours PostgreSQL — architecture, configuration, sauvegarde, réplication et tuning

Objectifs d'apprentissage

À la fin de ce cours, l'apprenant peut :

1. décrire l'architecture d'une instance PostgreSQL (postmaster, backends, processus auxiliaires, mémoire partagée, WAL) ;
2. choisir les paramètres clés (shared_buffers, work_mem, maintenance_work_mem) en fonction du matériel ;
3. lire et interpréter pg_hba.conf (authentification, ordre de matching) ;
4. créer et gérer des rôles avec privilèges et schémas ;
5. utiliser VACUUM, ANALYZE, autovacuum ;
6. lire un plan de requête (EXPLAIN, EXPLAIN ANALYZE) ;
7. mettre en place une sauvegarde cohérente (pg_dump, pg_basebackup, archivage WAL) ;
8. restaurer une base depuis une sauvegarde (pg_restore, PITR) ;
9. évaluer une stratégie de réplication (streaming, async vs sync) ;
10. opérer des commandes psql courants et lire les méta-commandes.

Prérequis

• Cours SQL et Bases de données (concepts tables, clés, indexes).
• Notions d'administration Linux (droits fichiers, services, variables env).
• Terminal et fichiers de config texte.
• PostgreSQL pas nécessairement installé (lecture et examen de la config suffisent pour débuter).

Parcours et chapitres

1. Architecture d'une instance PostgreSQL

Une instance = un cluster (au sens PostgreSQL : ensemble de bases, rôles, ressources) lancé par un processus parent postmaster.

Composants :

Postmaster : processus superviseur, écoute un port TCP (défaut 5432), crée un backend par session cliente.

Backend : processus travailleur, gère une session SQL.

Shared memory : tampons communs à tous les backends (shared buffers, tampons WAL, méta).

Processus auxiliaires : - Checkpointer : force l'écriture des pages sales sur disque à intervalles réguliers (checkpoint). - Background writer : écrit régulièrement des pages sales pour réduire les pics I/O. - WAL writer : envoie les enregistrements WAL du tampon au disque. - Autovacuum launcher/workers : détectent et lancent VACUUM/ANALYZE sur les tables.

WAL (Write-Ahead Log) : journal des modifications, écrit avant les données. Essentiel pour la récupération après crash et les sauvegardes cohérentes.

PGDATA : répertoire de base du cluster (/var/lib/postgresql/data typiquement).

2. Configuration et démarrage

Fichier clé : postgresql.conf (dans PGDATA).

Paramètres critiques :

# Connexion
port = 5432
listen_addresses = 'localhost'

# Mémoire partagée
shared_buffers = 256MB           # 25% RAM pour serveur dédié
effective_cache_size = 1GB        # RAM totale accessible

# Travail par session
work_mem = 4MB                    # **par opération de tri/hash**
maintenance_work_mem = 64MB       # VACUUM, CREATE INDEX

# WAL et performance
wal_level = replica               # minimum pour sauvegarde/réplication
checkpoint_timeout = 15min
max_wal_size = 1GB

# Autovacuum
autovacuum = on                   # **toujours on en production**
autovacuum_naptime = 10s
autovacuum_max_workers = 3

# Logging
log_min_duration_statement = 1000  # log requêtes > 1s (ms)
log_connections = on
log_disconnections = on

Redémarrage après modification :

Attention, effet notable. Cette commande peut s'exécuter avec des droits élevés. Lire le contexte, les droits et l’effet attendu indiqués après le bloc ; ne jamais l’exécuter sur un système réel sans validation, d’abord dans un environnement de test.

sudo systemctl restart postgresql

Ou rechargement à chaud (parameters non SUPER USER ONLY) :

ALTER SYSTEM SET work_mem = '8MB';
SELECT pg_reload_conf();

Connecté au cluster, reload appliqué aux futures sessions (pas aux actuelles).

3. Authentification et rôles (pg_hba.conf)

Fichier : pg_hba.conf (même répertoire que postgresql.conf).

Format : une ligne = une règle. Première correspondance gagne.

# Connexions locales (Unix socket)
local   all             all                                     trust

# TCP/IP localhost - authentification par clé publique (scram-sha-256)
host    postgres        postgres        127.0.0.1/32            scram-sha-256

# Réseau privé
host    all             app             192.0.2.0/24            scram-sha-256

# Refuser explicitement
host    all             all             203.0.113.5/32          reject

Champs : 1. Type : local (Unix socket), host (TCP), hostssl (TCP+SSL), hostnossl (TCP sans SSL). 2. Database : all, postgres, template1, ou nom spécifique. 3. User : all, nom rôle, ou liste user1,user2. 4. Address (TCP) : CIDR, ex. 192.0.2.0/24. 5. Method : trust (aucune auth, à éviter sauf dev), reject, scram-sha-256 (standard), md5 (deprecated), password (insecure), peer (local Unix socket, match OS user), cert (SSL client cert).

Rechargement :

SELECT pg_reload_conf();

4. Rôles et privilèges

Rôle = utilisateur ou groupe (concept unifié depuis PG 8.1).

Création :

CREATE ROLE app_user LOGIN PASSWORD 'mot-de-passe-fort';
CREATE ROLE app_group NOLOGIN;
GRANT app_group TO app_user;

• LOGIN : peut se connecter ; sans = groupe.
• SUPERUSER, CREATEDB, CREATEROLE : attributs puissants, éviter.
• app_user est maintenant membre de app_group.

Privilèges par objet :

GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.mytable TO app_user;
GRANT EXECUTE ON FUNCTION public.myfunction() TO app_user;
REVOKE ALL ON public.mytable FROM PUBLIC;  -- retirer les droits par défaut

Schémas :

Conteneurs logiques de tables, fonctions, etc. Défaut : public (accessible à tous par défaut en PG < 15).

CREATE SCHEMA myapp;
CREATE TABLE myapp.users (id INT PRIMARY KEY, name TEXT);
GRANT USAGE ON SCHEMA myapp TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA myapp TO app_user;

PG 15+ : CREATE sur public retiré de PUBLIC par défaut (sécurité). Les utilisateurs normaux ne peuvent plus créer dans public ; recommandation : créer un schéma par utilisateur.

5. Maintenance : VACUUM et ANALYZE

VACUUM : nettoie les versions mortes (MVCC) d'une table, récupère de l'espace.

VACUUM my_table;
ANALYZE my_table;
VACUUM ANALYZE my_table;  -- les deux

• VACUUM : marque l'espace libre comme réutilisable (rapide, compatible avec autres opérations).
• VACUUM FULL : réécrit la table (lent, verrou exclusif, à éviter).
• ANALYZE : met à jour les statistiques (utilisées par le planificateur).

Wraparound XID : après ~4 milliards de transactions, l'identifiant boucle → perte de données. PostgreSQL freeze les lignes anciennes et refuse les opérations si près du limite. VACUUM obligatoire avant le limite.

SELECT datname, age(datfrozenxid) FROM pg_database;

Affiche l'âge du XID le plus ancien en chaque base (doit rester < 50 millions pour sécurité).

Autovacuum : daemon qui lance VACUUM/ANALYZE automatiquement.

SHOW autovacuum;          -- on/off
SHOW autovacuum_naptime;  -- intervalle de vérification (default 10s)

6. Index et performance (EXPLAIN)

Types d'index : - B-Tree (défaut) : toutes les comparaisons (<, =, >, BETWEEN, IN, LIKE avec prefix). - Hash : égalité seulement (=). - GiST : géométrie, recherche proximité. - GIN : données multi-composantes (arrays, JSON full-text). - BRIN : très compact pour colonnes corrélées à l'ordre des lignes.

CREATE INDEX idx_nom ON ma_table(nom);
CREATE INDEX idx_geo ON geo_table USING GIN (location_data);

Lire un plan d'exécution :

EXPLAIN SELECT * FROM clients WHERE ville = 'Ville-A';

Sortie (exemple) :

                         QUERY PLAN                          ------------------------------------------------------------- Seq Scan on clients  (cost=0.00..35.00 rows=1000 width=244)   Filter: (ville = 'Ville-A')

• cost=0.00..35.00 : coût estimé de démarrage (0) à fin (35).
• rows=1000 : lignes estimées (peut être faux si stats obsolètes).
• Seq Scan : lecture séquentielle (lent pour grandes tables).

Avec exécution réelle :

EXPLAIN ANALYZE SELECT * FROM clients WHERE ville = 'Ville-A';

Affiche aussi actual time=..., rows=... (réel vs estimé).

Avertissement : EXPLAIN ANALYZE exécute réellement la requête. Pour INSERT/UPDATE/DELETE, encadrer par transaction + ROLLBACK :

BEGIN;
EXPLAIN ANALYZE INSERT INTO audit (msg) VALUES ('test');
ROLLBACK;  -- annule l'insertion réelle

7. Transactions et niveaux d'isolation

Defaut : READ COMMITTED (voir les données commises).

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
  SELECT COUNT(*) FROM clients;  -- snapshot pris **ici**
  -- autres SELECT voient le même snapshot
  -- INSERT/UPDATE/DELETE d'autres sessions invisibles
COMMIT;

Niveaux (du plus faible au plus strict) : 1. Read Uncommitted : traité comme Read Committed (PG ne le distingue pas). 2. Read Committed (défaut) : voit données commises (mais peut voir des anomalies de réplication). 3. Repeatable Read : snapshot au début de la transaction (évite les phantom reads en PG). 4. Serializable : strictement sériel (peut nécessiter des retrys sur conflits, SQLSTATE 40001).

Erreur de sérialisation : si deux transactions en Repeatable Read/Serializable se disputent, PostgreSQL refuse l'une d'elles.

-- Application doit retry
BEGIN;
  -- opérations
COMMIT;
-- En cas d'erreur 40001, retry from BEGIN

8. Sauvegarde et restauration

Objectif : sauvegarder un cluster en réponse à une défaillance ou changement.

pg_dump (sauvegarde logique, SQL)

Exporte une base en SQL ou format propriétaire.

pg_dump -Fc -f mydb.dump mydb

• -Fc : format personnalisé (compressé, flexible).
• -Ft : format tar (portable).
• -Fp (défaut) : SQL texte (humain-lisible).
• -s : schéma seulement.
• -a : données seulement.
• -t pattern : tables spécifiques.

Contexte : Terminal utilisateur ou compte postgres, base cible accessible. Droits : SELECT sur tous les objets de la base. Effet : crée un fichier dump (non compressé si -Fp, compressé sinon). Vérification : fichier créé, taille raisonnable. Retour arrière : supprimer le fichier dump (non destructif).

pg_restore (restauration depuis dump)

pg_restore -d newdb -C --clean mydb.dump

• -C : inclure CREATE DATABASE.
• --clean : DROP objects avant création.
• -j 4 : paralléliser (format directory/custom seulement).
• -e : arrêter à la première erreur.

Lire le dump avant restauration en production (risque d'exécution de code arbitraire si dump de source non fiable).

pg_basebackup (sauvegarde physique, continues)

Sauvegarde le cluster entier au niveau des fichiers, compatible avec la récupération point-à-temps (PITR).

pg_basebackup -D /backup/data -Ft -X stream

• -D : répertoire cible.
• -Ft : format tar.
• -X stream : WAL en parallèle (défaut ; requiert 2 slots de réplication).

Prérequis : rôle REPLICATION, postgresql.conf avec max_wal_senders >= 2.

Contexte : Terminal, cluster actif, connectivité réseau. Droits : privilège REPLICATION. Effet : copie tous les fichiers de base de données en snapshot cohérent. Vérification : répertoire /backup/data contient les fichiers. Retour arrière : supprimer /backup/data (non-destructif).

Sauvegarde WAL et PITR

Le journal WAL permet la récupération point-à-temps (PITR) : restaurer une sauvegarde de base ancienne, puis rejouer le WAL jusqu'à un instant précis.

Configuration (dans postgresql.conf) :

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /backup/wal/%f && cp %p /backup/wal/%f'

• wal_level = replica : enregistrer assez d'info pour la réplication.
• archive_mode = on : appeler archive_command pour chaque segment WAL.
• archive_command : copier le segment vers un archive sûr. Ne pas écraser un fichier existant.

Archivage : PostgreSQL complète un segment WAL (16 MB) → appelle archive_command → segment envoyé à /backup/wal/.

Récupération à moment précis :

# Restaurer la sauvegarde de basetar -xzf /backup/full.tar.gz -C /var/lib/postgresql/data​# Configurer la restauration# Dans postgresql.conf :# restore_command = 'cp /backup/wal/%f %p'# recovery_target_time = '2026-10-18 14:30:00'​# Créer le fichier signaltouch /var/lib/postgresql/data/recovery.signal​# Démarrerpg_ctl start

PostgreSQL découvre recovery.signal, lance recovery, rejoue le WAL jusqu'à 14:30:00 (le plus proche sans dépasser), supprime recovery.signal, démarre normalement.

9. Réplication en principes

Réplication physique (streaming) : envoi en continu du WAL d'une instance primaire à une standby.

Primaire (écriture) → WAL sender → WAL receiver → Standby (lecture-seule)

Avantages : exact bit-for-bit, startup rapide.
Limites : tout le cluster, pas de migration sélective.

Réplication logique : réplique les changements logiques (INSERT/UPDATE/DELETE), sélectif par table. Hors du cadre.

Synchrone vs asynchrone : - Asynchrone (défaut) : primaire valide le commit avant standby (performance, risque de perte si primaire crash). - Synchrone : primaire attend l'ACK standby (sûr, mais plus lent).

synchronous_commit = remote_apply  # attendrendre_commit = local      # défaut (rapide)
synchronous_standby_names = '*'    # quels standbys doivent répondre

Promotion manuelle :

SELECT pg_promote();  -- sur le standby, devenir primaire

PostgreSQL n'a pas de failover automatique ; outils externes (patroni, etcd) sont nécessaires.

10. psql : opérations courantes

Client interactif officiel.

Connexion :

psql -h localhost -p 5432 -U postgres -d mydb

Méta-commandes utiles :

\l                          -- lister bases
\c mydb                     -- se connecter à mydb
\dt                         -- lister tables
\d mytable                  -- structure de mytable
\d+                         -- détails (commentaires, OID)
\dn                         -- lister schémas
\du                         -- lister rôles
\di                         -- lister index
\df                         -- lister fonctions
\dx                         -- lister extensions
\timing                     -- afficher temps exécution
\conninfo                   -- info connexion
\q                          -- quitter
\set var value              -- variable psql
\x                          -- mode étendu (output vertical)
\i script.sql               -- exécuter fichier

Commandes utiles :

SELECT version();            -- version PG
SELECT current_user;         -- utilisateur courant
\watch 5                     -- relancer dernière requête toutes les 5s

11. Extensions PostgreSQL

PostgreSQL permet des extensions (contrib, tierces).

CREATE EXTENSION IF NOT EXISTS uuid-ossp;  -- génération UUID
CREATE EXTENSION IF NOT EXISTS pg_trgm;    -- full-text search amélioré
\dx                                        -- lister extensions installées

Hors cadre : développer une extension.

Exercices et vérifications

1. Architecture conceptuelle : Dessinez l'architecture d'une instance (postmaster, backends, mémoire partagée, WAL, données). Nommez chaque processus auxiliaire.
2. shared_buffers : Pour une machine 16 GB RAM dédiée à PG, quel shared_buffers suggéreriez-vous ? (Réponse : 25% = 4 GB.)
3. work_mem piège : Si 10 sessions concurrentes trient chacune 100 MB avec work_mem = 128MB, quel risque ? (Réponse : 10 × 128MB = 1.28 GB écriture disque → lent ; augmenter shared_buffers d'abord.)
4. pg_hba.conf : Écrire une ligne qui accepte seulement le rôle app depuis 192.0.2.0/24 en SCRAM-SHA-256.
5. VACUUM : Expliquer pourquoi VACUUM FULL ne doit pas être lancé en production sans préparation.
6. ANALYZE : Après une grosse suppression, pourquoi relancer ANALYZE est important ?
7. EXPLAIN : Comparer le plan d'une requête avec et sans index. Regarder Seq Scan vs Index Scan.
8. Transaction isolation : Deux sessions en REPEATABLE READ : l'une insère en table A, l'autre cherche les lignes dans A. À quel moment l'autre voit l'insertion ? (Réponse : au COMMIT de la première, qui clôt sa transaction ; après, la deuxième voit si elle relance une requête non, elle reste sur son snapshot jusqu'à son COMMIT.)
9. Sauvegarde : Décider quelle stratégie (pg_dump seule ? pg_basebackup + WAL ?) pour un RTO de 1 heure et RPO de 5 minutes.
10. PITR reconstruct : Vous restaurez une base depuis une sauvegarde de base à 12h, puis WAL jusqu'à 15h. Que se passe-t-il si le WAL s'arrête à 14h30 ? (Réponse : restauration jusqu'à 14h30 au plus tard.)
11. psql rapide : Lancer SELECT COUNT(*) FROM pg_tables; via psql (lister les tables système via pg_tables).
12. Autovacuum : Vérifier que autovacuum est actif (SHOW autovacuum). Si inactif, pourquoi ? (Risque : accumulation versions mortes, wraparound XID.)