PostgreSQL sur Ubuntu : Installation, Configuration et Optimisation des Performances

28 janvier 2026

14 min de lecture

Récent

PostgreSQL est le système de base de données relationnelle open source le plus avancé en utilisation productive aujourd’hui. Utilisé par des organisations allant des startups aux grandes entreprises, il alimente tout, des petites applications web aux systèmes massifs d’entreposage de données traitant des milliards de lignes. Ce guide vous accompagne dans l’installation de PostgreSQL sur Ubuntu Server, sa configuration pour une utilisation en production, la mise en place de l’authentification et de l’accès distant, l’implémentation de stratégies de sauvegarde et l’optimisation des paramètres de performance pour un débit optimal.

Prérequis

Avant de commencer, assurez-vous d’avoir :

Ubuntu Server 20.04, 22.04 ou 24.04 LTS
Accès au terminal avec les privilèges sudo
Au moins 1 Go de RAM (2 Go ou plus recommandé pour la production)
Familiarité de base avec SQL et les opérations en ligne de commande sous Linux
Un pare-feu configuré (consultez notre guide UFW pour la configuration)

Qu’est-ce que PostgreSQL ?

PostgreSQL (souvent appelé “Postgres”) est un système de gestion de base de données objet-relationnel (ORDBMS) en développement actif depuis plus de 35 ans. Initialement développé à l’Université de Californie à Berkeley, il est maintenant maintenu par une communauté mondiale de contributeurs.

Caractéristiques clés de PostgreSQL :

Conformité ACID — support complet de l’atomicité, la cohérence, l’isolation et la durabilité
MVCC (Contrôle de Concurrence Multi-Version) — les lecteurs ne bloquent jamais les écrivains et les écrivains ne bloquent jamais les lecteurs
Extensible — types de données personnalisés, opérateurs, fonctions et méthodes d’index
Conformité aux standards — l’implémentation la plus proche du standard SQL parmi toutes les bases de données
Fonctionnalités avancées — JSON/JSONB, recherche plein texte, partitionnement de tables, réplication logique, CTEs, fonctions de fenêtre
Sécurité robuste — sécurité au niveau des lignes, SSL/TLS, multiples méthodes d’authentification

Installation de PostgreSQL sur Ubuntu

Option 1 : Installer depuis les Dépôts Ubuntu

L’approche la plus simple utilise les paquets par défaut d’Ubuntu :

sudo apt update
sudo apt install postgresql postgresql-contrib

Cela installe la version incluse avec votre version d’Ubuntu (PostgreSQL 14 sur 22.04, PostgreSQL 16 sur 24.04).

Option 2 : Installer la Dernière Version depuis le Dépôt Officiel

Pour obtenir la dernière version de PostgreSQL (version 17 au moment de la rédaction), ajoutez le dépôt APT officiel de PostgreSQL :

sudo apt install -y gnupg2 wget
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /usr/share/keyrings/postgresql-archive-keyring.gpg

Mettez à jour les références du dépôt dans la liste des sources pour utiliser le trousseau signé :

sudo sed -i 's|deb http://apt.postgresql.org|deb [signed-by=/usr/share/keyrings/postgresql-archive-keyring.gpg] http://apt.postgresql.org|' /etc/apt/sources.list.d/pgdg.list
sudo apt update
sudo apt install -y postgresql-17 postgresql-contrib-17

Vérifier l’Installation

Après l’installation, PostgreSQL démarre automatiquement. Vérifiez qu’il fonctionne :

sudo systemctl status postgresql

Vérifiez la version installée :

psql --version

Sortie attendue :

psql (PostgreSQL) 17.x

Configuration Initiale

PostgreSQL stocke sa configuration dans deux fichiers principaux. Sur Ubuntu, ils se trouvent dans /etc/postgresql/<version>/main/.

postgresql.conf — Configuration du Serveur

Ce fichier contrôle le comportement du serveur, les paramètres de performance, la journalisation et les paramètres de connexion :

sudo nano /etc/postgresql/17/main/postgresql.conf

Paramètres clés à vérifier immédiatement :

# Paramètres de connexion
listen_addresses = 'localhost'    # Changer en '*' ou IP spécifique pour l'accès distant
port = 5432                       # Port par défaut de PostgreSQL
max_connections = 100             # Connexions simultanées maximales

# Paramètres de mémoire (ajuster selon la RAM de votre serveur)
shared_buffers = 256MB            # Commencer avec 25% de la RAM totale
work_mem = 4MB                    # Mémoire de tri par opération
maintenance_work_mem = 128MB      # Pour VACUUM, CREATE INDEX

