psql en script : codes de retour, ON_ERROR_STOP, CSV et variables

Dans un script, psql doit échouer quand une requête échoue, et rendre une sortie que le shell sait lire : par défaut, il ne fait ni l'un ni l'autre. Un fichier SQL dont la deuxième commande échoue continue jusqu'au bout, et psql sort avec le code 0 : la CI croit la migration réussie. Cette leçon donne les options qui rendent psql fiable dans un script shell ou une chaîne CI, toutes mesurées sous PostgreSQL 18.6 : codes de retour, ON_ERROR_STOP, transaction unique, sorties brute et CSV, variables et mots de passe.

Ce que vous allez apprendre

• Faire échouer un script quand une requête SQL échoue.
• Appliquer un lot de requêtes en une seule transaction.
• Produire une sortie exploitable par le shell : brute, CSV, export de table.
• Passer une valeur à une requête et un mot de passe à psql sans fuite.

Prérequis

Les leçons Lire le schéma et Transactions et concurrence. Dans les exemples, psql désigne le client du conteneur, appelé par docker exec -i formation-sql psql -U postgres -d infra ; avec un client installé sur le poste, les options sont les mêmes.

Quel code de retour rend psql ?

Un script shell ne voit d'un programme que son code de retour. Celui de psql dépend de la façon dont on lui passe les requêtes, et un cas surprend :

Situation	Code de retour mesuré
tout s'est bien passé, même si la requête rend zéro ligne	0
psql -c "..." dont la requête échoue	1
connexion impossible : base absente, serveur arrêté, mot de passe refusé	2
fichier ou entrée standard avec erreur, sans ON_ERROR_STOP	0
fichier ou entrée standard avec erreur, avec ON_ERROR_STOP=1	3

Glissez pour voir

Le quatrième cas est le piège. Un fichier de trois requêtes dont la deuxième vise une table inexistante affiche l'erreur, exécute la troisième, puis sort en 0. L'option -v ON_ERROR_STOP=1 arrête l'exécution à la première erreur et rend le code 3 :

psql -v ON_ERROR_STOP=1 < migration.sql
echo $?    # 3 si une requête a échoué

C'est l'option à mettre sur toute exécution de fichier SQL dans un script ou une CI, sans exception. Un résultat vide n'est pas une erreur : une requête qui ne trouve rien sort en 0, et c'est au script de tester ce qu'il a lu.

Tout ou rien : la transaction unique

ON_ERROR_STOP arrête l'exécution, mais ce qui précède l'erreur est déjà validé, chaque commande étant en autocommit. Mesuré sur un lot de deux insertions dont la seconde viole une clé étrangère : la première catégorie reste en base. L'option -1 (ou --single-transaction) enveloppe tout le fichier dans une seule transaction :

psql -1 -v ON_ERROR_STOP=1 < lot.sql

Avec -1, la même erreur annule aussi la première insertion : la base revient à l'état d'avant le lot, et le code de retour vaut 3. Le couple -1 -v ON_ERROR_STOP=1 est la forme sûre pour appliquer une migration : elle réussit entièrement, ou ne laisse aucune trace.

Une sortie que le shell sait lire

L'affichage par défaut, en tableau aligné avec en-tête et compteur de lignes, est fait pour l'œil. Deux options le rendent exploitable :

psql -At -c "SELECT hostname, ram_gb FROM servers WHERE environment = 'staging' ORDER BY 1"
# app-staging-01|4
# db-staging-01|16

-A supprime l'alignement, -t l'en-tête et le compteur ; le séparateur par défaut est |, que -F ',' remplace. Une colonne unique se lit directement dans une boucle :

for h in $(psql -At -c "SELECT hostname FROM servers WHERE environment = 'staging' ORDER BY 1"); do
  echo "traitement de $h"
done

Pour un fichier destiné à un tableur ou à un autre outil, --csv produit un CSV correct, avec en-tête et guillemets là où il en faut :

psql --csv -c "SELECT hostname, notes FROM servers WHERE id IN (3, 7) ORDER BY id"
# hostname,notes
# db-prod-01,PostgreSQL principal
# monitoring-01,Prometheus + Grafana

Exporter et importer avec \copy

La méta-commande \copy transfère une table ou le résultat d'une requête entre la base et un fichier du client, au format CSV. Vers la sortie standard, elle se redirige comme n'importe quelle commande. L'import, lui, suppose une table de destination : l'exemple la crée d'abord, puis charge un fichier import.csv de deux lignes :

