Listing and Switching Databases in PostgreSQL: The Complete Technical Guide

PostgreSQL manages multiple isolated databases within a single server instance, each with its own schema, roles, and privileges. To list all databases, run l inside psql or query SELECT datname FROM pg_catalog.pg_database; from any session. To switch databases, you must open a new connection — PostgreSQL enforces strict session-to-database binding with no in-session USE command equivalent.

This guide covers every method available to enumerate and connect to PostgreSQL databases, from raw psql commands and system catalog queries to connection strings, pg_hba.conf considerations, and multi-database workflow patterns used in production environments.

Why PostgreSQL Database Switching Works Differently

Most developers coming from MySQL expect a USE database_name; command. PostgreSQL deliberately omits this. Each PostgreSQL session is bound to exactly one database at connection time, and that binding is immutable for the lifetime of the session. This is an architectural decision rooted in PostgreSQL's process model: the backend process (postgres) loads the database's system catalog into shared memory at startup, and switching catalogs mid-session would require a full process restart anyway.

Understanding this constraint upfront prevents hours of debugging and shapes how you architect multi-database tooling, connection pools, and application configurations.

Listing All Databases in PostgreSQL

Method 1: The l Meta-Command in psql

The fastest way to enumerate databases is the l meta-command (alias: list) inside an interactive psql session.

psql -U postgres

Once connected:

l

This produces a formatted table similar to:

                                  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 |

The columns reveal more than just names: Encoding matters when migrating data between servers, Collate affects sort order and index behavior, and Access privileges use PostgreSQL's ACL notation (C = CONNECT, T = TEMPORARY, c = CREATE).

To get extended details including tablespace and connection limits, use:

l+

Method 2: Querying the pg_database System Catalog

For scripting, monitoring, or application-level introspection, query the pg_catalog.pg_database view directly. This works from any database in the cluster because system catalogs are globally visible.

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;

Filtering datistemplate = false excludes template0 and template1 from results — those are system templates, not operational databases. The datconnlimit column is critical in shared environments: a value of -1 means unlimited, while any positive integer caps concurrent connections to that database.

Production tip: Add pg_database_size() to your monitoring queries. A database silently growing past tablespace capacity is a common cause of write failures that are difficult to diagnose after the fact.

Method 3: Listing Databases Without Entering psql

For shell scripts and automation pipelines, you can retrieve the database list without entering an interactive session:

psql -U postgres -c "l"

Or for clean, script-parseable output:

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

The -t flag suppresses column headers and row counts, returning only raw values — ideal for piping into grep, awk, or Bash arrays.

To export the list to a file:

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

-A disables column alignment, producing one database name per line.
Switching Between Databases in PostgreSQL
Since you cannot switch databases within a live session, the correct approach is to terminate the current connection and establish a new one targeting the desired database. There are several ways to do this efficiently.
Method 1: Exit and Reconnect from the Shell
Inside psql, exit the current session:
q
Then connect to the target database:
psql -U postgres -d target_database
Method 2: Use c (Connect) Inside psql
This is the most practical method for interactive work. The c meta-command closes the current connection and opens a new one to the specified database — all within the same terminal session.
c target_database
You can also switch user and host simultaneously:
c target_database admin_user localhost 5432
Syntax: c [database [username [host [port]]]]

When you run c, psql will display a confirmation:

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

Important edge case: If the target database does not exist or the current user lacks CONNECT privilege, c will fail and drop you back to the previous connection. This is safer than it sounds — you will not be left without a connection, but you must handle this in scripts by checking the exit status.

Method 3: Connecting as a Different User

To connect to a database under a specific role:

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

Or using the c shorthand inside an existing session:

c myapp_db appuser

This is particularly useful when testing row-level security (RLS) policies or verifying that application users cannot access tables outside their schema.

Method 4: Using Connection Strings (URI Format)

PostgreSQL supports the libpq connection URI format, which encapsulates all connection parameters into a single string. This is the preferred method for application configuration, CI/CD pipelines, and infrastructure-as-code tools.

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

Or using the postgres:// scheme (both are valid):

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

The ?sslmode=require parameter enforces TLS encryption on the connection — a non-negotiable requirement for any database exposed beyond localhost. If you are hosting PostgreSQL on a VPS or dedicated server, always pair connection strings with sslmode=require or sslmode=verify-full and a valid SSL certificate.

Connection URI parameters of note:

Method 5: Using psql with Environment Variables

For repeated connections to the same cluster, set environment variables to avoid typing credentials repeatedly:

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

psql

In production, use a .pgpass file instead of PGPASSWORD to avoid exposing credentials in shell history or process lists:

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

Set permissions correctly or PostgreSQL will ignore the file:

chmod 600 ~/.pgpass

Comparison: Database Switching Methods

Managing Multiple Database Connections Efficiently

Using pgAdmin for GUI-Based Navigation

pgAdmin lists all databases under each registered server in the left-hand object tree. Clicking a database and opening the Query Tool automatically scopes all queries to that database. This is useful for exploratory work but is not suitable for automation.

Pitfall: pgAdmin maintains separate connection slots per database. If your PostgreSQL server has a low max_connections setting (default is 100), opening many databases in pgAdmin can exhaust the connection pool before your application even starts.

