Comment installer et utiliser PostgreSQL sur Ubuntu

Introduction

Les systèmes de gestion de bases de données sont un élément central de nombreux sites web et applications, fournissant un moyen structuré de stocker, organiser et accéder aux informations. PostgreSQL, communément appelé Postgres, est un système de gestion de bases de données relationnelles open-source de premier plan. Il offre une implémentation complète du langage de requête SQL et est bien réputé pour sa conformité aux standards et ses fonctionnalités avancées, telles que des transactions fiables et la concurrence sans verrouillage de lecture.

Ce guide montre le processus complet pour configurer PostgreSQL sur un serveur Ubuntu, en commençant par l’installation des packages logiciels nécessaires. Vous apprendrez ensuite comment interagir avec le système d’authentification de Postgres, qui est géré via les « rôles », y compris la création de nouveaux utilisateurs et bases de données. Ensuite, nous aborderons les fondamentaux de la gestion de bases de données : se connecter à votre nouvelle base de données, définir une structure de données en créant des tables et manipuler les données en utilisant des commandes SQL de base. Nous explorerons également les principaux sujets administratifs, y compris la configuration essentielle pour les performances, les stratégies de sauvegarde et de restauration, une comparaison de PostgreSQL avec d’autres moteurs de base de données, et un guide pour résoudre les problèmes courants.

Prérequis

Pour suivre ce tutoriel, vous aurez besoin d’un serveur Ubuntu qui a été configuré en suivant notre guide de configuration initiale du serveur pour Ubuntu. Après avoir terminé ce tutoriel préalable, votre serveur devrait disposer d’un utilisateur non root avec des permissions sudo et d’un pare-feu de base.

Étape 1 — Installation de PostgreSQL

Pour installer PostgreSQL, commencez par actualiser l’index des paquets locaux de votre serveur :

sudo apt update

Ensuite, installez le paquet postgres ainsi qu’un paquet -contrib qui ajoute certaines utilités et fonctionnalités supplémentaires :

sudo apt install postgresql postgresql-contrib

Vous pouvez vérifier la version en exécutant la commande suivante :

psql --version

Assurez-vous que le service est démarré :

sudo systemctl start postgresql.service

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

Par défaut, Postgres utilise un concept appelé « rôles » pour gérer l'authentification et l'autorisation. Ceux-ci sont, d'une certaine manière, similaires aux utilisateurs et groupes classiques de type Unix.

Lors de l'installation, Postgres est configuré pour utiliser l'authentification peer, ce qui signifie qu'il associe les rôles Postgres à un compte système Unix/Linux correspondant. Si un rôle existe dans Postgres, un nom d'utilisateur Unix/Linux portant le même nom peut se connecter en tant que ce rôle.

La procédure d'installation a créé un compte utilisateur appelé postgres qui est associé au rôle Postgres par défaut. Il existe plusieurs façons d'utiliser ce compte pour accéder à Postgres. Une des façons est de passer au compte postgres sur votre serveur en exécutant la commande suivante :

sudo -i -u postgres

Vous pouvez ensuite accéder à l'invite Postgres en exécutant :

psql

Cela vous connectera à l'invite PostgreSQL, et à partir de là, vous êtes libre d'interagir immédiatement avec le système de gestion de base de données.

Pour sortir de l'invite PostgreSQL, exécutez ce qui suit :

\q

Cela vous ramènera à l'invite de commande Linux postgres. Pour revenir à votre utilisateur système habituel, exécutez la commande exit :

exit

Une autre façon de se connecter à l'invite Postgres est d'exécuter la commande psql en tant que compte postgres directement avec sudo :

sudo -u postgres psql

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

Encore une fois, vous pouvez quitter la session interactive de Postgres en exécutant ce qui suit :

\q

Étape 3 — Création d'un nouveau rôle

Si vous êtes connecté avec le compte postgres, vous pouvez créer un nouveau rôle en exécutant la commande suivante :

createuser --interactive

Si, au contraire, vous préférez utiliser sudo pour chaque commande sans changer de compte habituel, exécutez :

sudo -u postgres createuser --interactive

Dans tous les cas, le script vous proposera quelques choix et, en fonction de vos réponses, exécutera les commandes Postgres appropriées pour créer un utilisateur selon vos spécifications.

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

Étape 4 — Création d'une nouvelle base de données

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

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

Si vous êtes connecté avec le compte postgres, vous taperiez quelque chose comme ce qui suit :

createdb sammy

Si, en revanche, vous préférez utiliser sudo pour chaque commande sans changer de compte, vous exécuteriez :

sudo -u postgres createdb sammy

Étape 5 — La méthode SQL pour créer un nouvel utilisateur et une nouvelle base de données (optionnel)

Les commandes shell createuser et createdb sont des outils pratiques. Cependant, pour plus de contrôle, vous pouvez effectuer les mêmes actions directement dans PostgreSQL en utilisant des commandes SQL. Cette approche est souvent plus claire lorsqu'il s'agit de définir des mots de passe ou d'accorder des permissions spécifiques dès le départ.

1. Tout d'abord, connectez-vous en tant qu'utilisateur administratif postgres :

   sudo -u postgres psql

   Une fois dans l'invite PostgreSQL, vous pouvez utiliser CREATE ROLE et CREATE DATABASE pour configurer votre nouvel utilisateur et votre base de données.

2. Créer un nouveau rôle (utilisateur) : Alors que la commande shell createuser est interactive, la commande CREATE ROLE vous permet de tout définir en une seule instruction. Pour créer un utilisateur nommé sammy qui peut se connecter (LOGIN) et possède un mot de passe, exécutez :

   CREATE ROLE sammy WITH LOGIN PASSWORD 'your_strong_password';

   Si cet utilisateur doit également créer des bases de données, vous pouvez accorder cette permission en même temps :

   CREATE ROLE sammy WITH LOGIN PASSWORD 'your_strong_password' CREATEDB;

   Cela fournit un sous-ensemble des permissions de superutilisateur, permettant spécifiquement à l'utilisateur de créer de nouvelles bases de données. Répondre « oui » à la question du superutilisateur dans l'assistant interactif accorde également cette permission, mais SUPERUSER en lui-même est un rôle beaucoup plus large et plus dangereux.

3. Créer une nouvelle base de données : Ensuite, créez la base de données. Il est conseillé d'attribuer la propriété de la nouvelle base de données au nouveau rôle que vous venez de créer.

   CREATE DATABASE sammydb OWNER sammy;

   Pour accorder à l'utilisateur sammy l'autorisation de se connecter à la nouvelle base de données (une étape suivante courante), vous pouvez exécuter :

   GRANT ALL PRIVILEGES ON DATABASE sammydb TO sammy;

   Cela accorde des privilèges au niveau de la base de données comme CONNECT, mais pas les permissions pour des tables spécifiques. Notez que cela n'accorde pas automatiquement les permissions pour voir ou modifier les tables de la base de données ; des commandes GRANT supplémentaires sur le schéma (par exemple, public) et sur les tables elles-mêmes sont nécessaires pour cela.