# Write-Ahead Logging
wal_level = replica               # Active la réplication et le PITR
max_wal_size = 1GB
min_wal_size = 80MB

# Journalisation
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_statement = 'ddl'             # Journaliser les instructions DDL
log_min_duration_statement = 1000 # Journaliser les requêtes durant plus de 1 seconde

pg_hba.conf — Authentification des Clients

Ce fichier contrôle qui peut se connecter, d’où et comment ils s’authentifient :

sudo nano /etc/postgresql/17/main/pg_hba.conf

La configuration par défaut ressemble typiquement à ceci :

# TYPE  DATABASE  USER      ADDRESS         METHOD
local   all       postgres                  peer
local   all       all                       peer
host    all       all       127.0.0.1/32    scram-sha-256
host    all       all       ::1/128         scram-sha-256

Après avoir modifié l’un ou l’autre des fichiers, redémarrez PostgreSQL :

sudo systemctl restart postgresql

Ou rechargez sans redémarrer (pour la plupart des modifications de pg_hba.conf) :

sudo systemctl reload postgresql

Méthodes d’Authentification

PostgreSQL prend en charge plusieurs méthodes d’authentification. Les comprendre est essentiel pour sécuriser votre base de données.

Authentification peer

Fait correspondre le nom d’utilisateur du système d’exploitation à un rôle PostgreSQL. Si vous êtes connecté en tant qu’utilisateur système postgres, vous pouvez vous connecter au rôle de base de données postgres sans mot de passe :

sudo -u postgres psql

C’est la méthode par défaut pour les connexions locales par socket Unix et elle est sécurisée car elle repose sur l’authentification du système d’exploitation.

Authentification md5

Utilise des mots de passe hachés en MD5. Bien que toujours fonctionnelle, elle est considérée comme obsolète :

host    all    all    192.168.1.0/24    md5

Authentification scram-sha-256 (Recommandée)

La méthode la plus sécurisée basée sur mot de passe, utilisant le mécanisme de défi-réponse SCRAM-SHA-256 :

host    all    all    192.168.1.0/24    scram-sha-256

Pour s’assurer que les nouveaux mots de passe utilisent SCRAM-SHA-256, définissez ceci dans postgresql.conf :

password_encryption = scram-sha-256

Important : Si vous passez de md5 à scram-sha-256, les mots de passe des utilisateurs existants doivent être réinitialisés car le format de hachage stocké est différent.

Création de Bases de Données et d’Utilisateurs

Connectez-vous à PostgreSQL en tant que superutilisateur :

sudo -u postgres psql

Créer un Nouveau Rôle (Utilisateur)

CREATE ROLE appuser WITH LOGIN PASSWORD 'StrongPassword123!';

Accorder des capacités spécifiques :

ALTER ROLE appuser CREATEDB;

Créer une Base de Données

CREATE DATABASE myappdb OWNER appuser;

Ou depuis la ligne de commande :

sudo -u postgres createdb -O appuser myappdb

Accorder des Privilèges

GRANT ALL PRIVILEGES ON DATABASE myappdb TO appuser;

Pour un contrôle plus granulaire sur les schémas et tables :

\c myappdb
GRANT USAGE ON SCHEMA public TO appuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO appuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO appuser;

Se Connecter en tant que Nouvel Utilisateur

psql -U appuser -d myappdb -h localhost

Configuration de l’Accès Distant

Par défaut, PostgreSQL n’accepte que les connexions depuis localhost. Pour activer l’accès distant :

Étape 1 : Mettre à jour listen_addresses

Éditez postgresql.conf :

listen_addresses = '*'

Ou restreignez à une interface spécifique :

listen_addresses = '192.168.1.10'

Étape 2 : Ajouter une Entrée dans pg_hba.conf

Autoriser un sous-réseau spécifique :

host    myappdb    appuser    192.168.1.0/24    scram-sha-256

Autoriser un seul hôte :

host    all    all    10.0.0.50/32    scram-sha-256

Étape 3 : Configurer le Pare-feu

Autorisez PostgreSQL à travers UFW :

sudo ufw allow from 192.168.1.0/24 to any port 5432

Ou autorisez depuis n’importe où (non recommandé pour la production) :

sudo ufw allow 5432/tcp

Étape 4 : Redémarrer et Tester

sudo systemctl restart postgresql

Testez depuis la machine distante :

psql -h 192.168.1.10 -U appuser -d myappdb

Commandes Essentielles de psql

Le terminal interactif psql est l’outil principal pour travailler avec PostgreSQL. Voici les commandes que vous utiliserez quotidiennement :

