Lister et changer de bases de données dans PostgreSQL : le guide technique complet

PostgreSQL gère plusieurs bases de données isolées au sein d’une seule instance de serveur, chacune avec son propre schéma, ses rôles et ses privilèges. Pour lister toutes les bases de données, exécutez l dans psql ou interrogez SELECT datname FROM pg_catalog.pg_database; depuis n’importe quelle session. Pour changer de base de données, vous devez ouvrir une nouvelle connexion — PostgreSQL applique une liaison stricte session-base de données sans équivalent de commande USE en cours de session.

Ce guide couvre toutes les méthodes disponibles pour énumérer et se connecter aux bases de données PostgreSQL, des commandes psql brutes et des requêtes de catalogue système aux chaînes de connexion, aux considérations pg_hba.conf, et aux modèles de flux de travail multi-bases de données utilisés dans les environnements de production.

Pourquoi le changement de base de données PostgreSQL fonctionne différemment

La plupart des développeurs venant de MySQL s’attendent à une commande USE database_name;. PostgreSQL l’omet délibérément. Chaque session PostgreSQL est liée à exactement une base de données au moment de la connexion, et cette liaison est immuable pour la durée de vie de la session. Il s’agit d’une décision architecturale enracinée dans le modèle de processus de PostgreSQL : le processus backend (postgres) charge le catalogue système de la base de données en mémoire partagée au démarrage, et changer de catalogue en cours de session nécessiterait de toute façon un redémarrage complet du processus.

Comprendre cette contrainte dès le départ évite des heures de débogage et façonne la manière dont vous architecturez les outils multi-bases de données, les pools de connexions et les configurations d’applications.

Lister toutes les bases de données dans PostgreSQL

Méthode 1 : La méta-commande l dans psql

La façon la plus rapide d’énumérer les bases de données est la méta-commande l (alias : list) dans une session psql interactive.

psql -U postgres

Une fois connecté :

l

Cela produit un tableau formaté similaire à :

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 myapp_db  | appuser  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 analytics | analyst  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | analyst=CTc/analyst
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

Les colonnes révèlent plus que de simples noms : Encoding est important lors de la migration de données entre serveurs, Collate affecte l’ordre de tri et le comportement des index, et Access privileges utilise la notation ACL de PostgreSQL (C = CONNECT, T = TEMPORARY, c = CREATE).

Pour obtenir des détails étendus incluant le tablespace et les limites de connexion, utilisez :

l+

Méthode 2 : Interroger le catalogue système pg_database

Pour les scripts, la surveillance ou l’introspection au niveau de l’application, interrogez directement la vue pg_catalog.pg_database. Cela fonctionne depuis n’importe quelle base de données du cluster car les catalogues système sont visibles globalement.

SELECT
    datname        AS database_name,
    pg_catalog.pg_get_userbyid(datdba) AS owner,
    pg_encoding_to_char(encoding) AS encoding,
    datcollate     AS collation,
    datctype       AS ctype,
    datconnlimit   AS connection_limit,
    pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(datname)) AS size
FROM pg_catalog.pg_database
WHERE datistemplate = false
ORDER BY datname;

Filtrer datistemplate = false exclut template0 et template1 des résultats — ce sont des modèles système, pas des bases de données opérationnelles. La colonne datconnlimit est critique dans les environnements partagés : une valeur de -1 signifie illimité, tandis que tout entier positif limite les connexions simultanées à cette base de données.

Conseil de production : Ajoutez pg_database_size() à vos requêtes de surveillance. Une base de données qui dépasse silencieusement la capacité du tablespace est une cause fréquente d’échecs d’écriture difficiles à diagnostiquer après coup.

Méthode 3 : Lister les bases de données sans entrer dans psql

Pour les scripts shell et les pipelines d’automatisation, vous pouvez récupérer la liste des bases de données sans entrer dans une session interactive :

psql -U postgres -c "l"

Ou pour une sortie propre et analysable par script :

psql -U postgres -t -c "SELECT datname FROM pg_database WHERE datistemplate = false;"

Le drapeau -t supprime les en-têtes de colonnes et les comptages de lignes, ne renvoyant que les valeurs brutes — idéal pour être redirigé vers grep, awk, ou des tableaux Bash.

