Vous trouverez en ligne les différentes versions complètes de ce document.

Vous n’avez pas le droit d’utiliser cette création à des fins commerciales.

Si vous modifiez, transformez ou adaptez cette création, vous n’avez le droit de distribuer la création qui en résulte que sous un contrat identique à celui-ci.

Vous devez citer le nom de l’auteur original de la manière indiquée par l’auteur de l’œuvre ou le titulaire des droits qui vous confère cette autorisation (mais pas d’une manière qui suggérerait qu’ils vous soutiennent ou approuvent votre utilisation de l’œuvre). À chaque réutilisation ou distribution de cette création, vous devez faire apparaître clairement au public les conditions contractuelles de sa mise à disposition. La meilleure manière de les indiquer est un lien vers cette page web. Chacune de ces conditions peut être levée si vous obtenez l’autorisation du titulaire des droits sur cette œuvre. Rien dans ce contrat ne diminue ou ne restreint le droit moral de l’auteur ou des auteurs.

Le texte complet de la licence est disponible sur http://creativecommons.org/licenses/by-nc-sa/2.0/fr/legalcode

Cela inclut les diapositives, les manuels eux-mêmes et les travaux pratiques. Cette formation peut également contenir quelques images et schémas dont la redistribution est soumise à des licences différentes qui sont alors précisées.

Sur les versions précédentes susceptibles d’être encore rencontrées en production, seuls quelques points très importants sont évoqués, en plus éventuellement de quelques éléments historiques.

Sauf précision contraire, le système d’exploitation utilisé est Linux.

Ce module propose un tour rapide des fonctionnalités principales du moteur : ACID, MVCC, transactions, journaux de transactions… ainsi que des objets SQL gérés (schémas, index, tablespaces, triggers…). Ce rappel des concepts de base permet d’avancer plus facilement lors des modules suivants.

Cette partie couvre les différentes fonctionnalités d’un moteur de bases de données. Il ne s’agit pas d’aller dans le détail de chacune, mais de donner une idée de ce qui est disponible. Les modules suivants de cette formation et des autres formations détaillent certaines de ces fonctionnalités.

La dernière version du standard SQL est SQL:2023. À ce jour, aucun SGBD ne la supporte complètement, mais :

• PostgreSQL progresse et s’en approche au maximum, au fil des versions ;
• la majorité de la norme est supportée, parfois avec des syntaxes différentes ;
• PostgreSQL est le SGDB le plus respectueux du standard.

Les propriétés ACID sont le fondement même de toute bonne base de données. Il s’agit de l’acronyme des quatre règles que toute transaction (c’est-à-dire une suite d’ordres modifiant les données) doit respecter :

• A : Une transaction est appliquée en « tout ou rien ».
• C : Une transaction amène la base d’un état stable à un autre.
• I : Les transactions n’agissent pas les unes sur les autres.
• D : Une transaction validée sera conservée de manière permanente.

Les bases de données relationnelles les plus courantes depuis des décennies (PostgreSQL bien sûr, mais aussi Oracle, MySQL, SQL Server, SQLite…) se basent sur ces principes, même si elles font chacune des compromis différents suivant leurs cas d’usage, les compromis acceptés à chaque époque avec la performance et les versions.

Atomicité :

Une transaction doit être exécutée entièrement ou pas du tout, et surtout pas partiellement, même si elle est longue et complexe, même en cas d’incident majeur sur la base de données. L’exemple basique est une transaction bancaire : le montant d’un virement doit être sur un compte ou un autre, et en cas de problème ne pas disparaître ou apparaître en double. Ce principe garantit que les données modifiées par des transactions valides seront toujours visibles dans un état stable, et évite nombre de problèmes fonctionnels comme techniques.

Cohérence :

Un état cohérent respecte les règles de validité définies dans le modèle, c’est-à-dire les contraintes définies dans le modèle : types, plages de valeurs admissibles, unicité, liens entre tables (clés étrangères), etc. Le non-respect de ces règles par l’applicatif entraîne une erreur et un rejet de la transaction.

Isolation :

Des transactions simultanées doivent agir comme si elles étaient seules sur la base. Surtout, elles ne voient pas les données non validées des autres transactions. Ainsi une transaction peut travailler sur un état stable et fixe, et durer assez longtemps sans risque de gêner les autres transactions.