Using PgBouncer for Connection Pooling

In production environments with frequent database switching, a connection pooler like PgBouncer dramatically reduces overhead. PgBouncer operates in three modes:

• Session mode: One server connection per client session. Functionally equivalent to direct connections.
• Transaction mode: Server connection is held only during a transaction. Most efficient for OLTP workloads.
• Statement mode: Connection returned after each statement. Incompatible with multi-statement transactions.

When running multiple application databases on a single PostgreSQL instance — a common pattern on VPS hosting or VPS with cPanel — PgBouncer in transaction mode can reduce active backend processes by an order of magnitude.

Cross-Database Queries with dblink and postgres_fdw

Since sessions are database-scoped, querying data across databases requires an extension. PostgreSQL provides two options:

dblink — older, procedural approach:

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 — modern, standards-compliant Foreign Data Wrapper:

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');

After setup, remote_events behaves like a local table. postgres_fdw supports predicate pushdown, meaning WHERE clauses are executed on the remote server, not locally — a critical performance distinction for large datasets.

System Databases: What You Must Not Touch

PostgreSQL ships with four databases in every fresh cluster:

template1 is cloned whenever you run CREATE DATABASE without specifying a template. If you install extensions or create schemas in template1, every new database inherits them. This is useful for standardizing environments but dangerous if done accidentally.

template0 exists as a pristine fallback. It is the only template that can be used when restoring a pg_dump archive with a different encoding or locale, because it has no user-created objects that could conflict.

Privileges, pg_hba.conf, and Connection Failures

A common source of confusion when switching databases is the distinction between PostgreSQL role-level privileges and pg_hba.conf authentication rules. Both must permit the connection independently.

Role-level check: The role must have CONNECT privilege on the target database:

GRANT CONNECT ON DATABASE target_database TO appuser;

pg_hba.conf check: The host-based authentication file (/etc/postgresql/15/main/pg_hba.conf on Debian/Ubuntu) must have a matching rule for the user, database, and source address. A typical entry:

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

After editing pg_hba.conf, reload the configuration without restarting the server:

sudo systemctl reload postgresql

Or from within psql:

SELECT pg_reload_conf();

Common failure pattern: A user has CONNECT privilege at the SQL level but pg_hba.conf has no matching rule. The error message (FATAL: no pg_hba.conf entry for host) is explicit, but developers often overlook the file entirely because they expect database permissions to be managed solely through SQL.

Practical Decision Matrix

Use this checklist to select the right connection approach for your scenario:

• Interactive exploration on a local dev machine: Use c dbname inside psql. Fast, no new process.
• Shell script iterating over multiple databases: Use psql -U postgres -d $dbname -c "..." in a loop with -t -A for clean output.
• Application connecting to one database: Use a connection URI with sslmode=require and a connection pool (PgBouncer or built-in driver pooling).
• Application needing data from two databases: Implement postgres_fdw on the primary database rather than managing two separate connection pools in application code.
• Verifying RLS or privilege isolation: Use c dbname role_name to impersonate the target role without leaving psql.
• Automated provisioning / infrastructure-as-code: Use environment variables or .pgpass with a service account; never hardcode credentials in scripts.
• High-concurrency production workload: Deploy PgBouncer in transaction mode between the application and PostgreSQL. On a dedicated server, tune max_connections in postgresql.conf to match your hardware's memory capacity (each backend uses approximately 5–10 MB of RAM).
• Multi-tenant SaaS with per-tenant databases: Consider schema-based multi-tenancy within a single database instead of per-tenant databases, to avoid connection pool fragmentation and simplify backup strategies.

For teams running PostgreSQL alongside web applications, pairing the database server with a properly configured shared hosting or VPS environment and a registered domain for the application layer completes the standard production stack.

FAQ

Can I switch databases without closing my psql session?

Yes. Use the c target_database meta-command inside psql. It closes the current backend connection and opens a new one to the specified database, all within the same terminal session. You can optionally specify a different user, host, and port in the same command.

Why does PostgreSQL not have a USE command like MySQL?

PostgreSQL's architecture binds a backend process to a single database at startup. The database's system catalog is loaded into shared memory for that process, and switching catalogs mid-session is architecturally equivalent to starting a new process. The c command in psql is the practical equivalent — it just makes the process restart transparent to the user.

How do I query data from two different PostgreSQL databases simultaneously?

Use the postgres_fdw extension to create a foreign server and foreign tables that map to the remote database. After setup, you can JOIN local and remote tables in a single query. For one-off queries, dblink is simpler but less performant and harder to maintain.

What happens if I connect to template1 and modify it?

Any objects you create in template1 — tables, extensions, schemas — will be cloned into every new database created with CREATE DATABASE (unless TEMPLATE template0 is explicitly specified). This is sometimes intentional (e.g., pre-installing uuid-ossp or pgcrypto), but accidental modifications can corrupt all subsequently created databases.

How do I find which database a current psql session is connected to?

Run the following inside psql:

SELECT current_database();

Or check the psql prompt itself — by default it displays dbname=# (superuser) or dbname=> (regular user), where dbname is the active database.