Commandes PostgreSQL de base avec exemples pratiques

Ce guide vous propose un flux de travail reproductible et à faible risque pour les opérations quotidiennes sur PostgreSQL. Vous apprendrez à faire l'inventaire de votre environnement, à vous connecter en toute sécurité, à inspecter les objets, à exécuter des requêtes en lecture seule, à effectuer des modifications contrôlées à l'intérieur de transactions, à gérer les rôles selon le principe du moindre privilège, à sauvegarder et restaurer logiquement, à analyser les performances des requêtes, et à récupérer après les pannes courantes. Chaque exemple utilise des noms et identifiants construits que vous remplacez par vos propres valeurs, ce qui permet d'adapter directement chaque motif à vos runbooks.

Inventaire de l'environnement et connexion sécurisée

Avant d'exécuter toute commande, capturez les informations de base. Cela évite les surprises lorsque les environnements diffèrent et vous donne une référence fiable pendant les incidents.

Prérequis

• Accès à un serveur PostgreSQL et le client psql installé localement ou sur un hôte bastion.
• Un rôle capable de se connecter et de lire les métadonnées du schéma. Pour les modifications, un rôle avec des privilèges explicites sur les objets cibles.
• Accessibilité réseau vers l'hôte et le port (par défaut 5432), y compris les règles de pare-feu ou les groupes de sécurité.

Vérifications de version et de topologie

psql --version
# Exemple de sortie : psql (PostgreSQL) 15.4

psql -h db.example.internal -U app_read -d appdb -c "SELECT version();"
# Exemple de sortie : PostgreSQL 15.4 on x86_64-pc-linux-gnu, compiled by gcc ...

Listez les bases de données visibles pour votre rôle et confirmez l'existence de la base cible :

psql -h db.example.internal -U app_read -d postgres -c "\l"

À l'intérieur de psql, vérifiez votre contexte de session :

SELECT current_user, session_user, current_database();
SHOW search_path;          -- Couramment : "$user", public
SHOW statement_timeout;    -- Exemple : 60s

Consignez les éléments suivants dans votre runbook :

• Instance unique ou primaire avec standbys ?
• Quels noms d'hôte correspondent au primaire versus aux répliques de lecture ?
• Quels rôles sont en lecture seule versus lecture-écriture ?

Réglages psql recommandés pour le travail interactif

\timing on          -- Affiche le temps d'exécution pour chaque instruction
\pset pager off     -- Désactive la pagination pour les sorties courtes
\set ON_ERROR_STOP on  -- Arrête l'exécution du script à la première erreur

Découverte en lecture seule et lectures ciblées

Commencez chaque session par des commandes en lecture seule. Elles ne comportent aucun risque de modification de données et renforcent la confiance avant de tenter des changements.

Lister les objets dans la base de données courante

\conninfo           -- Confirme hôte, port, utilisateur, base de données
\dn                 -- Liste les schémas
\dt                 -- Liste les tables dans search_path
\du                 -- Liste les rôles
\d+ customers       -- Décrit la table avec stockage, taille et contraintes
SELECT count(*) FROM customers;  -- Vérification rapide de cardinalité

Filtrer et limiter pour une inspection sûre

-- Inspecter les clients récents (exemple construit)
SELECT id, email, created_at
FROM customers
WHERE created_at >= now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 20;

Résultat attendu : de zéro à vingt lignes, offrant une vue rapide de l'activité récente sans scanner la table entière.

Modifications contrôlées avec transactions

N'exécutez jamais une modification de données sans enveloppe de transaction. Ce motif vous permet de vérifier l'effet exact avant de valider.

Modèle pour mises à jour sûres

BEGIN;
UPDATE customers
SET is_active = false
WHERE id = 12345;  -- Remplacez par une vraie valeur de clé primaire

-- Vérifier que le changement a affecté exactement une ligne
SELECT id, is_active FROM customers WHERE id = 12345;

-- Si la vérification passe :
COMMIT;
-- Si la vérification échoue (ex. nombre de lignes != 1) :
ROLLBACK;

Si vous attendez exactement une ligne et en voyez plus, lancez ROLLBACK immédiatement et affinez votre clause WHERE. Pour les modifications en masse, traitez par lots de quelques milliers de lignes et exécutez ANALYZE ensuite pour rafraîchir les statistiques du planificateur.

Gestion des rôles et moindre privilège

Les rôles d'application ne doivent jamais être superutilisateurs. Accordez uniquement les privilèges requis par la charge de travail.

Créer un rôle en lecture seule avec privilèges par défaut pour les futures tables

-- Exécuter en tant que superutilisateur ou rôle d'administration de sécurité
BEGIN;
CREATE ROLE app_read LOGIN PASSWORD 'REDACTED';
GRANT CONNECT ON DATABASE appdb TO app_read;
GRANT USAGE ON SCHEMA public TO app_read;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_read;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO app_read;
COMMIT;

Vérifier les octrois

\dp public.*   -- Affiche les privilèges sur les tables du schéma public

