Créer une base avec PostgreSQL

Magazine
Marque
GNU/Linux Magazine
HS n°
Numéro
44
|
Mois de parution
octobre 2009
|
Domaines


Résumé
Nous allons construire une petite base et voir quelques rudiments : création d'une base, création de quelques tables, insertion de données, sauvegarde. Le minimum pour savoir comment débuter.

Body

1. Qui suis-je ?

Nous allons tout faire en tant que super-utilisateur de la base de données. Ce sera postgres pour ceux qui ont installé PostgreSQL avec les paquets de leur distribution Linux ou l'utilisateur qui a lancé le serveur PostgreSQL pour ceux qui l'ont compilé.

Nous ne nous occuperons donc pas de la gestion des droits.

2. Créer une base de données

Une fois que le serveur est installé et qu'il est en cours d'exécution, la première chose à faire est de créer une base. Cette base va nous servir à y stocker des données. Il existe pour cela un outil utilisable sur la ligne de commande shell : createdb. Il dispose de plusieurs options, mais nous allons faire simple pour l'instant :

postgres@debian1:~$ createdb base1

Un seul argument, le nom de la base de données. Lorsque nous voudrons nous connecter à cette base, nous n'aurons que ce nom à fournir.

3. Créer une table

Pour créer une table, nous allons lancer la requête SQL adéquate via la console interactive de PostgreSQL. Cet outil est nommé psql. C'est certainement l'outil le plus utilisé pour interagir avec ce SGBD. Tout simplement parce que, bien qu'il puisse sembler simpliste, il est en fait extrêmement puissant tout en restant simple à utiliser.

Nous allons donc entrer dans la console interactive :

postgres@debian1:~$ psql base1

psql (8.4.0)

Saisissez « help » pour l'aide.

base1=#

base1=# est l'invite (aussi appelée en anglais prompt). Il indique le nom de la base où on est connecté. Il ne nous reste plus qu'à saisir une requête ou une méta-commande. Pour créer une table, il nous faut utiliser l'instruction CREATE TABLE. Il est possible d'avoir de l'aide sur les instructions SQL avec la méta-commande \h. Sans plus, elle renvoie la liste des instructions SQL connues. Avec l'instruction SQL, elle renvoie la syntaxe complète de l'instruction :

base1=# \h CREATE TABLE

Commande :    CREATE TABLE

Description : définir une nouvelle table

Syntaxe :                               

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE nom_table ( [

  { nom_colonne type_données [ DEFAULT expr_par_défaut ]              

    [ contrainte_colonne [ ... ] ]                                    

    | contrainte_table

    | LIKE table_parent [ { INCLUDING | EXCLUDING }

      { DEFAULTS | CONSTRAINTS | INDEXES } ] ... }

    [, ... ]

] )

[ INHERITS ( table_parent [, ... ] ) ]

[ WITH ( paramètre_stockage [= valeur] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]

[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]

[ TABLESPACE tablespace ]

(etc.)

Pour créer une table, il faut donc, au minimum, lui donner un nom et des colonnes. Les colonnes sont indiquées un peu comme les arguments d'une fonction : nom de la colonne, puis type de données de cette colonne. Créons une table contenant une liste d'articles. Nous aurons une colonne titre et une colonne auteur.

base1=# CREATE TABLE articles (titre text, auteur text);

CREATE TABLE

text est le type de données pour les champs contenant des chaînes de caractères. Le point-virgule à la fin permet d'indiquer à psql que la requête est terminée et que nous voulons qu'il l'exécute. Après une exécution réussie, psql renvoie un message de confirmation : CREATE TABLE dans notre cas.

Pour ajouter des données, nous utilisons l'ordre INSERT.

base1=# \h insert

Commande :    INSERT

Description : créer de nouvelles lignes dans une table

Syntaxe :

INSERT INTO table [ ( colonne [, ...] ) ]

    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | requête }

    [ RETURNING * | expression_sortie [ [ AS ] nom_sortie ] [, ...] ]

base1=# INSERT INTO articles (titre, auteur) VALUES ('Ubuntu: installation dual boot sur clef USB', 'Yves Bailly');

INSERT 0 1

base1=# INSERT INTO articles (titre, auteur) VALUES ('Stockage en ligne : NBD dans un tunnel SSH', 'Fabien Germain');

INSERT 0 1

base1=# INSERT INTO articles (titre, auteur) VALUES ('Parce qu''y''en a marre', 'Jean-Pierre Troll');

INSERT 0 1