4. Quittez la session postgres : Vous pouvez maintenant quitter l'invite psql pour l'utilisateur postgres.

   \q

Parce que vous avez défini un mot de passe, ce nouvel utilisateur peut s'authentifier en utilisant des méthodes autres que ident, ce qui est nécessaire pour les connexions à distance.

Étape 6 — Ouvrir 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 depuis votre compte non-root avec des 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 soit basculer et vous connecter à la base de données en exécutant ce qui suit :

sudo -i -u sammy

psql

Ou, vous pouvez le faire en ligne :

sudo -u sammy psql

Cette commande vous connectera automatiquement, à condition que tous les composants aient été correctement configurés.

Si vous voulez que votre utilisateur se connecte à une base de données différente, vous pouvez le faire en spécifiant la base de données comme suit :

psql -d postgres

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

\conninfo

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

Ceci est utile si vous vous connectez à des bases de données non par défaut ou avec des utilisateurs non par défaut.

Étape 7 — Création et suppression de tables

Maintenant que vous savez comment vous connecter au système de base de données PostgreSQL, vous pouvez apprendre quelques tâches de gestion de base de Postgres.

La syntaxe de base pour créer des tables 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 également ajouter éventuellement des contraintes de table pour chaque colonne.

Pour en savoir plus sur la création et la suppression de tables, consultez notre article sur la façon de créer et de gérer des tables dans Postgres.

À des fins de démonstration, créez le tableau suivant :

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

);

Cette commande va créer une table qui inventorie le matériel de terrain de jeu. La première colonne de la table contiendra les numéros d'identification du matériel du type serial, qui est un entier auto-incrémenté. Cette colonne a également la contrainte PRIMARY KEY, ce qui signifie que les valeurs qu'elle contient doivent être uniques et non nulles.

Les deux lignes suivantes créent des colonnes pour le type et la couleur de l'équipement, aucune des deux ne pouvant être vide. La ligne suivante crée également une colonne emplacement avec une contrainte exigeant que la valeur soit l'une des huit possibles. La dernière ligne crée une colonne date qui enregistre la date à laquelle vous avez installé l'équipement.

Pour deux des colonnes (equip_id et install_date), la commande ne spécifie pas de longueur de champ. La raison en est que certains types de données n’exigent pas une longueur définie car la longueur ou le format est implicite.

Vous pouvez voir votre nouvelle table en tapant :

\d

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

Votre table de terrain de jeu est ici, mais il y a aussi quelque chose appelé playground_equip_id_seq qui est de type sequence. Il s'agit d'une représentation du type serial que vous avez attribué à votre colonne equip_id. Cela garde une trace du numéro suivant dans la séquence et est créé automatiquement pour les colonnes de ce type.

Si vous voulez voir seulement le tableau sans la séquence, vous pouvez taper :

\dt

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

Avec une table prête, utilisons-la pour nous exercer à gérer des données.

Étape 8 — Ajouter, interroger et supprimer des données dans une table

Maintenant que vous avez une table, vous pouvez y insérer des données. À titre d'exemple, ajoutez un toboggan et une balançoire en appelant la table dans laquelle vous souhaitez ajouter des données, en nommant les colonnes, puis en fournissant 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 devriez faire attention lors de la saisie des données pour éviter quelques écueils courants. Par exemple, ne mettez pas les noms de colonnes entre guillemets, mais les valeurs de colonne que vous saisissez doivent être entre guillemets.

Une autre chose à garder à l'esprit est que vous ne devez pas entrer de valeur pour la colonne equip_id. En effet, celle-ci est générée automatiquement chaque fois que vous ajoutez une nouvelle ligne à la table.

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

SELECT * FROM playground;

 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 été rempli avec succès et que toutes vos autres données ont été correctement organisées.

Si le toboggan dans le terrain de jeu se casse et que vous devez l'enlever, vous pouvez également supprimer la ligne de votre tableau en tapant :

DELETE FROM playground WHERE type = 'slide';

Interrogez à nouveau la table :

SELECT * FROM playground;

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

Remarquez que la ligne slide ne fait plus partie du tableau.

Étape 9 — Ajouter et supprimer des colonnes d'un tableau

Après avoir créé un tableau, vous pouvez le modifier en ajoutant ou en supprimant des colonnes. Ajoutez une colonne pour afficher la dernière visite de maintenance de chaque équipement en tapant :

ALTER TABLE playground ADD last_maint date;

Si vous consultez à 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;

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

Si vous constatez que votre équipe utilise un outil séparé pour suivre l'historique de maintenance, vous pouvez supprimer la colonne en tapant :

ALTER TABLE playground DROP last_maint;

Cela supprime la colonne last_maint et toutes les valeurs qu'elle contient, mais laisse toutes les autres données intactes.

Étape 10 — Mise à jour des données dans un tableau

Jusqu'à présent, vous avez appris à ajouter des enregistrements à une table et à les supprimer, mais ce tutoriel n'a pas encore abordé la modification des entrées existantes.

Vous pouvez mettre à jour les valeurs d'une entrée existante en recherchant l'enregistrement souhaité et en définissant la colonne sur la valeur que vous souhaitez utiliser. Vous pouvez rechercher l'enregistrement swing (cela correspondra à chaque balançoire de votre tableau) et changer sa couleur en rouge. Cela pourrait être utile si vous avez donné une couche de peinture à la balançoire :

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

Vous pouvez vérifier que l'opération a réussi en interrogeant à nouveau les données :

SELECT * FROM playground;

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

Comme vous pouvez le voir, la lame est maintenant enregistrée comme étant rouge.

Conseils de configuration et de performance pour PostgreSQL

Configurer PostgreSQL correctement est essentiel pour une base de données stable et rapide. Les paramètres par défaut sont conçus pour être sûrs sur n'importe quel matériel, ce qui signifie qu'ils ne sont presque jamais adaptés à une charge de travail en production. L'optimisation des performances implique d'ajuster l'allocation des ressources—principalement la mémoire, les entrées/sorties et la gestion des connexions—pour correspondre au matériel de votre serveur et aux modèles de requêtes spécifiques de votre application.

Discutons de quelques paramètres de configuration clés et des stratégies générales pour améliorer les performances de la base de données.

Fichiers de configuration clés

Avant d’effectuer les réglages, vous devez savoir où se trouvent les fichiers de configuration. Sur Ubuntu, ils se trouvent dans /etc/postgresql/[version]/main/ :