Diagnostiquer les capacités des rôles

SELECT rolname, rolsuper, rolreplication, rolcanlogin
FROM pg_roles
WHERE rolname IN ('app_read', 'app_write');

Résultat attendu : les rôles d'application ne sont pas superutilisateurs et n'ont que les privilèges dont ils ont besoin.

Modifications de schéma et vérifications de performance

Créez les objets en toute sécurité et validez les plans de requête avant d'exécuter des instructions lourdes.

Créer une table avec la syntaxe moderne

BEGIN;
CREATE TABLE IF NOT EXISTS customers (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  is_active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now()
);
COMMIT;

\d+ customers  -- Confirmer colonnes, contraintes, index et taille

Expliquer les plans avant d'exécuter

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

Recherchez un Index Scan sur customers_email_idx. Si vous voyez un Sequential Scan sur une grande table, ajoutez un index :

BEGIN;
CREATE INDEX CONCURRENTLY IF NOT EXISTS customers_email_idx ON customers (email);
COMMIT;

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

CREATE INDEX CONCURRENTLY évite les verrous exclusifs longs mais prend plus de temps à construire. Exécutez-le pendant les périodes de faible trafic.

Sauvegarde logique et validation de la restauration

Les vidages logiques avec pg_dump sont portables et scriptables. Validez toujours en restaurant d'abord vers une base de données de test.

Vidage compressé d'une seule base de données

pg_dump -h db.example.internal -U backup_user -d appdb -F c -f appdb_2024-10-01.dump

Le code de sortie doit être 0. Consignez l'horodatage et la taille du vidage dans votre runbook.

Restauration vers une base de données de test et vérification

createdb -h db.example.internal -U admin appdb_restore_scratch
pg_restore -h db.example.internal -U admin -d appdb_restore_scratch appdb_2024-10-01.dump
psql -h db.example.internal -U admin -d appdb_restore_scratch -c "\dt"
psql -h db.example.internal -U admin -d appdb_restore_scratch -c "SELECT COUNT(*) FROM customers;"

Comparez la liste des tables et les comptages des tables clés avec un instantané récent de la base source. Ce n'est qu'après validation qu'il faut envisager une restauration en production.

Vidage du schéma seul pour une validation plus rapide

pg_dump -h db.example.internal -U backup_user -n public -F c -f appdb_public_2024-10-01.dump appdb

Diagnostic de performance des requêtes

Lorsqu'une requête semble lente, utilisez EXPLAIN ANALYZE en dehors des heures de pointe pour voir les métriques d'exécution réelles.

\timing on
EXPLAIN ANALYZE SELECT * FROM customers WHERE email = '[email protected]';

Interprétez la sortie :

• Sequential Scan sur de grandes tables : ajoutez ou ajustez un index.
• Temps réel élevé vs coût estimé : les statistiques sont obsolètes ; exécutez ANALYZE sur les tables concernées.
• Rows Removed by Filter élevé : envisagez un index partiel ou réécrivez le filtre pour qu'il soit plus sélectif.

Après de grosses modifications de données, exécutez toujours :

ANALYZE customers;

Surveillance des verrous et résolution des blocages

Les transactions longues peuvent bloquer d'autres sessions. Identifiez et résolvez les bloqueurs rapidement.

Afficher les sessions actives non inactives

SELECT pid, usename, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start ASC;

Trouver les PIDs bloquants

SELECT pg_blocking_pids(pid) AS blockers, pid, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