Connexion

# Se connecter en tant que superutilisateur postgres
sudo -u postgres psql

# Se connecter à une base de données spécifique
psql -U appuser -d myappdb -h localhost

# Se connecter avec une chaîne de connexion
psql "postgresql://appuser:password@localhost:5432/myappdb"

-- Lister toutes les bases de données
\l

-- Se connecter à une autre base de données
\c myappdb

-- Lister toutes les tables du schéma actuel
\dt

-- Décrire la structure d'une table
\d tablename

-- Lister tous les schémas
\dn

-- Lister tous les rôles/utilisateurs
\du

-- Afficher les informations de connexion actuelles
\conninfo

Exécution de Requêtes

-- Exécuter une requête
SELECT * FROM users LIMIT 10;

-- Activer l'affichage étendu pour les tables larges
\x auto

-- Chronométrer l'exécution des requêtes
\timing on

-- Exécuter un fichier SQL
\i /path/to/script.sql

-- Sauvegarder la sortie d'une requête dans un fichier
\o /tmp/output.txt
SELECT * FROM users;
\o

Quitter

-- Quitter psql
\q

Sauvegarde et Restauration

Une stratégie de sauvegarde solide est non négociable pour les bases de données en production.

pg_dump — Sauvegardes Logiques

Exporter une seule base de données :

# Format SQL (lisible par l'humain)
sudo -u postgres pg_dump myappdb > /backups/myappdb_$(date +%Y%m%d).sql

# Format personnalisé (compressé, supporte la restauration parallèle)
sudo -u postgres pg_dump -Fc myappdb > /backups/myappdb_$(date +%Y%m%d).dump

# Seulement des tables spécifiques
sudo -u postgres pg_dump -t users -t orders myappdb > /backups/tables_$(date +%Y%m%d).sql

Exporter toutes les bases de données :

sudo -u postgres pg_dumpall > /backups/all_databases_$(date +%Y%m%d).sql

pg_restore — Restaurer des Sauvegardes

Restaurer depuis le format personnalisé :

sudo -u postgres pg_restore -d myappdb /backups/myappdb_20260128.dump

Restaurer avec des tâches parallèles pour une récupération plus rapide :

sudo -u postgres pg_restore -j 4 -d myappdb /backups/myappdb_20260128.dump

Restaurer depuis le format SQL :

sudo -u postgres psql myappdb < /backups/myappdb_20260128.sql

pg_basebackup — Sauvegardes Physiques

Pour la récupération à un point dans le temps (PITR) et la configuration de la réplication :

sudo -u postgres pg_basebackup -D /backups/base -Ft -Xs -P

Options expliquées :

-D — répertoire de destination
-Ft — format tar
-Xs — diffuser les fichiers WAL pendant la sauvegarde
-P — afficher la progression

Script de Sauvegarde Automatisé

Créez une tâche cron pour les sauvegardes quotidiennes :

sudo nano /usr/local/bin/pg_backup.sh

#!/bin/bash
BACKUP_DIR="/backups/postgresql"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=30

mkdir -p "$BACKUP_DIR"

# Exporter toutes les bases de données en format personnalisé
for DB in $(sudo -u postgres psql -At -c "SELECT datname FROM pg_database WHERE datistemplate = false AND datname != 'postgres';"); do
    sudo -u postgres pg_dump -Fc "$DB" > "$BACKUP_DIR/${DB}_${TIMESTAMP}.dump"
done

# Supprimer les sauvegardes plus anciennes que la période de rétention
find "$BACKUP_DIR" -name "*.dump" -mtime +$RETENTION_DAYS -delete

echo "Backup completed: $TIMESTAMP"

sudo chmod +x /usr/local/bin/pg_backup.sh
sudo crontab -e

Ajoutez l’entrée cron :

0 2 * * * /usr/local/bin/pg_backup.sh >> /var/log/pg_backup.log 2>&1

Optimisation des Performances

Les performances de PostgreSQL dépendent fortement d’une configuration appropriée. Les paramètres par défaut sont conservateurs et conçus pour fonctionner sur du matériel minimal.

shared_buffers

Le paramètre de mémoire le plus important. Définit la quantité de mémoire que PostgreSQL utilise pour mettre en cache les blocs de données :

# Recommandé : 25% de la RAM totale du système
# Pour un serveur avec 16 Go de RAM :
shared_buffers = 4GB

work_mem

Mémoire allouée pour chaque opération de tri, hash join ou opération similaire. Attention — c’est par opération, pas par connexion :

# Pour les charges OLTP avec de nombreuses connexions :
work_mem = 4MB