• postgresql.conf : Le fichier principal de configuration du serveur. C'est ici que vous ajustez tous les paramètres liés à la performance.
• pg_hba.conf : Ce fichier contrôle l’authentification des clients (quels utilisateurs peuvent se connecter depuis quelles adresses). Il n’affecte pas directement les performances des requêtes mais est fondamental pour la sécurité.

Après avoir modifié le postgresql.conf, vous pouvez appliquer les changements en rechargeant le service PostgreSQL :

sudo systemctl reload postgresql

Cependant, certains paramètres (indiqués comme contexte postmaster dans la documentation) nécessitent un redémarrage complet :

sudo systemctl restart postgresql

Vous pouvez exécuter la commande suivante pour vérifier si vous devez redémarrer ou recharger après une modification :

SELECT context FROM pg_settings WHERE name = 'parameter_name';

En revanche, les modifications du pg_hba.conf nécessitent uniquement un rechargement.

sudo systemctl reload postgresql

Configuration de la mémoire

La mémoire est le facteur le plus important dans les performances d'une base de données. PostgreSQL utilise la mémoire partagée pour mettre en cache les données et une mémoire séparée par connexion pour des opérations comme le tri.

tampons_partagés

Ceci est le paramètre de mémoire le plus important. Il définit la quantité de mémoire que PostgreSQL consacre à son cache de données. Un cache plus grand signifie qu'une plus grande partie de votre ensemble de données peut être lue depuis la mémoire rapide plutôt que depuis le disque lent.

• Directive : Commencez par régler shared_buffers à 25 % de la RAM totale de votre système.

• Exemple : Sur un serveur avec 16 Go de RAM, un bon point de départ est 4 Go.

• Configuration (postgresql.conf) :

  # Default is 128MB, which is too low
  shared_buffers = 4GB

mémoire_de_travail

Ce paramètre définit la quantité de mémoire que chaque opération de base de données individuelle (comme un tri, une jointure par hachage ou un bitmap) peut utiliser avant de déborder sur des fichiers temporaires sur le disque. Les requêtes complexes peuvent comporter plusieurs opérations, chacune utilisant cette quantité de mémoire.

Le définir trop haut peut entraîner une épuisement de la mémoire si de nombreuses requêtes complexes s'exécutent en même temps. Le définir trop bas entraîne un tri fréquent et lent basé sur le disque.

• Guide : Commencez avec la valeur par défaut (4MB). Analysez vos requêtes lentes en utilisant EXPLAIN ANALYZE. Si vous voyez « Sort Method: external merge Disk » ou « HashAgg Disk », cela signifie que work_mem est trop faible. Augmentez-le progressivement (par exemple, 16MB, 32MB) pour votre session afin de tester, et ne le définissez globalement que si vous en êtes sûr.

mémoire_de_travail_pour_maintenance

Ce paramètre réserve de la mémoire pour les opérations de maintenance internes, telles que VACUUM, CREATE INDEX et ALTER TABLE ADD FOREIGN KEY.

• Directive : Définissez ceci plus haut que work_mem (par exemple, 128MB ou 256MB). Comme ces opérations ne sont pas exécutées aussi fréquemment que les requêtes utilisateur, vous pouvez vous permettre d'être plus généreux pour accélérer la maintenance.

Configuration des points de contrôle et du WAL

Les points de contrôle sont des processus où PostgreSQL écrit toutes les données "sales" (modifications en mémoire) dans les fichiers de données permanents sur le disque. C'est une opération très intensive en E/S. Le journal de préécriture (WAL) enregistre chaque transaction. Les ajuster permet d'équilibrer la charge d'E/S et le temps de récupération.

16MB

Gestion des connexions

Chaque connexion à PostgreSQL utilise de la mémoire. Un grand nombre de connexions peut épuiser les ressources du serveur.

• max_connections : Le nombre maximal de connexions simultanées. La valeur par défaut est 100. Bien qu'il soit tentant d'augmenter ce nombre, chaque connexion consomme de la mémoire.
• Utilisez un gestionnaire de pool de connexions : Pour les applications qui ouvrent et ferment de nombreuses connexions (comme la plupart des applications web), ne augmentez max_connections. Utilisez plutôt un gestionnaire de pool de connexions externe comme PgBouncer ou Pgpool-II. Un pool maintient un petit nombre de connexions persistantes à la base de données et sert de nombreuses connexions d'application simultanées, ce qui est beaucoup plus efficace.

Planification et analyse des requêtes

Aucun réglage du serveur ne peut corriger une requête inefficace.

• EXPLAIN ANALYZE : C'est votre outil principal pour l'optimisation des requêtes. Exécutez cette commande sur vos requêtes lentes pour voir le plan d'exécution réel et le temps passé.

  EXPLAIN ANALYZE SELECT * FROM users WHERE last_name = 'Smith';

  Recherchez « Scan séquentiel » (Seq Scan) sur de grandes tables indexées. Cela signifie souvent qu'il vous manque un index ou que la requête n'est pas assez sélective.

• Créer des index : Si vous filtrez ou faites souvent des jointures sur certaines colonnes (par exemple, user_id, email, created_at), elles sont de bons candidats pour un index.

  CREATE INDEX idx_users_on_email ON users (email);

• Exécutez VACUUM ANALYZE : PostgreSQL utilise un planificateur de requêtes qui repose sur des statistiques concernant les données de vos tables. Ces statistiques sont mises à jour par ANALYZE. Bien que autovacuum gère cela automatiquement, il est conseillé d'exécuter manuellement un VACUUM ANALYZE; après des chargements importants de données ou si vous remarquez que les plans de requêtes sont médiocres.

Sauvegarder et restaurer les bases de données PostgreSQL

Protéger vos données est une responsabilité principale de l'administration de bases de données. Des sauvegardes régulières et vérifiées constituent la défense la plus efficace contre la corruption des données, les pannes matérielles ou les erreurs humaines. PostgreSQL offre un ensemble d'outils robustes en ligne de commande pour créer des sauvegardes logiques et procéder à des restaurations.

Il existe deux principales catégories de sauvegardes :

1. Sauvegardes logiques (dumps SQL) : Ces sauvegardes se composent de commandes SQL qui, une fois exécutées, recréent les objets de la base de données (comme les tables et les index) ainsi que les données. Elles sont créées à l'aide des utilitaires pg_dump et pg_dumpall. Cette méthode est flexible et portable entre différentes versions de PostgreSQL.
2. Sauvegardes physiques (au niveau du système de fichiers) : Cette méthode consiste à copier les fichiers de données physiques depuis le répertoire de données de PostgreSQL. Elle est généralement utilisée pour le récupération jusqu’à un point dans le temps (PITR), une configuration avancée abordée plus loin dans cette section.

