1. Architecture et comparaison avec MySQL

1.1 Processus

PostgreSQL utilise un modèle un processus par connexion (pas de threads) : le processus postmaster écoute et fork un backend pour chaque client. Conséquence directe : une connexion coûte plusieurs Mo de mémoire et un fork, d'où l'importance d'un pooler (PgBouncer, section 15) au-delà de quelques centaines de connexions.

Processus	Rôle
postmaster	Processus père : écoute, fork des backends, redémarre les auxiliaires
backend	Un par session cliente
checkpointer	Écrit les pages modifiées sur disque lors des checkpoints
background writer	Écrit progressivement des pages sales pour libérer des buffers
walwriter	Vide les buffers WAL sur disque
autovacuum launcher / workers	Nettoyage MVCC et statistiques automatiques
walsender / walreceiver	Envoi / réception du WAL (réplication)
archiver	Exécute archive_command / archive_library pour chaque segment WAL
io workers (PG 18)	E/S asynchrones (io_method = worker par défaut, io_uring possible sous Linux)

ps -ef --forest | grep -A15 "[p]ostgres -D"
# ou depuis SQL
SELECT pid, backend_type, state FROM pg_stat_activity ORDER BY backend_type;

1.2 Mémoire

• shared_buffers : cache partagé de pages de 8 Ko. PostgreSQL s'appuie aussi sur le cache de pages de l'OS (double cache) : on ne lui donne donc pas 80 % de la RAM comme à InnoDB, mais plutôt 25 %.
• work_mem : mémoire par opération de tri / hachage, par nœud du plan et par processus parallèle.
• maintenance_work_mem : VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY.
• effective_cache_size : simple indication au planificateur (aucune allocation).

1.3 WAL et checkpoints

Toute modification est d'abord écrite dans le Write-Ahead Log (pg_wal/, segments de 16 Mo) puis appliquée en mémoire ; les fichiers de données sont mis à jour plus tard, au checkpoint. Après un crash, PostgreSQL rejoue le WAL depuis le dernier checkpoint. Le même WAL sert à la réplication physique et à la restauration à un instant donné (PITR).

• Juste après un checkpoint, la première modification d'une page écrit la page entière dans le WAL (full page write) : des checkpoints trop fréquents font exploser le volume de WAL.
• checkpoint_timeout (5 min par défaut) et max_wal_size (1 Go par défaut) déclenchent les checkpoints ; en production, viser des checkpoints déclenchés par le temps (15 min) et non par la taille.

1.4 MVCC

Un UPDATE ne modifie pas la ligne en place : il crée une nouvelle version (tuple) et marque l'ancienne comme morte pour les transactions futures. Chaque tuple porte xmin (transaction créatrice) et xmax (transaction qui l'a supprimé). Les lecteurs ne bloquent jamais les écrivains, mais :

• les versions mortes occupent de la place jusqu'au passage de VACUUM (section 6) ;
• une transaction longue (ou une session idle in transaction, ou un slot de réplication inactif) empêche le nettoyage de toute la base ;
• les identifiants de transaction sont sur 32 bits : il faut « geler » les vieux tuples pour éviter le wraparound.

1.5 Comparaison rapide avec MySQL / MariaDB

Sujet	PostgreSQL	MySQL (InnoDB)
Modèle de connexion	Processus par connexion, pooler externe recommandé	Thread par connexion (thread pool en option)
MVCC	Versions dans la table, nettoyage par VACUUM	Undo log, purge par thread dédié
Index clustered	Non (table en tas, CLUSTER ponctuel)	Oui, la clé primaire organise la table
DDL transactionnel	Oui (BEGIN; ALTER ...; ROLLBACK;)	Non (commit implicite)
Types et extensions	JSONB indexable (GIN), tableaux, plages, types utilisateur, PostGIS, extensions	JSON, moins d'extensibilité
Schémas	Base > schéma > objet	Base = schéma
Réplication	Physique (WAL) + logique (publication)	Binlog (logique), GTID, Group Replication
Cas sensible à la casse	Identifiants non quotés mis en minuscules	Dépend de l'OS / lower_case_table_names
Montée de version	Majeure = pg_upgrade ou réplication logique	Mise à jour en place le plus souvent

Voir aussi MySQL / MariaDB et SQL pour la syntaxe commune.

2. Installation (PGDG) et clusters

2.1 Dépôt officiel PGDG sur Debian / Ubuntu

Les dépôts de la distribution figent une version majeure. Le dépôt PGDG (apt.postgresql.org) fournit toutes les versions maintenues et leurs mises à jour mineures, ainsi que les extensions courantes.

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh   # ajoute le depot et la cle
sudo apt install -y postgresql-17 postgresql-contrib
# Versions installees cote client / serveur
psql --version
sudo -u postgres psql -c "SELECT version();"

2.2 Clusters Debian (postgresql-common)

Sur Debian/Ubuntu, un « cluster » est une instance (version + nom). Les configurations sont dans /etc/postgresql/<version>/<nom>/, les données dans /var/lib/postgresql/<version>/<nom>/.

pg_lsclusters                                   # liste version, nom, port, statut, chemins
sudo pg_createcluster 17 app2 --port 5433 --start -- --data-checksums
sudo pg_ctlcluster 17 app2 reload               # ou restart / stop
sudo systemctl status postgresql@17-app2
sudo pg_dropcluster 17 app2 --stop              # supprime donnees ET configuration

# Sur RHEL / Rocky (paquets PGDG) : un service par version
#   /usr/pgsql-17/bin/postgresql-17-setup initdb
#   systemctl enable --now postgresql-17

2.3 Disposition des disques

