List PostgreSQL databases with psql -l (or the \l meta-command inside psql), a query against pg_database, or pgAdmin’s GUI. The \l form is the canonical psql answer. The team that uses SQL queries (SELECT datname FROM pg_database) has the same info from any client.
Table of contents
- The psql meta-commands
- The SQL query
- pgAdmin GUI
- Connecting to a specific database
- Listing databases from a non-superuser
- Listing tables in a database
- FAQ
The psql meta-commands
Connect with psql -U postgres then:
postgres=# \lShows Name, Owner, Encoding, Collate, Ctype, Access privileges. Add + for verbose:
postgres=# \l+The verbose form includes Size, Tablespace, and Description. Templates (template0, template1) are shown with \l+ but hidden with \l. The team that wants to see template DBs uses \l+.
The SQL query
For programmatic access:
SELECT datname FROM pg_database WHERE datistemplate = false;The datistemplate = false filter excludes template databases (those starting with template). The team that wants all databases including templates removes the filter.
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
WHERE datistemplate = false
ORDER BY pg_database_size(datname) DESC;The team that needs size info joins with pg_database_size().
pgAdmin GUI
Open pgAdmin -> Servers -> [your server] -> Databases. The tree shows all databases. Right-click for SQL query, properties, etc. The team that uses pgAdmin has a visual interface for less-frequent tasks but uses psql for routine work.
Connecting to a specific database
After listing, connect:
psql -U postgres -d mydb
# or from inside psql:
postgres=# \c mydbThe team that uses \c (connect) navigates between databases in the same session.
Listing databases from a non-superuser
A non-superuser only sees databases they have access to:
SELECT datname FROM pg_database WHERE datname = current_database()
OR has_database_privilege(datname, 'CONNECT');The team that has limited DB user accounts sees only their allowed databases.
Listing tables in a database
Once connected to a database:
mydb=# \dtShows tables in the current schema. The team that uses \dt schema.* filters by schema.
FAQ
How do I list databases from the command line?
psql -l lists databases without connecting. The team that uses this in scripts gets a quick inventory.
Why don’t I see template0 and template1 with \l?
Templates are hidden by default. Use \l+ to see them. The team that needs to inspect templates uses \l+.
Can I list databases from a different host?
psql -h hostname -U user -l. The team that uses pg_hba.conf authentication has remote access.
How do I list databases with their sizes?
SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;. The team that finds unexpectedly large DBs uses this to identify them.
What does ‘postgres’ database do?
It’s the default database, used for cluster-wide operations (creating users, listing all DBs). The team that uses postgres for application data is doing it wrong - use a separate database per app.
If you are sizing the infrastructure for the kind of project this post covers, the RunxBuild hosting calculator is the right place to model the line items. The compute, the memory, the storage, the bandwidth, the database - each one is a separate number, and the team’s mental model for the platform is the sum of those numbers. The RunxBuild dashboard is where the team sees the actual usage in one place.
Useful related references: