5 min read Advanced 9.6k

PostgreSQL : installation et tuning

Published on May 17, 2026 at 04:02 PM

Installez PostgreSQL et configurez-le pour la production : memory tuning, vacuum, connexions, indexes. Le SGBD relationnel open-source de reference, robuste pour les grosses charges.

Introduction

PostgreSQL est le SGBD open-source le plus serieux : ACID strict, MVCC, JSONB, full-text search, extensions, replication, partitioning. Utilise par Apple, Instagram, Reddit, Spotify.

Ce tuto couvre l'installation et le tuning de base pour un serveur dedie.

Prerequis

VPS Linux Debian 12 / Ubuntu 24.04
2 vCPU, 4 Go RAM minimum (8 Go recommande pour prod)
50 Go SSD/NVMe
Acces root

Etape 1 : Installation

sudo apt update
sudo apt install -y postgresql postgresql-contrib
sudo systemctl enable --now postgresql
sudo -u postgres psql -c "SELECT version();"

Etape 2 : Premier utilisateur et database

sudo -u postgres psql

CREATE USER monapp WITH ENCRYPTED PASSWORD 'pass-fort';
CREATE DATABASE monapp_db OWNER monapp;
GRANT ALL PRIVILEGES ON DATABASE monapp_db TO monapp;
\q

Test :

psql -U monapp -h 127.0.0.1 -d monapp_db

Etape 3 : Acces reseau

/etc/postgresql/16/main/postgresql.conf :

listen_addresses = 'localhost'   # ou '*' si acces externe

/etc/postgresql/16/main/pg_hba.conf :

local   all             postgres                                peer
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
host    monapp_db       monapp          10.0.0.0/8              scram-sha-256

scram-sha-256 > md5 (plus sur, depuis PG 14).

sudo systemctl restart postgresql

Etape 4 : Tuning memoire

/etc/postgresql/16/main/postgresql.conf pour 4 Go RAM dedies a PG :

shared_buffers = 1GB                  # 25% RAM
effective_cache_size = 3GB            # 75% RAM
work_mem = 16MB                       # par tri/jointure
maintenance_work_mem = 256MB          # VACUUM, CREATE INDEX
wal_buffers = 16MB

Utilisez pgtune pour generer une config adaptee : https://pgtune.leopard.in.ua

Etape 5 : Connexions

max_connections = 100

Si vous avez besoin de plus, utilisez pgbouncer plutot que d'augmenter (chaque connexion PG coute ~10 Mo).

Etape 6 : WAL et checkpoints

wal_level = replica
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9
checkpoint_timeout = 15min

Pour SSD/NVMe :

random_page_cost = 1.1            # default 4.0 pour HDD
effective_io_concurrency = 200    # default 1 pour HDD

Etape 7 : Autovacuum

autovacuum empeche le bloat des tables. Ne le desactivez jamais.

autovacuum = on
autovacuum_naptime = 30s
autovacuum_max_workers = 4
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.025

Etape 8 : Logging

log_min_duration_statement = 500    # log queries > 500ms
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
log_line_prefix = '%t [%p]: db=%d,user=%u,client=%h '

Logs dans /var/log/postgresql/postgresql-16-main.log.

Etape 9 : Backup avec pg_dump

Backup d'une base :

sudo -u postgres pg_dump monapp_db | gzip > monapp_db-$(date +%F).sql.gz

Backup global :

sudo -u postgres pg_dumpall | gzip > all-$(date +%F).sql.gz

Restore :

gunzip -c monapp_db-2026-05-17.sql.gz | sudo -u postgres psql monapp_db

Pour les gros volumes, utilisez le format custom :

sudo -u postgres pg_dump -Fc monapp_db > monapp.dump
sudo -u postgres pg_restore -d monapp_db monapp.dump

Etape 10 : Backup avance avec pgBackRest

Pour PITR (point-in-time recovery), voir le tuto dedie pgBackRest. C'est l'outil de reference pour les sauvegardes serieuses.

Etape 11 : Performance

Indexes

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_date ON orders(created_at) WHERE status = 'pending';

Voir les requetes lentes :

SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Activez pg_stat_statements :

shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION pg_stat_statements;

EXPLAIN

EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

Recherchez Seq Scan (mauvais) vs Index Scan (bon) sur les grosses tables.

Etape 12 : Securite

Mots de passe forts (scram-sha-256)
pg_hba.conf restreint aux IPs autorisees
TLS via ssl = on et certificats
Pas d'acces direct depuis Internet (passez par VPN ou bastion)
Backups chiffres et offsite

Activer SSL :

ssl = on
ssl_cert_file = '/etc/ssl/certs/postgresql.crt'
ssl_key_file = '/etc/ssl/private/postgresql.key'

Depannage

"FATAL: password authentication failed"

Verifiez pg_hba.conf : la ligne match-elle votre client et methode ?

"too many connections"

SELECT count(*) FROM pg_stat_activity;

Augmentez max_connections ou ajoutez pgbouncer.

Slow queries

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

Killez si necessaire :

SELECT pg_cancel_backend(pid);
SELECT pg_terminate_backend(pid);  -- plus brutal

Disk full

PG ecrit beaucoup de WAL. Verifiez pg_wal :

sudo du -sh /var/lib/postgresql/16/main/pg_wal

Si replication retardee, les WAL s'accumulent. Verifiez les slots :

SELECT * FROM pg_replication_slots;

Commandes utiles

# Services
sudo systemctl status postgresql
sudo systemctl restart postgresql

# Tester la config sans restart
sudo -u postgres psql -c "SELECT pg_reload_conf();"

# Stats
sudo -u postgres psql -c "SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;"

# Connexions actives
sudo -u postgres psql -c "SELECT pid, usename, datname, state, query FROM pg_stat_activity;"

# Vacuum manuel
sudo -u postgres vacuumdb -d monapp_db -z

# Reindex
sudo -u postgres reindexdb monapp_db

# Version et settings
sudo -u postgres psql -c "SHOW config_file;"
sudo -u postgres psql -c "SHOW all;"

Conclusion

Avec PostgreSQL bien tune :

Performance excellente jusqu'a plusieurs TB
Concurrence elevee (MVCC)
Extensions riches (PostGIS, TimescaleDB, pgvector)
Robustesse ACID

Pour aller plus loin :

Configurez la replication streaming pour de la haute dispo
Utilisez pgBackRest pour des backups serieux
Pour des charges OLTP massives, considerez CitusDB (sharding) ou TimescaleDB (time series)

Ressources

Documentation officielle : https://www.postgresql.org/docs/current/
pgtune : https://pgtune.leopard.in.ua
pg_stat_statements : https://www.postgresql.org/docs/current/pgstatstatements.html
PostgreSQL Wiki : https://wiki.postgresql.org