PostgreSQL Administration

05 janvier 2024

Dalibo SCOP

Sur ce document

Formation Formation DBA1
Titre PostgreSQL Administration
Révision 24.01
ISBN N/A
PDF https://dali.bo/dba1_pdf
EPUB https://dali.bo/dba1_epub
HTML https://dali.bo/dba1_html
Slides https://dali.bo/dba1_slides

Vous trouverez en ligne les différentes versions complètes de ce document. La version imprimée ne contient pas les travaux pratiques. Ils sont présents dans la version numérique (PDF ou HTML).


Chers lectrices & lecteurs,

Nos formations PostgreSQL sont issues de nombreuses années d’études, d’expérience de terrain et de passion pour les logiciels libres. Pour Dalibo, l’utilisation de PostgreSQL n’est pas une marque d’opportunisme commercial, mais l’expression d’un engagement de longue date. Le choix de l’Open Source est aussi le choix de l’implication dans la communauté du logiciel.

Au‑delà du contenu technique en lui‑même, notre intention est de transmettre les valeurs qui animent et unissent les développeurs de PostgreSQL depuis toujours : partage, ouverture, transparence, créativité, dynamisme… Le but premier de nos formations est de vous aider à mieux exploiter toute la puissance de PostgreSQL mais nous espérons également qu’elles vous inciteront à devenir un membre actif de la communauté en partageant à votre tour le savoir‑faire que vous aurez acquis avec nous.

Nous mettons un point d’honneur à maintenir nos manuels à jour, avec des informations précises et des exemples détaillés. Toutefois malgré nos efforts et nos multiples relectures, il est probable que ce document contienne des oublis, des coquilles, des imprécisions ou des erreurs. Si vous constatez un souci, n’hésitez pas à le signaler via l’adresse !

À propos de DALIBO

DALIBO est le spécialiste français de PostgreSQL. Nous proposons du support, de la formation et du conseil depuis 2005.

Retrouvez toutes nos formations sur https://dalibo.com/formations

Remerciements

Ce manuel de formation est une aventure collective qui se transmet au sein de notre société depuis des années. Nous remercions chaleureusement ici toutes les personnes qui ont contribué directement ou indirectement à cet ouvrage, notamment :

Jean‑Paul Argudo, Alexandre Anriot, Carole Arnaud, Alexandre Baron, David Bidoc, Sharon Bonan, Franck Boudehen, Arnaud Bruniquel, Pierrick Chovelon, Damien Clochard, Christophe Courtois, Marc Cousin, Gilles Darold, Jehan‑Guillaume de Rorthais, Ronan Dunklau, Vik Fearing, Stefan Fercot, Pierre Giraud, Nicolas Gollet, Dimitri Fontaine, Florent Jardin, Virginie Jourdan, Luc Lamarle, Denis Laxalde, Guillaume Lelarge, Alain Lesage, Benoit Lobréau, Jean‑Louis Louër, Thibaut Madelaine, Adrien Nayrat, Alexandre Pereira, Flavie Perette, Robin Portigliatti, Thomas Reiss, Maël Rimbault, Julien Rouhaud, Stéphane Schildknecht, Julien Tachoires, Nicolas Thauvin, Be Hai Tran, Christophe Truffier, Cédric Villemain, Thibaud Walkowiak, Frédéric Yhuel.

Licence Creative Commons CC-BY-NC-SA

Cette formation est sous licence CC-BY-NC-SA. Vous êtes libre de la redistribuer et/ou modifier aux conditions suivantes :

  • Paternité
  • Pas d’utilisation commerciale
  • Partage des conditions initiales à l’identique

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.

Marques déposées

PostgreSQL® Postgres® et le logo Slonik sont des marques déposées par PostgreSQL Community Association of Canada.

Versions de PostgreSQL couvertes

Ce document ne couvre que les versions supportées de PostgreSQL au moment de sa rédaction, soit les versions 12 à 16.

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.

PostgreSQL : historique & communauté

PostgreSQL

Préambule

  • Quelle histoire !
    • parmi les plus vieux logiciels libres
    • et les plus sophistiqués
  • Souvent cité comme exemple
    • qualité du code
    • indépendance des développeurs
    • réactivité de la communauté

L’histoire de PostgreSQL est longue, riche et passionnante. Au côté des projets libres Apache et Linux, PostgreSQL est l’un des plus vieux logiciels libres en activité et fait partie des SGBD les plus sophistiqués à l’heure actuelle.