Créer des sauvegardes logiques avec pg_dump

L'utilitaire pg_dump sauvegarde une base de données unique. Il ne sauvegarde pas les objets globaux comme les rôles ou les espaces de tables. Il est très flexible et peut être exécuté sur une base de données en fonctionnement sans bloquer les lecteurs ni les rédacteurs.

Options courantes de pg_dump

Vous pouvez contrôler la sortie de pg_dump avec plusieurs options en ligne de commande :

• -F c (Format : personnalisé) : Il s'agit du format recommandé pour la plupart des sauvegardes. Il produit une archive binaire compressée qui n'est pas lisible par l'homme, mais permet des restaurations parallèles ainsi que le réordonnancement ou l'exclusion d'objets lors de la restauration.
• -F p (Format : simple) : C'est le format par défaut. Il génère un fichier .sql volumineux. Son principal inconvénient est qu'il doit être restauré en une seule fois et qu'il ne prend pas en charge les restaurations parallèles.
• -s (Schéma uniquement) : Sauvegarde uniquement la structure de la base de données (tables, vues, index) sans aucune donnée.
• -a (Données uniquement) : Sauvegarde uniquement les données, en supposant que le schéma existe déjà dans la cible.
• --exclude-table=TABLE_NAME : Exclut une table spécifique de la sauvegarde. Cela est utile pour ignorer les tables volumineuses, temporaires ou non essentielles.
• -f FILENAME : Spécifie le fichier de sortie.
• -U USER : Spécifie l'utilisateur PostgreSQL avec lequel se connecter.

Ceci est la commande recommandée pour la plupart des tâches de sauvegarde. Elle se connecte à la base de données mydatabase en tant qu'utilisateur postgres et crée un fichier compressé nommé mydatabase.dump.

pg_dump -U postgres -F c -f mydatabase.dump mydatabase

Restauration des sauvegardes logiques

L'outil que vous utilisez pour la restauration dépend du format de votre fichier de sauvegarde.

Restauration à partir d’une archive personnalisée (.dump)

Utilisez l'utilitaire pg_restore. Cet outil offre une grande flexibilité, y compris la possibilité de restaurer en parallèle et d'exécuter des vérifications préalables.

Au lieu de créer manuellement la base de données en premier, il est préférable d'utiliser l'option --create. Cette option indique à pg_restore d'exécuter la commande CREATE DATABASE à partir de l'archive. Pour ce faire, vous devez vous connecter à une base de données existante différente, comme la base de données par défaut postgres.

La commande suivante se connecte à la base de données postgres, supprime et recrée mydatabase (à cause de --clean et --create), et utilise 4 travaux en parallèle pour la restaurer.

pg_restore -U postgres -d postgres --create --clean -j 4 mydatabase.dump

• -d postgres : Se connecte à la base de données postgres pour exécuter la commande de création.
• --create : Indique à pg_restore de créer la base de données cible (par exemple, mydatabase) avant de restaurer les données dedans.
• --clean : Lorsqu'il est utilisé avec --create, ce paramètre supprime et recrée entièrement la base de données cible. Lorsqu'il est utilisé seul (restaurer dans une base de données existante), il supprime les objets de la base de données existante avant de les recréer.
• -j 4 : Utilise 4 jobs parallèles pour la restauration.

Nous nous connectons à la base de données postgres car pg_restore a besoin d'une connexion à une base de données existante sur le serveur pour exécuter la commande CREATE DATABASE pour notre nouvelle cible.

Restauration à partir d’un fichier texte simple (.sql)

Utilisez le client standard psql. Cette méthode nécessite que la base de données cible existe déjà.

1. Créez la base de données :

   createdb -U postgres mydatabase_new

2. Passez le fichier .sql dans psql :

   psql -U postgres -d mydatabase_new < mydatabase.sql

Sauvegarde de toutes les bases de données (pg_dumpall)

Pour sauvegarder toutes les bases de données sur le serveur, ainsi que tous les objets globaux (rôles, utilisateurs et tablespaces), utilisez l'utilitaire pg_dumpall. Cet outil ne génère qu'un fichier .sql en texte brut.

# Run as the postgres superuser

pg_dumpall -U postgres > all_databases.sql

Pour restaurer à partir d'un fichier pg_dumpall, vous le transférez dans psql. Vous devez vous connecter en tant que superutilisateur (comme postgres) à une base de données par défaut (comme postgres lui-même). Le fichier script contient toutes les commandes CREATE ROLE et CREATE DATABASE nécessaires et les exécutera à partir de là.

# Run as the postgres superuser

psql -U postgres -d postgres -f all_databases.sql

Sauvegarde uniquement des rôles et des variables globales

Un schéma courant et utile consiste à sauvegarder uniquement les objets globaux. Cela se fait avec l'option --globals-only et constitue un moyen efficace de reproduire les permissions des utilisateurs sur plusieurs serveurs.

pg_dumpall -U postgres --globals-only > roles.sql

Résumé de la stratégie de sauvegarde et de restauration

Le tableau suivant résume la stratégie de sauvegarde et de restauration :

.sql or .dump

.sql

Bonnes pratiques pour l'automatisation et la sauvegarde

Un plan de sauvegarde n'est complet que lorsqu'il est automatisé, vérifié et sécurisé.

• Automatisez vos sauvegardes : Utilisez un outil standard de Linux comme cron pour planifier vos commandes pg_dump ou pg_dumpall afin qu'elles s'exécutent chaque nuit.
• Gérez les mots de passe de manière sécurisée : Pour exécuter des sauvegardes dans un script non interactif, ne stockez pas les mots de passe en dur. Utilisez le fichier .pgpass. Créez un fichier à ~/.pgpass dans le répertoire personnel de l’utilisateur (par exemple, /var/lib/postgresql/.pgpass). Le fichier doit avoir des permissions strictes 0600, sinon PostgreSQL l’ignorera. Le format est hostname:port:database:user:password. Vous pouvez utiliser * comme caractère générique pour n’importe quel champ, par exemple, localhost:5432:*:postgres:votre_mot_de_passe.
• Testez vos restaurations : Une sauvegarde n'est utile que si elle peut être restaurée. Exercez-vous régulièrement à restaurer vos sauvegardes sur un serveur distinct, non destiné à la production, pour vérifier leur intégrité.
• Vérifiez vos sauvegardes : Après avoir créé une sauvegarde, ajoutez une étape script pour vérifier que la taille du fichier n’est pas nulle. Pour les formats personnalisés, vous pouvez exécuter pg_restore -l mydatabase.dump &> /dev/null (qui liste le contenu dans /dev/null) pour confirmer que l’archive n’est pas corrompue en vérifiant le code de sortie de la commande.
• Stockez les sauvegardes à distance : Ne stockez pas vos sauvegardes sur le même serveur que votre base de données. Une panne complète du serveur entraînerait la perte des deux. Copiez les sauvegardes vers un emplacement distant tel que Linux-Console.net Spaces ou un autre serveur en tant que dernière étape de votre script.

