Comment installer et utiliser PostgreSQL sur Ubuntu 18.04

Introduction

Les systèmes de gestion de bases de données relationnelles constituent un élément clé de nombreux sites Web et applications. Ils offrent un moyen structuré de stocker les informations, de les organiser et d’y accéder.

PostgreSQL, ou Postgres, est un système de gestion de base de données relationnelle qui fournit une implémentation du langage de requête SQL. Il est très apprécié pour de nombreux projets, petits et grands, et présente l’avantage de respecter les normes et d’être doté de nombreuses fonctionnalités avancées telles que les transactions fiables et la simultanéité sans verrouillage en lecture.

Ce guide montre comment installer Postgres sur une instance VPS Ubuntu 18.04 et fournit également des instructions pour l’administration de base de données.

Conditions préalables

Pour suivre ce tutoriel, vous aurez besoin d’un serveur Ubuntu 18.04 qui a été configuré en suivant notre guide de configuration initiale de serveur Ubuntu 18.04. Au terme de ce tutoriel préalable, votre serveur devrait avoir un utilisateur non root avec permissions sudo et un pare-feu basique.

Étape 1 - Installation de PostgreSQL

Les référentiels par défaut d’Ubuntu contiennent des packages Postgres, vous pouvez donc les installer en utilisant le gestionnaire de packages apt.

Dans la mesure où vous utilisez apt pour la première fois dans cette session, actualisez votre index local de packages. Puis, installez le package Postgres ainsi qu’un package -contrib qui ajoute quelques utilitaires et fonctionnalités supplémentaires :

sudo apt update
sudo apt install postgresql postgresql-contrib

Maintenant que le logiciel est installé, nous pouvons voir comment il fonctionne et en quoi il peut différer des systèmes de gestion de bases de données similaires que vous avez pu utiliser.

Étape 2 - Utilisation des rôles et des bases de données PostgreSQL

Par défaut, Postgres utilise la notion de « rôles » pour gérer l’authentification et l’autorisation. Ils sont, à certains égards, semblables aux comptes classiques de type Unix, mais Postgres ne fait pas de distinction entre utilisateurs et groupes et préfère le terme plus souple de « rôle ».

Une fois installé, Postgres est configuré pour utiliser l’authentification ident, ce qui signifie qu’il associe les rôles Postgres à un compte système Unix/Linux correspondant. Si un rôle existe au sein de Postgres, un nom d’utilisateur Unix/Linux portant le même nom peut se connecter à ce rôle.

La procédure d’installation a créé un compte utilisateur nommé postgres qui est associé au rôle Postgres par défaut. Pour utiliser Postgres, vous pouvez vous connecter à ce compte.

Il y a plusieurs façons d’utiliser ce compte pour accéder à Postgres.

Basculer sur le compte postgres

Basculez sur le compte postgres sur votre serveur en tapant :

sudo -i -u postgres

Vous pouvez maintenant accéder immédiatement à une invite Postgres en tapant :

psql

Cela vous permettra de vous connecter à l’invite PostgreSQL, et de là, vous serez libre d’interagir immédiatement avec le système de gestion de la base de données.

Sortez de l’invite PostgreSQL en tapant :

\q

Cela vous ramènera à l’invite de commande postgres de Linux.

Accéder à une invite Postgres sans changer de compte

Vous pouvez également exécuter la commande que vous souhaitez avec le compte postgres directement en utilisant sudo.

Par exemple, dans l’exemple précédent, on vous a indiqué de vous rendre à l’invite Postgres en basculant d’abord sur l’utilisateur postgres puis en exécutant psql pour ouvrir l’invite Postgres. Vous pouvez faire cela en une seule étape en exécutant la commande unique psql en tant qu’utilisateur postgres avec privilèges sudo, comme ceci :

sudo -u postgres psql

Cela vous connectera directement dans Postgres sans passer par le shell bash intermédiaire.

Là encore, vous pouvez quitter la session Postgres interactive en tapant :

\q

De nombreux cas d’utilisation nécessitent plus d’un rôle Postgres. Lisez ce qui suit pour savoir comment les configurer.

Étape 3 - Création d’un nouveau rôle

Actuellement, le rôle postgres est le seul à être configuré dans la base de données. Vous pouvez créer de nouveaux rôles à partir de la ligne de commande avec la commande createrole. L’indicateur --interactive vous invitera à indiquer le nom du nouveau rôle et vous demandera également si celui-ci doit disposer des autorisations de superutilisateur.

Si vous êtes connecté en tant que compte postgres, vous pouvez créer un nouvel utilisateur en tapant :

createuser --interactive

Si, au contraire, vous préférez utiliser sudo pour chaque commande sans quitter votre compte normal, tapez :

sudo -u postgres createuser --interactive

Le script vous proposera quelques choix et, en fonction de vos réponses, exécutera les bonnes commandes Postgres pour créer un utilisateur selon vos spécifications.

Output
Enter name of role to add: sammy
Shall the new role be a superuser? (y/n) y

