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, offrant un moyen structuré de stocker, organiser et accéder à l'information. PostgreSQL, communément appelé Postgres, est un système de gestion de bases de données relationnelles open source de premier plan. Il fournit une implémentation complète du langage de requête SQL et est reconnu pour sa conformité aux normes et ses fonctionnalités avancées, telles que des transactions fiables et la gestion de la concurrence sans verrouillage en lecture.

Ce guide démontre le processus complet pour configurer PostgreSQL sur un serveur Ubuntu, en commençant par l'installation des paquets logiciels nécessaires. Vous apprendrez ensuite comment interagir avec le système d'authentification de Postgres, qui est géré par des « rôles », incluant la création de nouveaux utilisateurs et de bases de données. Par la suite, nous aborderons les notions fondamentales 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 à l'aide de commandes SQL de base. Nous explorerons également les principaux sujets d'administration, incluant la configuration essentielle pour les performances, les stratégies de sauvegarde et de restauration, une comparaison de PostgreSQL avec d'autres moteurs de bases de données, ainsi qu'un guide pour résoudre les problèmes courants.

Prérequis

Pour suivre ce tutoriel, vous aurez besoin d'un serveur Ubuntu configuré en suivant notre guide de configuration initiale d'un serveur 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 quelques utilitaires 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 des 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, semblables aux utilisateurs et groupes classiques de style 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 Par défaut de Postgres. Il existe plusieurs façons d'utiliser ce compte pour accéder à Postgres. Une manière consiste à 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 pourrez interagir immédiatement avec le système de gestion de base de données.

Pour quitter l'invite de commande 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é en tant que 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 normal, 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 de Postgres fait par défaut est que pour tout rôle utilisé pour se connecter, ce rôle aura une base de données portant le 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, plutôt, 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 base de données (facultatif)

Les commandes shell createuser et createdb sont des outils pratiques. Cependant, pour un contrôle plus précis, vous pouvez effectuer ces 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 nouvelle base de données.

2. Créer un nouveau rôle (utilisateur) : Bien que la commande shell createuser soit 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 qui a 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 autorisation 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. Bien que répondre « oui » à la question du superutilisateur dans l'assistant interactif accorde également cette permission, SUPERUSER en lui-même est un rôle beaucoup plus large et 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 la permission 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 de permissions sur des tables spécifiques. Notez que cela ne donne pas automatiquement le droit de voir ou de 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. Quitter 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 que ce nouveau compte sera disponible, vous pourrez soit passer à celui-ci 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 facultativement 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 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 recense le matériel de terrain de jeu. La première colonne du tableau contiendra les numéros d'identification du matériel de type serial, qui est un entier à incrémentation automatique. Cette colonne a également la contrainte PRIMARY KEY, ce qui signifie que les valeurs qu'elle contient doivent être uniques et ne doivent pas être nulles.

Les deux lignes suivantes créent respectivement des colonnes pour le type et la couleur de l’équipement, dont aucune ne peut être vide. La ligne qui suit crée une colonne emplacement ainsi qu’une contrainte exigeant que la valeur soit l’une des huit valeurs 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 est que certains types de données n'exigent pas de longueur définie parce que 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 bac à sable est ici, mais il y a aussi quelque chose appelé 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. Cela permet de suivre le prochain numéro 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 un tableau prêt, utilisons-le pour pratiquer la gestion 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. Par exemple, ajoutez une diapositive et une balançoire en appelant la table à laquelle vous voulez 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 lorsque vous saisissez les données afin d’éviter quelques pièges courants. Par exemple, ne mettez pas les noms de colonnes entre guillemets, mais les valeurs des colonnes 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. Cela s'explique par le fait que cette valeur 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 la cour de récréation 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';

Interroger à 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 d'entretien de chaque équipement en tapant :

ALTER TABLE playground ADD last_maint date;

Si vous consultez à nouveau les informations de votre tableau, 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 de travail 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 ainsi que toutes les valeurs qu’elle contient, mais conserve toutes les autres données intactes.

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

Jusqu'à présent, vous avez appris comment ajouter des enregistrements à une table et comment les supprimer, mais ce tutoriel n'a pas encore abordé la façon de modifier les 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 table) et changer sa couleur en rouge. Cela pourrait être utile si vous avez donné une nouvelle 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 diapositive 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 conviennent presque jamais à une charge de travail en production. L'optimisation des performances implique d'ajuster l'allocation des ressources—principalement la mémoire, les E/S 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 de stratégies générales pour améliorer les performances de la base de données.