base1=# INSERT INTO articles (titre, auteur) VALUES ('Créez votre live CD Debian 5.0 Lenny', 'Denis Boor');

INSERT 0 1

Aïe, je me suis trompé dans le nom de notre rédacteur en chef préféré ! Pour corriger cela, nous pouvons utiliser l'instruction SQL UPDATE. Nous allons mettre à jour la colonne auteur, mais comment faire pour sélectionner la bonne ligne ? Si je sélectionne par rapport au titre, rien ne me dit que je vais pouvoir sélectionner une ligne unique. Dans notre cas, il n'y aura pas ce problème. Mais j'aurais pu ajouter d'autres articles de Jean-Pierre Troll... Tous ses articles ont pour titre « Parce qu'y'en a marre », ce qui rend leur distinction difficile. De ce fait, chaque table doit avoir une façon unique d'identifier chaque ligne. Le moyen habituel est d'associer à la table une clé primaire. Cette dernière peut se poser sur une colonne ou sur un groupe de colonne. Dans notre contexte, le meilleur moyen d'identifier un article est de connaître le numéro du magazine et le titre de l'article. Nous allons donc ajouter dans un premier temps une nouvelle colonne à la table articles :

base1=# ALTER TABLE articles ADD COLUMN numeromagazine integer;

ALTER TABLE

Nous allons ajouter la clé primaire :

base1=# ALTER TABLE articles ADD CONSTRAINT articles_pkey PRIMARY KEY (numeromagazine, titre);

NOTICE: ALTER TABLE / ADD PRIMARY KEY créera un index implicite « articles_pkey » pour la table « articles »

ALTER TABLE

Notez que, dès maintenant, je peux ajouter deux fois un article de même nom, à condition que le numéro du magazine soit différent :

base1=# INSERT INTO articles (numeromagazine, titre, auteur) VALUES (116, 'Parce qu''y''en a marre', 'Jean-Pierre Troll');

INSERT 0 1

base1=# INSERT INTO articles (numeromagazine, titre, auteur) VALUES (117, 'Parce qu''y''en a marre', 'Jean-Pierre Troll');

INSERT 0 1

base1=# INSERT INTO articles (numeromagazine, titre, auteur) VALUES (117, 'Parce qu''y''en a marre', 'Jean-Pierre Troll');

ERREUR: la valeur d'une clé dupliquée rompt la contrainte unique « articles_pkey »

base1=# INSERT INTO articles (numeromagazine, titre, auteur) VALUES (118, 'Parce qu''y''en a marre', 'Jean-Pierre Troll');

INSERT 0 1

Nous allons supprimer toutes les lignes, puis ajouter de nouveau les anciennes lignes en indiquant en plus le numéro. La suppression se fait avec l'instruction DELETE.

base1=# DELETE FROM articles;

DELETE 4

base1=# INSERT INTO articles (numeromagazine, titre, auteur) VALUES (118, 'Ubuntu: installation dual boot sur clef USB', 'Yves Bailly');

INSERT 0 1

base1=# INSERT INTO articles (numeromagazine, titre, auteur) VALUES (118, 'Stockage en ligne : NBD dans un tunnel SSH', 'Fabien Germain');

INSERT 0 1

base1=# INSERT INTO articles (numeromagazine, titre, auteur) VALUES (115, 'Parce qu''y''en a marre', 'Jean-Pierre Troll');

INSERT 0 1

base1=# INSERT INTO articles (numeromagazine, titre, auteur) VALUES (115, 'Créez votre live CD Debian 5.0 Lenny', 'Denis Boor');

Nous allons enfin pouvoir modifier cette ligne :

base1=# UPDATE articles SET auteur='Denis Bodor' WHERE numeromagazine=115 AND auteur='Denis Boor';

UPDATE 1

psql nous répond que nous avons bien modifié une seule ligne. Tout va bien.

SELECT est l'instruction SQL pour récupérer des informations. La clause WHERE aide à filtrer les lignes à récupérer. Par exemple :

base1=# SELECT auteur, titre FROM articles WHERE numeromagazine=115;

      auteur       |                titre

-------------------+--------------------------------------

Jean-Pierre Troll | Parce qu'y'en a marre

Denis Bodor       | Créez votre live CD Debian 5.0 Lenny

(2 lignes)

Cette requête récupère les lignes dont la colonne numeromagazine vaut 115 et affiche les colonnes auteur et titre.

Ajoutons une nouvelle table, celle des magazines. Nous lui donnerons deux colonnes, le numéro et la date de sortie.

