psql list databases is the meta-command \l (or its long form \list), which shows a table of all the databases on the current server, with the owner, the encoding, the collation, the ctype, and the access privileges for each. The command is the workhorse of any psql session — a developer who has just connected to a new server runs \l first to see what databases exist. The interesting part is the three forms (\l, \l+, the SQL query), the four flags (the pattern, the verbose mode, the headers, the script mode), and the five gotchas (the template databases, the permissions, the encoding mismatch, the connection, the size on disk) that quietly break a list that was supposed to be simple.
The reason “psql list databases” is its own question and not just “postgresql show databases” is that the \l command is the entry point to every psql session, and the \l command is the one that surfaces every Postgres gotcha. A developer who has just connected to a managed Postgres service and runs \l is a developer who is going to see the template databases (template0, template1), the default database (postgres), and the user’s databases — and the developer is going to need to know which is which.
Table of contents
- The short version
- The three commands that list databases
- The four flags that shape the output
- The five databases every Postgres server starts with
- The five gotchas that quietly break a list
- The five patterns a developer uses in real work
- FAQ
The short version
The \l meta-command lists the databases on the current server. The output is a table with the database name, the owner, the encoding, the collation, the ctype, and the access privileges. The \l+ variant adds the size on disk, the tablespace, and a description. The SQL query SELECT datname FROM pg_database WHERE datistemplate = false; is the programmatic form, the form a script uses, and the form that filters out the template databases. The three forms cover 99% of real use cases.
The three commands that list databases
A short, opinionated list of the three commands that list databases. The three are not interchangeable, and the developer should know which one they want before they run the command.
\l — the meta-command. The shortest and most common form. The output is a table of databases with the name, owner, encoding, collation, ctype, and access privileges. The form is the right answer for a developer who is exploring a new server, and the form is the right answer for a developer who wants a quick view.
\l+ — the verbose meta-command. The longer form that adds the size on disk, the tablespace, and a description column. The form is the right answer for a developer who wants to see how big each database is, and the form is the right answer for a developer who is debugging a disk space issue.
SELECT datname FROM pg_database WHERE datistemplate = false; — the SQL query. The programmatic form. The output is a list of database names, one per row. The form is the right answer for a script that needs to iterate over the databases, and the form is the right answer for a developer who wants to filter out the template databases (template0, template1).
The three commands are the floor. There is also \\dn (list schemas), \\dt (list tables), and \\du (list users), but the three are the ones the developer reaches for first. The three are the ones the developer should know.
The four flags that shape the output
The \l and \l+ commands accept a small set of flags that change the output. The flags are the same as the psql connection parameters, and the flags are the lever for the developer’s “I want a list that looks like this” use case.
\l <pattern>. A pattern that filters the database names. The pattern is a SQL LIKE pattern (\l app% matches databases starting with app). The flag is the right answer for a developer who is looking for a specific database and does not want to scroll through the full list.
\l+ (verbose). Adds the size on disk, the tablespace, and the description. The flag is the right answer for a developer who wants the full output, and the flag is the right answer for a developer who is debugging a disk space issue.
\l (with -E or --echo-hidden). Shows the SQL query that psql is running behind the scenes. The flag is the right answer for a developer who wants to learn the SQL, and the flag is the right answer for a developer who wants to use the SQL in a script.
psql --list (command-line). Lists the databases and exits. The flag is the right answer for a developer who wants to use psql as a one-off command in a shell script, and the flag is the right answer for a developer who does not want to enter the interactive REPL.
The four flags are the floor. The developer can also use \x (expanded display, for wide tables) and \pset (to change the output format), but the four are the ones the developer should know first.
The five databases every Postgres server starts with
A short, opinionated list of the databases every Postgres server starts with. The five are the ones the developer is going to see in the \l output, and the five are the ones the developer should know.
postgres. The default database. The database is created by the Postgres installer, and the database is the one the postgres user connects to by default. The database is the right answer for a developer who is just exploring the server, and the database is the one the developer should not use for application data.
template0. The first template database. The database is read-only, the database is the cleanest possible Postgres database, and the database is the one CREATE DATABASE ... TEMPLATE template0 uses to create a database without any extensions or data. The database should not be modified, and the database should not be connected to for application use.
template1. The second template database. The database is the default template for new databases, and the database is the one the developer can modify to add extensions or settings that should be present in every new database. The database is the right answer for a developer who wants to standardize on a set of extensions (e.g. pgcrypto, uuid-ossp).
Application databases. The databases the developer creates for the application. The names are usually something like app_production, app_staging, app_development, or myapp. The databases are the ones the application connects to, and the databases are the ones the developer cares about.
Test databases (sometimes). Some teams create a separate database for tests (e.g. app_test, app_ci). The databases are usually created by the test setup script and dropped after the test run, and the databases are usually owned by the CI user, not the application user.
The five categories are the floor. There is also the azure_maintenance database on Azure (used by the Azure Postgres service for internal operations), the azure_sys database on Citus, and the cloudsqladmin database on Google Cloud SQL. The five are the ones the developer should know first.
The five gotchas that quietly break a list
A short, opinionated list of gotchas that have actually broken real \l commands. None of them are dramatic. They are the boring ones.
The template databases clutter the list. A \l command on a fresh server returns postgres, template0, template1, and any application databases. The template databases are usually noise — the developer is looking for the application databases. The fix is the SQL query SELECT datname FROM pg_database WHERE datistemplate = false;, which filters out the templates.
The user does not have permission to see all databases. A \l command by a non-superuser returns only the databases the user has CONNECT permission on. The user may not see databases owned by other users, even if the user knows they exist. The fix is to connect as a superuser (postgres) to see all databases, or to grant the user CONNECT permission on the specific databases the user needs.
The encoding is different from what the client expects. A \l command on a server with LATIN1 encoding returns the database names as Latin1, which the client may render as garbage. The fix is to set the client’s encoding to match the server’s (PGCLIENTENCODING=LATIN1), or to set the server’s encoding to UTF8 (which is the modern default).
The list is empty. A \l command on a server that the developer has just connected to, with no application databases, returns only the template databases. The fix is to create the application database (CREATE DATABASE app_production;), and to verify with \l again.
The size on disk is huge. A \l+ command on a server with a large database returns a size that surprises the developer. The size is the total disk space used by the database, including the WAL files, the indexes, the toast tables, and the dead tuples. The fix is to run VACUUM to reclaim the dead tuples, and to check the indexes for bloat.
The five patterns a developer uses in real work
A short, opinionated list of the patterns a developer uses in real work. The patterns are not the only ones, but they are the ones the developer will see most often.
The “I just connected to a new server, what is here?” pattern. A developer has just connected to a new server, and the developer wants to know what databases are on the server. The pattern is \l (the meta-command) or \l+ (the verbose form). The pattern is the right answer for a developer who is exploring, and the pattern is the one the developer should use first.
The “I want to filter out the template databases” pattern. A developer wants to see only the application databases, not the template databases. The pattern is SELECT datname FROM pg_database WHERE datistemplate = false; (the SQL query). The pattern is the right answer for a script that needs to iterate over the databases, and the pattern is the right answer for a developer who wants a clean list.
The “I want to see the size of each database” pattern. A developer wants to know which database is using the most disk space. The pattern is \l+ (the verbose form) or the SQL query SELECT datname, pg_database_size(datname) FROM pg_database;. The pattern is the right answer for a developer who is debugging a disk space issue, and the pattern is the right answer for a developer who is planning a backup.
The “I want to list databases in a shell script” pattern. A developer wants to list the databases in a shell script, and pipe the result to for db in $(psql -l -t | cut -d \| -f 1); do ...; done. The pattern is the right answer for a script that needs to perform an action on every database (a backup, a vacuum, a migration), and the pattern is the right answer for a developer who wants to automate the workflow.
The “I want to list databases in a Python script” pattern. A developer wants to list the databases in a Python script, and use the psycopg2 (or asyncpg) library to query pg_database. The pattern is the right answer for an application that needs to discover the databases at runtime, and the pattern is the right answer for a developer who wants to build a multi-database admin tool.
The five patterns are the floor. There is also \\dn+ (list schemas with verbose), \\dt *.* (list tables in all schemas), and pg_ls_dir (list files in a directory), but the five are the ones the developer should learn first.
How this fits the rest of the stack
A database list rarely lives in isolation. The list is usually the entry point to a workflow (a backup, a migration, a debug) that involves the database, the application, and the platform. The platform that handles the database should make the rest of the stack feel like part of the same conversation.
The services layer is the part of the platform that runs the long-lived API the database serves. The database layer is the part that holds the data the API reads and writes. The static layer is the part that hosts the dashboard the developer uses to manage the database. The environment variables are the part that holds the connection string the API uses to connect.
A database on a platform where the service, the database, the storage, and the secrets are all in the same place is a database the team is going to be able to operate. A database on a platform where each piece is in a different console is a database the team is going to spend the first hour just opening the right tab.
For a team that wants to see the full cost of the project before it commits, the RunxBuild hosting calculator shows the line items together. The API, the database, the storage, the worker, the bandwidth — each one is a separate number, and the team’s mental model for the platform is the sum of those numbers.
FAQ
How do I list databases in psql?
Use the meta-command \l (or its long form \list). The output is a table of databases on the current server, with the owner, encoding, collation, ctype, and access privileges. The command is the right answer for a developer who is exploring a new server.
How do I list databases with their size?
Use the verbose meta-command \l+. The output adds the size on disk, the tablespace, and a description column. The command is the right answer for a developer who wants to see how big each database is.
How do I filter out the template databases?
Use the SQL query SELECT datname FROM pg_database WHERE datistemplate = false;. The output is a list of database names, excluding template0 and template1. The query is the right answer for a script that needs to iterate over the application databases.
Can I list databases without entering the psql REPL?
Yes. Use psql -l (or psql --list) to list the databases and exit. The output is the same as the \l meta-command, but the command is non-interactive. The command is the right answer for a shell script that needs to list the databases.
Why are some databases missing from the list?
The most common cause is permissions. A non-superuser only sees the databases the user has CONNECT permission on. The fix is to connect as a superuser (postgres) to see all databases, or to grant the user CONNECT permission on the specific databases the user needs.
How do I create a new database?
Use CREATE DATABASE <name>; in psql, or createdb <name> from the shell. The new database is created with the template1 database as the template, and the new database is owned by the user that ran the command. The pattern is the right answer for a developer who needs a new database for a new project or environment.
How do I drop a database?
Use DROP DATABASE <name>; in psql, or dropdb <name> from the shell. The database is dropped, and all the data in the database is gone. The command cannot be undone, and the command cannot be run inside a transaction. The pattern is the right answer for a developer who needs to remove a database, and the pattern is the right answer for a developer who is cleaning up after a test.