skip to content

Recherche
Syspirit
FR

➤PostgreSQL

Base de données PostgreSQL : administration, requêtes et sauvegarde !

BDD
Publié le
Karl Certa
Mis à jour le
#postgresql #database

PostgreSQL est un système de gestion de base de données relationnelle et objet open source, réputé pour sa robustesse, ses fonctionnalités avancées et sa conformité aux standards SQL.

🤔 Pourquoi PostgreSQL ?

PostgreSQL c’est pour quoi :

  • 🏗️ Données avec des liens - Clients → Commandes → Produits
  • 💰 Calculs précis - Argent, comptabilité (pas de bug d’arrondi)
  • 🔒 Sécurité - Les données ne se perdent jamais
  • 📊 Rapports complexes - Statistiques, tableaux de bord

À utiliser pour :

  • Site e-commerce (produits, commandes, clients)
  • Application de gestion (factures, stocks, employés)
  • Tout ce qui ressemble à Excel mais en plus gros

🚀 Installation

📌 Système🧠 Commande
🐧 Ubuntu/Debiansudo apt install postgresql postgresql-contrib
🎩 CentOS/RHELsudo yum install postgresql-server postgresql-contrib
🍎 macOS (Homebrew)brew install postgresql
🐳 Dockerdocker run -d postgres:15
🏢 Version officiellepostgresql.org/download

⚙️ Configuration de base

📌 Action🧠 Commande
🚀 Initialiser la basesudo postgresql-setup initdb (CentOS)
▶️ Démarrer le servicesudo systemctl start postgresql
🔄 Activer au démarragesudo systemctl enable postgresql
👤 Se connecter comme postgressudo -u postgres psql
🔐 Changer mot de passe postgresALTER USER postgres PASSWORD 'motdepasse';
🚪 Quitter psql\q

🗄️ Gestion des bases de données

📌 Action🧠 Commande
📋 Lister les bases\l (dans psql)
🆕 Créer une baseCREATE DATABASE monapp;
🗑️ Supprimer une baseDROP DATABASE monapp;
🔗 Se connecter à une base\c monapp (dans psql)
📊 Taille d’une base\l+ (dans psql)
💾 Sauvegarder une basepg_dump monapp > backup.sql
📥 Restaurer une basepsql monapp < backup.sql

👥 Gestion des utilisateurs

📌 Action🧠 Commande
📋 Lister les utilisateurs\du (dans psql)
👤 Créer un utilisateurCREATE USER jean WITH PASSWORD 'motdepasse';
🔐 Changer mot de passeALTER USER jean PASSWORD 'nouveau_mdp';
👑 Donner droits adminALTER USER jean WITH SUPERUSER;
🚫 Retirer droits adminALTER USER jean WITH NOSUPERUSER;
🗑️ Supprimer un utilisateurDROP USER jean;

🔒 Gestion des droits

📌 Action🧠 Commande
🔑 Donner tous droits sur BDDGRANT ALL PRIVILEGES ON DATABASE monapp TO jean;
📖 Donner droits lecture seuleGRANT SELECT ON ALL TABLES IN SCHEMA public TO jean;
✏️ Donner droits lecture/écritureGRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO jean;
🚫 Retirer des droitsREVOKE ALL PRIVILEGES ON DATABASE monapp FROM jean;
📋 Voir les droits d’une table\dp nom_table (dans psql)

💾 Sauvegarde et restauration

📌 Action🧠 Commande
💾 Sauvegarde complètepg_dump -U postgres monapp > backup.sql
🗜️ Sauvegarde compresséepg_dump -U postgres -Fc monapp > backup.dump
📥 Restauration SQLpsql -U postgres monapp < backup.sql
📦 Restauration dumppg_restore -U postgres -d monapp backup.dump
🌐 Sauvegarde distantepg_dump -h serveur -U user base > backup.sql
⏰ Sauvegarde avec timestamppg_dump -Fc monapp > backup_$(date +%Y%m%d).dump
🚢 Sauvegarde sur port custompg_dump -p 5433 -U postgres -Fc monapp > backup.dump
📦 Restauration avec logpg_restore -U postgres -d monapp --verbose backup.dump >> restore.log 2>&1

🔍 Monitoring et debug

📌 Action🧠 Commande
📈 Voir les connexions activesSELECT * FROM pg_stat_activity;
🐌 Requêtes lentes en coursSELECT query, state, query_start FROM pg_stat_activity WHERE state = 'active';
💾 Taille des basesSELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;
📊 Taille des tables`SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname
🔧 Tuer une connexionSELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = 'user';
⚡ Vider le cacheSELECT pg_reload_conf();

📝 Configuration pg_hba.conf

Fichier de configuration des accès réseau

Localisation

# Trouver le fichier
sudo -u postgres psql -c "SHOW hba_file;"
 
# Éditer (généralement)
sudo nano /var/lib/pgsql/data/pg_hba.conf  # CentOS
sudo nano /etc/postgresql/15/main/pg_hba.conf  # Ubuntu

Exemples de configuration

# L'utilisateur postgres peut se connecter localement sans mot de passe
local   all             postgres                                trust
 
# Seuls les PC du réseau 192.168.1.0 à 192.168.1.255 peuvent se connecter
# avec mot de passe (md5 = mot de passe chiffré)
host    all             all             192.168.1.0/24          md5
 
# Exemple concret : autoriser seulement le serveur web 192.168.1.10
host    monapp          webuser         192.168.1.10/32         md5
 
# DANGER : autorise tout le monde sur internet (à éviter !)
host    all             all             0.0.0.0/0               md5
 
# SSL obligatoire pour les connexions externes (plus sécurisé)
hostssl all             all             0.0.0.0/0               md5

Redémarrer après modification :

sudo systemctl reload postgresql