Créer une base avec PostgreSQL

Magazine
Marque
GNU/Linux Magazine
HS n°
Numéro
44
Mois de parution
octobre 2009
Spécialité(s)


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.

 



Article rédigé par

Par le(s) même(s) auteur(s)

Stockage efficace de données sous PostgreSQL

Magazine
Marque
GNU/Linux Magazine
HS n°
Numéro
118
Mois de parution
février 2022
Spécialité(s)
Résumé

Stocker des données dans une base PostgreSQL est assez simple : un CREATE TABLE, et c’est parti pour les insertions. Cependant, même si un CREATE TABLE semble assez simple, réfléchir à la construction de cet ordre SQL est important. Le type des données et l’ordre des colonnes jouent un rôle important sur la volumétrie de la table, et donc sur ses performances.

Réplication avec PostgreSQL

Magazine
Marque
Linux Pratique
HS n°
Numéro
51
Mois de parution
juin 2021
Spécialité(s)
Résumé

Un besoin souvent remonté des utilisateurs est de disposer d’un serveur très rapidement opérationnel quand le serveur en production est tombé. Restaurer une sauvegarde prend souvent du temps et même si ce temps ne semble pas très important, il est parfois crucial de pouvoir restaurer le service en quelques minutes, voire secondes. La réplication répond à ce besoin.

Les derniers articles Premiums

Les derniers articles Premium

Stubby : protection de votre vie privée via le chiffrement des requêtes DNS

Magazine
Marque
Contenu Premium
Spécialité(s)
Résumé

Depuis les révélations d’Edward Snowden sur l’espionnage de masse des communications sur Internet par la NSA, un effort massif a été fait pour protéger la vie en ligne des internautes. Cet effort s’est principalement concentré sur les outils de communication avec la généralisation de l’usage du chiffrement sur le web (désormais, plus de 90 % des échanges se font en HTTPS) et l’adoption en masse des messageries utilisant des protocoles de chiffrement de bout en bout. Cependant, toutes ces communications, bien que chiffrées, utilisent un protocole qui, lui, n’est pas chiffré par défaut, loin de là : le DNS. Voyons ensemble quels sont les risques que cela induit pour les internautes et comment nous pouvons améliorer la situation.

Surveillez la consommation énergétique de votre code

Magazine
Marque
Contenu Premium
Spécialité(s)
Résumé

Être en mesure de surveiller la consommation énergétique de nos applications est une idée attrayante, qui n'est que trop souvent mise à la marge aujourd'hui. C'est d'ailleurs paradoxal, quand on pense que de plus en plus de voitures permettent de connaître la consommation instantanée et la consommation moyenne du véhicule, mais que nos chers ordinateurs, fleurons de la technologie, ne le permettent pas pour nos applications... Mais c'est aussi une tendance qui s'affirme petit à petit et à laquelle à terme, il devrait être difficile d'échapper. Car même si ce n'est qu'un effet de bord, elle nous amène à créer des programmes plus efficaces, qui sont également moins chers à exécuter.

Donnez une autre dimension à vos logs avec Vector

Magazine
Marque
Contenu Premium
Spécialité(s)
Résumé

Avoir des informations précises et détaillées sur ce qu’il se passe dans une infrastructure, et sur les applications qu'elle héberge est un enjeu critique pour votre business. Cependant, ça demande du temps, temps qu'on préfère parfois se réserver pour d'autres tâches jugées plus prioritaires. Mais qu'un système plante, qu'une application perde les pédales ou qu'une faille de sécurité soit découverte et c'est la panique à bord ! Alors je vous le demande, qui voudrait rester aveugle quand l'observabilité a tout à vous offrir ?

Les listes de lecture

9 article(s) - ajoutée le 01/07/2020
Vous désirez apprendre le langage Python, mais ne savez pas trop par où commencer ? Cette liste de lecture vous permettra de faire vos premiers pas en découvrant l'écosystème de Python et en écrivant de petits scripts.
11 article(s) - ajoutée le 01/07/2020
La base de tout programme effectuant une tâche un tant soit peu complexe est un algorithme, une méthode permettant de manipuler des données pour obtenir un résultat attendu. Dans cette liste, vous pourrez découvrir quelques spécimens d'algorithmes.
10 article(s) - ajoutée le 01/07/2020
À quoi bon se targuer de posséder des pétaoctets de données si l'on est incapable d'analyser ces dernières ? Cette liste vous aidera à "faire parler" vos données.
Voir les 102 listes de lecture

Abonnez-vous maintenant

et profitez de tous les contenus en illimité

Je découvre les offres

Déjà abonné ? Connectez-vous