Pour exporter la liste vers un fichier :

psql -U postgres -t -A -c "SELECT datname FROM pg_database WHERE datistemplate = false;" > db_list.txt

-A désactive l’alignement des colonnes, produisant un nom de base de données par ligne.
Changer de base de données dans PostgreSQL
Puisque vous ne pouvez pas changer de base de données au sein d’une session active, l’approche correcte consiste à terminer la connexion actuelle et à en établir une nouvelle ciblant la base de données souhaitée. Il existe plusieurs façons de le faire efficacement.
Méthode 1 : Quitter et se reconnecter depuis le shell
Dans psql, quittez la session actuelle :
q
Puis connectez-vous à la base de données cible :
psql -U postgres -d target_database
Méthode 2 : Utiliser c (Connect) dans psql
C’est la méthode la plus pratique pour le travail interactif. La méta-commande c ferme la connexion actuelle et en ouvre une nouvelle vers la base de données spécifiée — tout au sein de la même session de terminal.
c target_database
Vous pouvez également changer d’utilisateur et d’hôte simultanément :
c target_database admin_user localhost 5432
Syntaxe : c [database [username [host [port]]]]

Lorsque vous exécutez c, psql affichera une confirmation :

You are now connected to database "target_database" as user "postgres".

Cas limite important : Si la base de données cible n’existe pas ou si l’utilisateur actuel ne dispose pas du privilège CONNECT, c échouera et vous ramènera à la connexion précédente. C’est plus sûr qu’il n’y paraît — vous ne vous retrouverez pas sans connexion, mais vous devez gérer cela dans les scripts en vérifiant le statut de sortie.

Méthode 3 : Se connecter en tant qu’utilisateur différent

Pour se connecter à une base de données sous un rôle spécifique :

psql -d myapp_db -U appuser -h localhost -p 5432

Ou en utilisant le raccourci c dans une session existante :

c myapp_db appuser

C’est particulièrement utile lors du test des politiques de sécurité au niveau des lignes (RLS) ou pour vérifier que les utilisateurs d’application ne peuvent pas accéder aux tables en dehors de leur schéma.

Méthode 4 : Utiliser des chaînes de connexion (format URI)

PostgreSQL prend en charge le format URI de connexion libpq, qui encapsule tous les paramètres de connexion dans une seule chaîne. C’est la méthode préférée pour la configuration des applications, les pipelines CI/CD et les outils d’infrastructure-as-code.

psql "postgresql://appuser:password@localhost:5432/myapp_db"

Ou en utilisant le schéma postgres:// (les deux sont valides) :

psql "postgres://appuser:password@db.example.com:5432/analytics?sslmode=require"

Le paramètre ?sslmode=require impose le chiffrement TLS sur la connexion — une exigence non négociable pour toute base de données exposée au-delà de localhost. Si vous hébergez PostgreSQL sur un VPS ou un serveur dédié, associez toujours les chaînes de connexion avec sslmode=require ou sslmode=verify-full et un certificat SSL valide.

Paramètres URI de connexion à noter :

Méthode 5 : Utiliser psql avec des variables d’environnement

Pour des connexions répétées au même cluster, définissez des variables d’environnement pour éviter de saisir les identifiants à répétition :

export PGHOST=localhost
export PGPORT=5432
export PGUSER=postgres
export PGPASSWORD=secret   # Use .pgpass file in production instead
export PGDATABASE=myapp_db

psql

En production, utilisez un fichier .pgpass plutôt que PGPASSWORD pour éviter d’exposer les identifiants dans l’historique du shell ou les listes de processus :

# ~/.pgpass format: hostname:port:database:username:password
localhost:5432:myapp_db:appuser:strongpassword

Définissez correctement les permissions, sinon PostgreSQL ignorera le fichier :

chmod 600 ~/.pgpass

Comparaison : méthodes de changement de base de données

Gérer efficacement plusieurs connexions de bases de données

Utiliser pgAdmin pour la navigation par interface graphique

pgAdmin liste toutes les bases de données sous chaque serveur enregistré dans l’arborescence d’objets de gauche. Cliquer sur une base de données et ouvrir l’outil de requête limite automatiquement toutes les requêtes à cette base de données. C’est utile pour le travail exploratoire mais ne convient pas à l’automatisation.

