Prise en main de psql : administrer PostgreSQL en ligne de commande

Vous avez installé PostgreSQL, le service tourne, pg_isready répond accepting connections. Maintenant, il faut entrer dedans. Comment lister les bases existantes ? Voir la structure d’une table ? Exécuter une requête, exporter un résultat en CSV, ou lancer un script SQL automatisé ?

psql est le client en ligne de commande officiel de PostgreSQL. C’est un outil beaucoup plus puissant qu’un simple prompt SQL : il intègre des méta-commandes pour explorer la structure, du formatage avancé pour lire les résultats, de l’import/export CSV, et un mode scripting complet avec variables et fichiers. Ce guide couvre tout ce qu’un administrateur ou un développeur utilise au quotidien.

Version utilisée

Ce guide s’appuie sur psql 18.3 (Debian 18.3-1.pgdg12+1) sous Debian 12. Les méta-commandes sont stables depuis PostgreSQL 12+ — les exemples fonctionnent sur toutes les versions supportées.

Ce que vous allez apprendre

• Se connecter à PostgreSQL en local (socket) et en réseau (TCP, URI, .pgpass)
• Explorer la structure d’un cluster avec les méta-commandes : \l, \dn, \dt, \d+, \du
• Créer et manipuler des objets : bases, schémas, tables, avec transactions
• Formater la sortie : mode étendu, chronométrage, export vers fichier
• Importer et exporter des données en CSV avec \copy
• Scripter des tâches avec psql -f, -v, \set et \gset

Dans quel contexte ?

psql est l’outil que vous utiliserez dans toutes ces situations :

• Vous venez d’installer PostgreSQL et voulez vérifier ce qui existe sur le cluster
• Vous devez créer une base, un schéma et des tables pour une nouvelle application
• Vous administrez un serveur et avez besoin de connaître la taille des bases, les connexions actives ou les verrous
• Vous devez importer un fichier CSV ou exporter des résultats pour un collègue
• Vous automatisez des tâches d’administration dans un script shell (migration, rapport, nettoyage)

Ce guide ne couvre pas…

Découvrir PostgreSQL Architecture, vocabulaire, processus, WAL — le contexte avant d'administrer

Installation Installer PostgreSQL depuis les dépôts PGDG et initialiser le cluster

Configuration Régler mémoire, connexions, WAL et journalisation

Sécurisation pg_hba.conf, TLS, rôles et moindre privilège

Prérequis

• PostgreSQL installé et le service actif (pg_isready retourne accepting connections)
• Accès au rôle postgres ou à un rôle avec LOGIN (voir le guide Installation)

Vérification rapide :

psql --version

psql (PostgreSQL) 18.3 (Debian 18.3-1.pgdg12+1)

Se connecter à PostgreSQL

Connexion locale (socket Unix)

La méthode la plus courante sur le serveur lui-même. Pas besoin de mot de passe — l’authentification peer vérifie que l’utilisateur système correspond au rôle PostgreSQL :

sudo -u postgres psql

Vous obtenez le prompt postgres=#. Le # indique un superuser ; un utilisateur normal verrait =>.

Pour se connecter directement à une base spécifique :

sudo -u postgres psql -d lab_admin

Connexion réseau (host, port, user, dbname)

Pour une connexion TCP (depuis un autre serveur, ou en local avec mot de passe) :

psql -h 127.0.0.1 -p 5432 -U admin_lab -d lab_admin

PostgreSQL demandera le mot de passe (méthode scram-sha-256 via pg_hba.conf).

URI de connexion

psql accepte aussi une URI complète — pratique pour les scripts et les variables d’environnement :

psql "postgresql://admin_lab@127.0.0.1:5432/lab_admin"

Format : postgresql://[user[:password]@][host][:port][/dbname][?param=value]

Ne mettez jamais le mot de passe dans l'URI

Inclure le mot de passe en clair dans une URI expose vos identifiants dans l’historique shell, les logs et les processus (/proc). Utilisez .pgpass ou la variable PGPASSWORD (en dernier recours).