Vous pouvez obtenir plus de contrôle en passant des indicateurs supplémentaires. Consultez les options en consultant la page man :

man createuser

Votre installation Postgres a maintenant un nouvel utilisateur, mais vous n’avez encore ajouté aucune base de données. La section suivante décrit ce processus.

Étape 4 - Création d’une nouvelle base de données

Une autre hypothèse posée par défaut par le système d’authentification Postgres est que tout rôle utilisé pour se connecter disposera d’une base de données du même nom à laquelle il pourra accéder.

Cela signifie que, si l’utilisateur que vous avez créé dans la section précédente est appelé sammy, ce rôle tentera de se connecter à une base de données également appelée « sammy » par défaut. Vous pouvez créer la base de données appropriée avec la commande createdb.

Si vous êtes connecté en tant que compte postgres, vous devez taper quelque chose comme :

createdb sammy

Si, au contraire, vous préférez utiliser sudo pour chaque commande sans quitter votre compte normal, vous devez taper :

sudo -u postgres createdb sammy

Cette flexibilité offre de multiples façons de créer des bases de données au besoin.

Étape 5 - Ouverture d’une invite Postgres avec le nouveau rôle

Pour vous connecter avec une authentification basée sur ident, vous aurez besoin d’un utilisateur Linux portant le même nom que votre rôle et votre base de données Postgres.

Si vous n’avez pas d’utilisateur Linux correspondant disponible, vous pouvez en créer un avec la commande adduser. Vous devrez le faire à partir de votre compte non root avec privilèges sudo (c’est-à-dire sans être connecté en tant qu’utilisateur postgres) :

sudo adduser sammy

Une fois ce nouveau compte disponible, vous pouvez basculer sur ce dernier et vous connecter à la base de données en tapant :

sudo -i -u sammy
psql

Ou alors, vous pouvez le faire en ligne :

sudo -u sammy psql

Cette commande vous connectera automatiquement, en supposant que tous les composants ont été correctement configurés.

Si vous souhaitez que votre utilisateur se connecte à une autre base de données, vous pouvez le faire en spécifiant la base de données comme ceci :

psql -d postgres

Une fois connecté, vous pouvez vérifier vos informations de connexion actuelles en tapant :

\conninfo

Output
You are connected to database "sammy" as user "sammy" via socket in "/var/run/postgresql" at port "5432".

Cette commande est utile si vous vous connectez à des bases de données qui ne sont pas celles par défaut ou avec des utilisateurs qui ne sont pas ceux par défaut.

Étape 6 - Création et suppression de tables

Maintenant que vous savez comment vous connecter au système de base de données PostgreSQL, il est temps de découvrir quelques tâches de gestion basiques de Postgres.

Tout d’abord, créez une table pour stocker certaines données. Par exemple, une table décrivant certains équipements d’une aire de jeux (« playground »).

La syntaxe de base de cette commande est la suivante :

CREATE TABLE table_name (
    column_name1 col_type (field_length) column_constraints,
    column_name2 col_type (field_length),
    column_name3 col_type (field_length)
);

Comme vous pouvez le voir, ces commandes donnent un nom à la table, puis définissent les colonnes ainsi que le type de colonne et la longueur maximale des données du champ. Vous pouvez aussi éventuellement ajouter des contraintes de table pour chaque colonne.

Vous pouvez en apprendre davantage sur comment créer et gérer des tables dans Postgres ici.

À des fins de démonstration, créez une simple table comme celle-ci :

CREATE TABLE playground (
    equip_id serial PRIMARY KEY,
    type varchar (50) NOT NULL,
    color varchar (25) NOT NULL,
    location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
    install_date date
);

Ces commandes permettront de créer une table répertoriant les équipements de l’aire de jeux. Cela commence par un ID d’équipement, qui est de type serial. Ce type de données est un entier auto-incrémenté. Vous avez également donné à cette colonne la contrainte primary key, ce qui signifie que les valeurs doivent être uniques et non nulles.

Pour deux des colonnes (equip_id et install_date), les commandes ne spécifient pas de longueur de champ. En effet, certains types de colonnes n’ont pas besoin d’une longueur fixe car la longueur est déduite du type.

Les deux commandes suivantes créent respectivement les colonnes type et color pour l’équipement, lesquelles ne peuvent pas être vides. La commande suivante crée une colonne location et une contrainte exigeant que la valeur soit l’une des huit valeurs possibles. La dernière commande crée une colonne date qui enregistre la date à laquelle vous avez installé l’équipement.

Vous pouvez voir votre nouvelle table en tapant :

\d

Output
                  List of relations
 Schema |          Name           |   Type   | Owner
--------+-------------------------+----------+-------
 public | playground              | table    | sammy
 public | playground_equip_id_seq | sequence | sammy
(2 rows)

Votre table « playground » est là, mais il y a aussi un élément nommé playground_equip_id_seq qui est du type sequence. Il s’agit d’une représentation du type serial que vous avez donné à votre colonne equip_id. Ceci permet de garder une trace du prochain numéro de la séquence et est créé automatiquement pour les colonnes de ce type.