Piège : pgAdmin maintient des slots de connexion séparés par base de données. Si votre serveur PostgreSQL a un paramètre max_connections faible (la valeur par défaut est 100), ouvrir de nombreuses bases de données dans pgAdmin peut épuiser le pool de connexions avant même que votre application ne démarre.

Utiliser PgBouncer pour le pooling de connexions

Dans les environnements de production avec des changements fréquents de bases de données, un pooler de connexions comme PgBouncer réduit considérablement la surcharge. PgBouncer fonctionne en trois modes :

• Mode session : Une connexion serveur par session client. Fonctionnellement équivalent aux connexions directes.
• Mode transaction : La connexion serveur n’est maintenue que pendant une transaction. Le plus efficace pour les charges de travail OLTP.
• Mode instruction : Connexion restituée après chaque instruction. Incompatible avec les transactions multi-instructions.

Lors de l’exécution de plusieurs bases de données d’application sur une seule instance PostgreSQL — un modèle courant sur l’hébergement VPS ou le VPS avec cPanel — PgBouncer en mode transaction peut réduire les processus backend actifs d’un ordre de grandeur.

Requêtes inter-bases de données avec dblink et postgres_fdw

Puisque les sessions sont limitées à une base de données, l’interrogation de données entre bases de données nécessite une extension. PostgreSQL propose deux options :

dblink — approche procédurale plus ancienne :

SELECT * FROM dblink(
    'dbname=analytics user=analyst host=localhost',
    'SELECT user_id, event_count FROM events WHERE date = current_date'
) AS remote(user_id INT, event_count BIGINT);

postgres_fdw — Foreign Data Wrapper moderne et conforme aux standards :

CREATE EXTENSION postgres_fdw;

CREATE SERVER analytics_server
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'localhost', dbname 'analytics', port '5432');

CREATE USER MAPPING FOR appuser
    SERVER analytics_server
    OPTIONS (user 'analyst', password 'secret');

CREATE FOREIGN TABLE remote_events (
    user_id INT,
    event_count BIGINT
)
SERVER analytics_server
OPTIONS (schema_name 'public', table_name 'events');

Après la configuration, remote_events se comporte comme une table locale. postgres_fdw prend en charge le pushdown de prédicats, ce qui signifie que les clauses WHERE sont exécutées sur le serveur distant, et non localement — une distinction de performance critique pour les grands ensembles de données.

Bases de données système : ce que vous ne devez pas toucher

PostgreSQL est livré avec quatre bases de données dans chaque nouveau cluster :

template1 est cloné chaque fois que vous exécutez CREATE DATABASE sans spécifier de modèle. Si vous installez des extensions ou créez des schémas dans template1, chaque nouvelle base de données les hérite. C’est utile pour standardiser les environnements mais dangereux si cela est fait accidentellement.

template0 existe comme solution de repli vierge. C’est le seul modèle qui peut être utilisé lors de la restauration d’une archive pg_dump avec un encodage ou une locale différente, car il ne contient aucun objet créé par l’utilisateur qui pourrait entrer en conflit.

Privilèges, pg_hba.conf, et échecs de connexion

Une source fréquente de confusion lors du changement de bases de données est la distinction entre les privilèges au niveau des rôles PostgreSQL et les règles d’authentification pg_hba.conf. Les deux doivent autoriser la connexion indépendamment.

Vérification au niveau du rôle : Le rôle doit avoir le privilège CONNECT sur la base de données cible :

GRANT CONNECT ON DATABASE target_database TO appuser;

Vérification pg_hba.conf : Le fichier d’authentification basée sur l’hôte (/etc/postgresql/15/main/pg_hba.conf sur Debian/Ubuntu) doit avoir une règle correspondante pour l’utilisateur, la base de données et l’adresse source. Une entrée typique :

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    myapp_db        appuser         10.0.0.0/8              scram-sha-256

Après avoir modifié pg_hba.conf, rechargez la configuration sans redémarrer le serveur :

sudo systemctl reload postgresql

Ou depuis psql :

SELECT pg_reload_conf();

