PostgreSQL

Serveur de bases de données relationnelles open source, omniprésent dans les architectures JVM et microservices (Spring Boot, Micronaut, JPA, HikariCP).

Installation et connexion


# Debian / Ubuntu
apt install postgresql postgresql-client
systemctl start postgresql
systemctl enable postgresql
 
# Docker
docker run -d -p 5432:5432 --name postgres \
  -e POSTGRES_USER=app \
  -e POSTGRES_PASSWORD=secret \
  -e POSTGRES_DB=appdb \
  postgres:16-alpine
 
# Vérification de la disponibilité
pg_isready -h localhost -p 5432
 
# Connexion interactive (utilisateur par défaut = postgres)
psql -U postgres
 
# Connexion via URI
psql "postgresql://app:***@localhost:5432/appdb"

Commandes de base

Métacommendes psql


-- Lister toutes les tables de la base courante
\dt
 
-- Liste détaillée (taille, description)
\dt+
 
-- Structure d'une table (colonnes, types, contraintes)
\d noms_table
 
-- Lister les utilisateurs / rôles
\du
 
-- Lister les bases de données
\l
 
-- Exécuter un fichier SQL
\i /chemin/vers/schema.sql
 
-- Se connecter à une autre base
\c app_prod

Création de bases et d’utilisateurs


-- Créer une base
CREATE DATABASE app_prod
    WITH OWNER = app_user
    ENCODING = 'UTF8'
    LC_COLLATE = 'fr_FR.UTF-8'
    LC_CTYPE = 'fr_FR.UTF-8'
    TEMPLATE = template0;
 
-- Créer un utilisateur avec mot de passe
CREATE USER app_user WITH PASSWORD '***';
 
-- L'attribut LOGIN est implicite avec CREATE USER dans PostgreSQL >= 14
-- Pour les versions antérieures :
-- CREATE USER app_user WITH LOGIN PASSWORD '***';
 
-- Accorder les droits
GRANT CONNECT ON DATABASE app_prod TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT CREATE ON SCHEMA public TO app_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO app_user;
 
-- Appliquer les privilèges futurs automatiquement (recommandé)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT ALL PRIVILEGES ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT ALL PRIVILEGES ON SEQUENCES TO app_user;

Pitfall : GRANT ALL PRIVILEGES ON ALL TABLES ne concerne que les tables existantes. ALTER DEFAULT PRIVILEGES s’applique aux tables créées ensuite. Les deux sont nécessaires pour un jeu de droits complet.

URI JDBC (usage Spring Boot) : jdbc:postgresql://host:***@localhost:5432/appdb

Requêtes utiles

Recherche plein texte


-- Recherche textuelle simple
SELECT titre,
    ts_rank(to_tsvector('french', contenu), plainto_tsquery('french', 'java spring')) AS score
FROM articles
WHERE to_tsvector('french', contenu) @@ plainto_tsquery('french', 'java spring')
ORDER BY score DESC;

CTE (Common Table Expression)


WITH montant_mensuel AS (
    SELECT DATE_TRUNC('month', cree_a) AS mois,
           SUM(montant) AS total
    FROM transactions
    GROUP BY DATE_TRUNC('month', cree_a)
)
SELECT mois, total,
    LAG(total) OVER (ORDER BY mois) AS mois_precedent,
    ROUND(100.0 * (total - LAG(total) OVER (ORDER BY mois))
          / LAG(total) OVER (ORDER BY mois), 1) AS evolution_pct
FROM montant_mensuel;

Fonctions de fenêtrage


-- Numérotation par département
SELECT nom, departement, salaire,
    ROW_NUMBER() OVER (PARTITION BY departement ORDER BY salaire DESC) AS rang
FROM employes;
 