Il existe plusieurs « niveaux d’isolation » pour définir précisément le comportement en cas de lectures ou écritures simultanées sur les mêmes données et pour arbitrer avec les contraintes de performances ; le niveau le plus contraignant exige que tout se passe comme si toutes les transactions se déroulaient successivement.

Durabilité :

Une fois une transaction validée par le serveur (typiquement : COMMIT ne retourne pas d’erreur, ce qui valide la cohérence et l’enregistrement physique), l’utilisateur doit avoir la garantie que la donnée ne sera pas perdue ; du moins jusqu’à ce qu’il décide de la modifier à nouveau. Cette garantie doit valoir même en cas d’événément catastrophique : plantage de la base, perte d’un disque… C’est donc au serveur de s’assurer autant que possible que les différents éléments (disque, système d’exploitation…) ont bien rempli leur office. C’est à l’humain d’arbitrer entre le niveau de criticité requis et les contraintes de performances et de ressources adéquates (et fiables) à fournir à la base de données.

NoSQL :

À l’inverse, les outils de la mouvance (« NoSQL », par exemple MongoDB ou Cassandra), ne fournissent pas les garanties ACID. C’est le cas de la plupart des bases non-relationnelles, qui reprennent le modèle BASE (Basically Available, Soft State, Eventually Consistent, soit succintement : disponibilité d’abord ; incohérence possible entre les réplicas ; cohérence… à terme, après un délai). Un intérêt est de débarasser le développeur de certaines lourdeurs apparentes liées à la modélisation assez stricte d’une base de données relationnelle. Cependant, la plupart des applications ont d’abord besoin des garanties de sécurité et cohérence qu’offrent un moteur transactionnel classique, et la décision d’utiliser un système ne les garantissant pas ne doit pas être prise à la légère ; sans parler d’autres critères comme la fragmentation du domaine par rapport au monde relationnel et son SQL (à peu près) standardisé. Avec le temps, les moteurs transactionnels ont acquis des fonctionnalités qui faisaient l’intérêt des bases NoSQL (en premier lieu la facilité de réplication et le stockage de JSON), et ces dernières ont tenté d’intégrer un peu plus de sécurité dans leur modèle.

MVCC (Multi Version Concurrency Control) est le mécanisme interne de PostgreSQL utilisé pour garantir la cohérence des données lorsque plusieurs processus accèdent simultanément à la même table.

MVCC maintient toutes les versions nécessaires de chaque ligne, ainsi chaque transaction voit une image figée de la base (appelée snapshot). Cette image correspond à l’état de la base lors du démarrage de la requête ou de la transaction, suivant le niveau d’isolation demandé par l’utilisateur à PostgreSQL pour la transaction.

MVCC fluidifie les mises à jour en évitant les blocages trop contraignants (verrous sur UPDATE) entre sessions et par conséquent de meilleures performances en contexte transactionnel.

C’est notamment MVCC qui permet d’exporter facilement une base à chaud et d’obtenir un export cohérent alors même que plusieurs utilisateurs sont potentiellement en train de modifier des données dans la base.

C’est la qualité de l’implémentation de ce système qui fait de PostgreSQL un des meilleurs SGBD au monde : chaque transaction travaille dans son image de la base, cohérent du début à la fin de ses opérations. Par ailleurs, les écrivains ne bloquent pas les lecteurs et les lecteurs ne bloquent pas les écrivains, contrairement aux SGBD s’appuyant sur des verrous de lignes. Cela assure de meilleures performances, moins de contention et un fonctionnement plus fluide des outils s’appuyant sur PostgreSQL.

L’exemple habituel et très connu des transactions est celui du virement d’une somme d’argent du compte de Bob vers le compte d’Alice. Le total du compte de Bob ne doit pas montrer qu’il a été débité de X euros tant que le compte d’Alice n’a pas été crédité de X euros. Nous souhaitons en fait que les deux opérations apparaissent aux yeux du reste du système comme une seule opération unitaire. D’où l’emploi d’une transaction explicite. En voici un exemple :