Au sein des différentes communautés libres, PostgreSQL est souvent cité comme exemple à différents niveaux :

  • qualité du code ;
  • indépendance des développeurs et gouvernance du projet ;
  • réactivité de la communauté ;
  • stabilité et puissance du logiciel.

Tous ces atouts font que PostgreSQL est désormais reconnu et adopté par des milliers de grandes sociétés de par le monde.


Au menu

  • Origines et historique du projet
  • Versions et feuille de route
  • Projets satellites
  • Sponsors et références
  • La communauté

Cette première partie est un tour d’horizon pour découvrir les multiples facettes du système de gestion de base de données libre PostgreSQL.

Les deux premières parties expliquent la genèse du projet et détaillent les différences entre les versions successives du logiciel. PostgreSQL est un des plus vieux logiciels libres ! Comprendre son histoire permet de mieux réaliser le chemin parcouru et les raisons de son succès.

Nous verrons ensuite certains projets satellites et nous listerons plusieurs utilisateurs renommés et cas d’utilisations remarquables.

Enfin, nous terminerons par une découverte de la communauté.


Un peu d’histoire…

  • La licence
  • L’origine du nom
  • Les origines du projet
  • Les principes

Licence

  • Licence PostgreSQL
  • Droit, sans coûts de licence, de :
    • utiliser, copier, modifier, distribuer (et même revendre)
  • Reconnue par l’Open Source Initiative
  • Utilisée par un grand nombre de projets de l’écosystème

PostgreSQL est distribué sous une licence spécifique, combinant la licence BSD et la licence MIT. Cette licence spécifique est reconnue comme une licence libre par l’Open Source Initiative.

Cette licence vous donne le droit de distribuer PostgreSQL, de l’installer, de le modifier… et même de le vendre. Certaines sociétés, comme EnterpriseDB et PostgresPro, produisent leur version propriétaire de PostgreSQL de cette façon.

PostgreSQL n’est pas pour autant complètement gratuit : il peut y avoir des frais et du temps de formation, des projets de migration depuis d’autres bases, ou d’intégration des différents outils périphériques indispensables en production.

Cette licence a ensuite été reprise par de nombreux projets de la communauté : pgAdmin, pgCluu, pgstat, etc.


PostgreSQL ?!?!

  • 1985 : Michael Stonebraker recode Ingres
  • post « ingres » postingres postgres
  • postgres PostgreSQL

PostgreSQL a une origine universitaire.

L’origine du nom PostgreSQL remonte au système de gestion de base de données Ingres, développé à l’université de Berkeley par Michael Stonebraker. En 1985, il prend la décision de reprendre le développement à partir de zéro et nomme ce nouveau logiciel Postgres, comme raccourci de post-Ingres.

En 1995, avec l’ajout du support du langage SQL, Postgres fut renommé Postgres95 puis PostgreSQL.

Aujourd’hui, le nom officiel est « PostgreSQL » (prononcé « post - gresse - Q - L »). Cependant, le nom « Postgres » reste accepté.


Principes fondateurs

  • Sécurité des données (ACID)
  • Respect des normes (ISO SQL)
  • Portabilité
  • Fonctionnalités intéressant le plus grand nombre
  • Performances
    • si pas de péril pour les données
  • Simplicité du code
  • Documentation

Depuis son origine, PostgreSQL a toujours privilégié la stabilité et le respect des standards plutôt que les performances.

La sécurité des données est un point essentiel. En premier lieu, un utilisateur doit être certain qu’à partir du moment où il a exécuté l’ordre COMMIT d’une transaction, les données modifiées relatives à cette transaction se trouvent bien sur disque et que même un crash ne pourra pas les faire disparaître. PostgreSQL est très attaché à ce concept et fait son possible pour forcer le système d’exploitation à ne pas conserver les données en cache, mais à les écrire sur disque dès l’arrivée d’un COMMIT.

L’intégrité des données, et le respect des contraintes fonctionnelles et techniques qui leur sont imposées, doivent également être garanties par le moteur à tout moment, quoi que fasse l’utilisateur. Par exemple, insérer 1000 caractères dans un champ contraint à 200 caractères maximum doit mener à une erreur explicite et non à l’insertion des 200 premiers caractères en oubliant les autres, comme cela s’est vu ailleurs. De même, un champ avec le type date ne contiendra jamais un 31 février, et un champ NOT NULL ne sera jamais vide. Tout ceci est formalisé par les propriétés (ACID) que possèdent toute bonne base de données relationnelle.

