PostgreSQL est le SGBD open source le plus complet, sécurisé et professionnel (on y reviendra).
Début du développement en 1985
Première version open source en 1995
Un nom changeant : Postgres (1985) → Postgres95 (1995) → PostgreSQL (1996)
Prononciation : « poste-graisse-cul-elle », et en anglais « poste-graisse-kyou-elle »
Versions supportées aujourd’hui : 9.3, 9.4, 9.5, 9.6, 10.
Un projet très actif : tous les ans, des nouveautés importantes :
UPSERT, nouveau type d’indexSELECT4e base de données la plus utilisée au monde après Oracle, MySQL et Microsoft SQL server.
De nombreuses communautés : essentiellement des développeurs webs, mais aussi une grande partie des sciences « dures », des astrophysiciens, biologistes, météorologues, etc.
Un exemple concret récent : en 2016 la Caisse d’Allocations Familiale est passée entièrement à Linux et PostgreSQL, un chantier géant de 22 M€. Au final :
sudo apt install postgresql
Télécharger et installer la dernière version sur https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
Une installation de PostgreSQL arrive avec son serveur, qui stocke les données et traite les requêtes, et son client, une interface pour se connecter au serveur.
Plusieurs versions de PostgreSQL peuvent être installées à la fois, et il peut y avoir plusieurs instance de chaque version → clusters
Évidemment, PostgreSQL peut contenir autant de bases de données que nécessaire. Mais en plus, à l’intérieur de chaque base de données il y a des schémas, des genre de sous-bases de données permettant d’avoir plusieurs fois des tables du même nom.
Chaque schéma contient des tables et éventuellement des vues (en quelque sorte des tables calculées automatiquement à partir d’autres tables).
Des extensions permettent d’ajouter des fonctionnalités : nouveaux types de données ou d’index, nouvelles fonctions, etc.
Bien d’autres subtilités, mais voilà les grandes lignes.
Pour la plupart des usages, on se sert du client pour se connecter à une base de données où on ne se sert que des tables.
psqlpsql, le client en terminal fourni avec le serveur PostgreSQL
Pour se connecter en mode root pour les premières fois et ponctuellement pour des opérations de maintenance :
sudo -u postgres psql
En se connectant avec l’utilisateur Linux postgres, on ouvre la base de données postgres avec l’utilisateur PostgreSQL postgres. Cet utilisateur PostgreSQL postgres est le superutilisateur par défaut.
Bon, ça a l’air compliqué, mais dites-vous que si vous avez besoin de faire quelque chose dans PostgreSQL et que vous avez un problème d’accès, l’artillerie lourde est sudo -u postgres psql
Évidemment, cet utilisateur PostgreSQL postgres ne doit jamais être utilisé dans une application.
Dans le client en mode superutilisateur (sudo -u postgres psql) :
CREATE ROLE essai WITH LOGIN ENCRYPTED PASSWORD 'votre mot de passe';
CREATE DATABASE essai OWNER essai;
Ne pas oublier les ;.
On passe à la la base de données essai pour pouvoir s’en servir :
\c essai
\ dans psqlCette commande \c n’est pas du SQL, il s’agit d’une syntaxe propre à psql pour effectuer des opérations hors du domaine SQL.
Voici les commandes les plus utiles :
| Commande | Description |
|---|---|
\q |
quitter |
\l |
liste des bases de données |
\c db_name |
se connecter à une base de données |
\? |
liste des commandes en \ |
\du |
liste des roles |
\dt |
liste des tables |
\dn |
liste des schémas |
À l’aide de la documentation PostgreSQL (et Internet en général), créer une base de données library possédée par un utilisateur librarian, contenant une table books avec les colonnes suivantes :
id, un identifiant autoincrémenté servant de clé primairetitle, un champ stockant le titre du livreauthor, un champ stockant le nom de l’auteurpages, le nombre de pagesis_author_alive, si oui ou non l’auteur est vivantEssayer d’utiliser les types de données les plus adaptés possibles.
Faire ensuite une requête pour ajouter vos trois livres préférés.
Effectuer une requête récupérant tous les livres dont l’auteur est décédé et dont le nom ne contient pas la lettre e ou E.
Si on se restreint au SQL, les autres solutions usuelles sont :
Oracle est assez équivalent à PostgreSQL en terme de fonctionnalités, mais… 17 500 € plus cher par poste.
Microsoft SQL server est plus abordable qu’Oracle, mais pauvre face à PostgreSQL, tout en étant très lié à Windows. Une version Linux est toutefois sortie récemment, Microsoft privilégiant désormais Linux à Windows Server.
Malheureusement, MySQL et SQLite sont non seulement moins complets que PostgreSQL, mais vaguement sournois.
Outre l’absence ou les défauts de nombreuses fonctionnalités dans MySQL (recherche plein texte basique et lente, recherches géographiques très limitées, pas d’indexs géographiques, etc), certaines fonctionnements sont traitres :
latin1_swedish_ci), même en France, aboutissant parfois à des caractères spéciaux erronésPas besoin de chercher loin pour SQLite, ces deux informations suffisent :
'bonjour' dans une colonne integer, 'blablablablabla' dans un VARCHAR(3) ou 3.14159 dans un integer. La plupart des utilisateurs de SQLite ignorent cela et il faut souvent des années pour qu’un développeur réalise qu’il y a un problème dans sa base de données à cause de cela.psql au quotidienHabituellement, on se connecte à l’aide d’un utilisateur créé, et non postgres.
Usage typique pour se connecter à l’aide d’un mot de passe :
psql -U user_name -h localhost -p 5432 db_name
Par défaut le nom de base de données est le nom d’utilisateur. Pour une base de données essai d’un utilisateur essai, ceci suffit :
psql -U essai -h localhost -p 5432
On constate que PostgreSQL nous demande bien le mot de passe, et le saisir nous connecte bien.
Cependant, cela ne marche que sur cette machine, on ne pourrait pas accéder au serveur PostgreSQL depuis une autre machine en utilisant l’IP du serveur PostgreSQL
En effet, PostgreSQL nécessite plus de configuration pour ce genre d’usage.
pg_hba.confC’est le fichier de configuration que vous modifierez le plus souvent.
Il s’agit probablement de la partie la plus complexe pour un débutant de PostgreSQL, d’autant qu’il faut quelques bases en réseau.
Ce fichier signifie “PostgreSQL Host-Based Authentication” et définit les règles de qui à accès à quoi, et depuis quel endroit. Il existe de nombreuses combinaisons de règles possibles, visibles dans la documentation.
Pour le modifier sous Linux, sudo nano /etc/postgresql/*/main/pg_hba.conf (remplacer * par le numéro de version si vous avez plusieurs versions installées en même temps).
Il contient par défaut des règles comme :
local all postgres peer
host all all 127.0.0.1/32 md5
local all all peerLa première ligne signifie que l’utilisateur postgres a le droit d’accéder à toutes les bases de données (all) lorsqu’il se connecte à partir de l’utilisateur Linux postgres (peer), le tout uniquement sur cette machine (local).
La seconde signifie que n’importe quel utilisateur (all) peut se connecter à n’importe quelle base de données (all) s’il se connecte avec un mot de passe (md5) via une connection depuis un hôte situé dans le sous-réseau 127.0.0.1/32 (c’est à dire uniquement 127.0.0.1).
pg_hba.confGrâce aux règles par défaut, on découvre par exemple qu’on peut se connecter sur n’importe quelle base de données, même postgres, à l’aide de notre utilisateur essai :
psql -U essai -h localhost -p 5432 postgres
Même s’il faut connaître le mot de passe et que cela ne fonctionne qu’en local, ce n’est pas idéal.
Il faut parcourir les possibilités pour parvenir à une installation sécurisée convenant à chaque situation.
Après modification de pg_hba.conf, il faut redémarrer le serveur PostgreSQL avec sudo systemctl restart postgresql. D’où l’intérêt d’avoir une bonne configuration dès le début, pour éviter de couper le service.
On cherche à faire une installation sécurisée pour un serveur mutualisé. Ce serveur héberge des applications Python et une même instance PostgreSQL pour tout le monde. Des dizaines d’utilisateurs Linux existent, et on veut à tout prix éviter qu’un utilisateur puisse voir ou modifier les données du voisin.
Un utilisateur Linux spécial, badass, a payé pour pouvoir accéder à trois bases de données à la fois.
Configurer PostgreSQL pour qu’il n’accepte que ces types de connection :
postgres peut se connecter sans mot de passe à toutes les bases de données, uniquement avec le nom d’utilisateur PostgreSQL postgres.bertrand ne pourra que se connecter qu’à la base de données bertrand, et uniquement avec l’utilisateur PostgreSQL bertrand. Bien entendu, il ne pourra se connecter à cette base de données que si elle existe.badass doit pouvoir se connecter aux bases de données clefs, bassins et lignes, le tout sans mot de passe.Écrire ensuite :
postgres de se connecter à postgresjean de se connecter à jeanbadass de se connecter à la base de données clefsL’autre fichier majeur de configuration PostgreSQL est postgresql.conf, qui définit tous les paramètres du serveur : port, chemins, logs, utilisation mémoire, activation de fonctionnalités, formats, règles de prédiction du query planner… C’est extrêmement complet.
Pourtant, il n’est pas nécessaire de modifier postgresql.conf, les paramètres par défaut sont idéaux dans la plupart des cas.
En revanche, il est possible de devoir modifier le port après installation d’une nouvelle version majeure. Si l’ancienne version est installée, la nouvelle aura le prochain port disponible, 5433. Il est ainsi possible de se retrouver avec plusieurs versions tournant chacune sur un port différent. En général, on garde l’ancienne version quelques minutes ou heures, le temps de faire migrer l’ancienne version avec pg_upgradecluster. Une fois la commande réussi, PostgreSQL propose de supprimer l’ancien cluster. Si vous acceptez, il modifie la configuration du nouveau cluster pour reprendre le port précédent, 5432.
Le reste des fichiers de configuration n’est quasiment jamais utile à modifier.
json & jsonbDepuis quelques années, PostgreSQL possède des fonctionnalités NoSQL grâce aux deux types de données json et jsonb.
Ils permettent simplement de stocker du JSON dans la base de données puis d’effectuer des requêtes dessus.
Les deux types de données sont les mêmes, sauf que jsonb est stocké de manière optimisé en binaire, prenant moins de place tout en étant plus rapide.
C’est ainsi qu’on peut désormais voir des commandes comme :
SELECT *
FROM my_table
WHERE data #>> '{author,age}' > 30;
alors que la colonne data stocke une donnée du type :
{
"title": "a book",
"author": {
"name": "an author",
"age": 29
}
}
pgadmin, une interface complète d’administration de serveur PostgreSQL
Équivalent pour PostgreSQL de PHPMyAdmin pour MySQL/Maria
pgadmin4 est officiellement la principale version, mais son installation est plus complexe pour l’instant.
Utilisons pgadmin3:
sudo apt install pgadmin3
Télécharger sur https://www.pgadmin.org/download/pgadmin-3-windows/
Puis installer normalement
Télécharger sur https://www.pgadmin.org/download/pgadmin-3-macos/
Puis installer normalement
Les possibilités de PostgreSQL en se limitent pas au standard SQL.
En plus du JSON, PostgreSQL propose une myriade d’autres fonctionalités.
L’une des plus célèbres est la recherche plein texte. Elle n’offre pas tout à fait autant de possibilités qu’une solution dédiée comme Elasticsearch, mais PostgreSQL arrive directement avec des configurations pour 16 langues, rendant la recherche plein texte beaucoup plus accessible qu’avec Elasticsearch.
Dans le CMS Wagtail, le moteur de recherche PostgreSQL que je réalise depuis un an devient depuis peu plus fiable et plus riche qu’avec Elasticsearch, tout en étant plus rapide à indexer.
En fouillant un peu dans les extensions officielles de PostgreSQL, on trouve de nombreuses petites pépites : PostGIS pour de la recherche géographique, ltree pour le stockage d’arborescence, fuzzystrmatch pour trouver des mots ayant une prononciation similaire, etc.
Une succession de commandes dans un fichier texte
Avantage : facile à modifier et à lire
Inconvénient : fichier assez volumineux du fait du format texte et de la redondance des commandes
pg_dump -U user_name -f db_name.sql db_name
psql -U user_name db_name | db_name.sql
Un fichier binaire extrêmement optimisé pour prendre le moins de place possible
Avantage : considérablement plus léger que le format texte
Inconvénient : impossible à modifier, incompatible d’une architecture de processeur à l’autre
pg_dump -U user_name -Fc -b -f db_name.backup db_name
pg_restore -U user_name -d db_name -e -j 5 db_name.backup
Un intérêt de pg_restore est qu’il fournit de nombreuses options. Par exemple -e permet d’annuler la restauration à la moindre erreur, indispensable pour faire du travail propre.
-j 5 permet d’indiquer que la restauration doit se faire en parallèle dans 5 processus, décuplant la vitesse.
La communauté PostgreSQL offre plusieurs solutions de réplication, la plus célèbre étant Slony.
Toutefois, mettre en place une réplication reste assez complexe pour des résultats discutables.
Il y a parfois confusion sur l’utilité d’une réplication : la réplication n’est pas une sauvegarde. Elle permet à un second serveur de prendre le relais si le premier tombe en panne, ou de répartir la charge entre plusieurs serveurs.
Il est honnêtement inutile de recourir à une réplication dans la quasi totalité des cas. En effet :
Une solution plus économe, robuste et censée est de mettre en place un système de copie statique des pages HTML de l’application web servie par un autre serveur. Le tout couplé à une tâche cron faisant quotidiennement une sauvegarde sur un serveur externe.
Comme toutes les bases de données et systèmes de fichiers, PostgreSQL utilise des méthodes rapides pour modifier les données pouvant aboutir à de la fragmentation, des indexs mal optimisés, et des statistiques périmées.
Ce problème, particulièrement les statistiques erronées, peut amener à de mauvaises prédictions du query planner, et ainsi à des requêtes trop lentes.
Pour corriger ces problèmes, il existe plusieurs commandes. Deux commandes sont à retenir, car elles font le nettoyage le plus complet :
VACUUM ANALYZE db_name;
REINDEX DATABASE db_name;Au quotidien, pas besoin d’effectuer ces commandes, un système, l’autovaccuum, se charge de le faire automatiquement lorsque c’est nécessaire.
Toutefois, dans certains cas il est utile d’exécuter manuellement ces commandes, notamment juste avant un test de performance.