• Système de fichiers XFS ou ext4, montage noatime ; pas de swap agressif (vm.swappiness = 1 à 10).
• pg_wal sur un volume distinct si le stockage le justifie (latence d'écriture séquentielle), mais jamais sur un volume plus lent ; surveiller son remplissage : WAL plein = instance arrêtée.
• Désactiver les Transparent Huge Pages en mode always, et activer de vraies huge pages (huge_pages = try + vm.nr_hugepages) au-delà de 16 à 32 Go de shared_buffers.
• vm.overcommit_memory = 2 pour éviter que l'OOM killer ne tue le postmaster (sinon, ajuster oom_score_adj).

Pour les réglages système généraux, voir Linux serveur.

3. Authentification : pg_hba.conf, SCRAM, TLS

3.1 pg_hba.conf

Chaque connexion est comparée aux lignes de pg_hba.conf dans l'ordre ; la première ligne qui correspond (type, base, utilisateur, adresse) détermine la méthode. Aucune ligne = refus.

# TYPE    DATABASE        USER            ADDRESS            METHOD
local     all             postgres                           peer
local     all             all                                peer
hostssl   app_prod        app_rw          10.0.10.0/24       scram-sha-256
hostssl   app_prod        +grp_lecture    10.0.20.0/24       scram-sha-256
hostssl   replication     replicator      10.0.10.12/32      scram-sha-256
hostssl   all             all             10.0.30.5/32       cert
host      all             all             0.0.0.0/0          reject
host      all             all             ::/0               reject

• hostssl n'accepte que TLS ; host accepte les deux ; hostnossl l'inverse.
• +grp_lecture désigne les membres du rôle ; @fichier lit une liste ; replication est un mot-clé pour les connexions de réplication physique.
• peer (socket local) associe l'utilisateur système au rôle du même nom ; cert authentifie par certificat client.
• Recharger : SELECT pg_reload_conf(); puis contrôler les erreurs de syntaxe avec SELECT * FROM pg_hba_file_rules WHERE error IS NOT NULL;

3.2 SCRAM-SHA-256 et fin de MD5

scram-sha-256 est la méthode par défaut depuis PostgreSQL 14. MD5 est déprécié (PostgreSQL 18 émet des avertissements lors de l'utilisation de mots de passe MD5) : migrez.

-- postgresql.conf : password_encryption = 'scram-sha-256' (defaut)
-- Reperer les roles encore en MD5
SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%';
-- Reinitialiser le mot de passe (re-hache en SCRAM) : \password dans psql
-- evite que le mot de passe apparaisse dans l'historique et les logs
\password app_rw

3.3 TLS

# postgresql.conf
ssl = on
ssl_cert_file = '/etc/postgresql/ssl/server.crt'
ssl_key_file  = '/etc/postgresql/ssl/server.key'      # proprietaire postgres, mode 0600
ssl_ca_file   = '/etc/postgresql/ssl/ca.crt'          # pour verifier les certificats clients
ssl_min_protocol_version = 'TLSv1.2'

# Cote client : verifier le serveur (sinon TLS ne protege pas du MITM)
psql "host=db1.example.com dbname=app_prod user=app_rw sslmode=verify-full sslrootcert=/etc/ssl/ca.crt"

sslmode client	Chiffré	Serveur vérifié
disable / allow / prefer	Non garanti	Non
require	Oui	Non
verify-ca	Oui	Certificat signé par l'AC
verify-full	Oui	AC + nom d'hôte

Génération des certificats : voir OpenSSL et PKI et certificats. Écoute réseau : listen_addresses limité aux interfaces utiles, et filtrage du port 5432 au pare-feu (Pare-feu).

4. Rôles, privilèges et schémas

4.1 Modèle recommandé

Un rôle est à la fois utilisateur et groupe. Le schéma de privilèges courant sépare le propriétaire des objets (qui fait les migrations) des comptes applicatifs :

Rôle	LOGIN	Usage
app_owner	Non (ou seulement pour les migrations)	Propriétaire de la base, des schémas et des tables
grp_rw	Non	Groupe lecture / écriture des données
grp_ro	Non	Groupe lecture seule
app_rw, bi_reader	Oui	Comptes réels, membres d'un groupe

CREATE ROLE app_owner NOLOGIN;
CREATE ROLE grp_rw NOLOGIN;
CREATE ROLE grp_ro NOLOGIN;
CREATE ROLE app_rw LOGIN IN ROLE grp_rw;   -- mot de passe ensuite via \password
CREATE ROLE bi_reader LOGIN IN ROLE grp_ro CONNECTION LIMIT 10;

CREATE DATABASE app_prod OWNER app_owner;
\c app_prod
REVOKE ALL ON DATABASE app_prod FROM PUBLIC;
GRANT CONNECT ON DATABASE app_prod TO grp_rw, grp_ro;

CREATE SCHEMA app AUTHORIZATION app_owner;
GRANT USAGE ON SCHEMA app TO grp_rw, grp_ro;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO grp_rw;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO grp_rw;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO grp_ro;

-- Objets FUTURS crees par app_owner : sans cela, chaque nouvelle table est invisible
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO grp_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
    GRANT USAGE, SELECT ON SEQUENCES TO grp_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
    GRANT SELECT ON TABLES TO grp_ro;

-- Chemin de recherche
ALTER ROLE app_rw IN DATABASE app_prod SET search_path = app;

4.2 Schéma public depuis PostgreSQL 15

Jusqu'à la version 14, tout rôle pouvait créer des objets dans le schéma public (droit CREATE accordé à PUBLIC), ce qui permettait des attaques par détournement de search_path. Depuis PostgreSQL 15, ce droit est retiré et public appartient à pg_database_owner. Une base migrée par pg_dump/pg_upgrade depuis une version ancienne conserve les anciens droits :

REVOKE CREATE ON SCHEMA public FROM PUBLIC;
ALTER SCHEMA public OWNER TO pg_database_owner;

4.3 Rôles prédéfinis utiles

Rôle	Usage
pg_read_all_data / pg_write_all_data	Lecture / écriture sur tout (PG 14+), pratique pour les sauvegardes ou l'audit
pg_monitor	Lecture des vues de statistiques et de pg_stat_activity complète : compte de supervision
pg_signal_backend	Annuler / terminer les requêtes d'autres rôles (non superuser)
pg_checkpoint	Lancer CHECKPOINT (PG 15+)
pg_maintain	VACUUM, ANALYZE, REINDEX, REFRESH MATERIALIZED VIEW sur toutes les tables (PG 17+, privilège MAINTAIN)

-- Qui est superuser ? (doit se limiter a postgres et un compte d'urgence)
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolreplication, rolbypassrls
FROM pg_roles WHERE rolsuper OR rolcreaterole OR rolreplication OR rolbypassrls;

-- Droits effectifs sur une table
SELECT grantee, privilege_type FROM information_schema.role_table_grants
WHERE table_schema = 'app' AND table_name = 'commande';

5. Tuning de la configuration

5.1 Paramètres clés selon la RAM

Valeurs de départ pour un serveur dédié, stockage SSD/NVMe, charge transactionnelle (OLTP) mixte. À ajuster ensuite en mesurant.

Paramètre	8 Go	32 Go	128 Go	Commentaire
shared_buffers	2 Go	8 Go	32 Go	~25 % de la RAM ; redémarrage requis
effective_cache_size	6 Go	24 Go	96 Go	~75 % ; indication pour le planificateur
work_mem	16 Mo	32 Mo	64 Mo	Voir le piège ci-dessous
maintenance_work_mem	512 Mo	1 Go	2 Go	Accélère VACUUM / CREATE INDEX
autovacuum_work_mem	-1	-1	1 Go	-1 = maintenance_work_mem ; PG 17 lève l'ancienne limite d'1 Go utile
max_connections	100	200	300	Garder bas, utiliser un pooler
max_wal_size	4 Go	16 Go	32 Go	Checkpoints par temps et non par volume
checkpoint_timeout	15 min	15 min	15 à 30 min	Plus long = moins de WAL, recovery plus longue
random_page_cost	1.1	1.1	1.1	4 par défaut (disques rotatifs)
effective_io_concurrency	200	200	200	SSD / NVMe (le défaut a été relevé en PG 18)
max_worker_processes / max_parallel_workers	4 / 4	8 / 8	16 / 16	Nombre de cœurs
max_parallel_workers_per_gather	2	2 à 4	4	Chaque worker consomme son propre work_mem

-- Modifier sans editer le fichier (ecrit dans postgresql.auto.conf)
ALTER SYSTEM SET work_mem = '32MB';
SELECT pg_reload_conf();

-- Parametres en attente de redemarrage
SELECT name, setting, pending_restart FROM pg_settings WHERE pending_restart;

-- D'ou vient la valeur effective ?
SELECT name, setting, unit, source, sourcefile FROM pg_settings
WHERE name IN ('shared_buffers','work_mem','max_wal_size');

5.2 Journalisation utile

log_min_duration_statement = 500ms     # requetes lentes
log_checkpoints = on                    # (defaut on depuis PG 15)
log_connections = on                    # PG 18 : valeurs plus fines possibles (receipt, authentication...)
log_disconnections = off
log_lock_waits = on                     # attentes de verrou > deadlock_timeout
log_temp_files = 0
log_autovacuum_min_duration = 10s
log_line_prefix = '%m [%p] %q%u@%d %r app=%a '
idle_in_transaction_session_timeout = '10min'
statement_timeout = 0                   # a definir par role plutot que globalement
lock_timeout = 0                        # idem ; utile dans les migrations : SET lock_timeout = '5s'

6. VACUUM, autovacuum, bloat et wraparound

6.1 Ce que fait VACUUM

Commande	Effet	Verrou
VACUUM	Marque l'espace des tuples morts comme réutilisable, met à jour la visibility map, gèle les tuples anciens	Léger, compatible lectures/écritures
VACUUM (FREEZE)	Gèle agressivement	Léger
VACUUM FULL	Réécrit la table pour rendre l'espace à l'OS	ACCESS EXCLUSIVE : table bloquée
ANALYZE	Met à jour les statistiques du planificateur	Léger

VACUUM ne rend pas l'espace au système (sauf les pages vides en fin de fichier) : une table qui a gonflé reste grosse, mais l'espace est réutilisé par les insertions suivantes.

6.2 Régler l'autovacuum

Une table est nettoyée quand n_dead_tup > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor x reltuples. Avec le facteur par défaut de 20 %, une table de 500 millions de lignes attend 100 millions de lignes mortes : beaucoup trop tard.

# postgresql.conf : plus agressif que le defaut sur SSD
autovacuum_max_workers = 5                 # PG 18 : modifiable sans redemarrage jusqu'a autovacuum_worker_slots
autovacuum_naptime = 30s
autovacuum_vacuum_cost_limit = 2000        # le defaut (200) bride fortement les workers
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_insert_scale_factor = 0.05   # tables en insertion seule (PG 13+)

-- Reglage par table pour les grosses tables tres modifiees
ALTER TABLE app.evenement SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_threshold = 10000,
    autovacuum_analyze_scale_factor = 0.005
);

-- Tables en retard de nettoyage
SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS pct_mort,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 20;

-- Autovacuum en cours et progression
SELECT p.pid, p.relid::regclass, p.phase, p.heap_blks_scanned, p.heap_blks_total,
       a.query_start
FROM pg_stat_progress_vacuum p JOIN pg_stat_activity a USING (pid);

6.3 Ce qui bloque le nettoyage

VACUUM ne peut supprimer que les versions invisibles pour toutes les transactions encore ouvertes. Le « xmin horizon » est retenu par :

1. une transaction longue ou une session idle in transaction ;
2. un slot de réplication inactif (physique avec hot_standby_feedback, ou logique) ;
3. une requête longue sur un standby avec hot_standby_feedback = on ;
4. une transaction préparée oubliée (pg_prepared_xacts).

-- Qui retient l'horizon ?
SELECT 'session' AS source, pid::text AS id, backend_xmin, age(backend_xmin) AS age_xid, state, xact_start
FROM pg_stat_activity WHERE backend_xmin IS NOT NULL
UNION ALL
SELECT 'slot', slot_name, xmin, age(xmin), active::text, NULL FROM pg_replication_slots WHERE xmin IS NOT NULL
UNION ALL
SELECT 'slot (catalog)', slot_name, catalog_xmin, age(catalog_xmin), active::text, NULL FROM pg_replication_slots WHERE catalog_xmin IS NOT NULL
UNION ALL
SELECT 'prepared', gid, transaction, age(transaction), owner::text, prepared FROM pg_prepared_xacts
ORDER BY age_xid DESC NULLS LAST;

6.4 Bloat

-- Mesure exacte (lit toute la table : a lancer hors pointe)
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('app.evenement');         -- dead_tuple_percent, free_percent
SELECT * FROM pgstattuple_approx('app.evenement');  -- estimation plus rapide
SELECT * FROM pgstatindex('app.evenement_pkey');    -- avg_leaf_density, leaf_fragmentation

Pour récupérer l'espace sans bloquer : pg_repack (extension, reconstruit la table en ligne avec un court verrou final) plutôt que VACUUM FULL. Pour les index : REINDEX INDEX CONCURRENTLY.

6.5 Wraparound : le risque majeur

Les identifiants de transaction (XID) sont sur 32 bits, soit environ 2,1 milliards de transactions « dans le passé » visibles. VACUUM gèle les vieux tuples pour qu'ils restent visibles. Si le gel prend trop de retard :

• à autovacuum_freeze_max_age (200 millions par défaut), un autovacuum « anti-wraparound » est lancé, même si autovacuum est désactivé, et il n'est pas interruptible facilement ;
• à vacuum_failsafe_age (1,6 milliard, PG 14+), VACUUM passe en mode d'urgence (ignore le cost delay, saute le nettoyage des index) ;
• à environ 3 millions de XID de la limite, PostgreSQL refuse toute nouvelle transaction en écriture pour protéger les données.

-- Age par base (alerter a 500 M, critique a 1 G)
SELECT datname, age(datfrozenxid) AS age_xid,
       round(100.0 * age(datfrozenxid) / 2147483647, 1) AS pct_limite
FROM pg_database ORDER BY 2 DESC;

-- Tables les plus anciennes
SELECT c.oid::regclass AS table_name, age(c.relfrozenxid) AS age_xid,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS taille
FROM pg_class c WHERE c.relkind IN ('r','m','t')
ORDER BY age(c.relfrozenxid) DESC LIMIT 15;

-- Meme logique pour les multixacts : mxid_age(datminmxid)

7. Index

7.1 Types d'index

Type	Opérateurs / cas d'usage	Remarques
B-tree	=, <, >, BETWEEN, ORDER BY, LIKE 'abc%' (avec text_pattern_ops ou collation C)	Défaut ; dédoublonnage (PG 13+) ; skip scan sur index multicolonne (PG 18)
Hash	= uniquement	Journalisé depuis PG 10 ; rarement préférable au B-tree
GIN	JSONB (@>, existence de clés), tableaux, recherche plein texte, trigrammes (pg_trgm pour LIKE '%abc%')	Coûteux en écriture (fastupdate), excellent en lecture
GiST / SP-GiST	Géométrie (PostGIS), plages, plus proche voisin, contraintes d'exclusion	Index « lossy », recheck
BRIN	Très grandes tables dont l'ordre physique suit la colonne (horodatage d'insertion)	Minuscule (quelques Mo pour des To) ; inefficace si les données sont désordonnées

7.2 Index utiles en production

-- Creation SANS bloquer les ecritures (plus lent, ne s'execute pas dans une transaction)
CREATE INDEX CONCURRENTLY idx_commande_client ON app.commande (client_id, date_commande DESC);

-- Index partiel : seulement les lignes interrogees
CREATE INDEX CONCURRENTLY idx_commande_attente ON app.commande (date_commande)
    WHERE statut = 'EN_ATTENTE';

-- Index couvrant (index-only scan sans visiter la table)
CREATE INDEX CONCURRENTLY idx_client_email ON app.client (email) INCLUDE (nom, prenom);

-- Index sur expression
CREATE INDEX CONCURRENTLY idx_client_email_lower ON app.client (lower(email));

-- JSONB
CREATE INDEX CONCURRENTLY idx_evt_payload ON app.evenement USING gin (payload jsonb_path_ops);

-- BRIN sur une table de journal de plusieurs centaines de Go
CREATE INDEX idx_log_ts_brin ON app.journal USING brin (horodatage) WITH (pages_per_range = 64);

-- Reconstruire sans verrou bloquant
REINDEX INDEX CONCURRENTLY app.idx_commande_client;

7.3 Index inutiles et manquants

-- Index jamais utilises depuis la remise a zero des stats (hors index uniques / PK)
SELECT s.schemaname, s.relname, s.indexrelname, s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS taille
FROM pg_stat_user_indexes s JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0 AND NOT i.indisunique
ORDER BY pg_relation_size(s.indexrelid) DESC;
-- ATTENTION : verifier aussi sur les standbys (les stats sont locales a chaque noeud)

-- Tables lues surtout en sequentiel (candidates a un index)
SELECT relname, seq_scan, seq_tup_read, idx_scan, n_live_tup
FROM pg_stat_user_tables
WHERE seq_scan > 0 AND n_live_tup > 100000
ORDER BY seq_tup_read DESC LIMIT 20;

-- Cles etrangeres sans index cote enfant (DELETE parent tres lent, verrous)
SELECT c.conrelid::regclass AS table_enfant, c.conname, a.attname
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
WHERE c.contype = 'f'
  AND NOT EXISTS (SELECT 1 FROM pg_index i
                  WHERE i.indrelid = c.conrelid AND i.indkey[0] = c.conkey[1]);

8. EXPLAIN et pg_stat_statements

8.1 Lire un plan

EXPLAIN (ANALYZE, BUFFERS, SETTINGS, WAL)
SELECT c.nom, count(*)
FROM app.commande o JOIN app.client c ON c.id = o.client_id
WHERE o.date_commande >= now() - interval '30 days'
GROUP BY c.nom;
-- PG 18 : BUFFERS est inclus par defaut avec ANALYZE

• ANALYZE exécute réellement la requête (attention aux UPDATE/DELETE : encadrer par BEGIN; ... ROLLBACK;).
• Comparer rows= estimé et actual rows= : un écart d'un facteur 10 ou plus indique des statistiques insuffisantes (ANALYZE, ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 1000, statistiques étendues CREATE STATISTICS pour les colonnes corrélées).
• Buffers : shared hit (cache), read (disque ou cache OS), temp (débordement de work_mem).
• Le temps d'un nœud est actual time x loops : une boucle imbriquée sur 100 000 itérations d'un index scan à 0,05 ms représente 5 s.
• Les nœuds Sort Method: external merge Disk ou Batches: 16 sur un hachage signalent un work_mem trop faible pour cette requête.

Nœud	Signification	Piste
Seq Scan sur grosse table avec filtre sélectif	Pas d'index utilisable	Index (éventuellement partiel / expression)
Index Scan avec beaucoup de Rows Removed by Filter	Index peu sélectif pour ce filtre	Index multicolonne dans le bon ordre
Bitmap Heap Scan + lossy	work_mem insuffisant pour le bitmap	work_mem local
Nested Loop avec estimation à 1 ligne, réel 50 000	Mauvaise estimation	Statistiques, CREATE STATISTICS
Index Only Scan avec Heap Fetches élevé	Visibility map pas à jour	VACUUM plus fréquent

8.2 pg_stat_statements

# postgresql.conf (redemarrage)
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = top
track_io_timing = on

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 15 par temps total (la charge reelle du serveur)
SELECT round(total_exec_time::numeric / 1000, 1) AS total_s,
       calls,
       round(mean_exec_time::numeric, 2) AS moy_ms,
       round(100 * total_exec_time / sum(total_exec_time) OVER (), 1) AS pct,
       shared_blks_read, temp_blks_written,
       left(regexp_replace(query, '\s+', ' ', 'g'), 120) AS requete
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 15;

-- Remise a zero apres une optimisation (pour mesurer l'effet)
SELECT pg_stat_statements_reset();

9. Sauvegarde, PITR et pgBackRest

9.1 Les deux familles

Critère	Logique (pg_dump)	Physique (pg_basebackup, pgBackRest)
Granularité	Base, schéma, table	Instance entière
Restauration à un instant donné	Non	Oui, avec l'archivage WAL
Portabilité	Entre versions majeures et architectures	Même version majeure, même architecture
Durée de restauration	Longue (recréation des index)	Rapide (copie de fichiers + rejeu WAL)
Usage	Extraction, migration, copie d'environnement	Sauvegarde de production, PRA

9.2 pg_dump / pg_restore

Format (-F)	Restauration	Parallèle	Sélectif
p (plain SQL)	psql	Non	Non
c (custom, compressé)	pg_restore	Restauration oui	Oui
d (répertoire)	pg_restore	Sauvegarde et restauration	Oui
t (tar)	pg_restore	Non	Oui

# Dump parallele en format repertoire, compression zstd
pg_dump -h db1.example.com -U backup -d app_prod -Fd -j 4 -Z zstd:3 -f /backup/app_prod_$(date +%F)

# Objets globaux (roles, tablespaces) : NON inclus dans pg_dump
pg_dumpall -h db1.example.com -U postgres --globals-only > /backup/globals_$(date +%F).sql

# Restauration parallele dans une base vide
createdb -O app_owner app_restore
pg_restore -d app_restore -j 4 --no-owner --role=app_owner /backup/app_prod_2026-10-18

# Restaurer une seule table
pg_restore -d app_restore -t commande -n app /backup/app_prod_2026-10-18

# Inventaire du contenu (et liste editable pour restauration selective : -L)
pg_restore -l /backup/app_prod_2026-10-18 > contenu.lst

9.3 Sauvegarde physique et PITR

Le principe : une copie des fichiers de données (base backup) + l'archive continue des segments WAL. On restaure la copie puis on rejoue le WAL jusqu'à l'instant voulu (juste avant le DROP TABLE malheureux).

# postgresql.conf (archivage minimaliste ; en production, preferer pgBackRest)
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
archive_timeout = 300    # force un segment au moins toutes les 5 min (RPO)

# Base backup en flux, avec le WAL necessaire et un manifeste
pg_basebackup -h db1.example.com -U replicator -D /backup/base_$(date +%F) \
    -Ft -z -X stream -c fast -P --manifest-checksums=SHA256
pg_verifybackup /backup/base_2026-10-18     # format tar pris en charge depuis PG 18 (avant : format plain)

# PG 17 : sauvegarde incrementale (necessite summarize_wal = on)
pg_basebackup -D /backup/incr_1 --incremental=/backup/full/backup_manifest
pg_combinebackup /backup/full /backup/incr_1 -o /restore/data

# Restauration PITR (instance arretee, repertoire de donnees vide)
# 1. Copier le base backup dans le repertoire de donnees
# 2. postgresql.conf :
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-10-18 14:31:00+02'
recovery_target_action = 'pause'      # verifier les donnees avant de promouvoir
# 3. Creer le fichier signal puis demarrer
touch $PGDATA/recovery.signal
# 4. Controler, puis : SELECT pg_wal_replay_resume();  (ou pg_promote())

9.4 pgBackRest : configuration complète

pgBackRest gère sauvegardes complètes / différentielles / incrémentales en parallèle, l'archivage WAL asynchrone, la compression, le chiffrement du dépôt, les dépôts S3/Azure/GCS, la rétention, la vérification et la restauration (y compris en delta). C'est l'outil de référence pour un serveur autonome comme pour Patroni.

# /etc/pgbackrest/pgbackrest.conf  (sur le serveur PostgreSQL)
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2               # garde 2 completes (et les diff/incr + WAL associes)
repo1-retention-diff=6
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=<secret-genere-et-sequestre>
compress-type=zst
process-max=4
start-fast=y
archive-async=y
spool-path=/var/spool/pgbackrest
log-level-console=info
log-level-file=detail

# Second depot hors site (S3 compatible), pour la regle 3-2-1
repo2-type=s3
repo2-path=/pgbackrest
repo2-s3-bucket=sauvegardes-pg
repo2-s3-endpoint=s3.example.com
repo2-s3-region=eu-west-1
repo2-s3-key=<cle>
repo2-s3-key-secret=<secret>
repo2-retention-full=4

[main]
pg1-path=/var/lib/postgresql/17/main
pg1-port=5432

# postgresql.conf
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'

# Initialisation et controle
sudo -u postgres pgbackrest --stanza=main stanza-create
sudo -u postgres pgbackrest --stanza=main check          # verifie archivage + depots

# Sauvegardes (a planifier : cron ou timer systemd, utilisateur postgres)
sudo -u postgres pgbackrest --stanza=main --type=full backup     # dimanche
sudo -u postgres pgbackrest --stanza=main --type=diff backup     # autres jours
sudo -u postgres pgbackrest --stanza=main --repo=2 --type=full backup

# Etat, retention appliquee, taille, WAL disponibles
sudo -u postgres pgbackrest --stanza=main info

# Restauration PITR (instance arretee) ; --delta ne reecrit que les fichiers differents
sudo -u postgres pgbackrest --stanza=main --delta \
    --type=time --target="2026-10-18 14:31:00+02" --target-action=promote restore

# Verification d'integrite du depot
sudo -u postgres pgbackrest --stanza=main verify

10. Réplication physique (streaming)

10.1 Mise en place d'un standby

# Sur le primaire (postgresql.conf)
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
wal_keep_size = 0              # on s'appuie sur les slots + l'archive
hot_standby = on               # cote standby : lectures autorisees

-- Role de replication
CREATE ROLE replicator WITH REPLICATION LOGIN;   -- puis \password replicator
-- pg_hba.conf : hostssl replication replicator 10.0.10.12/32 scram-sha-256

# Sur le standby (repertoire de donnees vide, instance arretee)
sudo -u postgres pg_basebackup -h 10.0.10.11 -U replicator \
    -D /var/lib/postgresql/17/main -X stream -c fast -P \
    -R -C -S standby_db2
# -R : ecrit primary_conninfo et cree standby.signal
# -C -S : cree le slot de replication "standby_db2" sur le primaire
sudo systemctl start postgresql@17-main

-- Sur le primaire : etat et retard
SELECT application_name, client_addr, state, sync_state,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS retard_octets,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

-- Sur le standby
SELECT pg_is_in_recovery(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(),
       now() - pg_last_xact_replay_timestamp() AS retard_temps;
SELECT status, sender_host, slot_name FROM pg_stat_wal_receiver;

10.2 Slots de réplication : protection et danger

Un slot garantit que le primaire conserve le WAL tant que le standby ne l'a pas consommé. Sans slot, un standby trop en retard décroche définitivement ; avec un slot, un standby arrêté fait grossir pg_wal indéfiniment jusqu'à saturer le disque du primaire.

# Garde-fou indispensable (PG 13+)
max_slot_wal_keep_size = 50GB     # au-dela, le slot est invalide plutot que de remplir le disque
idle_replication_slot_timeout = '2d'   # PG 18 : invalide les slots inactifs depuis trop longtemps

SELECT slot_name, slot_type, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS wal_retenu,
       pg_size_pretty(safe_wal_size) AS marge
FROM pg_replication_slots;
-- wal_status : reserved (ok), extended, unreserved (danger), lost (slot inutilisable)

-- Supprimer un slot orphelin (standby decommissionne)
SELECT pg_drop_replication_slot('standby_ancien');

10.3 synchronous_commit et réplication synchrone

synchronous_commit	Le COMMIT attend	Perte possible si le primaire meurt
off	Rien (écriture WAL différée)	Quelques centaines de ms de transactions (pas de corruption)
local	Flush local	Ce que le standby n'a pas reçu
remote_write	Standby a écrit (pas flushé)	Crash simultané des deux OS
on (défaut)	Flush local + flush standby synchrone	Aucune (si standby synchrone)
remote_apply	Standby a rejoué (lecture cohérente sur le standby)	Aucune ; latence maximale

# Quorum : au moins 1 des 2 standbys doit confirmer
synchronous_standby_names = 'ANY 1 (db2, db3)'
# Peut etre baisse par transaction pour les ecritures non critiques :
#   SET LOCAL synchronous_commit = off;

10.4 Conflits de requêtes sur le standby

Une requête longue sur un standby peut être annulée (« canceling statement due to conflict with recovery ») quand le rejeu doit supprimer des versions qu'elle lit. Options : max_standby_streaming_delay (laisser le rejeu attendre, au prix du retard) ou hot_standby_feedback = on (le primaire conserve ces versions, au prix de bloat sur le primaire). Pour la BI lourde, un standby dédié avec un délai élevé est souvent le meilleur compromis.

11. Haute disponibilité : Patroni

11.1 Comparatif des solutions

Solution	Principe	Points forts	Limites
Patroni	Agent Python sur chaque nœud, consensus via DCS (etcd, Consul, ZooKeeper, Kubernetes)	Standard de fait, bascule automatique fiable, API REST, gestion de la configuration, mode synchrone	Un cluster etcd à exploiter (3 nœuds minimum)
repmgr	Outil de gestion de la réplication + démon de surveillance	Simple, pas de DCS	Pas de vrai consensus : risque de split-brain, à combiner avec du fencing
pg_auto_failover	Nœud « monitor » central	Simple à déployer	Le monitor est un point unique ; dynamique de développement plus faible
CloudNativePG	Opérateur Kubernetes	Intégré à Kubernetes (sauvegardes, bascule, déclaratif)	Nécessite Kubernetes (voir Kubernetes)
Pacemaker / Corosync	Cluster HA générique + agent PostgreSQL	Fencing (STONITH) natif	Complexe, expertise rare

11.2 Architecture Patroni type

                 clients
                    |
          HAProxy (x2, keepalived VIP)   ou vip-manager
          :5000 -> primaire  (check GET /primary sur :8008)
          :5001 -> replicas  (check GET /replica sur :8008)
           /            |            \
   pg1 + Patroni   pg2 + Patroni   pg3 + Patroni
           \            |            /
            etcd1 -- etcd2 -- etcd3   (DCS : verrou du leader, TTL)

# /etc/patroni/patroni.yml (extrait, noeud pg1)
scope: pg-prod
name: pg1
restapi:
  listen: 10.0.10.11:8008
  connect_address: 10.0.10.11:8008
etcd3:
  hosts: 10.0.10.21:2379,10.0.10.22:2379,10.0.10.23:2379
bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    synchronous_mode: true
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        wal_level: replica
        hot_standby: "on"
        max_wal_senders: 10
        max_replication_slots: 10
        archive_mode: "on"
        archive_command: pgbackrest --stanza=main archive-push %p
postgresql:
  listen: 10.0.10.11:5432
  connect_address: 10.0.10.11:5432
  data_dir: /var/lib/postgresql/17/main
  bin_dir: /usr/lib/postgresql/17/bin
  authentication:
    replication: { username: replicator, password: "<depuis-un-coffre>" }
    superuser:   { username: postgres,   password: "<depuis-un-coffre>" }
  create_replica_methods: [pgbackrest, basebackup]
  pgbackrest:
    command: pgbackrest --stanza=main --delta restore
    keep_data: true
    no_params: true
watchdog:
  mode: required       # /dev/watchdog : evite deux primaires si Patroni se fige
  device: /dev/watchdog

patronictl -c /etc/patroni/patroni.yml list
patronictl -c /etc/patroni/patroni.yml switchover --leader pg1 --candidate pg2   # bascule planifiee
patronictl -c /etc/patroni/patroni.yml failover                                  # forcee (primaire mort)
patronictl -c /etc/patroni/patroni.yml edit-config      # parametres PostgreSQL : via le DCS, pas postgresql.conf
patronictl -c /etc/patroni/patroni.yml restart pg-prod --pending
patronictl -c /etc/patroni/patroni.yml pause            # maintenance : plus de bascule auto
patronictl -c /etc/patroni/patroni.yml reinit pg-prod pg3   # reconstruire un replica

12. Réplication logique

12.1 Principe et cas d'usage

La réplication logique décode le WAL en changements de lignes (INSERT/UPDATE/DELETE) pour un ensemble de tables publiées. L'abonné est une instance en écriture, éventuellement d'une autre version majeure. Usages : montée de version quasi sans coupure, migration de serveur, consolidation de bases, alimentation d'un entrepôt, réplication partielle (tables, colonnes, lignes filtrées depuis PG 15).

-- Editeur (source) : wal_level = logical (redemarrage)
CREATE ROLE repl_logique WITH REPLICATION LOGIN;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO repl_logique;
CREATE PUBLICATION pub_app FOR TABLES IN SCHEMA app;
-- ou : FOR TABLE app.commande, app.client WHERE (pays = 'FR')

-- Abonne (cible) : le SCHEMA doit exister (DDL non replique)
pg_dump -h source.example.com -U postgres -d app_prod --schema-only | psql -d app_prod

CREATE SUBSCRIPTION sub_app
    CONNECTION 'host=source.example.com dbname=app_prod user=repl_logique sslmode=verify-full'
    PUBLICATION pub_app
    WITH (copy_data = true, streaming = parallel);

-- Suivi
SELECT * FROM pg_stat_subscription;                         -- cote abonne
SELECT srsubid, srrelid::regclass, srsubstate FROM pg_subscription_rel;  -- etat par table (r = pret)
SELECT slot_name, active, confirmed_flush_lsn FROM pg_replication_slots WHERE slot_type = 'logical';  -- cote editeur

12.2 Limites à connaître

Élément	Comportement	Contournement
DDL	Non répliqué	Appliquer les migrations des deux côtés (abonné d'abord pour les ajouts de colonnes)
Séquences	Valeurs non répliquées (support annoncé pour les versions suivantes)	Resynchroniser avec setval() au moment de la bascule
Tables sans clé primaire	UPDATE/DELETE refusés côté éditeur	Ajouter une PK ou REPLICA IDENTITY FULL (coûteux)
Conflits (doublon de clé côté abonné)	La souscription s'arrête en erreur et rejoue en boucle	Corriger la donnée, ou ALTER SUBSCRIPTION ... SKIP (lsn = ...) ; disable_on_error
Slot logique abandonné	Retient WAL et catalog_xmin (bloat du catalogue)	Supprimer la souscription proprement (DROP SUBSCRIPTION) ou le slot
Bascule du primaire (HA)	Avant PG 17, les slots logiques étaient perdus lors d'un failover	PG 17+ : failover = true sur la souscription et sync_replication_slots sur les standbys

13. Montée de version majeure

13.1 Choisir la méthode

Méthode	Interruption	Retour arrière	Remarques
pg_dump / pg_restore	Durée du dump + restauration (heures sur de gros volumes)	Ancienne instance intacte	Simple, défragmente, adapté aux petites bases
pg_upgrade (copie)	Proportionnelle à la taille	Ancienne instance intacte	Double l'espace disque
pg_upgrade --link	Quelques minutes, indépendant de la taille	Impossible une fois la nouvelle instance démarrée (fichiers partagés)	Sauvegarde préalable impérative
pg_upgrade --clone / --swap (PG 18)	Rapide	Clone : ancienne intacte (FS avec reflink : XFS, Btrfs)	Selon le système de fichiers
Réplication logique	Secondes (bascule applicative)	Possible (réplication inverse)	Plus de préparation, limites de la section 12

13.2 pg_upgrade --link pas à pas (Debian, 16 vers 17)

# 0. Sauvegarde complete verifiee (pgBackRest) + extensions disponibles pour la version cible
sudo apt install postgresql-17 postgresql-17-<extensions-utilisees>

# 1. Nouveau cluster vide, memes options (locale, encodage, checksums)
sudo pg_createcluster 17 main --start=false -- --data-checksums   # si l'ancien les avait

# 2. Controle a blanc (instances arretees ou --check sur l'ancienne en ligne)
sudo -u postgres /usr/lib/postgresql/17/bin/pg_upgrade \
  -b /usr/lib/postgresql/16/bin -B /usr/lib/postgresql/17/bin \
  -d /var/lib/postgresql/16/main -D /var/lib/postgresql/17/main \
  -o "-c config_file=/etc/postgresql/16/main/postgresql.conf" \
  -O "-c config_file=/etc/postgresql/17/main/postgresql.conf" \
  --link --jobs 4 --check

# 3. Arret, migration reelle (meme commande sans --check)
sudo systemctl stop postgresql@16-main
# ... pg_upgrade ... --link --jobs 4

# 4. Reporter la configuration (postgresql.conf, pg_hba.conf), echanger les ports, demarrer
sudo systemctl start postgresql@17-main

# 5. Statistiques : conservees par pg_upgrade a partir de PG 18 ;
#    pour une cible <= 17, les recalculer immediatement (sinon plans catastrophiques)
sudo -u postgres vacuumdb --all --analyze-in-stages --jobs 4

# 6. Mettre a jour les extensions puis supprimer l'ancien cluster apres validation
#    (pg_upgrade genere un script update_extensions.sql le cas echeant)
sudo pg_dropcluster 16 main

# Alternative Debian en une commande :
#   sudo pg_upgradecluster -m upgrade --link 16 main

14. Supervision et requêtes utiles

14.1 Vues de statistiques

Vue	Contenu
pg_stat_activity	Sessions, requête courante, état, attentes (wait_event_type)
pg_stat_database	Commits, rollbacks, cache hit, deadlocks, fichiers temporaires, conflits
pg_stat_user_tables / _indexes	Scans, lignes mortes, derniers vacuum / analyze
pg_stat_replication / pg_replication_slots	Retard et état des standbys et slots
pg_stat_io (PG 16+)	E/S par type de backend et de contexte
pg_stat_checkpointer (PG 17+)	Checkpoints programmés vs demandés (séparé de pg_stat_bgwriter)
pg_stat_archiver	Archivage WAL : dernier succès / échec
pg_stat_wal	Volume de WAL généré, full page writes
pg_stat_progress_*	Progression de VACUUM, CREATE INDEX, ANALYZE, CLUSTER, basebackup, COPY

14.2 Requêtes de diagnostic

-- Sessions actives et longues
SELECT pid, usename, datname, client_addr, state,
       now() - xact_start  AS duree_transaction,
       now() - query_start AS duree_requete,
       wait_event_type, wait_event, left(query, 100) AS requete
FROM pg_stat_activity
WHERE state <> 'idle' AND backend_type = 'client backend'
ORDER BY xact_start NULLS LAST;

-- Chaines de blocage : qui bloque qui
SELECT bloque.pid AS pid_bloque, left(bloque.query, 60) AS requete_bloquee,
       bloqueur.pid AS pid_bloqueur, bloqueur.state AS etat_bloqueur,
       left(bloqueur.query, 60) AS requete_bloqueur,
       now() - bloqueur.xact_start AS duree_bloqueur
FROM pg_stat_activity bloque
JOIN LATERAL unnest(pg_blocking_pids(bloque.pid)) AS b(pid) ON true
JOIN pg_stat_activity bloqueur ON bloqueur.pid = b.pid;

-- Annuler la requete (douce) puis terminer la session (forte)
SELECT pg_cancel_backend(12345);
SELECT pg_terminate_backend(12345);

-- Sessions "idle in transaction" depuis plus de 5 minutes
SELECT pid, usename, application_name, now() - state_change AS inactive_depuis
FROM pg_stat_activity
WHERE state = 'idle in transaction' AND now() - state_change > interval '5 minutes';

-- Taux de cache par base (indicatif ; une valeur basse sur OLTP merite analyse)
SELECT datname, round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS cache_hit_pct,
       temp_files, pg_size_pretty(temp_bytes) AS temp, deadlocks
FROM pg_stat_database WHERE datname IS NOT NULL;

-- Plus grosses tables (donnees + index + TOAST)
SELECT c.oid::regclass AS objet,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total,
       pg_size_pretty(pg_relation_size(c.oid))       AS table_seule
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','m') AND n.nspname NOT IN ('pg_catalog','information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC LIMIT 20;

-- Checkpoints demandes (max_wal_size trop petit si requested >> timed)
SELECT num_timed, num_requested, write_time, sync_time FROM pg_stat_checkpointer;  -- PG 17+

-- Archivage en echec
SELECT archived_count, last_archived_wal, failed_count, last_failed_wal, last_failed_time
FROM pg_stat_archiver;

14.3 Seuils d'alerte suggérés

Indicateur	Avertissement	Critique
Âge XID (age(datfrozenxid))	500 M	1 G
Retard de réplication	30 s / 1 Go	5 min / 10 Go ou standby déconnecté
Slot inactif ou wal_status unreserved	Inactif > 1 h	unreserved / lost
Connexions / max_connections	75 %	90 %
Transaction la plus longue	15 min	1 h
Échecs d'archivage	1 échec récent	Échecs continus > 15 min
Espace disque pg_wal / données	75 %	90 %
Dernière sauvegarde complète réussie	> 8 jours	> 15 jours

Collecte : postgres_exporter (Prometheus) ou le template PostgreSQL de Zabbix (agent 2), avec un rôle dédié membre de pg_monitor. Tableaux de bord et alerting : voir Monitoring.

15. Partitionnement et PgBouncer

15.1 Partitionnement déclaratif

Utile pour les très grosses tables avec une clé naturelle de découpage (date) : purge instantanée par DROP/DETACH d'une partition au lieu d'un DELETE massif, VACUUM et index plus petits, élagage des partitions dans les plans. Ce n'est pas un outil de performance universel : une requête qui ne filtre pas sur la clé de partition parcourt toutes les partitions.

CREATE TABLE app.mesure (
    capteur_id  bigint      NOT NULL,
    horodatage  timestamptz NOT NULL,
    valeur      double precision,
    PRIMARY KEY (capteur_id, horodatage)        -- la cle de partition doit faire partie de la PK
) PARTITION BY RANGE (horodatage);

CREATE TABLE app.mesure_2026_10 PARTITION OF app.mesure
    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE TABLE app.mesure_2026_11 PARTITION OF app.mesure
    FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');
CREATE TABLE app.mesure_defaut PARTITION OF app.mesure DEFAULT;

-- Index cree sur la table mere = propage a toutes les partitions
CREATE INDEX ON app.mesure (horodatage);

-- Purge : detacher sans bloquer les lectures (PG 14+), puis archiver / supprimer
ALTER TABLE app.mesure DETACH PARTITION app.mesure_2025_10 CONCURRENTLY;
DROP TABLE app.mesure_2025_10;

-- Verifier l'elagage
EXPLAIN SELECT avg(valeur) FROM app.mesure WHERE horodatage >= '2026-10-15';

15.2 PgBouncer

pool_mode	Connexion serveur rendue au pool	Compatible avec
session	À la déconnexion du client	Tout ; faible gain
transaction	À la fin de chaque transaction	La plupart des applications web ; pas d'état de session (SET, verrous consultatifs de session, tables temporaires, LISTEN)
statement	Après chaque requête	Autocommit uniquement

# /etc/pgbouncer/pgbouncer.ini
[databases]
app_prod = host=10.0.10.11 port=5432 dbname=app_prod

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_user = pgbouncer_auth                     ; role qui lit les secrets via auth_query
auth_query = SELECT usename, passwd FROM pgbouncer.get_auth($1)
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 20                         ; connexions serveur par couple (base, utilisateur)
reserve_pool_size = 5
server_idle_timeout = 600
max_prepared_statements = 200                  ; requetes preparees en mode transaction (PgBouncer 1.21+)
client_tls_sslmode = require
client_tls_cert_file = /etc/pgbouncer/tls/server.crt
client_tls_key_file  = /etc/pgbouncer/tls/server.key
server_tls_sslmode = verify-full
server_tls_ca_file = /etc/pgbouncer/tls/ca.crt
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats

# Console d'administration
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer
SHOW POOLS;      -- cl_waiting > 0 durablement = pool trop petit ou requetes trop longues
SHOW STATS;
SHOW SERVERS;
RELOAD;
PAUSE app_prod;  -- maintenance : vide proprement les transactions en cours
RESUME app_prod;

16. Dépannage et checklist de production

16.1 Symptôme, cause, correction

Symptôme	Cause probable	Correction
FATAL: no pg_hba.conf entry for host ...	Aucune ligne ne correspond (adresse, base, utilisateur, ou SSL requis)	Ajouter la ligne au bon endroit (ordre !), pg_reload_conf(), vérifier pg_hba_file_rules
FATAL: sorry, too many clients already	max_connections atteint, souvent des sessions inactives ou une fuite de connexions	Analyser pg_stat_activity par application, PgBouncer, idle_session_timeout
Disque plein sur pg_wal	Archivage en échec, slot inactif, checkpoints bloqués	pg_stat_archiver, pg_replication_slots ; corriger l'archivage ou supprimer le slot. Ne jamais supprimer de fichiers dans pg_wal à la main
Table qui grossit sans cesse malgré l'autovacuum	Horizon xmin retenu (transaction longue, slot, prepared)	Requête de la section 6.3, puis pg_repack si nécessaire
database is not accepting commands to avoid wraparound data loss	Gel en retard	Section 6.5 : libérer l'horizon, VACUUM ciblé
Requête soudain lente après ANALYZE, upgrade ou chargement massif	Statistiques absentes / changement de plan	ANALYZE, comparer les plans, statistiques étendues
canceling statement due to conflict with recovery	Requête longue sur standby	Section 10.4
could not serialize access / deadlock détecté	Concurrence applicative	Ordre d'accès cohérent aux lignes, retry applicatif, transactions courtes
ALTER TABLE qui fige toute l'application	Verrou ACCESS EXCLUSIVE en attente derrière une longue transaction, bloquant toutes les requêtes suivantes	SET lock_timeout = '5s'; avant chaque migration et retry ; vérifier les longues transactions avant
Standby qui ne rattrape plus : requested WAL segment has already been removed	Pas de slot ni d'archive accessible	Configurer restore_command vers l'archive, ou reconstruire le standby
Erreurs de checksum / invalid page in block	Corruption matérielle ou système de fichiers	Ne pas activer zero_damaged_pages à la légère ; pg_amcheck, restauration depuis sauvegarde / bascule sur standby, contrôle du matériel
OOM killer tue un backend, l'instance redémarre	work_mem x opérations x connexions, overcommit	Section 5.1, vm.overcommit_memory = 2, pooler

# Journaux (Debian)
sudo tail -f /var/log/postgresql/postgresql-17-main.log
journalctl -u postgresql@17-main -n 100

# Verification d'integrite (tables et index B-tree)
sudo -u postgres pg_amcheck --all --heapallindexed --jobs 4

16.2 Checklist de production

Installation et sécurité

• [ ] Version majeure supportée, dernière mineure appliquée, dépôt PGDG.
• [ ] Data checksums actifs ; système de fichiers et paramètres noyau (overcommit, swappiness, THP, huge pages) réglés.
• [ ] listen_addresses restreint, port filtré au pare-feu, pg_hba.conf en hostssl + scram-sha-256, ligne finale reject.
• [ ] Aucun mot de passe MD5 ; TLS avec sslmode=verify-full côté clients.
• [ ] Rôles séparés propriétaire / applicatif / lecture / supervision ; default privileges ; CREATE retiré sur public.
• [ ] Superusers limités ; pas d'application connectée en postgres.

Performance et maintenance

• [ ] shared_buffers, effective_cache_size, work_mem modéré, maintenance_work_mem, max_wal_size, random_page_cost adaptés.
• [ ] Autovacuum plus agressif que le défaut ; réglages par table pour les grosses tables.
• [ ] idle_in_transaction_session_timeout et statement_timeout par rôle.
• [ ] pg_stat_statements actif, log_min_duration_statement, log_lock_waits, log_temp_files.
• [ ] Revue trimestrielle des index inutilisés / invalides et du bloat.

Continuité

• [ ] pgBackRest (ou équivalent) : complète + différentielles, archivage WAL, dépôt chiffré, copie hors site.
• [ ] Restauration PITR testée automatiquement et chronométrée (RTO réel connu).
• [ ] Au moins un standby ; max_slot_wal_keep_size défini ; slots surveillés.
• [ ] Si HA : Patroni + etcd 3 nœuds + watchdog, switchover et perte de nœud testés.
• [ ] Procédure de montée de version écrite et répétée sur une copie.

Supervision

• [ ] Alertes : âge XID, retard de réplication, slots, connexions, longues transactions, archivage, disque, âge de la dernière sauvegarde.
• [ ] Journaux centralisés ; tableau de bord des requêtes les plus coûteuses.