PostgreSQL sur VPS : base fiable et contrôlée

Les bases managées (RDS, Cloud SQL) facturent au Go stocké, aux IOPS et aux connexions, et limitent souvent les extensions disponibles. En déployant PostgreSQL sur votre propre VPS, vous activez pg_stat_statements, PostGIS, pgvector ou TimescaleDB sans restriction, vous choisissez votre version exacte (15, 16, 17) et vous gardez vos données dans une juridiction que vous maîtrisez. Un VPS avec disque SSD offre des latences I/O très basses, idéales pour les workloads transactionnels d'une application en production. Vous payez un forfait fixe et prévisible, et vous décidez seul de votre stratégie de réplication, de chiffrement et de rétention des backups.

Pour une instance PostgreSQL de développement ou un petit projet en production, prévoyez un VPS avec 2 vCPU, 4 Go de RAM et 40 Go de SSD NVMe : PostgreSQL aime la mémoire pour son cache de pages. Pour une charge plus soutenue (plusieurs centaines de connexions ou jeux de données de plusieurs dizaines de Go), visez 4 vCPU et 8 Go de RAM. Côté logiciel : Ubuntu 22.04 ou 24.04 LTS, Docker Engine et le plugin Docker Compose v2, un nom de domaine pointant vers le VPS (ex : db.mondomaine.com) si vous exposez l'accès distant, et un pare-feu ufw configuré pour ne laisser passer que le strict nécessaire. Réservez aussi un volume Docker dédié pour PGDATA afin de découpler les données du conteneur.

Une sauvegarde non testée est une sauvegarde qui n'existe pas. Trois approches complémentaires couvrent des besoins différents.

pg_dump + cron est le point de départ : simple à mettre en place, il produit un fichier SQL logique restaurable sur n'importe quelle version compatible. Planifiez-le à 3h du matin, compressez avec --format=custom (format binaire compressé, restauration sélective par table), et copiez l'archive vers un stockage externe (objet S3-compatible, autre VPS) avec rclone ou scp. Conservez 7 jours en local et 30 jours en distant.

pgBackRest va plus loin : il supporte les backups incrémentaux et différentiels, la compression parallèle et la restauration point-in-time (PITR) à partir des archives WAL. C'est la solution recommandée dès que votre base dépasse quelques Go ou que vous avez besoin de revenir à un état précis à la minute près après un DELETE accidentel. Il se configure via un fichier pgbackrest.conf et s'intègre à un cron ou un timer systemd.

Test de restauration mensuel : exécutez régulièrement pg_restore -d test_restore <dump> sur un VPS de test pour vérifier l'intégrité de vos archives. Un script hebdomadaire qui restaure la dernière sauvegarde et vérifie le nombre de tables ou une requête de contrôle suffit à détecter une corruption silencieuse avant qu'elle ne soit critique.

La haute disponibilité PostgreSQL sur VPS n'implique pas nécessairement Patroni ou un cluster Pacemaker complexe. La réplication streaming native couvre la majorité des besoins avec un minimum de configuration.

Réplication streaming manuelle : sur le VPS primaire, activez wal_level = replica, ajoutez un utilisateur dédié replicator dans pg_hba.conf et définissez max_wal_senders = 3. Sur le VPS secondaire (standby), lancez pg_basebackup pour initialiser une copie physique, créez un fichier standby.signal et configurez primary_conninfo dans postgresql.conf. La réplication démarre automatiquement au redémarrage. Avec hot_standby = on, la standby accepte les requêtes en lecture seule — parfait pour déporter les rapports et le monitoring.

Patroni ajoute la bascule automatique (failover) : il surveille le primaire via une clé dans etcd, Consul ou ZooKeeper, et promeut automatiquement la standby si le primaire devient inaccessible pendant plus de 30 secondes. Sur deux VPS, il suffit d'un etcd tiers (VM légère ou service managé) pour le consensus. Patroni est la voie recommandée dès que vous ne pouvez pas tolérer une intervention manuelle de 5 à 15 minutes lors d'une panne.

Supervision du lag de réplication : SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS lag_bytes FROM pg_stat_replication sur le primaire vous donne le décalage en octets en temps réel. Un lag supérieur à quelques Mo indique une saturation réseau ou une standby surchargée.

OOM killer tue PostgreSQL : le signal OOM dans /var/log/syslog (oom_kill_process) indique que la mémoire est épuisée. Causes principales : shared_buffers trop élevé, work_mem mal calibré face au nombre de connexions, ou processus concurrent (Elasticsearch, Node.js) qui consomme tout le swap. Diagnostic : cat /proc/meminfo | grep -E 'MemFree|Cached|Buffers' et ps aux --sort=-%mem | head -10. Remède : réduire work_mem, activer huge_pages = try dans postgresql.conf, ou upgrader le VPS.

Connexions épuisées (FATAL: sorry, too many clients already) : max_connections est atteint. Court terme : SELECT count(*), state FROM pg_stat_activity GROUP BY state pour identifier les connexions idle. Long terme : déployez pgbouncer en mode transaction (pool_mode = transaction) devant PostgreSQL — il multiplex des centaines de connexions applicatives vers un pool de 20-50 connexions réelles.

Autovacuum saturé : des tables avec beaucoup d'UPDATE ou de DELETE accumulent des tuples morts (dead tuples) qui gonflent les tables et ralentissent les scans. SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10 révèle les tables problématiques. Accélérez l'autovacuum en réduisant autovacuum_vacuum_scale_factor (ex : 0.01 au lieu de 0.2) pour les tables critiques via ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = 0.01).

Checkpoints trop fréquents (LOG: checkpoint occurring too frequently) : le WAL se remplit plus vite que checkpoint_timeout (défaut 5 min). Augmentez max_wal_size (ex : 2-4 Go) et checkpoint_timeout à 15-30 min pour espacer les checkpoints sans risquer une perte de données excessive en cas de crash.