Fichiers de configuration clés

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

• postgresql.conf : Le fichier de configuration principal du serveur. C’est ici que vous ajustez tous les paramètres liés aux performances.
• 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 il 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 ne nécessitent qu'un rechargement.

sudo systemctl reload postgresql

Configuration de la mémoire

La mémoire est le facteur le plus important dans la performance des bases de données. PostgreSQL utilise la mémoire partagée pour mettre en cache les données et une mémoire distincte 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 vos données actuelles peut être lue à partir de la mémoire rapide plutôt que du 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éverser sur des fichiers temporaires sur le disque. Les requêtes complexes peuvent comporter plusieurs opérations, chacune utilisant cette quantité de mémoire.

Le régler 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 régler trop bas provoque un tri fréquent et lent sur disque.

• Ligne directrice : Commencez avec la valeur par défaut (4MB). Analysez vos requêtes lentes en utilisant EXPLAIN ANALYZE. Si vous voyez « Méthode de tri : fusion externe Disk » ou « HashAgg Disk », cela signifie que work_mem est trop petit. 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.

• Ligne directrice : Définissez ceci à un niveau supérieur à work_mem (par exemple, 128MB ou 256MB). Comme ces opérations ne sont pas exécutées aussi fréquemment que les requêtes des utilisateurs, vous pouvez vous permettre d’être plus généreux afin d’accélérer la maintenance.

Configuration du point de contrôle et du WAL