Si vous voulez voir uniquement la table sans la séquence, vous pouvez taper :

\dt

Output
          List of relations
 Schema |    Name    | Type  | Owner
--------+------------+-------+-------
 public | playground | table | sammy
(1 row)

Étape 7 - Ajout, requête et suppression de données dans une table

Maintenant que vous avez une table, vous pouvez y insérer quelques données.

À titre d’exemple, ajoutez un toboggan (« slide ») et une balançoire (« swing ») en appelant la table à laquelle vous voulez les ajouter, en nommant les colonnes et en fournissant ensuite des données pour chaque colonne, comme ceci :

INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2017-04-28');
INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2018-08-16');

Vous devez faire attention lors de la saisie des données afin d’éviter quelques problèmes courants. Pour commencer, les noms des colonnes ne doivent pas être mis entre guillemets, mais les valeurs des colonnes que vous saisissez doivent l’être.

Une autre chose à garder à l’esprit est que vous n’entrez pas de valeur pour la colonne equip_id. Cette dernière est en effet générée automatiquement à chaque fois qu’une nouvelle ligne est créée dans la table.

Récupérez les informations que vous avez ajoutées en tapant :

SELECT * FROM playground;

Output
 equip_id | type  | color  | location  | install_date
----------+-------+--------+-----------+--------------
        1 | slide | blue   | south     | 2017-04-28
        2 | swing | yellow | northwest | 2018-08-16
(2 rows)

Ici, vous pouvez voir que votre equip_id a bien été renseigné et que toutes vos autres données ont été organisées correctement.

Si le toboggan de l’aire de jeu se casse et que vous devez l’enlever, vous pouvez également enlever la ligne de votre table en tapant :

DELETE FROM playground WHERE type = 'slide';

Interrogez à nouveau la table :

SELECT * FROM playground;

Output
 equip_id | type  | color  | location  | install_date
----------+-------+--------+-----------+--------------
        2 | swing | yellow | northwest | 2018-08-16
(1 row)

Vous pouvez constater que votre toboggan ne fait plus partie de la table.

Étape 8 - Ajout et suppression de colonnes dans une table

Après avoir créé une table, vous pouvez la modifier pour ajouter ou supprimer des colonnes relativement facilement. Ajoutez une colonne pour indiquer la dernière visite de maintenance pour chaque équipement en tapant :

ALTER TABLE playground ADD last_maint date;

Si vous affichez à nouveau les informations de votre table, vous verrez que la nouvelle colonne a été ajoutée (mais qu’aucune donnée n’a été saisie) :

SELECT * FROM playground;

Output
 equip_id | type  | color  | location  | install_date | last_maint
----------+-------+--------+-----------+--------------+------------
        2 | swing | yellow | northwest | 2018-08-16   |
(1 row)

La suppression d’une colonne est tout aussi simple. Si vous constatez que vos collègues utilisent un outil distinct pour suivre l’historique de la maintenance, vous pouvez supprimer la colonne en tapant :

ALTER TABLE playground DROP last_maint;

Cela supprime la colonne last_maint et toutes les valeurs qui s’y trouvent, mais laisse toutes les autres données intactes.

Étape 9 - Mise à jour des données dans une table

Jusqu’à présent, vous avez appris comment ajouter des enregistrements à une table et comment les supprimer, mais ce tutoriel n’a pas encore évoqué comment modifier des entrées existantes.

Vous pouvez mettre à jour les valeurs d’une entrée existante en interrogeant l’enregistrement que vous souhaitez et en paramétrant la colonne sur la valeur que vous souhaitez utiliser. Vous pouvez effectuer une requête pour l’enregistrement « swing » (cela correspondra à* chaque* balançoire de votre table) et changer sa couleur en « red » (rouge). Cela pourrait être utile si vous donniez un coup de peinture à votre balançoire :

UPDATE playground SET color = 'red' WHERE type = 'swing';

Vous pouvez vérifier que l’opération a réussi en effectuant une nouvelle requête :

SELECT * FROM playground;

Output
 equip_id | type  | color | location  | install_date
----------+-------+-------+-----------+--------------
        2 | swing | red   | northwest | 2010-08-16
(1 row)

Comme vous pouvez le voir, votre balançoire est maintenant enregistrée comme étant rouge.

Conclusion

Vous avez maintenant configuré PostgreSQL sur votre serveur Ubuntu 18.04. Cependant, il y a encore beaucoup de choses à découvrir sur Postgres. Voici d’autres guides sur l’utilisation de Postgres :

• Une comparaison des systèmes de gestion de bases de données relationnelles
• Découvrir comment créer et gérer des tables avec Postgres
• Apprendre à mieux gérer les rôles et les autorisations
• Créer des requêtes dans Postgres avec Select
• Découvrir comment sécuriser PostgreSQL
• Découvrir comment sauvegarder une base de données Postgres