base1=# CREATE TABLE magazines (numeromagazine integer primary key, datesortie date);

NOTICE: CREATE TABLE / PRIMARY KEY créera un index implicite « magazines_pkey » pour la table « magazines »

CREATE TABLE

Remarquez que la définition de clé primaire est intégrée à la définition de la table. Maintenant, nous allons pouvoir ajouter des éléments dans cette table.

base1=# INSERT INTO magazines (numeromagazine, datesortie) VALUES (115, '2009-04-01');

INSERT 0 1

base1=# INSERT INTO magazines (numeromagazine, datesortie) VALUES (116, '2009-05-01');

INSERT 0 1

base1=# INSERT INTO magazines (numeromagazine, datesortie) VALUES (117, '2009-06-01');

INSERT 0 1

base1=# INSERT INTO magazines (numeromagazine, datesortie) VALUES (118, '2009-07-01');

INSERT 0 1

Un des gros intérêts des SGBD, c'est de pouvoir mettre la vérification des données de leur côté. Par exemple, nous pouvons très bien faire en sorte qu'il ne soit pas possible d'insérer un article pour lequel le numéro du magazine n'existe pas dans la table magazines. Le terme technique est « clé étrangère ». Voici sa déclaration

base1=# ALTER TABLE articles ADD CONSTRAINT mag FOREIGN KEY (numeromagazine) REFERENCES magazines(numeromagazine);

ALTER TABLE

Testons de suite :

base1=# INSERT INTO articles (numeromagazine, auteur, titre) VALUES (115, 'Lionel Tricon', 'Espionnez vos applications avec strace et ltrace');

INSERT 0 1

Le numéro 115 existe bien, donc l'insertion est acceptée.

base1=# insert into articles (numeromagazine, auteur, titre) values (114, 'Olivier Delhomme', 'ZFS sous GNU/Linux');

ERREUR: une instruction insert ou update sur la table « articles » viole la contrainte de clé

étrangère « mag »

DÉTAIL : La clé (numeromagazine)=(114) n'est pas présente dans la table « magazines ».

Le numéro 114 n'existe pas, donc un échec a lieu lors de cette insertion.

base1=# INSERT INTO magazines (numeromagazine, datesortie) VALUES (114, '2009-03-01');

INSERT 0 1

base1=# INSERT INTO articles (numeromagazine, auteur, titre) VALUES (114, 'Olivier Delhomme', 'ZFS sous GNU/Linux');

INSERT 0 1

Après insertion du magazine 114 dans la table magazines, l'insertion de l'article est possible.

Avec ces tables, nous allons pouvoir faire des requêtes un peu plus complexes. Par exemple, trouver la date de sortie de chaque article. Le lien est la colonne numeromagazine. Nous allons récupérer une colonne d'une table et une colonne d'une autre table et nous allons les joindre comme ceci :

base1=# SELECT datesortie, titre FROM articles, magazines

base1-# WHERE articles.numeromagazine = magazines.numeromagazine

base1-# ORDER BY datesortie;

datesortie |                      titre

------------+--------------------------------------------------

2009-03-01 | ZFS sous GNU/Linux

2009-04-01 | Créez votre live CD Debian 5.0 Lenny

2009-04-01 | Parce qu'y'en a marre

2009-04-01 | Espionnez vos applications avec strace et ltrace

2009-05-01 | Parce qu'y'en a marre

2009-06-01 | Parce qu'y'en a marre

2009-07-01 | Parce qu'y'en a marre

2009-07-01 | Ubuntu: installation dual boot sur clef USB

2009-07-01 | Stockage en ligne : NBD dans un tunnel SSH

(9 lignes)

Pour éviter d'avoir à saisir des grosses requêtes, nous pouvons en enregistrer certaines dans des vues :

base1=# CREATE VIEW date_et_titre_article AS

base1-# SELECT datesortie, titre FROM articles, magazines

base1-# WHERE articles.numeromagazine = magazines.numeromagazine

base1-# ORDER BY datesortie;

CREATE VIEW

base1=# SELECT * FROM date_et_titre_article;

datesortie |                      titre

------------+--------------------------------------------------

2009-03-01 | ZFS sous GNU/Linux

2009-04-01 | Créez votre live CD Debian 5.0 Lenny

2009-04-01 | Parce qu'y'en a marre

2009-04-01 | Espionnez vos applications avec strace et ltrace

2009-05-01 | Parce qu'y'en a marre

2009-06-01 | Parce qu'y'en a marre