Comparaison de PostgreSQL avec d'autres moteurs de base de données sur Ubuntu

Les dépôts de paquets d'Ubuntu offrent un accès à une grande variété de moteurs de bases de données. Le meilleur choix dépend entièrement des besoins de votre application, tels que son modèle de données, ses besoins en scalabilité et ses garanties de cohérence.

• PostgreSQL : Cette base de données objet-relationnelle est réputée pour son niveau élevé de conformité aux standards, son intégrité des données et son ensemble de fonctionnalités robustes. Elle est entièrement conforme aux normes ACID et prend bien en charge les types de données avancés ainsi que les requêtes complexes, ce qui en fait un choix courant pour une base de données principale dite « système de référence ».
• MySQL : Dans le cadre de la pile LAMP originale, MySQL est l'une des bases de données open-source les plus populaires. Elle est réputée pour sa facilité d'utilisation, ses performances de lecture historiquement élevées et sa communauté active. Elle est également entièrement conforme à l'ACID (avec le moteur InnoDB par défaut) et appréciée pour les applications web simples.
• MariaDB : Un fork de MySQL dirigé par la communauté. Bien qu'il conserve une grande compatibilité (en particulier avec les anciennes versions), il a évolué et introduit ses propres fonctionnalités, il peut donc ne pas être un remplacement complet et direct pour les applications basées sur les fonctionnalités modernes de MySQL 8.0 et ultérieures.
• SQLite : Ce n'est pas une base de données client-serveur. C'est une bibliothèque en C qui intègre un moteur de base de données complet dans une application, stockant l'ensemble de la base de données dans un seul fichier. Elle gère bien plusieurs lecteurs concurrents, mais n'est pas idéale pour les applications avec des écritures concurrentes fréquentes ou des scénarios multi-utilisateurs en réseau.
• MongoDB : Une base de données NoSQL de premier plan, MongoDB stocke les données sous forme de documents flexibles semblables à du JSON. Cela permet un schéma flexible et elle est conçue pour une haute disponibilité et une mise à l’échelle horizontale. Bien qu’elle soit réputée pour sa flexibilité, MongoDB prend également en charge les transactions ACID sur plusieurs documents depuis la version 4.0, ce qui en fait un choix viable pour les applications nécessitant une forte cohérence.
• Redis : Il s'agit d'un magasin de structures de données en mémoire, ce qui signifie qu'il conserve toutes ses données dans la RAM pour une vitesse exceptionnelle. Ce n'est pas un remplacement général d'une base de données sur disque, mais il est presque toujours utilisé en complément d'une telle base (comme PostgreSQL) pour fonctionner comme un cache haute vitesse ou un stockage de sessions.

Voici une comparaison de PostgreSQL avec d'autres moteurs de base de données populaires disponibles sur Ubuntu.

Comparaison des moteurs de base de données

Problèmes courants de PostgreSQL et dépannage sur Ubuntu

Exécuter PostgreSQL sur Ubuntu peut présenter des défis spécifiques, surtout pour les utilisateurs novices de son architecture sur les systèmes basés sur Debian. La plupart des erreurs entrent dans des catégories prévisibles : conflits d'installation, règles d'authentification, permissions des fichiers et gestion des services.

Avant de dépanner, votre outil le plus important est le fichier journal de PostgreSQL. Sur les installations standard d'Ubuntu, vous pouvez le trouver à /var/log/postgresql/postgresql-[version]-main.log. Vérifiez toujours ce journal en premier, car il fournit des messages d'erreur spécifiques qui guideront votre diagnostic.

Problèmes d'installation

Ces problèmes surviennent généralement lors du processus initial apt install ou lorsque initdb tente pour la première fois de créer un nouveau cluster de bases de données.

Conflits de paquets ou incompatibilités de version

Ce problème survient lorsque votre système tente d’installer des paquets PostgreSQL provenant de deux sources différentes, telles que le dépôt par défaut d’Ubuntu et le dépôt officiel du PostgreSQL Global Development Group (PGDG). Les dépôts d’Ubuntu contiennent souvent des versions plus anciennes et stables, tandis que le dépôt PGDG fournit les dernières versions. Les mélanger peut entraîner des dépendances cassées.

Dépannage :

• Vérifiez vos sources : Utilisez apt-cache policy postgresql pour voir quelles versions sont disponibles et depuis quel dépôt.
• Choisissez une source : Il est fortement recommandé d’utiliser le dépôt PGDG pour obtenir la version la plus récente. Si vous ajoutez le dépôt PGDG, vous devrez peut-être utiliser le pinning d’apt pour donner priorité à ses paquets par rapport à ceux par défaut d’Ubuntu.
• Réparer les installations endommagées : Si vous êtes déjà dans un état de conflit, sudo apt-get -f install peut le résoudre. Sinon, vous devrez peut-être purger complètement tous les paquets PostgreSQL (sudo apt-get purge "postgresql-*") et réinstaller à partir d'une source unique et propre.

Le port 5432 est déjà utilisé

Cette erreur signifie qu’un autre programme écoute déjà sur le port PostgreSQL par défaut (5432). C’est courant si une ancienne instance de PostgreSQL non désinstallée n’a pas réussi à s’arrêter ou si vous avez plusieurs clusters PostgreSQL en fonctionnement (une fonctionnalité du système pg_ctlcluster d’Ubuntu).

Dépannage :

• Identifier le processus : Utilisez netstat pour savoir ce qui utilise le port.

  sudo netstat -tulpn | grep 5432

• Vérifiez les autres clusters : Si le processus est postgres, utilisez la commande pg_lsclusters pour voir tous les clusters configurés et leurs statuts.

  pg_lsclusters

  La sortie pourrait montrer un 'en ligne' et un 'hors service'.

• Arrêtez le cluster indésirable : Si vous trouvez un cluster ancien ou non prévu en fonctionnement, arrêtez-le en utilisant sa version et son nom.

  sudo pg_ctlcluster 13 main stop

• Changer le port : Si un autre service utilise déjà correctement le port 5432, vous devez configurer votre nouveau cluster PostgreSQL pour utiliser un port différent en modifiant son fichier postgresql.conf.

Erreurs de paramètres régionaux et d'encodage lors de initdb

Des erreurs telles que « nom de locale invalide » ou « impossible de déterminer la locale par défaut » lors de la configuration initiale du cluster (initdb) signifient que les paramètres de locale de votre système Ubuntu sont manquants ou mal configurés. PostgreSQL hérite de ces paramètres du système d'exploitation pour déterminer l'ordre de tri (collation), la classification des caractères et le formatage.