Variables d’environnement et .pgpass

Plutôt que de répéter les options de connexion, configurez des variables d’environnement :

export PGHOST=127.0.0.1
export PGPORT=5432
export PGUSER=admin_lab
export PGDATABASE=lab_admin
psql    # se connecte automatiquement avec ces paramètres

Pour éviter de taper le mot de passe, créez un fichier .pgpass :

# Format : hostname:port:database:username:password
echo "127.0.0.1:5432:lab_admin:admin_lab:<change-me>" > ~/.pgpass
chmod 600 ~/.pgpass

Permissions obligatoires

PostgreSQL refuse d’utiliser .pgpass si les permissions ne sont pas 600. Le chmod n’est pas optionnel.

\conninfo : vérifier sa connexion

Une fois connecté, vérifiez toujours où vous êtes :

\conninfo

      Parameter       |        Value
----------------------+---------------------
 Database             | lab_admin
 Client User          | postgres
 Socket Directory     | /var/run/postgresql
 Server Port          | 5432
 Protocol Version     | 3.0
 Password Used        | false
 SSL Connection       | false
 Superuser            | on

Les informations clés : Database (la base active), Password Used (false = socket peer), SSL Connection (false = pas de chiffrement réseau).

Les méta-commandes essentielles

Les méta-commandes commencent par \ et sont interprétées par psql lui-même, pas par le serveur SQL. Elles permettent d’explorer la structure sans écrire de requêtes sur les catalogues système.

Explorer : \l, \dn, \dt, \d+, \di, \du

Lister les bases de données :

\l

   Name    |   Owner   | Encoding | Locale Provider | Collate |  Ctype  | Access privileges
-----------+-----------+----------+-----------------+---------+---------+-----------------------
 lab_admin | admin_lab | UTF8     | libc            | C.UTF-8 | C.UTF-8 |
 postgres  | postgres  | UTF8     | libc            | C.UTF-8 | C.UTF-8 |
 template0 | postgres  | UTF8     | libc            | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |           |          |                 |         |         | postgres=CTc/postgres
 template1 | postgres  | UTF8     | libc            | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |           |          |                 |         |         | postgres=CTc/postgres

Lister les schémas (avec détails) :

\dn+

  Name  |       Owner       |           Access privileges            |      Description
--------+-------------------+----------------------------------------+------------------------
 app    | postgres          |                                        |
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                   |

Lister les tables d’un schéma :

\dt app.*

 Schema |   Name    | Type  |  Owner
--------+-----------+-------+----------
 app    | clients   | table | postgres
 app    | commandes | table | postgres

Voir la structure d’une table :

\d app.clients

                                  Table "app.clients"
   Column    |  Type   | Collation | Nullable |                 Default
-------------+---------+-----------+----------+-----------------------------------------
 id          | integer |           | not null | nextval('app.clients_id_seq'::regclass)
 nom         | text    |           | not null |
 email       | text    |           | not null |
 ville       | text    |           |          |
 inscription | date    |           |          | CURRENT_DATE
Indexes:
    "clients_pkey" PRIMARY KEY, btree (id)
    "clients_email_key" UNIQUE CONSTRAINT, btree (email)
Referenced by:
    TABLE "app.commandes" CONSTRAINT "commandes_client_id_fkey" FOREIGN KEY (client_id) REFERENCES app.clients(id)

Ajoutez + pour plus de détails (stockage, compression, contraintes NOT NULL explicites) :

\d+ app.clients

Lister les index :

\di app.*

 Schema |       Name        | Type  |  Owner   |   Table
--------+-------------------+-------+----------+-----------
 app    | clients_email_key | index | postgres | clients
 app    | clients_pkey      | index | postgres | clients
 app    | commandes_pkey    | index | postgres | commandes

Lister les rôles :

\du

 Role name |                         Attributes
-----------+------------------------------------------------------------
 admin_lab | Create DB
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