# Pour les requêtes analytiques avec moins de connexions :
work_mem = 64MB

effective_cache_size

Indique au planificateur de requêtes la quantité de mémoire disponible pour le cache (cache du système d’exploitation + shared_buffers). Ne réserve pas de mémoire — influence uniquement la planification des requêtes :

# Recommandé : 50-75% de la RAM totale du système
# Pour un serveur avec 16 Go de RAM :
effective_cache_size = 12GB

Configuration WAL

Les paramètres de Write-Ahead Logging impactent significativement les performances d’écriture :

# Paramètres de checkpoint
checkpoint_completion_target = 0.9
max_wal_size = 2GB
min_wal_size = 1GB

# Pour les charges d'écriture intensives
wal_buffers = 64MB
synchronous_commit = on          # Définir à 'off' uniquement si vous pouvez tolérer une perte de données minimale

Connexions et Parallélisme

# Connexions maximales (chacune consomme ~10 Mo de RAM)
max_connections = 200

# Exécution de requêtes parallèles
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_worker_processes = 8

Après l’Optimisation

Appliquez les modifications en redémarrant PostgreSQL :

sudo systemctl restart postgresql

Vérifiez les paramètres :

SHOW shared_buffers;
SHOW work_mem;
SHOW effective_cache_size;

Surveillance avec pg_stat_activity

La vue pg_stat_activity fournit des informations en temps réel sur toutes les connexions et requêtes actives.

Voir les Requêtes Actives

SELECT pid, usename, datname, state, query, query_start,
       now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

Trouver les Requêtes de Longue Durée

SELECT pid, usename, datname, query,
       now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '5 minutes';

Annuler ou Terminer une Requête

-- Annulation élégante (envoie SIGINT)
SELECT pg_cancel_backend(12345);

-- Terminaison forcée (envoie SIGTERM)
SELECT pg_terminate_backend(12345);

Statistiques de la Base de Données

-- Statistiques au niveau de la base de données
SELECT datname, numbackends, xact_commit, xact_rollback,
       blks_read, blks_hit,
       round(blks_hit::numeric / (blks_read + blks_hit) * 100, 2) AS cache_hit_ratio
FROM pg_stat_database
WHERE datname = 'myappdb';

Statistiques des Tables

-- Statistiques d'accès aux tables
SELECT relname, seq_scan, idx_scan, n_tup_ins, n_tup_upd, n_tup_del, n_live_tup
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

Surveillance des Verrous

-- Voir les verrous actuels
SELECT pid, locktype, relation::regclass, mode, granted
FROM pg_locks
WHERE NOT granted;

Table de Référence des Commandes psql

Commande	Description
`\l`	Lister toutes les bases de données
`\c dbname`	Se connecter à une base de données
`\dt`	Lister les tables du schéma actuel
`\dt+`	Lister les tables avec leurs tailles
`\d tablename`	Décrire une table
`\di`	Lister les index
`\dv`	Lister les vues
`\df`	Lister les fonctions
`\du`	Lister les rôles/utilisateurs
`\dn`	Lister les schémas
`\dp`	Lister les privilèges d’accès aux tables
`\x`	Basculer l’affichage étendu
`\timing`	Basculer le chronométrage des requêtes
`\i file.sql`	Exécuter un fichier SQL
`\o file.txt`	Envoyer la sortie vers un fichier
`\e`	Ouvrir la requête dans l’éditeur
`\copy`	Commande COPY côté client
`\password`	Changer le mot de passe d’un utilisateur
`\conninfo`	Afficher les informations de connexion
`\q`	Quitter psql

Dépannage

PostgreSQL Refuse de Démarrer

Vérifiez les journaux pour les erreurs :

sudo journalctl -u postgresql -n 50
sudo cat /var/log/postgresql/postgresql-17-main.log

Causes courantes :

Le port 5432 est déjà utilisé par une autre instance
Fichiers de configuration corrompus (erreurs de syntaxe dans postgresql.conf)
Mémoire partagée insuffisante — vérifiez la valeur de shared_buffers

Connexion Refusée

Si vous obtenez psql: error: connection refused :

# Vérifier que PostgreSQL fonctionne
sudo systemctl status postgresql

# Vérifier sur quelle adresse et quel port il écoute
sudo ss -tlnp | grep 5432

# Vérifier que pg_hba.conf autorise votre connexion
sudo cat /etc/postgresql/17/main/pg_hba.conf

Authentification Échouée

FATAL: password authentication failed for user "appuser"

Vérifiez le mot de passe et la méthode d’authentification :