psql -c "\copy (SELECT hostname, environment FROM servers ORDER BY id) TO STDOUT WITH (FORMAT csv, HEADER)" > serveurs.csv
psql -c "CREATE TABLE import_test (hostname text, environment text)"
psql -c "\copy import_test FROM STDIN WITH (FORMAT csv, HEADER)" < import.csv
# COPY 2

\copy doit être seul dans son -c : mêlé à une requête SQL dans la même chaîne, il provoque une erreur de syntaxe. La commande SQL COPY, sans barre oblique inverse, fait la même chose côté serveur : elle lit et écrit des fichiers sur la machine de la base, et exige des droits élevés. Depuis un poste ou une CI, c'est \copy qu'on utilise.

Passer une valeur à une requête

Un script doit souvent injecter une valeur, un environnement ou un nom d'hôte, dans une requête. La tentation est de l'insérer dans la chaîne avec le shell, et c'est une injection SQL :

# ❌ la valeur fait partie du code SQL : elle peut le réécrire
env="x' OR '1'='1"
psql -At -c "SELECT count(*) FROM servers WHERE environment = '$env'"
# 12 : tous les serveurs, au lieu d'aucun

psql fournit des variables : -v nom=valeur les définit, et :'nom' les insère dans la requête entre apostrophes, échappées. La même valeur malveillante est alors traitée comme une simple chaîne :

echo "SELECT count(*) FROM servers WHERE environment = :'env';" | psql -At -v env="x' OR '1'='1"
# 0

Deux précisions mesurées. Les variables ne sont pas interpolées dans -c : psql -v env=staging -c "... :'env'" échoue sur une erreur de syntaxe, d'où la requête passée par l'entrée standard ou par -f. Et :"nom" insère un identifiant échappé, un nom de table par exemple. La leçon sur les requêtes paramétrées montre l'équivalent depuis un langage de programmation.

Récupérer une valeur dans une variable

\gset remplace le point-virgule final d'une requête qui rend une ligne : il range chaque colonne dans une variable psql du même nom, réutilisable ensuite.

SELECT count(*) AS nb FROM servers WHERE active \gset
\echo serveurs actifs : :nb
-- serveurs actifs : 11

C'est utile dans un fichier SQL qui doit calculer puis décider, sans aller-retour par le shell.

Le mot de passe, sans fuite

Un mot de passe écrit sur la ligne de commande apparaît dans l'historique du shell et dans la liste des processus. La variable d'environnement PGPASSWORD évite l'historique mais reste visible de l'environnement du processus. La solution prévue par PostgreSQL est le fichier ~/.pgpass, une ligne par connexion, au format hôte:port:base:utilisateur:mot_de_passe, dont le chemin peut être changé par la variable PGPASSFILE.

Ce fichier n'est lu que s'il est privé. Mesuré : en 0644, psql avertit password file ... has group or world access; permissions should be u=rw (0600) or less, l'ignore, et la connexion échoue avec le code 2 ; en 0600, elle réussit. Dans un script, l'option -w interdit à psql de demander le mot de passe au clavier : sans mot de passe disponible, il échoue au lieu de rester bloqué.

Vérifier qu'une base répond

Pour un healthcheck ou une attente au démarrage, pg_isready teste qu'un serveur accepte les connexions, sans requête ni authentification. Il rend 0 si le serveur répond, et 2 s'il ne répond pas : mesuré sur un port fermé, localhost:5433 - no response, code 2. Une boucle d'attente dans un script de déploiement l'appelle jusqu'à obtenir 0.

À retenir

• psql sur un fichier continue après une erreur et sort en 0 : -v ON_ERROR_STOP=1, toujours, qui rend le code 3.
• -1 fait de tout le fichier une seule transaction : une migration réussit entièrement ou ne laisse rien.
• -At donne une sortie brute pour le shell, --csv un CSV, \copy exporte et importe par le client.
• Une valeur insérée par le shell dans une requête est une injection ; -v nom=valeur et :'nom' l'échappent, par l'entrée standard ou -f.
• Le mot de passe va dans un .pgpass en 0600 ; -w évite l'attente d'une saisie ; pg_isready vérifie qu'une base répond.

Contrôle de connaissances

Contrôle de connaissances

Validez vos connaissances avec ce quiz interactif

6 questions

6 min.

70% requis

Informations

• Le chronomètre démarre au clic sur Démarrer
• Questions à choix multiples, vrai/faux et réponses courtes
• Vous pouvez naviguer entre les questions
• Les résultats détaillés sont affichés à la fin

Démarrer le quiz

Lance le quiz et démarre le chronomètre

×

📖 Voir la documentation →