Annuler ou terminer le backend fautif (coordonner avec le propriétaire d'abord)

SELECT pg_cancel_backend(<pid>);   -- Doux : annule la requête courante
SELECT pg_terminate_backend(<pid>); -- Dernier recours : coupe la connexion

Modes de défaillance courants et actions correctives

Procédures de restauration et de récupération

Abandonner une requête longue nuisible

SELECT pg_cancel_backend(<pid>);   -- Essayer d'abord
SELECT pg_terminate_backend(<pid>); -- Dernier recours

Annuler des changements de privilèges incorrects

BEGIN;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM app_read;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_read;
COMMIT;

Récupérer à partir d'une sauvegarde logique après corruption de données ou suppressions accidentelles

1. Créer une base de récupération fraîche :

   createdb -h db.example.internal -U admin appdb_recover_2024_10_01

1. Restaurer le dernier vidage valide :

   pg_restore -h db.example.internal -U admin -d appdb_recover_2024_10_01 appdb_2024-10-01.dump

1. Valider le schéma et les comptages clés :

   \dt
   SELECT COUNT(*) FROM customers;  -- Comparer avec l'instantané pré-changement

1. Si seules des tables spécifiques sont affectées, les restaurer sélectivement dans la base de production :

   pg_restore -h db.example.internal -U admin -d appdb --table=public.customers appdb_2024-10-01.dump

Motifs de sécurité pour les modifications en masse

• Testez toujours la clause WHERE avec un SELECT d'abord ; vérifiez le nombre exact de lignes.
• Utilisez les clés primaires ou des index étroits dans la clause WHERE.
• Traitez les grosses mises à jour par lots de 1 000 à 5 000 lignes :

  BEGIN;
  WITH c AS (
    SELECT id FROM customers WHERE is_active = false LIMIT 5000
  )
  UPDATE customers SET is_active = true WHERE id IN (SELECT id FROM c);
  COMMIT;

• Exécutez ANALYZE après chaque lot pour maintenir les statistiques du planificateur à jour.

Liste de contrôle opérationnelle

Personnalisez cette liste pour votre environnement et joignez-la à chaque runbook.

Inventaire et configuration

• [ ] Enregistrer les versions serveur et client : psql --version, SELECT version();
• [ ] Confirmer hôte, base de données, rôle : \conninfo, SELECT current_database(), current_user;
• [ ] Activer les réglages psql utiles : \timing on, \pset pager off, \set ON_ERROR_STOP on

Découverte (lecture seule)

• [ ] Lister les objets : \dn, \dt, \d+ nom_table
• [ ] Vérifier les comptages et l'activité récente avec des SELECT limités

Gestion des changements

• [ ] Démarrer une transaction : BEGIN;
• [ ] Vérifier les lignes cibles avec SELECT avant de modifier
• [ ] Appliquer le changement avec une clause WHERE précise ; attendre des comptages exacts
• [ ] Vérifier avec un SELECT ciblé ; COMMIT ou ROLLBACK selon le cas

Performance et verrous

• [ ] Utiliser EXPLAIN (et EXPLAIN ANALYZE hors pointe) sur les requêtes lourdes
• [ ] Inspecter pg_stat_activity pour les bloqueurs ; annuler avec pg_cancel_backend si nécessaire

Sauvegarde et restauration

• [ ] Exécuter pg_dump pour les sauvegardes ciblées ; enregistrer le code de sortie et la taille du vidage
• [ ] Restaurer vers une base de test ; valider le schéma et les comptages des tables clés

Sécurité

• [ ] Examiner les rôles : \du et pg_roles ; s'assurer qu'aucun rôle d'application n'est superutilisateur
• [ ] Vérifier les privilèges avec \dp et appliquer des octrois au moindre privilège

Validation post-changement

• [ ] Re-vérifier les tailles et comptages des tables si impactées
• [ ] Exécuter ANALYZE sur les tables mises à jour
• [ ] Consigner une brève note de changement avec les résultats de vérification

Exemples pratiques de bout en bout

Exemple 1 : Basculer un indicateur client en toute sécurité

-- 1) Vérifier que la ligne cible existe
SELECT id, is_active FROM customers WHERE id = 12345;  -- Attendre 1 ligne

-- 2) Changer à l'intérieur d'une transaction
BEGIN;
UPDATE customers SET is_active = NOT is_active WHERE id = 12345;  -- Attendre UPDATE 1
SELECT id, is_active FROM customers WHERE id = 12345;  -- Vérifier la nouvelle valeur
COMMIT;  -- ou ROLLBACK si inattendu

Exemple 2 : Ajouter un index après avoir confirmé un séquentiel scan

EXPLAIN SELECT * FROM customers WHERE email = '[email protected]';
-- Si Seq Scan apparaît et la table est grande :
BEGIN;
CREATE INDEX CONCURRENTLY IF NOT EXISTS customers_email_idx ON customers (email);
COMMIT;

-- Re-vérifier le plan
EXPLAIN SELECT * FROM customers WHERE email = '[email protected]';

Exemple 3 : Sauvegarde logique minimale d'un seul schéma

pg_dump -h db.example.internal -U backup_user -n public -F c -f appdb_public_2024-10-01.dump appdb

# Valider avec une restauration vers une base de test
createdb -h db.example.internal -U admin appdb_scratch
pg_restore -h db.example.internal -U admin -d appdb_scratch appdb_public_2024-10-01.dump
psql -h db.example.internal -U admin -d appdb_scratch -c "\dt"

Conclusion

Vous disposez maintenant d'un ensemble pragmatique et reproductible de commandes et de motifs PostgreSQL pour opérer en toute sécurité en production. Commencez chaque session par un inventaire de l'environnement et une connexion intentionnelle. Utilisez la découverte en lecture seule avant tout changement. Enfermez toutes les écritures dans des transactions et vérifiez les comptages de lignes et les résultats avant de valider. Employez EXPLAIN pour éviter les surprises de performance, et EXPLAIN ANALYZE hors pointe pour diagnostiquer les requêtes lentes. Sauvegardez logiquement avec pg_dump, validez chaque restauration dans une base de test, et documentez le résultat. Préparez les pannes avec des étapes de restauration claires, la résolution des sessions bloquantes, et une procédure de récupération testée à partir de vidages logiques. Adoptez la liste de contrôle dans vos runbooks, pilotez-la sur un environnement non critique, capturez les sorties de vérification, et étendez l'approche une fois qu'elle a fait ses preuves.