Modèle d’échec courant : Un utilisateur dispose du privilège CONNECT au niveau SQL mais pg_hba.conf n’a pas de règle correspondante. Le message d’erreur (FATAL: no pg_hba.conf entry for host) est explicite, mais les développeurs ignorent souvent complètement le fichier car ils s’attendent à ce que les permissions de base de données soient gérées uniquement via SQL.

Matrice de décision pratique

Utilisez cette liste de contrôle pour sélectionner la bonne approche de connexion selon votre scénario :

• Exploration interactive sur une machine de développement locale : Utilisez c dbname dans psql. Rapide, sans nouveau processus.
• Script shell itérant sur plusieurs bases de données : Utilisez psql -U postgres -d $dbname -c "..." dans une boucle avec -t -A pour une sortie propre.
• Application se connectant à une seule base de données : Utilisez un URI de connexion avec sslmode=require et un pool de connexions (PgBouncer ou pooling intégré au pilote).
• Application nécessitant des données de deux bases de données : Implémentez postgres_fdw sur la base de données principale plutôt que de gérer deux pools de connexions séparés dans le code de l’application.
• Vérification de l’isolation RLS ou des privilèges : Utilisez c dbname role_name pour usurper l’identité du rôle cible sans quitter psql.
• Provisionnement automatisé / infrastructure-as-code : Utilisez des variables d’environnement ou .pgpass avec un compte de service ; ne codez jamais les identifiants en dur dans les scripts.
• Charge de travail de production à haute concurrence : Déployez PgBouncer en mode transaction entre l’application et PostgreSQL. Sur un serveur dédié, ajustez max_connections dans postgresql.conf en fonction de la capacité mémoire de votre matériel (chaque backend utilise environ 5 à 10 MB de RAM).
• SaaS multi-locataires avec des bases de données par locataire : Envisagez une multi-location basée sur les schémas au sein d’une seule base de données plutôt que des bases de données par locataire, afin d’éviter la fragmentation du pool de connexions et de simplifier les stratégies de sauvegarde.

Pour les équipes exécutant PostgreSQL aux côtés d’applications web, associer le serveur de base de données à un environnement d’hébergement mutualisé ou VPS correctement configuré et un domaine enregistré pour la couche applicative complète la pile de production standard.

FAQ

Puis-je changer de base de données sans fermer ma session psql ?

Oui. Utilisez la méta-commande c target_database dans psql. Elle ferme la connexion backend actuelle et en ouvre une nouvelle vers la base de données spécifiée, tout au sein de la même session de terminal. Vous pouvez optionnellement spécifier un utilisateur, un hôte et un port différents dans la même commande.

Pourquoi PostgreSQL n’a-t-il pas de commande USE comme MySQL ?

L’architecture de PostgreSQL lie un processus backend à une seule base de données au démarrage. Le catalogue système de la base de données est chargé en mémoire partagée pour ce processus, et changer de catalogue en cours de session est architecturalement équivalent à démarrer un nouveau processus. La commande c dans psql est l’équivalent pratique — elle rend simplement le redémarrage du processus transparent pour l’utilisateur.

Comment puis-je interroger des données de deux bases de données PostgreSQL différentes simultanément ?

Utilisez l’extension postgres_fdw pour créer un serveur étranger et des tables étrangères qui correspondent à la base de données distante. Après la configuration, vous pouvez joindre avec JOIN des tables locales et distantes dans une seule requête. Pour des requêtes ponctuelles, dblink est plus simple mais moins performant et plus difficile à maintenir.

Que se passe-t-il si je me connecte à template1 et que je le modifie ?

Tout objet que vous créez dans template1 — tables, extensions, schémas — sera cloné dans chaque nouvelle base de données créée avec CREATE DATABASE (sauf si TEMPLATE template0 est explicitement spécifié). C’est parfois intentionnel (par exemple, pré-installer uuid-ossp ou pgcrypto), mais des modifications accidentelles peuvent corrompre toutes les bases de données créées ultérieurement.

Comment puis-je savoir à quelle base de données une session psql actuelle est connectée ?

Exécutez ce qui suit dans psql :

SELECT current_database();

Ou vérifiez l’invite psql elle-même — par défaut, elle affiche dbname=# (superutilisateur) ou dbname=> (utilisateur ordinaire), où dbname est la base de données active.