# Vérifier la méthode d'authentification dans pg_hba.conf
sudo grep -v '^#' /etc/postgresql/17/main/pg_hba.conf | grep -v '^$'

# Réinitialiser le mot de passe via l'authentification peer
sudo -u postgres psql -c "ALTER USER appuser PASSWORD 'NewPassword123!';"

Requêtes Lentes

Activez la journalisation des requêtes lentes :

# Dans postgresql.conf
log_min_duration_statement = 500  # Journaliser les requêtes durant plus de 500ms

Utilisez EXPLAIN ANALYZE pour diagnostiquer :

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

Recherchez les parcours séquentiels sur les grandes tables et ajoutez des index si nécessaire :

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Problèmes d’Espace Disque

Vérifiez les tailles des bases de données :

SELECT pg_database.datname,
       pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database
ORDER BY pg_database_size(pg_database.datname) DESC;

Trouvez les plus grandes tables :

SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

Exécutez VACUUM pour récupérer de l’espace :

sudo -u postgres vacuumdb --all --analyze

Résumé

PostgreSQL est une base de données puissante et prête pour la production qui récompense une configuration appropriée. Dans ce guide, vous avez appris comment installer PostgreSQL depuis le dépôt officiel, configurer l’authentification avec scram-sha-256, créer des bases de données et des rôles, activer l’accès distant en toute sécurité, implémenter des stratégies de sauvegarde avec pg_dump et pg_basebackup, optimiser les paramètres de performance pour votre matériel et surveiller l’activité de la base de données avec pg_stat_activity.

Pour une approche complète du renforcement du serveur, associez cette configuration avec un pare-feu correctement configuré — consultez notre guide du Pare-feu UFW pour des instructions détaillées. Vous devriez également consulter notre Liste de Vérification de Sécurité pour Serveurs Linux pour vous assurer que toute votre pile serveur suit les meilleures pratiques de sécurité depuis le niveau du système d’exploitation.

Prochaines étapes à considérer :

Configurer la réplication en streaming pour la haute disponibilité
Implémenter le pooling de connexions avec PgBouncer pour les applications à fort trafic
Configurer les connexions SSL/TLS pour une communication chiffrée entre client et serveur
Explorer la réplication logique pour des migrations sans temps d’arrêt

Frequently Asked Questions

Quelle est la différence entre PostgreSQL et MySQL ?

PostgreSQL est un système de base de données objet-relationnel avancé qui met l'accent sur la conformité aux standards, l'extensibilité et l'intégrité des données. Il prend en charge les requêtes complexes, le JSON, la recherche plein texte et l'indexation avancée. MySQL est plus simple et traditionnellement plus rapide pour les charges de lecture intensives, mais PostgreSQL excelle dans les transactions complexes, les écritures concurrentes et l'exactitude des données. PostgreSQL est le choix privilégié pour les applications nécessitant la conformité ACID et des fonctionnalités SQL avancées.

Comment réinitialiser le mot de passe de l'utilisateur postgres ?

Passez à l'utilisateur système postgres avec 'sudo -u postgres psql', puis exécutez ALTER USER postgres PASSWORD 'nouveaumotdepasse'; dans l'invite psql. Si vous êtes complètement bloqué, éditez pg_hba.conf pour définir temporairement la méthode d'authentification sur 'trust' pour les connexions locales, redémarrez PostgreSQL, changez le mot de passe, puis remettez pg_hba.conf sur 'scram-sha-256' et redémarrez à nouveau.

PostgreSQL accepte-t-il les connexions distantes par défaut ?

Non. Par défaut, PostgreSQL n'écoute que sur localhost (127.0.0.1). Pour activer les connexions distantes, vous devez modifier listen_addresses dans postgresql.conf vers l'IP du serveur ou '*' pour toutes les interfaces, et ajouter une entrée correspondante dans pg_hba.conf pour autoriser l'hôte ou le sous-réseau distant. Une règle de pare-feu autorisant le port 5432 est également requise.

Quelle est la stratégie de sauvegarde recommandée pour PostgreSQL ?

Pour les bases de données petites à moyennes, utilisez pg_dump pour des sauvegardes logiques produisant des fichiers SQL ou en format personnalisé. Pour les grandes bases de données en production, utilisez pg_basebackup pour des sauvegardes physiques de base combinées avec l'archivage WAL pour la récupération à un point dans le temps (PITR). Automatisez les sauvegardes avec cron, stockez-les hors site et testez régulièrement les restaurations. Le format personnalisé (-Fc) est préféré pour pg_dump car il prend en charge la restauration parallèle et la restauration sélective de tables.