2009-07-01 | Parce qu'y'en a marre

2009-07-01 | Ubuntu: installation dual boot sur clef USB

2009-07-01 | Stockage en ligne : NBD dans un tunnel SSH

(9 lignes)

Cette vue se comporte maintenant comme une table. Par exemple, nous pouvons filtrer sur la date :

base1=# SELECT * FROM date_et_titre_article

base1-# WHERE datesortie BETWEEN '2009-05-01' AND '2009-06-01';

datesortie |                      titre

------------+--------------------------------------------------

2009-05-01 | Parce qu'y'en a marre

2009-06-01 | Parce qu'y'en a marre

(2 lignes)

Bon, maintenant, nous savons insérer, supprimer et modifier des données grâce aux instructions INSERT, DELETE et UPDATE. Nous savons les récupérer grâce à l'instruction SELECT. Tout ceci est très bien. Mais encore faut-il pouvoir les sauvegarder, car on n'est jamais à l'abri d'un disque défectueux. L'outil pour PostgreSQL s'appelle pg_dump. Son utilisation basique est des plus simples :

postgres@debian1:~$ pg_dump base1 > base1.dump

Eh voilà ! Autant dire qu'il est difficile de trouver plus simple. Le résultat est un fichier texte SQL capable de générer la base base1, avec la même structure et les mêmes données. Pour la restaurer, psql est l'outil à connaître. Créons une nouvelle base et restaurons notre sauvegarde sur cette nouvelle base :

postgres@debian1:~$ createdb base2

postgres@debian1:~$ psql -f base1.dump base2

postgres@debian1:~$ psql base1

psql (8.4.0)

Saisissez « help » pour l'aide.

base1=# SELECT count(*) FROM articles;

  count

-------

  9

(1 ligne)

Conclusion

Ainsi se conclut notre entrée dans le monde de PostgreSQL. Évidemment, il y a plein d'autres choses à découvrir, mais ceci devrait vous permettre de commencer plus simplement.


Sur le même sujet

Gérer une base de données avec Adminer

Magazine
Marque
Linux Pratique
Numéro
121
|
Mois de parution
septembre 2020
|
Domaines
Résumé

La gestion des bases de données relationnelles est une technologie essentielle pour les entreprises. Sa complexité nécessite de disposer de logiciels pratiques et fiables pour manipuler les données avec efficacité et en toute sécurité. Adminer entre dans la catégorie poids plume de ces outils, mais ne manque pas d'arguments pour séduire les administrateurs de bases de données et les développeurs.

Sécurisation du serveur

Magazine
Marque
Linux Pratique
HS n°
Numéro
48
|
Mois de parution
septembre 2020
|
Domaines
Résumé

Notre serveur RHEL est désormais fin prêt à être utilisé et maintenu, sans peine et de manière confortable. Cependant, notre travail n’est pas terminé. Il nous reste encore un élément crucial à valider : nous assurer que le serveur est aussi sûr et protégé que possible face à un éventuel assaillant mal attentionné.

Détection d'anomalies par ACP

Magazine
Marque
MISC
Numéro
111
|
Mois de parution
septembre 2020
|
Domaines
Résumé

Retour de vacances. L’analyse du SIEM après un mois d’absence montre que dix incidents ont été déclenchés sur la base des alertes automatiques et ont pu être gérés convenablement par la chaîne de traitement d’incidents. Tout est-il sous contrôle ? Un analyste aimerait rapidement s’en assurer en complétant cette supervision par sa propre analyse du mois écoulé. Mais par où commencer ? Il est inenvisageable de regarder un mois de logs « rapidement » et d’autant plus quand on ne sait pas précisément ce que l’on cherche… Une solution possible est de recourir à des outils statistiques qui permettent d’identifier des périodes d’activité atypiques sur lesquelles concentrer son analyse. L’analyse en composantes principales (ACP ou PCA en anglais) est une méthode statistique qui peut répondre relativement efficacement à cette problématique. L’article présente cette méthode et son apport dans la détection d’anomalies, en prenant comme exemple l’analyse de flux réseaux.

Effectuer des sauvegardes avec rdiff-backup

Magazine
Marque
Linux Pratique
Numéro
121
|
Mois de parution
septembre 2020
|
Domaines
Résumé