BEGIN;
UPDATE comptes SET solde=solde-200 WHERE proprietaire='Bob';
UPDATE comptes SET solde=solde+200 WHERE proprietaire='Alice';
COMMIT;

Contrairement à d’autres moteurs de bases de données, PostgreSQL accepte aussi les instructions DDL dans une transaction. En voici un exemple :

BEGIN;
CREATE TABLE capitaines (id serial, nom text, age integer);
INSERT INTO capitaines VALUES (1, 'Haddock', 35);

SELECT age FROM capitaines;

 age
---
  35

ROLLBACK;
SELECT age FROM capitaines;

ERROR:  relation "capitaines" does not exist
LINE 1: SELECT age FROM capitaines;
                        ^

Nous voyons que la table capitaines a existé à l’intérieur de la transaction. Mais puisque cette transaction a été annulée (ROLLBACK), la table n’a pas été créée au final.

Cela montre aussi le support du DDL transactionnel au sein de PostgreSQL : PostgreSQL n’effectue aucun COMMIT implicite sur des ordres DDL tels que CREATE TABLE, DROP TABLE ou TRUNCATE TABLE. De ce fait, ces ordres peuvent être annulés au sein d’une transaction.

Un point de sauvegarde est une marque spéciale à l’intérieur d’une transaction qui autorise l’annulation de toutes les commandes exécutées après son établissement, restaurant la transaction dans l’état où elle était au moment de l’établissement du point de sauvegarde.

BEGIN;
CREATE TABLE capitaines (id serial, nom text, age integer);
INSERT INTO capitaines VALUES (1, 'Haddock', 35);
SAVEPOINT insert_sp;
UPDATE capitaines SET age = 45 WHERE nom = 'Haddock';
ROLLBACK TO SAVEPOINT insert_sp;
COMMIT;

SELECT age FROM capitaines WHERE nom = 'Haddock';

 age
---
  35

Malgré le COMMIT après l’UPDATE, la mise à jour n’est pas prise en compte. En effet, le ROLLBACK TO SAVEPOINT a permis d’annuler cet UPDATE mais pas les opérations précédant le SAVEPOINT.

À partir de la version 12, il est possible de chaîner les transactions avec COMMIT AND CHAIN ou ROLLBACK AND CHAIN. Cela veut dire terminer une transaction et en démarrer une autre immédiatement après avec les mêmes propriétés (par exemple, le niveau d’isolation).

Chaque transaction, en plus d’être atomique, s’exécute séparément des autres. Le niveau de séparation demandé sera un compromis entre le besoin applicatif (pouvoir ignorer sans risque ce que font les autres transactions) et les contraintes imposées au niveau de PostgreSQL (performances, risque d’échec d’une transaction).

Le standard SQL spécifie quatre niveaux, mais PostgreSQL n’en supporte que trois (il n’y a pas de read uncommitted : les lignes non encore committées par les autres transactions sont toujours invisibles).

Les journaux de transactions (appelés souvent WAL) sont une garantie contre les pertes de données. Il s’agit d’une technique standard de journalisation appliquée à toutes les transactions, pour garantir l’intégrité (la base reste cohérente quoiqu’il arrive) et la durabilité (ce qui est validé ne sera pas perdu).

Ainsi lors d’une modification de donnée, l’écriture au niveau du disque se fait généralement en deux temps :

• écriture des modifications dans le journal de transactions, avec écriture forcée sur disque (« synchronisation ») lors d’un COMMIT ;
• écriture dans le fichier de données bien plus tard, lors d’un « checkpoint ».

Ainsi en cas de crash :

• PostgreSQL redémarre ;
• il vérifie s’il reste des données non intégrées aux fichiers de données dans les journaux (mode recovery) ;
• si c’est le cas, ces données sont recopiées dans les fichiers de données afin de retrouver un état stable et cohérent ;
• PostgreSQL peut alors s’ouvrir sans perte des transactions validées lors du crash (une transaction interrompue, et donc jamais validée, est perdue).

Les écritures dans le journal se font de façon séquentielle, donc sans grand déplacement de la tête d’écriture (sur un disque dur classique, c’est l’opération la plus coûteuse). De plus, comme nous n’écrivons que dans un seul fichier de transactions, la synchronisation sur disque, lors d’un COMMIT, peut se faire sur ce seul fichier, si le système de fichiers le supporte. Concrètement, ces journaux sont des fichiers de 16 Mo par défaut, avec des noms comme 0000000100000026000000AF, dans le répertoire pg_wal/ de l’instance PostgreSQL (répertoire souvent sur une partition dédiée).