Le respect des normes est un autre principe au cœur du projet. Les développeurs de PostgreSQL cherchent à coller à la norme SQL le plus possible. PostgreSQL n’est pas compatible à cette norme à 100 %, aucun moteur ne l’est, mais il cherche à s’en approcher. Tout nouvel ajout d’une syntaxe ne sera accepté que si la syntaxe de la norme est ajoutée. Des extensions sont acceptées pour différentes raisons (performances, fonctionnalités en avance sur le comité de la norme, facilité de transition d’un moteur de bases de données à un autre) mais si une fonctionnalité existe dans la norme, une syntaxe différente ne peut être acceptée que si la syntaxe de la norme est elle-aussi présente.

La portabilité est importante : PostgreSQL tourne sur l’essentiel des systèmes d’exploitation : Linux (plate-forme à privilégier), macOS, les Unix propriétaires, Windows… Tout est fait pour que cela soit encore le cas dans le futur.

Ajouter des fonctionnalités est évidemment l’un des buts des développeurs de PostgreSQL. Cependant, comme il s’agit d’un projet libre, rien n’empêche un développeur de proposer une fonctionnalité, de la faire intégrer, puis de disparaître laissant aux autres la responsabilité de la corriger le cas échéant. Comme le nombre de développeurs de PostgreSQL est restreint, il est important que les fonctionnalités ajoutées soient vraiment utiles au plus grand nombre pour justifier le coût potentiel du débogage. Donc ne sont ajoutées dans PostgreSQL que ce qui est vraiment le cœur du moteur de bases de données et que ce qui sera utilisé vraiment par le plus grand nombre. Une fonctionnalité qui ne sert que une à deux personnes aura très peu de chances d’être intégrée. (Le système des extensions offre une élégante solution aux problèmes très spécifiques.)

Les performances ne viennent qu’après tout ça. En effet, rien ne sert d’avoir une modification du code qui permet de gagner énormément en performances si cela met en péril le stockage des données. Cependant, les performances de PostgreSQL sont excellentes et le moteur permet d’opérer des centaines de tables, des milliards de lignes pour plusieurs téraoctets de données, sur une seule instance, pour peu que la configuration matérielle soit correctement dimensionnée.

La simplicité du code est un point important. Le code est relu scrupuleusement par différents contributeurs pour s’assurer qu’il est facile à lire et à comprendre. En effet, cela facilitera le débogage plus tard si cela devient nécessaire.

Enfin, la documentation est là-aussi un point essentiel dans l’admission d’une nouvelle fonctionnalité. En effet, sans documentation, peu de personnes pourront connaître cette fonctionnalité. Très peu sauront exactement ce qu’elle est supposée faire, et il serait donc très difficile de déduire si un problème particulier est un manque actuel de cette fonctionnalité ou un bug.

Tous ces points sont vérifiés à chaque relecture d’un patch (nouvelle fonctionnalité ou correction).


Origines

  • Années 1970 : Michael Stonebraker développe Ingres à Berkeley
  • 1985 : Postgres succède à Ingres
  • 1995 : Ajout du langage SQL
  • 1996 : Libération du code : Postgres devient PostgreSQL
  • 1996 : Création du PostgreSQL Global Development Group

L’histoire de PostgreSQL remonte au système de gestion de base de données Ingres, développé dès 1973 à l’Université de Berkeley (Californie) par Michael Stonebraker.

Lorsque ce dernier décide en 1985 de recommencer le développement de zéro, il nomme le logiciel Postgres, comme raccourci de post-Ingres. Des versions commencent à être diffusées en 1989, puis commercialisées.

Postgres utilise alors un langage dérivé de QUEL, hérité d’Ingres, nommé POSTQUEL1. En 1995, lors du remplacement par le langage SQL par Andrew Yu and Jolly Chen, deux étudiants de Berkeley, Postgres est renommé Postgres95.

En 1996, Bruce Momijan et Marc Fournier convainquent l’Université de Berkeley de libérer complètement le code source. Est alors fondé le PGDG (PostgreSQL Development Group), entité informelle — encore aujourd’hui — regroupant l’ensemble des contributeurs. Le développement continue donc hors tutelle académique (et sans son fondateur historique Michael Stonebraker) : PostgreSQL 6.0 est publié début 1997.

Plus d’informations :


