PostgreSQLTM sur un VPS: installer, régler, sauvegarder

Une installation PostgreSQLTM par défaut est adaptée à un poste de travail des années 1990. Sur une VPS de 2 à 8 Go, quelques réglages changent tout — voici la version courte, avec des nombres réels.

1. Installation

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

Ubuntu navires PostgreSQL 16 sur 24.04. Cette version est assez moderne pour tout ce que vous pourriez exécuter.

2. Créer une base de données et un utilisateur

sudo -u postgres psql <<'SQL'
CREATE USER app WITH PASSWORD 'strong-password';
CREATE DATABASE appdb OWNER app;
SQL
psql "postgresql://app:strong-password@localhost/appdb" -c "SELECT 1;"

N'utilisez jamais le superutilisateur postgres dans une application. Un utilisateur par application, une base de données par projet.

3. Tune pour votre RAM

Modifier /etc/postgresql/16/main/postgresql.conf et définir les valeurs en fonction de la taille du serveur :

systemctl restart postgresql
sudo -u postgres psql -c "SHOW shared_buffers;"

Si votre application ouvre des centaines de connexions, placez PgBoncer devant au lieu de lever max connections — chaque connexion coûte de la mémoire.

4. Les décharges quotidiennes avec rotation

cat > /usr/local/bin/pg-dump-all <<'EOF'
#!/bin/sh
set -e
DIR=/var/backups/postgres
mkdir -p "$DIR"
sudo -u postgres pg_dumpall --globals-only > "$DIR/globals.sql"
for db in $(sudo -u postgres psql -Atc "SELECT datname FROM pg_database WHERE datistemplate = false"); do
  sudo -u postgres pg_dump -Fc "$db" > "$DIR/$db.dump"
done
find "$DIR" -type f -mtime +7 -delete
EOF
chmod +x /usr/local/bin/pg-dump-all

Ajouter à cron avec crontab -e:

30 3 * * * /usr/local/bin/pg-dump-all

Les décharges sont des instantanés cohérents; les copies de fichiers d'un répertoire de données en direct ne le sont pas. Puis expédiez les décharges hors site — notre Guide résiste couvre cette partie.

5. Restaurer une décharge

sudo -u postgres createdb appdb_restore
sudo -u postgres pg_restore -d appdb_restore /var/backups/postgres/appdb.dump

Réponses rapides

Puis-je ouvrir PostgreSQLTM à Internet ?

Mieux vaut pas. Gardez-le sur localhost et laissez l'application lui parler directement, ou utilisez un tunnel SSH. Si vous devez l'exposer, n'autoriser que votre IP dans le pare-feu et demander TLS.

Quel plan ai-je besoin pour PostgreSQLTM?

2 GB RAM est parfait pour le développement et les petites applications, 4 GB est confortable pour un site de production, et 8 GB gère une base de données occupée avec cache. Le stockage NVMe compte plus que des noyaux supplémentaires ici.

Comment puis-je vérifier quelles requêtes sont lentes?

Activer l'extension pg stat statements et trier par temps moyen. Il montre habituellement un ou deux index manquants qui expliquent la plupart de la charge.

Une base de données de production confortable commence à Buran-2, 13/mois.