Naviguer : \c, SET search_path

Pour changer de base de données sans quitter psql :

\c lab_admin

Pour accéder aux tables du schéma app sans préfixer :

SET search_path TO app, public;
SELECT * FROM clients LIMIT 2;

 id |     nom      |       email       | ville | inscription
----+--------------+-------------------+-------+-------------
  1 | Alice Martin | alice@example.com | Paris | 2026-01-15
  2 | Bob Dupont   | bob@example.com   | Lyon  | 2026-02-20

search_path est temporaire

SET search_path ne modifie que la session courante. Pour un changement permanent, utilisez ALTER ROLE admin_lab SET search_path TO app, public; ou ALTER DATABASE lab_admin SET search_path TO app, public; — ces valeurs s’appliqueront à chaque nouvelle connexion et surchargent la valeur globale de postgresql.conf. Réservez postgresql.conf uniquement quand vous voulez imposer un search_path à l’ensemble du serveur.

Aide : \h (SQL), \? (méta-commandes)

Deux systèmes d’aide intégrés :

• \h CREATE TABLE — affiche la syntaxe SQL complète d’une commande
• \? — liste toutes les méta-commandes disponibles dans psql

\h CREATE TABLE

Command:     CREATE TABLE
Description: define a new table
Syntax:
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [...] | table_constraint | LIKE source_table [...] }
  [, ... ]
] )
...
URL: https://www.postgresql.org/docs/18/sql-createtable.html

L’URL en fin de sortie pointe directement vers la documentation officielle de la version installée.

Tableau récapitulatif des méta-commandes courantes

Commande	Description
\l	Lister les bases de données
\c <base>	Se connecter à une autre base
\dn+	Lister les schémas (avec permissions)
\dt [schema.*]	Lister les tables
\d <table>	Structure d’une table (colonnes, types, index)
\d+ <table>	Structure détaillée (stockage, compression, contraintes)
\di [schema.*]	Lister les index
\du	Lister les rôles
\conninfo	Informations de connexion courante
\x	Basculer en mode étendu (une ligne = un enregistrement vertical)
\timing	Activer/désactiver le chronométrage des requêtes
\o <fichier>	Rediriger la sortie vers un fichier
\i <fichier>	Exécuter un script SQL
\copy	Import/export CSV (côté client)
\! <cmd>	Exécuter une commande shell
\h <cmd>	Aide sur une commande SQL
\?	Liste de toutes les méta-commandes
\q	Quitter psql

Glissez pour voir

Créer et gérer des objets

Créer une base et un schéma

CREATE DATABASE mon_projet;
\c mon_projet

CREATE SCHEMA backend;

Toute table créée dans le schéma backend sera isolée du schéma public :

