Imagine you are preparing for a database migration or conducting a security audit—how do you quickly see every database running on your PostgreSQL server? For administrators, listing databases is not just routine; it’s critical when verifying environments before upgrades or checking compliance during audits. Whether you need an instant overview or want to automate reporting across dozens of servers, knowing how to use psql to list databases will save time and prevent mistakes. This guide will walk you through beginner-friendly commands up to advanced queries and automation techniques using psql, PostgreSQL’s trusted command-line tool.
What is psql in PostgreSQL?
The psql tool is PostgreSQL’s official command-line interface. It lets you connect directly to your database server so you can run SQL queries, manage users, check logs, and perform almost any administrative task. Why do so many professionals still rely on command-line tools like psql? They are fast, scriptable, and work well over remote connections—making them ideal for automation and CI/CD pipelines where GUIs fall short. If you manage PostgreSQL at scale or need repeatable processes in scripts or cron jobs, mastering psql is essential.
How to List Databases Using Postgres psql Command?
Listing databases with psql is quick and requires no deep SQL knowledge. First, connect to your PostgreSQL server from your terminal:
psql -U your_username -d postgres
If your server runs on another host or port, specify them like this:
psql -h your_host -p 5432 -U your_username -d postgres
Replace your_username and your_host with your actual credentials and hostname or IP address. Once connected and at the postgres=# prompt inside the shell, list all databases by typing:
\l
or
\list
Both commands show every database on the server along with columns such as Name, Owner, Encoding, Collate, Ctype, and Access privileges. Here’s what typical output looks like:
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
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 | =c/postgres +
| | | | | postgres=CTc/postgres
mydb | dbuser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
Need more details? Use:
\l+
or
\list+
The plus sign adds columns such as Size, Tablespace, and Description—vital information if you’re planning storage upgrades or tracking database comments.
Want to filter by name? Try:
\l mydb*
This lists only databases whose names start with "mydb". Filtering helps when managing servers with many databases.
For quick checks outside an interactive session—like inside shell scripts—run:
psql -U your_username -c '\l'
This prints the list then exits immediately. It’s perfect for automated health checks or inventory reports.
How to List Databases by Querying pg_database in psql?
Sometimes meta-commands aren’t enough—you might want custom views or need results formatted for other tools. PostgreSQL stores all database metadata in a system catalog called pg_database. You can query it directly using SQL after connecting via psql.
To see every database name:
SELECT datname FROM pg_database;
Want only user-created databases (excluding internal templates)? Use:
SELECT datname FROM pg_database WHERE datistemplate = false;
For deeper insights—including size (in human-readable format), connection settings, encoding, collation—run:
SELECT datname AS database_name,
pg_size_pretty(pg_database_size(datname)) AS size,
datallowconn AS allow_connections,
datconnlimit AS connection_limit,
encoding,
datcollate,
datctype
FROM pg_catalog.pg_database;
Need results sorted by size so you can spot large databases quickly? Add an order clause:
SELECT datname AS database_name,
pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_catalog.pg_database
ORDER BY pg_database_size(datname) DESC;
Why use direct SQL queries here? They’re flexible: filter out test databases (WHERE datname NOT LIKE 'test%'), join with other catalogs (like pg_stat_activity) to see active connections per database, or export results into external monitoring systems. This approach scales well when integrating with dashboards or alerting tools.
You can also run these queries from graphical clients like pgAdmin if that fits your workflow better—but remember that scripting with psql remains faster for batch jobs.
Automating Database Enumeration with Scripts
Automation saves time when managing many servers or running regular audits. With psql, exporting lists of databases becomes simple.
To write all database details into a CSV file suitable for spreadsheets:
psql -U your_username -c '\l+' -A -F',' > databases.csv
Here:
-A removes aligned formatting.
-F',' sets comma as the field separator.
Output goes straight into "databases.csv" for easy sharing.
Need just the names of user-created databases (for backup scripts)? Try this one-liner:
psql -U your_username -t -c "SELECT datname FROM pg_database WHERE datistemplate = false;"
The -t flag removes headers and footers so you get clean output—ideal for feeding into loops that back up each database automatically.
You can schedule these commands via cron jobs on Linux or Task Scheduler on Windows for regular reporting without manual effort. Automation ensures consistency across environments and reduces human error—a must-have for busy operations teams.
Introducing Vinchin Backup & Recovery: Enterprise-Level Protection for Your Databases
Beyond simply listing PostgreSQL databases, robust data protection should be part of every administrator's toolkit. Vinchin Backup & Recovery stands out as a professional enterprise-level solution supporting today’s mainstream platforms—including first-class support for PostgreSQL alongside Oracle, MySQL, SQL Server, MariaDB, PostgresPro, and MongoDB environments. With features such as incremental backup tailored specifically for PostgreSQL workloads; batch backup capabilities; multiple level data compression; comprehensive data retention policies including GFS retention; plus ransomware protection built-in across all supported platforms—Vinchin Backup & Recovery delivers reliability at scale while simplifying compliance management and disaster recovery planning.
Managing backups through its intuitive web console takes just four steps:
1. Go to Database Backup> Backup, and select the data source from the licensed database list.
2. Select the target node and storage for the job.
3. Set up desired backup strategies.
4. View settings and click Submit.
Join thousands of global enterprises who trust Vinchin Backup & Recovery—top-rated worldwide—to secure their mission-critical data! Start today risk-free with a full-featured 60-day free trial by clicking below.
Postgres psql List Databases FAQs
Q1: How can I list only user-created databases without templates?
A1: Run SELECT datname FROM pg_database WHERE datistemplate = false; in psql for a filtered list.
Q2: What should I do if I get an authentication error connecting with psql?
A2: Check pg_hba.conf settings; switch local method from peer to md5 then restart PostgreSQL service if needed.
Q3: How do I export all database names matching a pattern?
A3: Run \l pattern* inside psql shell or use SELECT datname FROM pg_database WHERE datname LIKE 'pattern%'; in SQL script mode.
Conclusion
Listing databases in PostgreSQL using psql meta-commands or custom SQL queries gives administrators both speed and flexibility—from quick checks to detailed reporting. For full protection of your environment beyond visibility alone, consider Vinchin backup solutions alongside these techniques so you can manage—and safeguard—your data confidently every day.
