// formation · Fondamentaux en informatique
Bases de données et SQL avec PostgreSQL
- 11 chapitres
- ≈ 15 min de lecture
- Cours 1 / 8
- Accès libre
PostgreSQL est un système de gestion de bases de données relationnelles (SGBDR) libre, développé depuis plus de trente-cinq ans par une communauté internationale. On le retrouve derrière des outils de supervision, de gestion de parc, des ERP, des logiciels métiers et une grande partie des applications web. Ce cours donne aux équipes IT les bases nécessaires pour installer une instance, concevoir un schéma simple, écrire des requêtes SQL, diagnostiquer une lenteur, sécuriser les accès et sauvegarder correctement. Les sept travaux pratiques construisent, sur Debian 13, la base d’inventaire d’un parc informatique, de l’installation jusqu’à la sauvegarde automatisée.
À qui s’adresse ce cours : techniciens et administrateurs systèmes et réseau, DSI, RSSI, stagiaires et équipes d’exploitation, dans une petite structure comme dans un grand groupe ou une administration, qui doivent administrer ou dépanner des bases PostgreSQL sans en être les spécialistes. Prérequis : être à l’aise avec la ligne de commande Linux et l’édition de fichiers de configuration.
Pourquoi une base de données relationnelle
Un tableur ou un fichier CSV suffit tant qu’une seule personne travaille sur quelques centaines de lignes. Dès que plusieurs personnes ou applications lisent et modifient les mêmes données en même temps, que les volumes grossissent ou que la cohérence devient critique, il faut un SGBD : un logiciel qui stocke les données, contrôle leur validité, gère les accès concurrents, applique des droits et garantit qu’une opération interrompue ne laisse pas les données dans un état incohérent.
Le modèle relationnel organise les données en tables reliées entre elles par des clés. Il s’oppose à des modèles dits NoSQL (documents, clé-valeur, graphes) adaptés à d’autres usages. Pour la plupart des applications de gestion, il reste le choix par défaut, car il garantit l’intégrité des données et se manipule avec un langage standard, SQL. Dans le paysage libre, PostgreSQL côtoie MySQL et MariaDB, que présente notre article MariaDB, la base de données open source ; il se distingue par sa conformité au standard SQL, la richesse de ses types et son extensibilité.
Pour une équipe IT, la question n’est pas seulement « faut-il une base ? » mais « qui l’exploite ? ». Dans une petite structure, l’administrateur système est souvent aussi l’administrateur de bases ; dans un grand groupe, une équipe dédiée (DBA) gère les instances, mais les équipes systèmes, réseau et sécurité doivent comprendre leur fonctionnement pour superviser, sauvegarder, auditer et dépanner.
PostgreSQL : projet, licence et versions
PostgreSQL est publié sous la licence PostgreSQL, proche des licences BSD et MIT : utilisation, modification et redistribution libres, y compris dans des produits commerciaux. Le projet n’appartient à aucun éditeur ; plusieurs entreprises y contribuent et proposent du support.
Le rythme des versions est régulier. Une version majeure sort chaque année : PostgreSQL 19 est sortie le 19 novembre 2026, la 18 en septembre 2025, la 17 en septembre 2024. Chaque version majeure est maintenue cinq ans, avec des versions mineures (correctifs de bogues et de sécurité) publiées au moins tous les trois mois. Une version mineure (17.11 vers 17.12, par exemple) s’installe sans changer le format des données ; une version majeure (17 vers 18) demande une migration. En novembre 2026, les versions 15 à 19 sont maintenues ; la 14 ne l’est plus depuis le 12 novembre 2026.
Côté installation, deux sources officielles cohabitent sous Debian et Ubuntu : les dépôts de la distribution (Debian 13 fournit PostgreSQL 17), stables et mis à jour par l’équipe de sécurité de la distribution, et le dépôt du projet, apt.postgresql.org (PGDG), qui propose toutes les versions maintenues. Les distributions de la famille Red Hat disposent d’un dépôt équivalent. Le choix se fait selon la version exigée par l’application et la politique de mise à jour de l’organisation.
Bonne pratique : tenez un inventaire des instances avec leur version majeure et la date de fin de maintenance correspondante. Une base oubliée sur une version qui ne reçoit plus de correctifs est une vulnérabilité durable.
Architecture d’une instance
Le vocabulaire de PostgreSQL est parfois déroutant. Un cluster de bases de données désigne une instance : un ensemble de bases gérées par un même serveur, partageant un répertoire de données, un port (5432 par défaut) et une configuration. Rien à voir avec une grappe de serveurs. Une instance contient plusieurs bases, isolées les unes des autres ; une connexion se fait toujours à une base précise. Chaque base contient des schémas (espaces de noms, public par défaut), qui contiennent eux-mêmes les tables, vues, index et fonctions. Les rôles (utilisateurs et groupes) sont, eux, communs à toute l’instance.
Côté processus, un processus principal (postmaster) écoute les connexions et crée un processus dédié par connexion. Des processus d’arrière-plan assurent l’écriture sur disque, les points de contrôle, l’autovacuum et l’écriture des journaux. Ce modèle explique pourquoi le nombre de connexions simultanées (paramètre max_connections, 100 par défaut) doit rester raisonnable : au-delà de quelques centaines, on place un gestionnaire de connexions (pooler) entre les applications et la base.
Toute modification est d’abord écrite dans le journal de transactions (WAL, Write-Ahead Log) avant d’être appliquée aux fichiers de données. En cas d’arrêt brutal, le serveur rejoue le journal au redémarrage pour retrouver un état cohérent. Le même journal sert aussi à la réplication et à la restauration à un instant donné.
Sur Debian, les emplacements sont les suivants : configuration dans /etc/postgresql/17/main/ (postgresql.conf pour les paramètres, pg_hba.conf pour l’authentification), données dans /var/lib/postgresql/17/main, journaux du serveur dans /var/log/postgresql/. Le paquet postgresql-common ajoute des outils propres à Debian : pg_lsclusters pour lister les instances, pg_ctlcluster pour les piloter, pg_upgradecluster pour les migrer. Sur une autre distribution, les chemins diffèrent : la commande SQL SHOW config_file; donne toujours la réponse.
Le modèle relationnel : tables, types et clés
Une table est un ensemble de lignes ayant toutes les mêmes colonnes. Chaque colonne a un type qui limite les valeurs acceptées. Choisir le bon type est la première protection des données :
integeretbigintpour les entiers,numericpour les montants exacts (jamais de type flottant pour de l’argent) ;textpour les chaînes,varchar(n)si une longueur maximale a un sens métier ;boolean,dateettimestamptz(horodatage avec fuseau, à préférer àtimestamppour enregistrer un instant) ;- des types spécialisés :
inetetcidrpour les adresses et réseaux IP,uuid,jsonbpour des documents JSON indexables.
La clé primaire identifie chaque ligne de façon unique ; on utilise le plus souvent un identifiant numérique généré par la base, déclaré GENERATED ALWAYS AS IDENTITY (forme standard SQL, préférable à l’ancien pseudo-type serial). La clé étrangère relie une ligne à une ligne d’une autre table et interdit les références orphelines : un équipement ne peut pas pointer vers un site inexistant. Les contraintes NOT NULL, UNIQUE et CHECK complètent le dispositif.
La normalisation consiste à ne stocker chaque information qu’une fois. Si le nom de la ville d’un site est recopié sur chaque équipement, un déménagement oblige à modifier des centaines de lignes, avec le risque d’en oublier. On crée donc une table sites et chaque équipement y fait référence. Le schéma du laboratoire suit ce principe : sites (1) ← equipements (n) ← interventions (n), chaque flèche représentant une clé étrangère.
À retenir : une règle de gestion écrite dans le schéma (type, contrainte, clé étrangère) s’applique à toutes les applications et à tous les utilisateurs. Une règle codée seulement dans une application peut être contournée par un import, un script ou une autre application.
Le langage SQL et l’outil psql
SQL (Structured Query Language) est le langage normalisé des bases relationnelles. Ses instructions se répartissent en familles :
- DDL (définition) :
CREATE,ALTER,DROPpour les tables, index et vues ; - DML (manipulation) :
SELECT,INSERT,UPDATE,DELETE; - DCL (contrôle) :
GRANT,REVOKEpour les privilèges ; - TCL (transactions) :
BEGIN,COMMIT,ROLLBACK.
Particularité utile de PostgreSQL : la plupart des instructions DDL sont elles-mêmes transactionnelles. On peut créer une table et modifier une colonne dans une transaction, puis tout annuler.
L’outil d’administration de référence est psql, client en ligne de commande. Outre le SQL, il accepte des méta-commandes commençant par une barre oblique inverse, exécutées par le client : \l (bases), \c (changer de base), \dt (tables), \d nom (structure d’un objet), \du (rôles), \dp (privilèges), \x (affichage vertical), \timing (durée des requêtes), \copy (import et export de fichiers côté client). L’invite indique la base courante et se termine par # pour un superutilisateur, > sinon.
sudo -u postgres psql # administrateur, par socket local
psql -h localhost -U parc_admin -d parc # rôle nominatif, en TCP avec mot de passe
psql "host=192.168.56.10 dbname=parc user=lecteur_bi sslmode=require"
Des outils graphiques comme pgAdmin ou DBeaver rendent les mêmes services, mais psql est disponible partout, scriptable et suffisant pour l’essentiel de l’exploitation.
Interroger les données : filtres, jointures et agrégats
La requête SELECT se lit dans un ordre logique précis : la base assemble d’abord les tables (FROM et JOIN), filtre les lignes (WHERE), les regroupe (GROUP BY), filtre les groupes (HAVING), calcule les colonnes affichées, puis trie (ORDER BY) et limite (LIMIT). Comprendre cet ordre évite la plupart des erreurs, par exemple vouloir filtrer sur un agrégat dans un WHERE.
SELECT s.nom AS site, count(i.id) AS nb, coalesce(sum(i.duree_min), 0) AS minutes
FROM sites s
JOIN equipements e ON e.site_id = s.id
LEFT JOIN interventions i ON i.equipement_id = e.id
WHERE e.actif
GROUP BY s.nom
HAVING count(i.id) > 0
ORDER BY minutes DESC;
Les jointures associent les lignes de plusieurs tables. La jointure interne (JOIN) ne garde que les lignes qui ont une correspondance des deux côtés ; la jointure externe gauche (LEFT JOIN) garde toutes les lignes de la table de gauche et complète par NULL, ce qui permet de trouver les absents (équipements sans intervention, comptes sans connexion récente…). La valeur NULL signifie « inconnu » : elle se teste avec IS NULL, jamais avec =.
Les agrégats (count, sum, avg, min, max) calculent un résultat par groupe. Une vue enregistre une requête sous un nom et s’interroge comme une table : elle simplifie le travail des utilisateurs et permet de leur donner accès à une présentation des données plutôt qu’aux tables.
Sécurité : dans une application ou un script, ne construisez jamais une requête en concaténant une valeur saisie par un utilisateur. Les requêtes paramétrées, proposées par tous les pilotes, sont la seule protection fiable contre l’injection SQL.
Transactions, concurrence et MVCC
Une transaction regroupe plusieurs opérations qui réussissent ou échouent ensemble. Elle respecte les propriétés ACID : atomicité (tout ou rien), cohérence (les contraintes sont respectées à la fin), isolation (les transactions concurrentes ne se perturbent pas au-delà de ce que prévoit le niveau d’isolation) et durabilité (une fois validée, la modification survit à une panne grâce au WAL).
BEGIN;
UPDATE equipements SET site_id = 2 WHERE nom_hote = 'pc-compta-01';
-- contrôle du nombre de lignes modifiées, puis :
COMMIT; -- ou ROLLBACK; pour tout annuler
Par défaut, psql et la plupart des pilotes sont en mode autocommit : chaque instruction isolée est validée immédiatement. Le niveau d’isolation par défaut de PostgreSQL est Read Committed : chaque instruction voit les données validées au moment où elle commence. Les niveaux REPEATABLE READ et SERIALIZABLE offrent des garanties plus fortes, au prix d’erreurs de sérialisation que l’application doit savoir rejouer.
Pour gérer la concurrence, PostgreSQL utilise le MVCC (contrôle de concurrence multiversion) : une mise à jour ne modifie pas la ligne en place, elle crée une nouvelle version et marque l’ancienne comme périmée. Conséquence pratique très appréciable : les lectures ne bloquent pas les écritures, et les écritures ne bloquent pas les lectures. Contrepartie : les anciennes versions occupent de l’espace jusqu’à leur nettoyage par VACUUM, exécuté automatiquement par l’autovacuum. Une transaction restée ouverte des heures (session oubliée, application défaillante) empêche ce nettoyage et fait gonfler les tables : c’est un point de supervision important. PostgreSQL 19 ajoute la commande REPACK, qui peut reconstruire une table gonflée sans bloquer lectures et écritures avec l’option CONCURRENTLY.
Index et performances
Sans index, une recherche lit toute la table (parcours séquentiel). Un index est une structure annexe, triée, qui permet de retrouver rapidement les lignes correspondant à une valeur ou à une plage. Le type par défaut, B-tree, couvre l’égalité, les comparaisons et les tris ; d’autres types existent pour des besoins particuliers (GIN pour jsonb et la recherche plein texte, GiST pour des données géométriques ou réseau, BRIN pour de très grandes tables naturellement ordonnées).
Un index a un coût : il ralentit chaque écriture et occupe de l’espace disque. On indexe donc à partir des requêtes réellement exécutées, en commençant par les colonnes utilisées dans les filtres et les jointures. Attention : PostgreSQL crée automatiquement un index pour les clés primaires et les contraintes d’unicité, pas pour les colonnes de clés étrangères. Un index composite (plusieurs colonnes) se construit en plaçant d’abord les colonnes testées par égalité, puis celle testée par plage.
L’outil de diagnostic est EXPLAIN, qui affiche le plan d’exécution choisi par le planificateur. Avec EXPLAIN ANALYZE, la requête est exécutée et le plan indique les temps et nombres de lignes réels, à comparer avec les estimations. Des estimations très éloignées de la réalité signalent souvent des statistiques périmées, que la commande ANALYZE met à jour.
EXPLAIN (ANALYZE, BUFFERS)
SELECT date_intervention, technicien FROM interventions
WHERE equipement_id = 3 AND date_intervention >= now() - interval '7 days';
Pour trouver les requêtes à optimiser, deux outils complémentaires : le paramètre log_min_duration_statement, qui journalise les requêtes dépassant une durée, et l’extension pg_stat_statements, qui agrège les statistiques de toutes les requêtes (elle doit être chargée par shared_preload_libraries, ce qui impose un redémarrage). En production, un index se crée avec CREATE INDEX CONCURRENTLY pour ne pas bloquer les écritures. Le réglage mémoire (notamment shared_buffers et work_mem) vient en second : un index manquant coûte bien plus qu’un paramètre mal ajusté.
Sécurité : rôles, privilèges et authentification
Dans PostgreSQL, utilisateurs et groupes sont des rôles. Un rôle avec l’attribut LOGIN peut se connecter ; un rôle sans LOGIN sert de groupe. On accorde les privilèges aux groupes, puis on y rattache les comptes. Les droits se donnent avec GRANT et se retirent avec REVOKE, à plusieurs niveaux : base (CONNECT), schéma (USAGE, CREATE), table (SELECT, INSERT, UPDATE, DELETE…), voire colonne. ALTER DEFAULT PRIVILEGES règle les droits des objets qui seront créés plus tard. Des rôles prédéfinis facilitent les cas courants, par exemple pg_read_all_data pour un accès en lecture à toutes les données, ou pg_monitor pour la supervision.
Quelques réglages par défaut méritent d’être connus. Depuis PostgreSQL 15, le schéma public appartient au propriétaire de la base et les autres rôles n’y ont plus le droit CREATE. En revanche, tout rôle peut se connecter à toute base tant qu’on n’a pas retiré CONNECT à PUBLIC. Un modèle de rôles simple et robuste, valable pour une petite base comme pour un parc important :
- un rôle propriétaire par application, sans connexion distante, utilisé uniquement pour les évolutions de schéma ;
- un compte applicatif limité aux opérations nécessaires ;
- un groupe en lecture seule pour le reporting et les audits ;
- des comptes nominatifs pour les administrateurs, le superutilisateur
postgresn’étant utilisé qu’en local.
L’authentification est décidée par le fichier pg_hba.conf, lu de haut en bas : pour chaque connexion, la première ligne correspondant au type (local, host, hostssl), à la base, au rôle et à l’adresse source s’applique. Les méthodes courantes sont peer (connexion locale, nom du compte système), scram-sha-256 (mot de passe, à privilégier), ldap ou gss pour s’appuyer sur l’annuaire de l’entreprise. La méthode md5 est dépréciée depuis PostgreSQL 18 et trust (aucune vérification) n’a pas sa place hors d’un laboratoire. Le paramètre listen_addresses (par défaut localhost) détermine les interfaces d’écoute ; on y ajoute seulement l’interface nécessaire, et l’on chiffre les connexions réseau avec TLS (type hostssl et sslmode=verify-full côté client avec un certificat de confiance).
Bonnes pratiques de durcissement : pas de port 5432 exposé sur Internet ; filtrage par pare-feu et par
pg_hba.conf; un compte par usage ; journalisation des connexions (log_connections) ; mises à jour mineures appliquées rapidement ; revue périodique des rôles et de leurs droits. Pour un accès distant ponctuel, un tunnel SSH ou un VPN évite d’ouvrir la base au réseau.
Sauvegarde, restauration et haute disponibilité
Deux familles de sauvegardes coexistent. La sauvegarde logique, avec pg_dump, exporte une base sous forme d’instructions SQL ou d’archive : elle est portable entre versions et architectures, permet de restaurer une seule table, mais ne ramène qu’à l’instant où elle a commencé. Elle ne contient pas les rôles, qu’on exporte avec pg_dumpall --globals-only. Le format personnalisé (-Fc) ou répertoire (-Fd, qui autorise une sauvegarde parallèle avec -j) se restaure avec pg_restore.
La sauvegarde physique, avec pg_basebackup ou des outils spécialisés, copie les fichiers de l’instance ; combinée à l’archivage continu des WAL, elle permet la restauration à un instant donné (PITR), par exemple juste avant une suppression accidentelle survenue à 15 h 42. Elle concerne toute l’instance et exige la même version majeure à la restauration.
pg_dump -Fc -d parc -f parc.dump # sauvegarde logique d'une base
pg_dumpall --globals-only -f globals.sql # rôles et tablespaces
pg_restore -d parc_restore parc.dump # restauration dans une base vide
pg_restore -d parc --data-only -t interventions parc.dump # une seule table
Le choix dépend de deux objectifs à fixer avec les métiers : la perte de données maximale admissible (RPO) et la durée maximale d’interruption (RTO). Dans une petite structure, un pg_dump nocturne copié hors site, testé chaque mois, est un bon point de départ. Dans une grande entreprise, on combine sauvegardes physiques, archivage des WAL, réplication en continu vers un ou plusieurs serveurs secondaires (qui peuvent aussi servir les lectures) et une bascule automatisée par un gestionnaire de haute disponibilité. Dans tous les cas, les copies suivent la règle 3-2-1, détaillée dans notre article Restic : sauvegardes chiffrées, dédupliquées et vérifiables, et au moins une copie est hors ligne ou immuable pour résister à un rançongiciel.
À retenir : la réplication n’est pas une sauvegarde. Une suppression accidentelle ou un chiffrement malveillant est répliqué en quelques millisecondes sur le serveur secondaire.
Exploiter PostgreSQL au quotidien
Une fois la base en production, l’exploitation repose sur quelques routines :
- Mises à jour mineures : à appliquer à chaque publication (au moins trimestrielle), par le gestionnaire de paquets, suivies d’un redémarrage de l’instance. Elles corrigent des failles de sécurité.
- Mises à jour majeures : à planifier avant la fin de maintenance, avec
pg_upgrade(oupg_upgradeclustersous Debian), ou par sauvegarde et restauration logiques, toujours testées sur une copie. - Supervision : disponibilité, espace disque (un serveur plein s’arrête), nombre de connexions, transactions longues, réplication, âge de la dernière sauvegarde réussie. La vue
pg_stat_activitymontre les sessions en cours et ce qu’elles exécutent ; le rôlepg_monitordonne à l’outil de supervision les droits de lecture nécessaires. - Journaux : erreurs, connexions refusées, requêtes lentes, à centraliser avec les autres journaux du système d’information.
- Revues périodiques : rôles et privilèges, règles de
pg_hba.conf, index inutilisés (pg_stat_user_indexes), croissance des tables.
SELECT pid, usename, client_addr, state, now() - xact_start AS duree_tx, left(query, 60)
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY duree_tx DESC NULLS LAST;
La taille de l’organisation change surtout l’outillage. Une petite équipe peut gérer quelques instances à la main avec une procédure écrite et un script de sauvegarde. Une grande DSI industrialise : déploiement par Ansible ou Terraform, configuration standardisée, rôles reliés à l’annuaire, supervision centralisée, haute disponibilité et tests de restauration automatisés. Les notions vues dans ce cours restent les mêmes. Notre article PostgreSQL, système de gestion de base de données relationnelle complète cette présentation par un panorama des usages et des extensions.
Check-list d’une instance saine : version maintenue et mises à jour mineures à jour ; écoute limitée aux interfaces utiles ;
pg_hba.confrestrictif enscram-sha-256et TLS ; un compte par usage ; sauvegarde quotidienne externalisée et restauration testée ; supervision de l’espace disque, des connexions et des transactions longues.
Évaluation : Bases de données et SQL avec PostgreSQL
Répondez aux questions puis vérifiez votre score. Une seule bonne réponse par question.
Pour aller plus loin sur le blog : PostgreSQL, système de gestion de base de données relationnelle · MariaDB, la base de données open source · Kopia : sauvegardes chiffrées et immuables
// travaux pratiques
7 TP pour mettre en pratique
- TP01 - Installer PostgreSQL et se connecter avec psql
- TP02 - Créer une base, un rôle et des tables avec contraintes
- TP03 - Insérer, modifier et importer des données en transaction
- TP04 - Interroger les données : filtres, jointures et agrégats
- TP05 - Index et plans d’exécution avec EXPLAIN
- TP06 - Droits d’accès, rôles applicatifs et connexions distantes
- TP07 - Sauvegarder et restaurer avec pg_dump et pg_restore
// formation sur mesure
Former votre équipe sur ce sujet ?
Azur Secure anime des sessions intra-entreprise, à distance ou dans vos locaux sur la Côte d'Azur, adaptées au niveau de vos équipes.