Dépannage :

• Vérifiez les paramètres régionaux de votre système : Exécutez locale -a pour voir toutes les locales disponibles.

• Générer les paramètres régionaux manquants : Si le paramètre régional souhaité (par exemple, en_US.UTF-8) n'est pas répertorié, vous devez le générer.

  sudo locale-gen en_US.UTF-8
  
  sudo update-locale

• Réinitialiser le cluster : Après avoir configuré les paramètres régionaux du système, vous devrez peut-être supprimer et recréer le cluster PostgreSQL. La commande pg_createcluster est la méthode standard sur Ubuntu pour cela.

  Avertissement : cela détruit toutes les données du cluster

  sudo pg_dropcluster [version] main
  
  sudo pg_createcluster [version] main

Problèmes d'authentification

Voici les problèmes les plus fréquents auxquels les utilisateurs font face après une installation réussie.

Échec de l'authentification du pair

C'est l'erreur la plus courante. Cela signifie que vous essayez de vous connecter localement, et que le nom d'utilisateur Ubuntu avec lequel vous êtes actuellement connecté ne correspond pas au nom du rôle de base de données PostgreSQL que vous essayez d'utiliser.

Par défaut, PostgreSQL sur Ubuntu utilise l'authentification peer pour les connexions locales. Cette méthode demande au système d'exploitation : « Quel est le nom d'utilisateur du processus qui se connecte ? » et autorise la connexion si ce nom d'utilisateur correspond au rôle de base de données demandé.

Par exemple, si vous êtes connecté en tant que myuser et que vous exécutez psql -U postgres, cela échoue parce que le système d'exploitation signale votre nom d'utilisateur comme myuser, pas postgres.

Solution :

La manière correcte de se connecter en tant que superutilisateur postgres est de d'abord adopter l'identité de l'utilisateur système postgres en utilisant sudo.

sudo -u postgres psql

Si vous souhaitez modifier ce comportement, vous devez définir un mot de passe pour le rôle postgres puis éditer le fichier pg_hba.conf pour utiliser md5 ou scram-sha-256 au lieu de peer.

Mauvaise configuration de pg_hba.conf

