Installer PostgreSQL production-ready sur Debian
Installer PostgreSQL prend deux minutes. Le rendre prêt pour la production (sécurisé, correctement dimensionné, sauvegardé, supervisé) en prend une bonne heure, et c'est cette heure que la plupart des tutoriels passent sous silence. Ce guide vous emmène de la commande apt install jusqu'à un serveur que vous pouvez confier à une application réelle sans croiser les doigts.
Il a été rédigé et testé sur Debian 12 (Bookworm) et Debian 13 (Trixie) avec PostgreSQL 17. Tout s'applique également à Ubuntu 22.04 / 24.04, à quelques chemins près.
Prérequis
- Un VPS Linux YorkHost sous Debian, avec accès root ou sudo
- Une connexion SSH fonctionnelle, idéalement sécurisée par clé
- Un firewall en place, voir Configurer UFW
PostgreSQL est gourmand en RAM (cache des pages) et en IOPS (écritures du journal WAL à chaque commit). Pour une application de production, partez au minimum sur un VPS-8 (4 vCores, 8 Go RAM, SSD Enterprise). Si votre base est votre cœur de métier, la gamme VPS Ultimate sur NVMe avec Xeon Silver/Gold fait une différence très nette sur les temps de commit et les gros VACUUM.
Étape 1 : Préparer le système
Mettez le système à jour et réglez le fuseau horaire (les horodatages de vos logs et de vos sauvegardes en dépendent) :
sudo apt update && sudo apt upgrade -y
sudo timedatectl set-timezone Europe/Paris
sudo apt install -y curl ca-certificates gnupg lsb-release
Étape 2 : Installer PostgreSQL depuis le dépôt officiel PGDG
Debian fournit PostgreSQL dans ses dépôts, mais dans une version figée à la sortie de la distribution (PostgreSQL 15 sur Debian 12). Le dépôt officiel PGDG (PostgreSQL Global Development Group) vous donne accès à toutes les versions supportées et aux mises à jour mineures dès leur publication. C'est la méthode recommandée par le projet PostgreSQL lui-même.
# Installe l'outil de gestion du dépôt et ajoute le dépôt PGDG
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -y
# Installe PostgreSQL 17 (serveur + client + contrib)
sudo apt update
sudo apt install -y postgresql-17 postgresql-client-17 postgresql-contrib
Vérifiez que le cluster est démarré :
pg_lsclusters
Vous devez voir quelque chose comme :
Ver Cluster Port Status Owner Data directory Log file
17 main 5432 online postgres /var/lib/postgresql/17/main /var/log/postgresql/postgresql-17-main.log
Debian a sa propre logique, différente des autres distributions :
| Élément | Emplacement |
|---|---|
| Fichiers de configuration | /etc/postgresql/17/main/ |
| Données | /var/lib/postgresql/17/main/ |
| Logs | /var/log/postgresql/postgresql-17-main.log |
| Service systemd | postgresql@17-main (et postgresql qui les regroupe tous) |
Les outils pg_lsclusters, pg_ctlcluster et pg_upgradecluster sont spécifiques à Debian et vous simplifieront la vie, notamment pour les montées de version.
Activez le démarrage automatique (normalement déjà fait) :
sudo systemctl enable --now postgresql
Étape 3 : Premier contact et mot de passe du superutilisateur
Sur Debian, l'utilisateur système postgres se connecte sans mot de passe en local via l'authentification peer (le système vérifie que vous êtes bien l'utilisateur Unix postgres). C'est sûr, et c'est ce que vous utiliserez pour l'administration :
sudo -u postgres psql
Définissez tout de même un mot de passe au superutilisateur, indispensable si un jour vous vous connectez via le réseau :
\password postgres
Puis quittez avec \q.
Étape 4 : Créer un rôle et une base pour votre application
Ne faites jamais tourner une application avec le compte postgres. Créez un rôle dédié, propriétaire de sa base, et rien de plus :
sudo -u postgres psql
-- Un rôle par application, avec un mot de passe fort
CREATE ROLE monapp WITH LOGIN PASSWORD 'un-mot-de-passe-long-et-aleatoire';
-- Sa base, dont il est propriétaire
CREATE DATABASE monapp_db OWNER monapp ENCODING 'UTF8' LC_COLLATE 'fr_FR.UTF-8' LC_CTYPE 'fr_FR.UTF-8' TEMPLATE template0;
Si la commande échoue avec invalid LC_COLLATE locale name, la locale n'est pas générée sur le système. Lancez sudo dpkg-reconfigure locales, cochez fr_FR.UTF-8, puis relancez la commande. Vous pouvez aussi simplement utiliser en_US.UTF-8 ou C.UTF-8 : le choix de la locale n'influe que sur l'ordre de tri des chaînes, pas sur le stockage des accents.
Depuis PostgreSQL 15, le schéma public n'est plus ouvert en écriture à tout le monde. Comme monapp est propriétaire de la base, il possède aussi son schéma public et peut y créer des tables. Si vous ajoutez plus tard un second rôle (en lecture seule pour un outil de reporting par exemple) :
CREATE ROLE reporting WITH LOGIN PASSWORD 'autre-mot-de-passe';
GRANT CONNECT ON DATABASE monapp_db TO reporting;
\c monapp_db
GRANT USAGE ON SCHEMA public TO reporting;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting;
ALTER DEFAULT PRIVILEGES FOR ROLE monapp IN SCHEMA public GRANT SELECT ON TABLES TO reporting;
Testez la connexion applicative en local :
psql -h 127.0.0.1 -U monapp -d monapp_db
Étape 5 : Authentification et accès réseau
Deux fichiers pilotent qui peut se connecter et comment : postgresql.conf (sur quelles interfaces écouter) et pg_hba.conf (qui, depuis où, avec quelle méthode).
Vérifier que le chiffrement des mots de passe est scram-sha-256
PostgreSQL 17 utilise scram-sha-256 par défaut. Vérifiez-le, car un vieux md5 traînant dans une configuration héritée est une faille classique :
sudo -u postgres psql -c "SHOW password_encryption;"
Cas A : l'application tourne sur le même VPS (recommandé)
C'est le cas le plus simple et le plus sûr. PostgreSQL n'écoute que sur localhost, ce qui est déjà le réglage par défaut :
sudo nano /etc/postgresql/17/main/postgresql.conf
listen_addresses = 'localhost'
Dans pg_hba.conf, assurez-vous que les connexions locales TCP utilisent scram-sha-256 :
sudo nano /etc/postgresql/17/main/pg_hba.conf
# 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
Rien à ouvrir dans le firewall. C'est terminé pour le réseau.
Cas B : l'application est sur un autre serveur
Si votre application tourne sur un second VPS YorkHost, il faut exposer le port 5432, mais uniquement à cette IP, et uniquement en TLS.
# postgresql.conf
listen_addresses = '*'
# pg_hba.conf : n'autorisez que l'IP du serveur applicatif, en TLS obligatoire
hostssl monapp_db monapp 203.0.113.42/32 scram-sha-256
Puis limitez l'accès au niveau du firewall :
sudo ufw allow from 203.0.113.42 to any port 5432 proto tcp
Une ligne host all all 0.0.0.0/0 md5 dans pg_hba.conf avec ufw allow 5432 est la configuration que l'on retrouve dans la quasi-totalité des bases compromises. Les scanners testent le port 5432 en continu. L'Anti-DDoS YorkHost protège votre serveur contre les attaques volumétriques, mais rien ne remplace un firewall correctement restreint face à une tentative de connexion légitime au niveau protocolaire.
Activer TLS
Debian génère déjà un certificat auto-signé (ssl-cert-snakeoil) et l'active par défaut, ce qui suffit pour chiffrer le trafic entre deux de vos serveurs. Pour un certificat reconnu, utilisez Let's Encrypt via certbot puis :
ssl = on
ssl_cert_file = '/etc/letsencrypt/live/db.example.com/fullchain.pem'
ssl_key_file = '/etc/letsencrypt/live/db.example.com/privkey.pem'
ssl_min_protocol_version = 'TLSv1.2'
Les fichiers de clé doivent être lisibles par l'utilisateur postgres (copiez-les dans /etc/postgresql/17/main/ avec chmod 600 et chown postgres:postgres si nécessaire).
Rechargez après toute modification de ces deux fichiers :
sudo systemctl reload postgresql
Étape 6 : Tuning mémoire et disque
La configuration par défaut de PostgreSQL est prévue pour tourner sur une machine minuscule. Sur un VPS moderne, elle laisse la majeure partie des ressources inutilisées. Voici les réglages qui comptent vraiment, avec des valeurs calibrées pour un VPS-8 YorkHost (4 vCores, 8 Go RAM, SSD). Adaptez proportionnellement.
sudo nano /etc/postgresql/17/main/postgresql.conf
# ---- Mémoire ----
shared_buffers = 2GB # ~25 % de la RAM
effective_cache_size = 6GB # ~75 % de la RAM : ce que le noyau + PG peuvent cacher
work_mem = 16MB # par opération de tri/hash, par connexion : restez prudent
maintenance_work_mem = 512MB # VACUUM, CREATE INDEX
huge_pages = try
# ---- Connexions ----
max_connections = 100 # au-delà, utilisez PgBouncer (voir plus bas)
# ---- Disque SSD / NVMe ----
random_page_cost = 1.1 # 4.0 par défaut, calibré pour des disques mécaniques
effective_io_concurrency = 200
# ---- Journal WAL et checkpoints ----
wal_buffers = 64MB
min_wal_size = 1GB
max_wal_size = 4GB
checkpoint_completion_target = 0.9
checkpoint_timeout = 15min
# ---- Planificateur ----
default_statistics_target = 100
# ---- Parallélisme (4 vCores) ----
max_worker_processes = 4
max_parallel_workers = 4
max_parallel_workers_per_gather = 2
max_parallel_maintenance_workers = 2
Quelques explications pour ne pas appliquer ça à l'aveugle :
shared_buffersest le cache interne de PostgreSQL. 25 % de la RAM est la règle éprouvée ; au-delà de 40 %, vous entrez en concurrence avec le cache du noyau Linux, sans gain.work_memse multiplie : une requête avec plusieurs tris et 100 connexions simultanées peut consommer100 × plusieurs × work_mem. Une valeur modeste globale, augmentée ponctuellement avecSET work_mem = '256MB'dans les sessions de reporting, est la bonne approche.random_page_costdit au planificateur combien coûte une lecture aléatoire par rapport à une lecture séquentielle. Sur SSD la différence est faible, donc1.1. Laisser4.0pousse PostgreSQL à ignorer vos index sur des tables moyennes.max_wal_sizeplus élevé réduit la fréquence des checkpoints, donc les pics d'écriture. Le prix : un redémarrage après crash un peu plus long.
Ces paramètres nécessitent un redémarrage (pas un simple reload) :
sudo systemctl restart postgresql
sudo -u postgres psql -c "SHOW shared_buffers;"
Pour dimensionner rapidement une autre taille de VPS, pgtune.leopard.in.ua génère un jeu de valeurs cohérent à partir de la RAM, du nombre de cœurs et du type de charge. Utilisez-le comme point de départ, puis observez.
Huge pages (optionnel, recommandé au-delà de 8 Go)
Avec huge_pages = try, PostgreSQL les utilise si le noyau en met à disposition. Pour en réserver assez pour 2 Go de shared_buffers (pages de 2 Mo, comptez une marge) :
echo "vm.nr_hugepages = 1100" | sudo tee /etc/sysctl.d/90-postgresql.conf
sudo sysctl --system
sudo systemctl restart postgresql
Étape 7 : Journalisation utile
Les logs par défaut sont trop discrets pour diagnostiquer un problème de performance. Activez au minimum ceci :
log_min_duration_statement = 500 # journalise toute requête > 500 ms
log_checkpoints = on
log_lock_waits = on
log_temp_files = 0 # journalise les tris qui débordent sur disque
log_autovacuum_min_duration = 1000
log_line_prefix = '%m [%p] %u@%d %h '
log_timezone = 'Europe/Paris'
Et installez pg_stat_statements, l'extension qui vous dira quelles requêtes consomment votre serveur :
shared_preload_libraries = 'pg_stat_statements'
sudo systemctl restart postgresql
sudo -u postgres psql -d monapp_db -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"
Pour voir le top 10 des requêtes les plus coûteuses :
SELECT calls, round(total_exec_time::numeric, 0) AS total_ms,
round(mean_exec_time::numeric, 1) AS mean_ms, left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
Étape 8 : Sauvegardes
C'est le point qui sépare un serveur « qui marche » d'un serveur de production. Deux niveaux complémentaires.
Niveau 1 : dump logique quotidien avec pg_dump
Un dump logique est portable, lisible, et permet de restaurer une seule table. Créez un script :
sudo mkdir -p /var/backups/postgresql
sudo chown postgres:postgres /var/backups/postgresql
sudo nano /usr/local/bin/pg-backup.sh
#!/bin/bash
set -euo pipefail
BACKUP_DIR=/var/backups/postgresql
DATE=$(date +%Y-%m-%d_%H%M)
RETENTION_DAYS=14
# Dump de chaque base au format "custom" (compressé, restauration sélective possible)
for DB in $(psql -Atc "SELECT datname FROM pg_database WHERE datistemplate = false AND datname <> 'postgres';"); do
pg_dump -Fc -Z 6 -f "$BACKUP_DIR/${DB}_${DATE}.dump" "$DB"
done
# Rôles et paramètres globaux (non inclus dans pg_dump)
pg_dumpall --globals-only > "$BACKUP_DIR/globals_${DATE}.sql"
# Rotation
find "$BACKUP_DIR" -type f -mtime +$RETENTION_DAYS -delete
echo "Sauvegarde PostgreSQL terminée : $DATE"
sudo chmod +x /usr/local/bin/pg-backup.sh
sudo -u postgres /usr/local/bin/pg-backup.sh # test
Planifiez-le chaque nuit à 3 h dans le cron de l'utilisateur postgres :
sudo -u postgres crontab -e
0 3 * * * /usr/local/bin/pg-backup.sh >> /var/log/postgresql/backup.log 2>&1
Pour restaurer une base :
sudo -u postgres createdb monapp_db_restore
sudo -u postgres pg_restore -d monapp_db_restore /var/backups/postgresql/monapp_db_2026-09-08_0300.dump
Si le VPS est perdu, le dump l'est aussi. Envoyez ces fichiers hors du serveur chaque nuit. Le guide Backup automatique offsite avec Restic ou Borg explique comment le faire en quelques minutes vers un VPS Storage YorkHost. Par ailleurs, les sauvegardes 7 jours glissants incluses avec votre VPS YorkHost sont des snapshots du disque complet : très pratiques pour revenir en arrière après une mauvaise manipulation système, mais un snapshot pris pendant une écriture n'est pas garanti cohérent pour une base de données. Les deux mécanismes se complètent.
Niveau 2 : sauvegarde physique et Point-In-Time Recovery
Un dump quotidien signifie que vous pouvez perdre jusqu'à 24 h de données. Si ce n'est pas acceptable, il faut archiver le journal WAL en continu pour pouvoir restaurer à n'importe quelle seconde. L'outil de référence est pgBackRest :
sudo apt install -y pgbackrest
Configuration minimale, avec un dépôt local (à répliquer hors-site avec Restic/Borg ensuite) :
sudo nano /etc/pgbackrest.conf
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=une-phrase-secrete-longue
compress-type=zst
log-level-console=info
[main]
pg1-path=/var/lib/postgresql/17/main
Dans postgresql.conf :
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'
wal_level = replica
Puis :
sudo mkdir -p /var/lib/pgbackrest && sudo chown postgres:postgres /var/lib/pgbackrest && sudo chmod 750 /var/lib/pgbackrest
sudo systemctl restart postgresql
sudo -u postgres pgbackrest --stanza=main stanza-create
sudo -u postgres pgbackrest --stanza=main check
sudo -u postgres pgbackrest --stanza=main --type=full backup
Planifiez un full hebdomadaire et un diff quotidien dans le cron de postgres. La documentation pgBackRest décrit la restauration à un instant précis (--type=time).
Étape 9 : Connexions et pooling avec PgBouncer
Chaque connexion PostgreSQL est un processus qui coûte plusieurs Mo de RAM. Une application web moderne qui ouvre 200 connexions sur un VPS-8 le mettra à genoux, non pas à cause des requêtes mais des connexions elles-mêmes. PgBouncer mutualise les connexions :
sudo apt install -y pgbouncer
sudo nano /etc/pgbouncer/pgbouncer.ini
[databases]
monapp_db = host=127.0.0.1 port=5432 dbname=monapp_db
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 500
default_pool_size = 20
Générez la ligne d'authentification (le hash SCRAM tel que stocké par PostgreSQL) :
sudo -u postgres psql -Atc "SELECT '\"' || rolname || '\" \"' || rolpassword || '\"' FROM pg_authid WHERE rolname = 'monapp';" | sudo tee /etc/pgbouncer/userlist.txt
sudo systemctl enable --now pgbouncer
Pointez ensuite votre application sur le port 6432 au lieu de 5432. En mode transaction, évitez les prepared statements côté client (ou activez le support côté PgBouncer ≥ 1.21 avec max_prepared_statements = 100).
Étape 10 : Autovacuum et maintenance
PostgreSQL ne réécrit pas les lignes en place : chaque UPDATE laisse une ancienne version que l'autovacuum nettoie. Il est activé par défaut et il ne faut jamais le désactiver. Sur des tables très actives, rendez-le simplement plus agressif :
autovacuum_vacuum_scale_factor = 0.05 # 0.2 par défaut : trop tard sur les grosses tables
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_cost_limit = 1000 # laisse le vacuum travailler plus vite sur SSD
Surveillez le « bloat » (espace mort) avec cette requête :
SELECT relname, n_dead_tup, n_live_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;
Étape 11 : Mises à jour
Mises à jour mineures (17.x → 17.y)
Elles corrigent des bugs et des failles, sans changer le format des données. Appliquez-les sans hésiter :
sudo apt update && sudo apt upgrade -y
Le service est redémarré automatiquement. Quelques secondes d'indisponibilité, prévenez vos utilisateurs si nécessaire.
Mises à jour majeures (17 → 18)
Le format des données change. Debian rend l'opération remarquablement simple avec pg_upgradecluster :
# 1. Sauvegarde complète d'abord, toujours
sudo -u postgres /usr/local/bin/pg-backup.sh
# 2. Installe la nouvelle version (crée un cluster 18/main vide sur le port 5433)
sudo apt install -y postgresql-18
# 3. Supprime le cluster vide créé automatiquement
sudo pg_dropcluster --stop 18 main
# 4. Migre 17/main vers 18/main (méthode "upgrade" = pg_upgrade, rapide)
sudo pg_upgradecluster -m upgrade 17 main
# 5. Vérifie que tout fonctionne sur le nouveau cluster, puis supprime l'ancien
pg_lsclusters
sudo pg_dropcluster 17 main
sudo apt purge -y postgresql-17
Relancez ANALYZE sur vos bases après la migration (les statistiques du planificateur ne sont pas transférées) :
sudo -u postgres vacuumdb --all --analyze-in-stages
Checklist production
- PostgreSQL installé depuis le dépôt PGDG, version supportée
- Rôle applicatif dédié,
postgresjamais utilisé par l'application -
listen_addressesrestreint,pg_hba.confsans0.0.0.0/0, méthodescram-sha-256 - Port 5432 fermé ou limité à des IP précises dans UFW
- TLS actif pour toute connexion distante
-
shared_buffers,effective_cache_size,random_page_costadaptés au VPS -
log_min_duration_statementetpg_stat_statementsactifs - Dump quotidien testé et copié hors du serveur
- Archivage WAL (pgBackRest) si la perte de 24 h de données est inacceptable
- Restauration testée au moins une fois sur une base vide
- Mises à jour mineures appliquées régulièrement
Dépannage
FATAL: password authentication failed for user
Le mot de passe est faux, ou la ligne pg_hba.conf qui correspond utilise peer alors que vous vous connectez en TCP (ou l'inverse). Vérifiez avec -h 127.0.0.1 pour forcer TCP, et lisez /var/log/postgresql/postgresql-17-main.log : PostgreSQL y indique la ligne de pg_hba.conf utilisée.
could not connect to server: Connection refused
Le serveur n'écoute pas sur cette adresse. ss -tlnp | grep 5432 montre les interfaces écoutées ; vérifiez listen_addresses puis le firewall.
FATAL: sorry, too many clients already
max_connections est atteint. Ne l'augmentez pas à l'infini : mettez PgBouncer en place (étape 9) et cherchez les connexions oubliées avec SELECT count(*), state FROM pg_stat_activity GROUP BY state;.
Le serveur ne démarre plus après modification de la config
sudo -u postgres /usr/lib/postgresql/17/bin/postgres -D /var/lib/postgresql/17/main -C shared_buffers teste la lecture de la configuration, et journalctl -u postgresql@17-main -n 50 affiche l'erreur exacte. Le cas classique : shared_buffers trop grand pour la RAM, ou un huge_pages = on sans pages réservées.
Disque plein
PostgreSQL refuse d'écrire et peut se retrouver en mode dégradé. Libérez de l'espace (vieux dumps, logs), puis vérifiez que le WAL ne s'accumule pas : un archive_command qui échoue empêche la suppression des segments WAL, et pg_wal/ grossit jusqu'à saturer le disque. Consultez Gestion de l'espace disque.
Aller plus loin
Un serveur PostgreSQL unique, même parfaitement réglé, reste un point de défaillance unique. Si votre activité ne peut pas tolérer une interruption le temps de restaurer un dump, la suite logique est la haute disponibilité : réplication, bascule automatique et routage des connexions. Nous avons décrit ce que cela implique réellement dans PostgreSQL en haute disponibilité : Patroni, etcd et basculement automatique.
C'est exactement ce que fait l'Infrastructure Managée YorkHost : un cluster PostgreSQL Patroni + etcd réparti sur plusieurs datacenters français, sauvegardes vérifiées, CVE appliquées sous 48 h, et un ingénieur identifié qui connaît votre base plutôt qu'un ticket. Hébergé et exploité en France, sous droit français.