L’écriture définitive dans les fichiers de données est asynchrone, et généralement lissée, ce qui est meilleur pour les performances qu’une écriture immédiate. Cette opération est appelée « checkpoint » et périodique (5 minutes par défaut, ou plus).

Divers paramètres et fonctionnalités peuvent altérer ce comportement par défaut, par exemple pour des raisons de performances.

À côté de la sécurité et des performances, le mécanisme des journaux de transactions est aussi utilisé pour des fonctionnalités très intéressantes, comme le PITR et la réplication physique, basés sur le rejeu des informations stockées dans ces journaux.

Pour plus d’informations :

• PostgreSQL et ses journaux de transactions, > Guillaume Lelarge, GNU/Linux Magazine n°108 (septembre 2008), encore d’actualité pour l’essentiel.

PostgreSQL supporte différentes solutions pour la sauvegarde.

La plus simple revient à sauvegarder à froid tous les fichiers des différents répertoires de données mais cela nécessite d’arrêter le serveur, ce qui occasionne une mise hors production plus ou moins longue, suivant la volumétrie à sauvegarder.

L’export logique se fait avec le serveur démarré. Plusieurs outils sont proposés : pg_dump pour sauvegarder une base, pg_dumpall pour sauvegarder toutes les bases. Suivant le format de l’export, l’import se fera avec les outils psql ou pg_restore. Les sauvegardes se font à chaud et sont cohérentes sans blocage de l’activité (seuls la suppression des tables et le changement de leur définition sont interdits).

Enfin, il est possible de sauvegarder les fichiers à chaud. Cela nécessite de mettre en place l’archivage des journaux de transactions. L’outil pg_basebackup est conseillé pour ce type de sauvegarde.

Il est à noter qu’il existe un grand nombre d’outils développés par la communauté pour faciliter encore plus la gestion des sauvegardes avec des fonctionnalités avancées comme le PITR (Point In Time Recovery) ou la gestion de la rétention, notamment pg_back (sauvegarde logique), pgBackRest ou barman (sauvegarde physique).

PostgreSQL dispose de la réplication depuis de nombreuses années.

Le premier type de réplication intégrée est la réplication physique. Il n’y a pas de granularité, c’est forcément l’instance complète (toutes les bases de données), et au niveau des fichiers de données. Cette réplication est asymétrique : un seul serveur primaire effectue lectures comme écritures, et les serveurs secondaires n’acceptent que des lectures.

Le deuxième type de réplication est bien plus récent vu qu’il a été ajouté en version 10. Il s’agit d’une réplication logique, où les données elles-mêmes sont répliquées. Cette réplication est elle aussi asymétrique. Cependant, ceci se configure table par table (et non pas au niveau de l’instance comme pour la réplication physique). Avec la version 15, il devient possible de choisir quelles colonnes sont publiées et de filtre les lignes à publier.

La réplication logique n’est pas intéressante quand nous voulons un serveur sur lequel basculer en cas de problème sur le primaire. Dans ce cas, il vaut mieux utiliser la réplication physique. Par contre, c’est le bon type de réplication pour une réplication partielle ou pour une mise à jour de version majeure.

Dans les deux cas, les modifications sont transmises en asynchrone (avec un délai possible). Il est cependant possible de la configurer en synchrone pour tous les serveurs ou seulement certains.

Faute de pouvoir intégrer toutes les fonctionnalités demandées dans PostgreSQL, ses développeurs se sont attachés à permettre à l’utilisateur d’étendre lui-même les fonctionnalités sans avoir à modifier le code principal.

Ils ont donc ajouté la possibilité de créer des extensions. Une extension contient un ensemble de types de données, de fonctions, d’opérateurs, etc. en un seul objet logique. Il suffit de créer ou de supprimer cet objet logique pour intégrer ou supprimer tous les objets qu’il contient. Cela facilite grandement l’installation et la désinstallation de nombreux objets. Les extensions peuvent être c