Les erreurs dans le fichier pg_hba.conf (authentification basée sur l'hôte) sont la principale cause des échecs de connexion. Ce fichier, situé à /etc/postgresql/[version]/main/pg_hba.conf, est un ensemble de règles qui contrôle qui peut se connecter depuis où à quelle base de données en utilisant quelle méthode. Une seule erreur de syntaxe ou un ordre de règles incorrect peut vous bloquer l'accès.

Dépannage :

• L'ordre des règles est important : PostgreSQL lit ce fichier de haut en bas et utilise la première règle correspondante. Les règles plus spécifiques doivent toujours être placées avant les règles plus générales.

• Recharger après modifications : Après avoir modifié pg_hba.conf, vous devez recharger le service PostgreSQL pour que les modifications prennent effet :

  sudo systemctl reload postgresql

Voici des erreurs courantes de configuration :

host all all 127.0.0.1/32 scram-sha-256

host all all 0.0.0.0/0 reject, host all all 192.168.1.100/32 md5

local all postgres md5

Problèmes d'autorisation

Ces erreurs apparaissent souvent sous la forme de « Permission refusée » dans les journaux ou lors de tentatives de connexion.

Impossible d'accéder au fichier socket

Des erreurs comme « impossible de se connecter au serveur : Aucun fichier ou répertoire de ce type » ou « Permission refusée » lors de la référence à /var/run/postgresql/.s.PGSQL.5432 signifient généralement l’une des deux choses suivantes :

1. Le service ne fonctionne pas : Le fichier de socket est supprimé lorsque le service s'arrête. Vérifiez l'état du service avec sudo systemctl status postgresql.
2. Autorisations utilisateur : Le répertoire du socket appartient à postgres et est accessible par le groupe postgres. Si votre utilisateur système ne fait pas partie du groupe postgres, vous ne pouvez pas utiliser le socket.

Solution :

Ajoutez votre utilisateur au groupe postgres. Vous devez vous déconnecter et vous reconnecter pour que ce changement prenne effet.

sudo usermod -a -G postgres $USER

Autorisations du répertoire de données

Si le service PostgreSQL ne parvient pas à démarrer et que les journaux mentionnent « Permission refusée » concernant le répertoire de données (par exemple, /var/lib/postgresql/[version]/main), la propriété du répertoire ou les permissions des fichiers sont incorrectes.

PostgreSQL exige que son répertoire de données soit exclusivement possédé par l'utilisateur système postgres et ait des permissions 0700 (seul le propriétaire peut lire, écrire et exécuter). Il s'agit d'une mesure de sécurité. Ce problème survient souvent après la restauration d'une sauvegarde ou le déplacement manuel du répertoire de données.

Solution :

Réinitialisez récursivement la propriété et les autorisations.

# Correct the ownership (replace 18 with your version)

sudo chown -R postgres:postgres /var/lib/postgresql/18/main



# Correct the permissions

sudo chmod 0700 /var/lib/postgresql/18/main

Gestion des services

Ces problèmes concernent le démarrage, l'arrêt et la gestion du service postgresql lui-même.

Le service ne démarre pas après les modifications de configuration

Si le service ne démarre pas immédiatement après que vous ayez modifié postgresql.conf ou pg_hba.conf, il est probable que vous ayez introduit une erreur de syntaxe.

Dépannage :

1. Vérifiez les journaux : C’est le moyen le plus rapide de trouver l’erreur. sudo journalctl -u postgresql ou le journal spécifique à /var/log/postgresql/postgresql-[version]-main.log affichera souvent le numéro de ligne exact du paramètre invalide.

2. Erreurs courantes dans postgresql.conf :

   • Les valeurs de chaîne non citées (par exemple, log_destination=stderr est correct ; log_destination=/var/log/my.log est incorrect, cela devrait être 'var/log/my.log').
   • Valeurs incorrectes pour les paramètres (par exemple, shared_buffers=10GB alors que vous n'avez que 8 Go de RAM).

Confusion autour de plusieurs clusters

Le paquet postgresql-common d'Ubuntu inclut un ensemble de scripts wrapper (pg_lsclusters, pg_ctlcluster, pg_createcluster) qui vous permettent d'exécuter plusieurs instances PostgreSQL distinctes (clusters) sur la même machine, souvent de différentes versions.

C'est une fonctionnalité puissante mais qui peut prêter à confusion. Vous pourriez modifier le fichier de configuration pour la version 18 tout en vous connectant à la version 13, ce qui vous amènerait à croire que vos « modifications ne s'appliquent pas ».

Dépannage :

• Lister tous les clusters : Commencez toujours ici pour voir ce qui fonctionne, sur quel port, et l'emplacement de ses fichiers de configuration.

  pg_lsclusters
  # Ver Cluster Port Status Owner    Data directory                       Log file
  # 13  main    5432 down   postgres /var/lib/postgresql/13/main         ...
  # 18  main    5433 online postgres /var/lib/postgresql/18/main         ...

• Gérer des clusters spécifiques : Lors du démarrage, de l’arrêt ou du rechargement, spécifiez toujours le cluster que vous souhaitez modifier :

  sudo pg_ctlcluster 18 main reload

Limites des ressources

Ces erreurs se produisent lorsque PostgreSQL demande plus de ressources (comme de la mémoire) que ce que le système d'exploitation est configuré pour fournir.

Erreurs de mémoire partagée

Les erreurs au démarrage telles que « impossible de créer un segment de mémoire partagée : argument invalide » ou « plus d'espace disponible sur le périphérique » signifient souvent que le paramètre shared_buffers de PostgreSQL est supérieur à la limite du noyau de votre système d'exploitation pour la mémoire partagée (SHMMAX).

PostgreSQL utilise un grand bloc de mémoire partagée pour la mise en cache et la communication entre processus. Si la demande dépasse ce que le système d'exploitation autorise, le système refuse la demande et PostgreSQL ne parvient pas à démarrer. Cela est courant dans les environnements à mémoire limitée ou dans les conteneurs.

Dépannage :

1. Vérifier les limites du système d'exploitation :

   cat /proc/sys/kernel/shmmax

2. Vérifiez postgresql.conf : Examinez votre paramètre shared_buffers.

3. Solution 1 (Recommandé) : Réduisez la valeur de shared_buffers dans postgresql.conf pour qu'elle soit en toute sécurité inférieure à la limite shmmax.

4. Solution 2 (Avancé) : Augmentez la limite shmmax du noyau. Modifiez /etc/sysctl.conf et ajoutez une ligne comme kernel.shmmax=134217728 (pour 128 Mo). Appliquez-la avec sudo sysctl -p. Cela est généralement nécessaire uniquement pour l’optimisation des performances.

FAQ

1. Qu'est-ce que PostgreSQL et pourquoi devrais-je l'utiliser sur Ubuntu ?

PostgreSQL, souvent appelé Postgres, est un système de gestion de bases de données objet-relationnel puissant et open-source. Il jouit d'une solide réputation pour sa fiabilité, la richesse de ses fonctionnalités et l'intégrité des données, étant en développement actif depuis plus de 30 ans.

Vous devriez envisager de l'utiliser pour vos applications car il prend entièrement en charge la conformité ACID (Atomicité, Cohérence, Isolation, Durabilité), gère bien les charges de travail à haute concurrence et prend en charge un large éventail de types de données avancés et d'extensions, telles que l'extension PostGIS pour les données géospatiales.

Utiliser PostgreSQL sur Ubuntu est un choix courant car Ubuntu est une distribution Linux populaire et stable pour les serveurs. PostgreSQL est inclus dans les dépôts de paquets par défaut d’Ubuntu, ce qui rend l’installation et la réception des mises à jour de sécurité en temps opportun un processus simple géré par le gestionnaire de paquets apt du système.

2. Comment installer PostgreSQL sur Ubuntu ?

Vous pouvez installer PostgreSQL directement depuis les dépôts par défaut d’Ubuntu en utilisant la commande apt.

Tout d'abord, actualisez l'index des paquets locaux de votre serveur pour vous assurer que vous disposez des dernières listes de paquets :

sudo apt update

Ensuite, installez le paquet principal PostgreSQL ainsi que le paquet contrib, qui inclut des utilitaires et des extensions supplémentaires :

sudo apt install postgresql postgresql-contrib

3. Comment puis-je démarrer et activer le service PostgreSQL ?

Après une installation réussie, le service PostgreSQL devrait démarrer automatiquement. Vous pouvez vérifier son état en utilisant systemctl.

sudo systemctl status postgresql

Vous pouvez voir une sortie comme celle-ci :

Output
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Wed 2025-10-29 10:30:01 IST; 1min 12s ago
   Main PID: 1234 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 4662)
...

Si le service ne fonctionne pas, vous pouvez le démarrer manuellement :

sudo systemctl start postgresql

Pour vous assurer que la base de données démarre automatiquement à chaque démarrage du serveur, vous devez activer le service :

sudo systemctl enable postgresql

4. Comment puis-je créer une nouvelle base de données dans PostgreSQL ?

La méthode la plus simple consiste à utiliser l'utilitaire en ligne de commande createdb. Cette commande doit être exécutée par un rôle PostgreSQL qui a la permission de créer des bases de données.

Par défaut, le compte utilisateur Linux postgres est configuré pour se connecter en tant que superutilisateur de la base de données postgres. Vous pouvez passer à cet utilisateur et exécuter la commande :

sudo -i -u postgres

createdb my_database

Sinon, vous pouvez vous connecter à l'invite interactive de PostgreSQL (psql) et utiliser la commande SQL CREATE DATABASE.

Tout d'abord, accédez à l'invite en tant qu'utilisateur postgres :

sudo -i -u postgres

psql

Ensuite, depuis l'invite psql (qui ressemble à postgres=# car postgres est un superutilisateur), exécutez la commande SQL. N'oubliez pas de terminer les commandes SQL par un point-virgule.

CREATE DATABASE my_database;

Vous pouvez quitter l'invite psql en tapant \q, puis tapez exit pour revenir à votre utilisateur habituel.

5. Comment créer et gérer des utilisateurs et des rôles PostgreSQL ?

Dans PostgreSQL, la gestion des utilisateurs se fait via des rôles. Un rôle peut être un utilisateur de base de données ou un groupe d'utilisateurs, mais un rôle avec le privilège LOGIN est considéré comme un utilisateur.

1. Création d'un utilisateur

Vous pouvez utiliser l'utilitaire en ligne de commande createuser. Il est préférable de l'exécuter en tant qu'utilisateur postgres.

sudo -i -u postgres

Une méthode recommandée consiste à utiliser l'option --interactive pour être invité à configurer les paramètres et l'option --pwprompt pour définir un mot de passe immédiatement.

createuser --interactive --pwprompt

Cela demandera le nom du rôle, un mot de passe, et si le rôle doit être un superutilisateur ou avoir d'autres permissions de haut niveau.

Output
Enter name of role to add: sammy
Enter password for new role: 
Enter it again: 
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n

Vous pouvez également créer un utilisateur avec une seule commande SQL depuis l'invite psql. Cela crée un utilisateur minimal qui peut seulement se connecter.

CREATE ROLE sammy WITH LOGIN PASSWORD 'secure_password';

2. Attribution des privilèges

Créer un utilisateur ne lui donne pas automatiquement accès à une base de données. Vous devez accorder les privilèges explicitement. Ce processus comporte plusieurs étapes :

1. Accorder l'accès à Connect : Tout d'abord, accordez au nouveau rôle la permission de se connecter à votre base de données.

   GRANT CONNECT ON DATABASE my_database TO sammy;

   Erreur Courante : Utiliser GRANT ALL PRIVILEGES ON DATABASE my_database TO sammy; est trompeur. Cette commande ne donne que les permissions CONNECT, CREATE et TEMP sur la base de données elle-même, pas sur les tables qu'elle contient.

2. Utilisation du schéma de permissions : Ensuite, vous devez accorder l'autorisation d'accéder au schéma dans la base de données. Sans cela, l'utilisateur peut se connecter mais ne peut voir aucune table.

   GRANT USAGE ON SCHEMA public TO sammy;

3. Accorder des permissions sur les tables : Vous pouvez maintenant accorder les permissions spécifiques dont l'utilisateur a besoin sur les objets à l'intérieur du schéma. Connectez-vous à la base de données en tant que superutilisateur pour le faire :

   \c my_database

   Vous êtes maintenant connecté à my_database.

4. Accorder des autorisations sur les objets existants : Pour donner au rôle sammy un accès en lecture/écriture à toutes les tables existantes, exécutez :

   GRANT ALL ON ALL TABLES IN SCHEMA public TO sammy;
   
   GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO sammy;

   Si vous souhaitez uniquement accorder un accès en lecture seule, vous utiliseriez GRANT SELECT au lieu de GRANT ALL.

5. (Optionnel) Accorder des autorisations pour les objets futurs : Les commandes ci-dessus n’affectent que les tables existantes. Pour accorder automatiquement des autorisations pour toutes les nouvelles tables créées à l’avenir (par exemple, par un administrateur), vous devez modifier les privilèges par défaut :

   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO sammy;
   
   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO sammy;

6. Quels sont les commandes de base de PostgreSQL pour les débutants ?

L'outil le plus important à apprendre est le terminal interactif psql. Une fois connecté (par exemple, en exécutant sudo -i -u postgres puis psql), vous pouvez utiliser les commandes méta (qui commencent par un antislash \) pour naviguer et inspecter votre base de données.

Voici quelques-unes des commandes psql les plus courantes :

\l or \list

\c database_name

\conninfo

\dt

\du

\d table_name

\?

\q

7. Comment puis-je me connecter à une base de données PostgreSQL depuis le terminal ?

La manière dont vous vous connectez dépend de la méthode d'authentification configurée dans le fichier pg_hba.conf. (Ce fichier se trouve généralement à /etc/postgresql/<version>/main/pg_hba.conf). Par défaut, PostgreSQL sur Ubuntu utilise l'authentification peer pour les connexions locales.

L’authentification peer fonctionne en obtenant le nom d’utilisateur du système d’exploitation du client depuis le noyau et en l’utilisant comme nom d’utilisateur autorisé pour la base de données. Cela ne fonctionne que pour les connexions locales effectuées via un socket Unix.

1. Connexion en tant qu'administrateur postgres (authentification locale peer)

C'est la manière la plus simple de se connecter localement. Vous utilisez sudo pour exécuter des commandes en tant qu'utilisateur Linux postgres, auquel l'authentification peer associe automatiquement l'utilisateur superutilisateur de la base de données postgres.

sudo -i -u postgres

psql

Cela vous connecte via un socket Unix local à la base de données par défaut nommée postgres.

2. Connexion en tant qu'utilisateur spécifique (authentification peer locale vs. password TCP/IP)

Disons que vous avez créé un rôle PostgreSQL nommé sammy, mais que vous êtes connecté à votre serveur en tant qu'utilisateur Linux différent (par exemple, ubuntu).

• Première tentative : Échec (Incompatibilité d'authentification Peer) : Si vous exécutez psql -U sammy -d my_database, cela échouera probablement. Cette commande tente d'utiliser le socket Unix local, donc l'authentification peer essaie de faire correspondre votre utilisateur OS (ubuntu) avec l'utilisateur de la base de données (sammy). Comme ils ne correspondent pas, la connexion est refusée.

• Tentative 2 : Succès (Authentification par mot de passe) : Pour contourner l’authentification peer, vous pouvez forcer une connexion TCP/IP (réseau) en utilisant -h localhost. Par défaut, le fichier pg_hba.conf d’Ubuntu est configuré pour utiliser l’authentification md5 ou scram-sha-256 (mot de passe) pour les connexions TCP/IP locales.

  psql -U sammy -d my_database -h localhost

  Le terminal vous demandera ensuite le mot de passe que vous avez défini pour le rôle sammy.

  Astuce : Pour éviter de saisir un mot de passe à chaque connexion, vous pouvez créer un fichier .pgpass dans votre répertoire personnel. Ce fichier stocke en toute sécurité vos mots de passe de connexion. Assurez-vous de définir ses permissions de fichier à 0600 (lecture/écriture uniquement pour votre utilisateur).

• Essai 3 : Succès (Correspondance d'authentification par pair) : Si vous avez également un utilisateur système Linux nommé sammy, vous pouvez utiliser sudo pour exécuter psql en tant que cet utilisateur. L'authentification peer réussirait alors.

  sudo -i -u sammy
  
  psql -d my_database

Conclusion

Ce guide fournit une explication complète pour l'installation et la gestion de PostgreSQL sur un serveur Ubuntu. Vous avez commencé par l'installation initiale du logiciel, configuré l'authentification des utilisateurs en créant des rôles et créé de nouvelles bases de données. Nous avons ensuite abordé les commandes SQL fondamentales pour la manipulation des données et exploré les tâches administratives essentielles, y compris la configuration des performances, les procédures de sauvegarde et de restauration, ainsi que le dépannage des problèmes courants.

Vous disposez désormais d'un serveur de base de données fonctionnel et des compétences de base nécessaires pour le maintenir de manière sécurisée et efficace. La capacité à gérer les données, à contrôler l'accès des utilisateurs et à garantir l'intégrité de votre base de données vous prépare à soutenir des applications complexes.

Si vous souhaitez en savoir plus sur Postgres et comment l’utiliser, nous vous encourageons à consulter les guides suivants :

• SQLite vs MySQL vs PostgreSQL : Une comparaison des systèmes de gestion de bases de données relationnelles
• Exercez-vous à exécuter des requêtes avec SQL

Merci d'apprendre avec la communauté Linux-Console.net.