-- Moyenne glissante sur 3 mois
SELECT date, montant,
    AVG(montant) OVER (ORDER BY date
                       ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moyenne_3m
FROM revenus;
 
-- Décalage (compare avec la ligne précédente)
SELECT date, valeur,
    LAG(valeur) OVER (ORDER BY date) AS valeur_hier,
    valeur - LAG(valeur) OVER (ORDER BY date) AS delta
FROM mesures;

EXPLAIN ANALYZE


-- Analyse détaillée d'une requête (coût, temps réel, instructions)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.nom, COUNT(o.id) AS nb_commandes
FROM utilisateurs u
LEFT JOIN commandes o ON u.id = o.utilisateur_id
GROUP BY u.id, u.nom
HAVING COUNT(o.id) > 5;

Champs clés du résultat :

actual time : temps réel d’exécution (ms).
Buffers : accès disque (hit = cache, miss = disque).
Sort Method : type de tri (memory vs disk).
Hash Join vs Nested Loop : la jointure la plus adaptée dépend de la taille.

Configuration Spring Boot / HikariCP / JPA

application.yml


spring:
  datasource:
    url: jdbc:postgresql://host:***@localhost:5432/appdb
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      connection-timeout: 30000
  jpa:
    hibernate:
      ddl-auto: update
    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQLDialect
    open-in-view: false

Pitfall : dans HikariCP, minimum-idle doit être inférieur ou égal à maximum-pool-size. Une valeur minimum-idle supérieure au maximum provoque une erreur de démarrage.

Profile test — base H2 mémoire


spring:
  datasource:
    url: jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1
    driver-class-name: org.h2.Driver
  jpa:
    database-platform: org.hibernate.dialect.H2Dialect

Vérification depuis un conteneur


# Tester la connexion depuis un conteneur Spring
docker exec -it spring-app \
  pg_isready -h postgres -p 5432 -U app_user -d app_prod

Pitfall : ddl-auto: update est pratique en développement mais dangereux en production. Utiliser validate ou des migrations Flyway/Liquibase en production.

Maintenance

pg_dump / pg_restore


# Dump custom (recommandé — compression + restauration sélective)
pg_dump -U postgres -Fc --compress=9 -f backup.dump app_prod
 
# Dump SQL brut
pg_dump -U postgres -Fp -f backup.sql app_prod
 
# Restauration depuis un dump custom
pg_restore -U postgres -d app_prod -v backup.dump
 
# Restauration avec création préalable de la base
createdb app_prod
pg_restore -U postgres -d app_prod backup.dump
 
# Restauration sélective (une seule table)
pg_restore -U postgres -d app_prod -t noms_tables backup.dump

Le format Fc (custom) offre une compression supérieure et permet la restauration sélective (pg_restore -t). Le format Fd (directory) est indiqué pour les très gros dumps (parallélisation possible avec -j).

VACUUM et statistiques


-- Nettoyer les tuples morts (à faire régulièrement, surtout après gros UPDATE/DELETE)
VACUUM (VERBOSE, ANALYZE) articles;
 
-- VACUUM complet (verrouillage exclusif) — à éviter en production
VACUUM FULL articles;
 
-- Mettre à jour les statistiques pour le planificateur
ANALYZE articles;
 
-- Combiné (recommandé en maintenance planifiée)
VACUUM (VERBOSE, ANALYZE, HEAP_FLAGS) articles;

Pitfall : VACUUM FULL récupère l’espace disque mais bloque la table. À programmer en fenêtre de maintenance.

Statistiques des tables


-- Taille physique (données + index)
SELECT pg_size_pretty(pg_total_relation_size('articles'));
 
-- Taille des données uniquement
SELECT pg_size_pretty(pg_relation_size('articles'));
 
-- Nombre de lignes estimé
SELECT reltuples::bigint AS est_lignes
FROM pg_class WHERE relname = 'articles';
 
-- Tables les plus grosses
SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) AS taille
FROM pg_class WHERE relkind = 'r'
ORDER BY pg_total_relation_size(oid) DESC
LIMIT 10;

Statut des connexions


-- Connexions actives par base
SELECT datname, count(*)
FROM pg_stat_activity
GROUP BY datname
ORDER BY count DESC;
 
-- Connexions par état (idle, active, idle in transaction)
SELECT state, count(*) FROM pg_stat_activity
GROUP BY state;

Extensions courantes


-- UUID aléatoires
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v4();
 
-- UUID déterministe (v5, basé sur un namespace + nom)
SELECT uuid_generate_v5(uuid_ns_url(), 'mon-nom');
 
-- Trigrammes (recherche texte rapide)
CREATE EXTENSION IF NOT EXISTS pg_trgm;
 
-- pg_cron : planification de requêtes depuis PostgreSQL
CREATE EXTENSION IF NOT EXISTS pg_cron;
SELECT cron.schedule('cleanup', '0 3 * * *',
    'DELETE FROM sessions WHERE cree_a < now() - interval 30 days');

PostGIS (géolocalisation)


CREATE EXTENSION postgis;
 
-- Ajouter une colonne géométrique
SELECT AddGeometryColumn('lieux', 'geom', 4326, 'POINT', 2);
 
-- Requêtes spatiales
SELECT nom, ST_Distance(geom, ST_GeomFromText('POINT(2.3522 48.8566)', 4326)) AS distance_m
FROM lieux
ORDER BY distance_m
LIMIT 5;

Dépannage

Queries bloquantes


-- Lister les requêtes qui attendent un lock
SELECT a.pid, a.state, a.query, a.wait_event_type, a.wait_event,
       a.query_start, now() - a.query_start AS duree,
       b.relname
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
JOIN pg_class b ON b.oid = l.relation
WHERE l.granted = false;

Verrous détenteurs


-- Qui bloque qui ?
SELECT
    blocked.pid  AS bloque_pid,
    blocked.query AS blocked_query,
    blocking.pid  AS bloquant_pid,
    blocking.query AS blocking_query,
    blocking.state AS bloquant_state
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks bk ON bk.locktype = bl.locktype
                 AND bk.relation = bl.relation
                 AND bk.pid != bl.pid
JOIN pg_stat_activity blocking ON blocking.pid = bk.pid
WHERE blocked.pid != pg_backend_pid();

Terminer une requête


-- Annulation propre (SIGTERM)
SELECT pg_terminate_backend(<pid>);
 
-- Annulation immédiate (SIGINT)
SELECT pg_cancel_backend(<pid>);
 
-- Option : régler le timeout maximal d'une transaction
ALTER SYSTEM SET statement_timeout = '60s';
SELECT pg_reload_conf();

Tables indisponibles (frozen)


-- Vérifier l'âge des transactions (indique si un VACUUM a été oublié)
SELECT relname, age(relfrozenxid) AS age_transaction
FROM pg_class WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC
LIMIT 10;
 
-- Si âge > 1 milliard, forcer le vacuum
VACUUM (FREEZE) nom_table;

Références

Documentation officielle PostgreSQL
PgTune — recommandations de tuning automatique
PostgreSQL Exercises — exercices pratiques SQL PostgreSQL
10 Minute Postgres Setup — guide de démarrage