CREATE TABLE backend.utilisateurs (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  login TEXT UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

Créer une table, insérer, interroger

Voici une table réelle créée sur notre lab :

CREATE TABLE app.clients (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  nom TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  ville TEXT,
  inscription DATE DEFAULT CURRENT_DATE
);

INSERT INTO app.clients (nom, email, ville, inscription) VALUES
  ('Alice Martin', 'alice@example.com', 'Paris', '2026-01-15'),
  ('Bob Dupont', 'bob@example.com', 'Lyon', '2026-02-20'),
  ('Claire Durand', 'claire@example.com', 'Marseille', '2026-03-10'),
  ('David Moreau', 'david@example.com', 'Toulouse', '2026-03-28'),
  ('Eva Bernard', 'eva@example.com', 'Bordeaux', '2026-04-01');

SELECT * FROM app.clients;

 id |      nom      |       email        |   ville   | inscription
----+---------------+--------------------+-----------+-------------
  1 | Alice Martin  | alice@example.com  | Paris     | 2026-01-15
  2 | Bob Dupont    | bob@example.com    | Lyon      | 2026-02-20
  3 | Claire Durand | claire@example.com | Marseille | 2026-03-10
  4 | David Moreau  | david@example.com  | Toulouse  | 2026-03-28
  5 | Eva Bernard   | eva@example.com    | Bordeaux  | 2026-04-01

Transactions : BEGIN, COMMIT, ROLLBACK

Toute modification peut être testée dans une transaction avant d’être validée :

BEGIN;
INSERT INTO app.clients (nom, email, ville) VALUES ('Test Rollback', 'test@example.com', 'Nantes');
SELECT count(*) FROM app.clients;

 count
-------
     6

L’insertion est visible dans la transaction. Mais si vous annulez :

ROLLBACK;
SELECT count(*) FROM app.clients;

 count
-------
     5

La ligne a disparu. C’est le mécanisme fondamental des transactions : rien n’est définitif tant que vous n’avez pas exécuté COMMIT.

Réflexe admin : BEGIN avant tout DDL risqué

Prenez l’habitude d’ouvrir une transaction avant tout ALTER TABLE, DROP TABLE ou UPDATE sans clause WHERE. PostgreSQL supporte le DDL transactionnel pour la plupart des opérations — un ROLLBACK annulera proprement un DROP TABLE ou un ALTER TABLE non commité. Exception : CREATE DATABASE, DROP DATABASE et CREATE TABLESPACE ne peuvent pas s’exécuter dans un bloc de transaction.

Supprimer proprement

DROP supprime un objet. Si d’autres objets en dépendent, PostgreSQL refuse par défaut :

DROP TABLE app.clients;
-- ERROR: cannot drop table app.clients because other objects depend on it

Pour forcer la suppression avec les dépendances :

DROP TABLE app.clients CASCADE;
-- supprime aussi la contrainte FK dans app.commandes

CASCADE est irréversible

DROP ... CASCADE supprime silencieusement les objets dépendants (contraintes, vues, fonctions). Listez d’abord les dépendances avec \d+ <table> avant de dropper quoi que ce soit.

Formater et filtrer la sortie

\x : mode étendu (affichage vertical)

Pour les tables larges (beaucoup de colonnes), le mode étendu affiche chaque ligne comme un bloc clé/valeur :

\x
SELECT * FROM app.clients WHERE id = 1;

-[ RECORD 1 ]------------------
id          | 1
nom         | Alice Martin
email       | alice@example.com
ville       | Paris
inscription | 2026-01-15

Tapez \x à nouveau pour revenir au mode tableau. Vous pouvez aussi utiliser \x auto pour laisser psql choisir le mode le plus lisible selon la largeur du résultat.

\timing : chronométrer les requêtes

\timing
SELECT count(*) FROM app.clients;

 count
-------
     5
(1 row)

Time: 0.782 ms

Indispensable pour repérer les requêtes lentes. \timing à nouveau pour désactiver.

\o : rediriger la sortie vers un fichier

Pour capturer le résultat d’une requête dans un fichier :

\o /tmp/clients_par_ville.txt
SELECT nom, ville FROM app.clients ORDER BY ville;
\o

Le fichier /tmp/clients_par_ville.txt contient :

      nom      |   ville
---------------+-----------
 Eva Bernard   | Bordeaux
 Bob Dupont    | Lyon
 Claire Durand | Marseille
 Alice Martin  | Paris
 David Moreau  | Toulouse

\o sans argument rétablit la sortie sur le terminal.

Requêtes utiles d’administration

Taille des bases :

SELECT datname, pg_size_pretty(pg_database_size(datname)) AS taille
FROM pg_database WHERE datistemplate = false
ORDER BY pg_database_size(datname) DESC;

  datname  | taille
-----------+---------
 lab_admin | 7894 kB
 postgres  | 7654 kB

Connexions actives :

SELECT pid, usename, datname, state, LEFT(query, 60) AS query
FROM pg_stat_activity WHERE backend_type = 'client backend';

 pid  | usename  |  datname  | state  |                            query
------+----------+-----------+--------+--------------------------------------------------------------
 5504 | postgres | lab_admin | active | SELECT pid, usename, datname, state, LEFT(query ...

Taille des tables :

SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS taille
FROM pg_tables WHERE schemaname = 'app'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

 schemaname | tablename | taille
------------+-----------+--------
 app        | clients   | 48 kB
 app        | commandes | 32 kB

Verrous actifs :

SELECT pid, mode, granted, relation::regclass
FROM pg_locks WHERE relation IS NOT NULL;

Importer et exporter des données

\copy : import/export CSV côté client

\copy est une méta-commande psql qui transfère les données entre le client et le serveur. Contrairement à la commande SQL COPY, elle ne nécessite pas que le fichier soit accessible depuis le serveur.

Exporter en CSV :

\copy app.clients TO '/tmp/clients.csv' WITH (FORMAT csv, HEADER)

COPY 5

Le fichier contient :

id,nom,email,ville,inscription
1,Alice Martin,alice@example.com,Paris,2026-01-15
2,Bob Dupont,bob@example.com,Lyon,2026-02-20
3,Claire Durand,claire@example.com,Marseille,2026-03-10
...

Importer depuis un CSV :

\copy app.clients (nom, email, ville) FROM '/tmp/nouveaux_clients.csv' WITH (FORMAT csv, HEADER)

\\copy vs COPY

\copy (avec backslash) est une commande psql — le fichier est lu/écrit côté client. COPY (SQL pur) est une commande serveur — le fichier doit être sur le serveur, accessible par l’utilisateur postgres. En administration courante, préférez \copy — c’est plus simple et plus sûr.

Limites de \\copy

\copy prend tout le reste de la ligne comme argument : pas d’interpolation de variables psql ni d’expansion backquote dans ses paramètres. De plus, les données transitent par la connexion client/serveur — pour des imports massifs (millions de lignes), la commande SQL COPY exécutée directement sur le serveur sera nettement plus rapide.

Exécuter un script SQL : \i et psql -f

Depuis psql avec \i :

\i /tmp/rapport.sql

Depuis le shell avec psql -f :

sudo -u postgres psql -d lab_admin -f /tmp/rapport.sql

Voici un exemple de script SQL (rapport.sql) :

\echo === Rapport clients ===
SELECT ville, count(*) AS nb_clients
FROM app.clients GROUP BY ville ORDER BY nb_clients DESC;

\echo === Dernières commandes ===
SELECT c.nom, o.montant, o.statut
FROM app.commandes o JOIN app.clients c ON o.client_id = c.id
ORDER BY o.created_at DESC LIMIT 3;

Sortie :

=== Rapport clients ===
   ville   | nb_clients
-----------+------------
 Lyon      |          1
 Marseille |          1
 Toulouse  |          1
 Paris     |          1
 Bordeaux  |          1

=== Dernières commandes ===
     nom      | montant |  statut
--------------+---------+----------
 Alice Martin |  149.99 | livree
 Alice Martin |   89.50 | en_cours
 Bob Dupont   |  250.00 | livree

Scripter avec psql

One-liner : psql -c

Pour exécuter une seule requête depuis le shell (dans un script Bash, un cron, etc.) :

sudo -u postgres psql -d lab_admin -c "SELECT nom, ville FROM app.clients WHERE ville = 'Paris';"

     nom      | ville
--------------+-------
 Alice Martin | Paris

Ajoutez -t (tuples only) et -A (unaligned) pour un résultat exploitable par un script :

sudo -u postgres psql -d lab_admin -tA -c "SELECT count(*) FROM app.clients;"
# Sortie : 5

Règles de -c à connaître

Chaque chaîne passée à -c est soit du SQL pur, soit une seule méta-commande — jamais un mélange des deux dans la même option. Côté SQL, la chaîne entière est envoyée au serveur comme une requête unique, donc exécutée dans une seule transaction implicite. Si vous avez besoin de plusieurs transactions, utilisez plusieurs -c ou découpez avec BEGIN/COMMIT explicites. Les variables psql (-v) ne sont pas interpolées dans -c.

Variables avec -v et \set

psql supporte des variables substituées dans les requêtes. Elles se définissent avec -v au lancement ou \set en interactif.

Avec -v (les variables sont substituées dans les scripts, pas dans -c) :

sudo -u postgres psql -d lab_admin -v ville="'Lyon'" <<'SQL'
SELECT * FROM app.clients WHERE ville = :ville;
SQL

 id |    nom     |      email      | ville | inscription
----+------------+-----------------+-------+-------------
  2 | Bob Dupont | bob@example.com | Lyon  | 2026-02-20

Variables psql et -c ne font pas bon ménage

Les variables définies avec -v ne sont pas interpolées dans les requêtes passées avec -c. Utilisez un heredoc (<<'SQL'), \i ou -f pour que l’interpolation fonctionne.

En interactif avec \set :

\set ville 'Marseille'
SELECT * FROM app.clients WHERE ville = :'ville';

La syntaxe :'ville' entoure la valeur de guillemets simples — indispensable pour les chaînes de caractères.

\gset : capturer le résultat dans une variable

SELECT count(*) AS total FROM app.clients \gset
\echo Il y a :total clients

Il y a 5 clients

\gset exécute la requête et stocke chaque colonne du résultat comme variable psql. C’est très utile dans les scripts pour enchaîner des requêtes conditionnelles.

\! : exécuter une commande shell depuis psql

Pas besoin de quitter psql pour lancer une commande système :

\! hostname

pg-lab

\! date

Mon Apr 13 09:50:55 UTC 2026

Dépannage

FATAL: role "bob" does not exist

Vous essayez de vous connecter avec un rôle qui n’existe pas. Vérifiez :

\du

Créez le rôle si nécessaire :

CREATE ROLE bob LOGIN PASSWORD '<change-me>';

FATAL: Peer authentication failed

Vous tentez une connexion socket avec un utilisateur système qui ne correspond à aucun rôle PostgreSQL. Solutions :

• Utiliser sudo -u postgres psql (connexion en tant que rôle postgres)
• Passer en TCP : psql -h 127.0.0.1 -U admin_lab (utilise scram-sha-256 au lieu de peer)

psql: error: connection to server on socket ... failed: No such file or directory

Le socket n’existe pas — le service PostgreSQL n’est probablement pas démarré :

pg_isready
sudo systemctl status postgresql@18-main

La sortie est tronquée ou illisible

• Colonnes trop larges → \x auto pour basculer en mode étendu automatique
• Résultat paginé → \pset pager off pour désactiver le pager
• Résultat sans bordures → \pset format unaligned puis \pset fieldsep ',' pour un pseudo-CSV

À retenir

• psql est bien plus qu’un prompt SQL — les méta-commandes (\l, \dt, \d+, \du) évitent d’écrire des requêtes catalogue.
• Vérifiez toujours votre connexion avec \conninfo avant de travailler.
• Utilisez \x auto pour les tables larges et \timing pour repérer les requêtes lentes.
• \copy (côté client) est plus pratique et plus sûr que COPY (côté serveur) pour l’import/export CSV.
• Les variables psql (-v, \set, \gset) fonctionnent dans les scripts et heredocs, mais pas avec -c. Chaque SQL passé à -c est une transaction unique.
• BEGIN / ROLLBACK avant la plupart des DDL risqués — sauf CREATE/DROP DATABASE et CREATE TABLESPACE qui refusent un bloc transactionnel.
• Les requêtes d’administration (pg_stat_activity, pg_database_size, pg_locks) sont vos outils de diagnostic quotidiens.

Prochaines étapes

Configurer PostgreSQL Régler mémoire, connexions, WAL et journalisation pour votre workload

Sauvegarde et restauration pg_dump, pg_basebackup, archivage WAL et PITR

Sécuriser PostgreSQL pg_hba.conf restrictif, TLS, séparation de rôles

Monitoring et maintenance pg_stat_*, autovacuum, bloat et alertes

×

📖 Voir la documentation →