Tous les jours, nous créons et manipulons des données. Certaines plus importantes que d’autres. Une chose que nous partageons tous c’est bien la peur de les perdre. Peut-être avez-vous déjà perdu des données suite à une panne de votre disque de stockage, l’attaque d’un virus ou le vol de votre ordinateur. Les personnes ayant déjà connu cette situation comprennent les tracas que cela peut causer. Vous allez découvrir dans ce tutoriel comment limiter le risque d’y faire face. La solution se trouve en un mot : « sauvegarde ». L’outil que nous allons vous présenter ici a été conçu pour vous aider à réaliser cette tâche de manière efficace et efficiente.

Aller plus loin avec coreboot

Magazine
Marque
GNU/Linux Magazine
HS n°
Numéro
110
|
Mois de parution
septembre 2020
|
Domaines
Résumé

Rappelez-vous dans le numéro 220 de GNU/Linux Magazine, j’avais écrit un petit article sur coreboot, le BIOS libre. Je vous propose cette fois d’aller plus loin, en explorant quelques façons de sécuriser un peu le processus de boot, et plus encore.

Par le même auteur

Évolution de PostgreSQL en version 11

Magazine
Marque
GNU/Linux Magazine
HS n°
Numéro
101
|
Mois de parution
mars 2019
|
Domaines
Résumé
La version 11 de PostgreSQL est sortie le 18 octobre. Une première version corrective est déjà sortie et la seconde est prévue pour le 14 février 2019. Cette nouvelle version est principalement une version 10 améliorée : rien de bien révolutionnaire, mais de nouvelles fonctionnalités qui aident bien.

La version 10 de PostgreSQL et la réplication

Magazine
Marque
GNU/Linux Magazine
HS n°
Numéro
99
|
Mois de parution
novembre 2018
|
Domaines
Résumé
Depuis la version 9.0, les développeurs de PostgreSQL améliorent sans cesse la réplication interne. Cette version ne fait pas exception, et continue à proposer de nouvelles options sur la réplication physique. Elle ajoute aussi un type de réplication attendu depuis longtemps, la réplication logique.

Performances et supervision avec PostgreSQL en version 10

Magazine
Marque
GNU/Linux Magazine
Numéro
220
|
Mois de parution
novembre 2018
|
Domaines
Résumé
Après avoir présenté les nouveautés majeures de la version 10, il nous reste à nous pencher sur les performances et la supervision. Cette version apporte beaucoup d’améliorations sur ces deux sujets. Les nouveautés en termes de supervision raviront les administrateurs cherchant à mieux comprendre le comportement de PostgreSQL. Quant aux améliorations de performance, elles sont nombreuses, la plus attendue étant un meilleur support de la parallélisation. Deux excellentes raisons pour installer cette version 10… avant de passer à la version 11 !

PostgreSQL 10, quelques nouvelles fonctionnalités

Magazine
Marque
GNU/Linux Magazine
HS n°
Numéro
97
|
Mois de parution
juillet 2018
|
Domaines
Résumé
La version 10 de PostgreSQL est sortie depuis quelques mois, il est temps de faire un tour des nouvelles fonctionnalités et des changements majeurs. Ce premier article va discuter de différents points particulièrement intéressants, mais les trois grosses nouveautés, à savoir le partitionnement, la réplication logique et les améliorations au niveau performance et supervision seront abordées chacune dans leur propre article.

Partitionnement avec PostgreSQL 10

Magazine
Marque
GNU/Linux Magazine
Numéro
217
|
Mois de parution
juillet 2018
|
Domaines
Résumé
Le partitionnement dans PostgreSQL a toujours été un contournement d’autres fonctionnalités pour arriver à une séparation plus ou moins invisible, du point de vue de l’application, des données d’une table sur plusieurs tables. Il y eut de nombreuses tentatives au fil des ans pour améliorer cela, mais il y eut autant d’échecs... sauf avec cette version 10 qui réussit haut la main ce challenge. Cet article explique les différentes améliorations liées au partitionnement en version 10. On y voit aussi que certaines limitations restent présentes.

PostgreSQL 9.6 : les fonctionnalités moteurs

Magazine
Marque
GNU/Linux Magazine
Numéro
201
|
Mois de parution
février 2017
|
Domaines
Résumé
Dans un précédent article (voir GNU/Linux Magazine n°198), nous avons pu voir la nouveauté phare de la version 9.6 de PostgreSQL. Mais il ne s’agit pas de la seule amélioration de cette nouvelle version. Les développeurs ont passé aussi beaucoup de temps sur l’API de gestion des tables distantes, sur les possibilités offertes par la réplication physique, et sur plusieurs optimisations du moteur. Ils ont aussi travaillé sur différents aspects de l’administration comme la sauvegarde et la sécurité.