Apparition de la communauté internationale

  • ~ 2000: Communauté japonaise (JPUG)
  • 2004 : PostgreSQLFr
  • 2006 : SPI
  • 2007 : Communauté italienne
  • 2008 : PostgreSQL Europe et US
  • 2009 : Boom des PGDay
  • 2011 : Postgres Community Association of Canada
  • 2017 : Community Guidelines
  • …et ça continue

Les années 2000 voient l’apparition de communautés locales organisées autour d’association ou de manière informelle. Chaque communauté organise la promotion, la diffusion d’information et l’entraide à son propre niveau.

En 2000 apparaît la communauté japonaise (JPUG). Elle dispose déjà d’un grand groupe, capable de réaliser des conférences chaque année, d’éditer des livres et des magazines. Elle compte, au dernier recensement connu, plus de 3000 membres.

En 2004 naît l’association française (loi 1901) appelée PostgreSQL Fr. Cette association a pour but de fournir un cadre légal pour pouvoir participer à certains événements comme Solutions Linux, les RMLL ou d’en organiser comme le pgDay.fr (qui a déjà eu lieu à Toulouse, Nantes, Lyon, Toulon, Marseille). Elle permet aussi de récolter des fonds pour aider à la promotion de PostgreSQL.

En 2006, le PGDG intègre Software in the Public Interest, Inc.(SPI), une organisation à but non lucratif chargée de collecter et redistribuer des financements. Elle a été créée à l’initiative de Debian et dispose aussi de membres comme LibreOffice.org.

Jusque là, les événements liés à PostgreSQL apparaissaient plutôt en marge de manifestations, congrès, réunions… plus généralistes. En 2008, douze ans après la création du projet, des associations d’utilisateurs apparaissent pour soutenir, promouvoir et développer PostgreSQL à l’échelle internationale. PostgreSQL UK organise une journée de conférences à Londres, PostgreSQL Fr en organise une à Toulouse. Des « sur-groupes » apparaissent aussi pour aider les groupes locaux : PGUS rassemble les différents groupes américains, plutôt organisés géographiquement, par État ou grande ville. De même, en Europe, est fondée PostgreSQL Europe, association chargée d’aider les utilisateurs de PostgreSQL souhaitant mettre en place des événements. Son principal travail est l’organisation d’un événement majeur en Europe tous les ans : pgconf.eu, d’abord à Paris en 2009, puis dans divers pays d’Europe jusque Milan en 2019. Cependant, elle aide aussi les communautés allemande, française et suédoise à monter leur propre événement (respectivement PGConf.DE, pgDay Paris et Nordic PGday).

Dès 2010, nous dénombrons plus d’une conférence par mois consacrée uniquement à PostgreSQL dans le monde. Ce mouvement n’est pas prêt de s’arrêter :

En 2011, l’association Postgres Community Association of Canada voit le jour. Elle est créée par quelques membres de la Core Team pour gérer le nom déposé PostgreSQL, le logo, le nom de domaine sur Internet, etc.

Vu l’émergence de nombreuses communautés internationales, la communauté a décidé d’écrire quelques règles pour ces communautés. Il s’agit des Community Guidelines, apparues en 2017, et disponibles sur le site officiel.


Progression du code

  • 1,6 millions de lignes
    • dont 1/4 de commentaires
    • le reste surtout en C
  • Nombres de commit par mois :
Évolution du nombre de commit dans le dépôt PostgreSQL

Le dépôt principal de PostgreSQL a été un dépôt CVS, passé depuis à git. Il est en accès public en lecture.

Le graphe ci-dessus (source) représente l’évolution du nombre de commit dans les sources de PostgreSQL. L’activité ne se dément pas. Le plus intéressant est certainement de noter que l’évolution est constante. Il n’y a pas de gros pic, ni dans un sens, ni dans l’autre.

Début 2023, PostgreSQL est composé d’1,6 millions de lignes de code, dont un quart de commentaires. Ce ratio montre que le code est très commenté, très documenté. Ceci fait qu’il est facile à lire, et donc pratique à déboguer. Et le ratio ne change pas au fil des ans. Le code est essentiellement en C, pour environ 200 développeurs actifs, à environ 200 commits par mois ces dernières années.


Les versions de PostgreSQL

Quelle version utiliser ?

  • Historique
  • Numérotation
  • Mises à jour mineures et majeures
  • Les versions courantes
  • Quelle version en production ?
  • Les forks & dérivés

Historique