Les points de contrôle sont des processus où PostgreSQL écrit toutes les données « sales » (les modifications conservées 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. L'optimisation de ces éléments aide à équilibrer la charge des 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 maximum 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), n'augmentez max_connections. Utilisez plutôt un gestionnaire de pool de connexions externe comme PgBouncer ou Pgpool-II. Un gestionnaire de pool maintient un petit nombre de connexions persistantes à la base de données et dessert de nombreuses connexions simultanées des applications, ce qui est bien 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';

  Cherchez « Sequential Scan » (Seq Scan) sur de grandes tables indexées. Cela signifie souvent qu'un index est manquant ou que la requête n'est pas suffisamment sélective.

• Créer des index : Si vous filtrez ou faites souvent des jointures sur des colonnes spécifiques (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 se base sur les statistiques concernant les données de vos tables. Ces statistiques sont mises à jour par ANALYZE. Bien que autovacuum s'en occupe automatiquement, il est recommandé d'exécuter manuellement VACUUM ANALYZE; après des chargements de données importants ou si vous constatez que les plans de requête sont inefficaces.

Sauvegarder et restaurer des 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 fournit un ensemble d'outils robustes en ligne de commande pour créer des sauvegardes logiques et effectuer des restaurations.

Il existe deux principales catégories de sauvegardes :

1. Sauvegardes logiques (dumps SQL) : Ces sauvegardes se composent de commandes SQL qui, lorsqu'elles sont 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 avec les utilitaires pg_dump et pg_dumpall. Cette méthode est flexible et portable entre différentes versions de PostgreSQL.
2. Sauvegardes physiques (niveau système de fichiers) : Cette méthode consiste à copier les fichiers de données physiques du répertoire de données de PostgreSQL. Elle est généralement utilisée pour le Récupération à un instant précis (PITR), une configuration avancée qui sera 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 activité sans bloquer les lecteurs ou les écrivains.

Options courantes de pg_dump

Vous pouvez contrôler la sortie de pg_dump avec plusieurs options de 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éarrangement ou l'exclusion des objets lors de la restauration.
• -F p (Format : texte brut) : C'est le format par défaut. Il génère un gros fichier .sql. 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 seulement) : Sauvegarde uniquement la structure de la base de données (tables, vues, index) sans aucune donnée.
• -a (Données seulement) : 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

Restaurer les 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, la meilleure pratique est 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 tâches parallèles 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 d’y restaurer les données.
• --clean : Lorsqu'utilisé avec --create, ce paramètre supprimera et recréera toute la base de données cible. Lorsqu'il est utilisé seul (restauration dans une base de données existante), il supprime les objets de base de données existants avant de les recréer.
• -j 4 : Utilise 4 tâches parallèles pour la restauration.

Nous nous connectons à la base de données postgres parce que 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 brut (.sql)

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

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

   createdb -U postgres mydatabase_new

2. Piper 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 produit 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 transmettez à 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 de 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

Sauvegarder uniquement les rôles et les éléments globaux

Un modèle 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 différents 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 en matière d'automatisation et de sauvegarde

Un plan de sauvegarde n'est pas complet tant qu'il n'est pas automatisé, vérifié et sécurisé.

• Automatisez vos sauvegardes : Utilisez un outil Linux standard comme cron pour planifier l'exécution nocturne de vos commandes pg_dump ou pg_dumpall.
• Gérer 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 de 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. Pratiquez régulièrement la restauration de vos sauvegardes sur un serveur séparé, non destiné à la production, afin de vérifier leur intégrité.
• Vérifiez vos sauvegardes : Après avoir créé une sauvegarde, ajoutez une étape de 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.
• Stocker les sauvegardes à distance : Ne stockez pas vos sauvegardes sur le même serveur que votre base de données. Une défaillance complète du serveur entraînerait la perte des deux. Copiez les sauvegardes vers un emplacement distant comme Linux-Console.net Spaces ou un autre serveur comme 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 exigences de votre application, telles que son modèle de données, ses besoins en matière de scalabilité et ses garanties de cohérence.

• PostgreSQL : Cette base de données objet-relationnelle est reconnue pour son haut niveau de conformité aux standards, l'intégrité de ses données et son ensemble de fonctionnalités robustes. Elle est entièrement compatible ACID et prend 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 « système de référence ».
• MySQL : Faisant partie de la pile LAMP originale, MySQL est l'une des bases de données open-source les plus populaires. Elle est connue pour sa facilité d'utilisation, ses performances de lecture historiquement solides et sa communauté active. Elle est également entièrement conforme à l'ACID (avec le moteur par défaut InnoDB) et est privilégiée pour les applications web simples.
• MariaDB : Un fork communautaire de MySQL. Bien qu'il conserve une grande compatibilité (surtout avec les anciennes versions), il a divergé et introduit ses propres fonctionnalités, donc il pourrait ne pas être un remplacement complet pour les applications utilisant les fonctionnalités modernes de MySQL 8.0 et plus.
• SQLite : Il ne s'agit pas d'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, en stockant toute la base de données dans un seul fichier. Elle gère bien plusieurs lecteurs simultanés, mais n'est pas idéale pour des applications avec des écritures fréquentes en concurrence 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 est conçu pour une haute disponibilité et un dimensionnement horizontal. Bien qu'elle soit connue pour sa flexibilité, MongoDB prend également en charge les transactions ACID multi-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 en RAM pour une vitesse exceptionnelle. Ce n'est pas un remplacement polyvalent pour une base de données sur disque, mais il est presque toujours utilisé en complément de celle-ci (comme PostgreSQL) pour agir comme un cache à haute vitesse ou un gestionnaire 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 se regroupent 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 paquet 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 pourriez devoir utiliser le système de pinning d'apt pour privilégier ses paquets par rapport aux paquets par défaut d'Ubuntu.
• Réparer les installations cassé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 est déjà à l’écoute sur le port PostgreSQL par défaut (5432). Cela se produit souvent 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érifier les autres clusters : Si le processus est postgres, utilisez la commande pg_lsclusters pour voir tous les clusters configurés et leurs états.

  pg_lsclusters

  La sortie pourrait afficher un « en ligne » et un « hors service ».

• Arrêtez le cluster indésirable : Si vous trouvez un ancien cluster ou un cluster non désiré en cours d'exécution, 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 l'initdb

Des erreurs telles que « nom de paramètre régional invalide » ou « impossible de déterminer le paramètre régional par défaut » lors de l’installation initiale du cluster (initdb) signifient que les paramètres régionaux 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 tous les paramètres régionaux 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 ce faire.

  Avertissement : Cela détruit toutes les données dans le 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 par les pairs

Ceci 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 de rôle 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 à moi ? » 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 indique que votre nom d'utilisateur est myuser, pas postgres.

Solution :

La façon correcte de se connecter en tant que superutilisateur postgres est d'abord de prendre 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 de 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 les 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 de configuration courantes :

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 « Permission refusée » dans les journaux ou lorsqu'on essaie de se connecter.

Impossible d’accéder au fichier de socket

Des erreurs telles que « 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 :

1. Le service n'est pas en cours d'exécution : 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 n'est pas dans le 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 » en rapport avec 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 la propriété de 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éinitialiser récursivement la propriété et les permissions.

# 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 sont liés au démarrage, à l'arrêt et à la gestion du service postgresql lui-même.

Échec du démarrage du service après des modifications de configuration

Si le service ne démarre pas immédiatement après avoir modifié postgresql.conf ou pg_hba.conf, vous avez probablement introduit une erreur de syntaxe.

Dépannage :

1. Vérifiez les journaux : C'est la façon la 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 :

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

Confusion à propos de plusieurs groupes

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 séparées (clusters) sur la même machine, souvent de différentes versions.

C'est une fonctionnalité puissante mais qui peut prêter à confusion. Il se peut que vous modifiiez le fichier de configuration pour la version 18 tout en étant connecté à la version 13, ce qui pourrait vous faire croire que vos « modifications ne s'appliquent pas ».

Dépannage :

• Lister tous les clusters : Commencez toujours ici pour voir ce qui est en cours d'exécution, 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 « pas d’espace disponible sur le périphérique » signifient souvent que le paramètre shared_buffers de PostgreSQL est plus grand que 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. S'il demande plus que ce que le système d'exploitation permet, le système d'exploitation 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 : Regardez votre paramètre shared_buffers.

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

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

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 base de données relationnelle orientée objet puissant et open-source. Il est reconnu pour sa fiabilité, la richesse de ses fonctionnalités et l'intégrité de ses données, étant en développement actif depuis plus de 30 ans.

Vous devriez envisager de l'utiliser pour vos applications car il prend en charge pleinement la conformité ACID (Atomicité, Cohérence, Isolation, Durabilité), gère bien les charges de travail à haute concurrence et prend en charge une large gamme de types de données avancés et d'extensions, comme 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 voulu 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 à partir des 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 afin de vous assurer que vous disposez des dernières listes de paquets :

sudo apt update

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

sudo apt install postgresql postgresql-contrib

3. Comment 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 statut en utilisant systemctl.

sudo systemctl status postgresql

Vous pourriez voir une sortie comme ceci :

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 ayant 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=# parce que 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 taper 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é à définir les paramètres et l'option --pwprompt pour définir immédiatement un mot de passe.

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 uniquement se connecter.

CREATE ROLE sammy WITH LOGIN PASSWORD 'secure_password';

2. Attribution des privilèges

La création d'un utilisateur ne lui donne pas automatiquement accès à une base de données. Vous devez accorder les privilèges explicitement. Ce processus comprend plusieurs étapes :

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

   GRANT CONNECT ON DATABASE my_database TO sammy;

   Piège courant : Utiliser GRANT ALL PRIVILEGES ON DATABASE my_database TO sammy; est trompeur. Cette commande ne confère 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 de l'attribution de schéma : Ensuite, vous devez accorder l'autorisation d'accéder au schéma à l'intérieur de 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 permissions 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 voulez seulement accorder un accès en lecture seule, vous utiliseriez GRANT SELECT au lieu de GRANT ALL.

5. (Optionnel) Accorder des permissions pour les objets futurs : Les commandes ci-dessus n’affectent que les tables existantes. Pour accorder automatiquement des permissions à 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 méta-commandes (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 à partir du noyau et en l’utilisant comme nom d’utilisateur autorisé dans 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 peer locale)

C'est la façon la plus simple de se connecter localement. Vous utilisez sudo pour exécuter des commandes en tant qu'utilisateur Linux postgres, ce que 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 locale peer vs TCP/IP password)

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

• Première tentative : Échec (Mauvaise correspondance de l’authentification par pair) : Si vous exécutez psql -U sammy -d my_database, cela échouera probablement. Cette commande tente d’utiliser la socket Unix locale, 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 (par 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 de manière sécurisée vos mots de passe de connexion. Assurez-vous de définir ses permissions de fichier à 0600 (lecture/écriture uniquement pour votre utilisateur).

• Tentative 3 : Réussite (Correspondance d'authentification par pair) : Si vous avez également un utilisateur système Linux nommé sammy, vous pourriez 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 établi de nouvelles bases de données. Nous avons ensuite couvert 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, et le dépannage des problèmes courants.

Vous disposez maintenant 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é de gérer les données, de gérer l’accès des utilisateurs et d’assurer 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 sur la façon de l'utiliser, nous vous encourageons à consulter les guides suivants :

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

Merci de apprendre avec la communauté Linux-Console.net.