Introduction
One of the essential operations in PostgreSQL server management is listing the databases that currently exist on the server. There are three methods to view all PostgreSQL databases:
- Using the psql command-line interface.
- Querying the server with the SELECT statement.
- Finding the list in a database client.
This tutorial shows you how to list databases in PostgreSQL using all three methods.
Prerequisites:
- Administrator privileges.
- PostgreSQL installed and set up.
List PostgreSQL Databases Using psql
The psql CLI is a PostgreSQL frontend that allows users to interact with the server by issuing queries to PostgreSQL and displaying the results.
psql allows users to use meta-commands to perform routine tasks, such as connecting to a database, viewing all databases, etc. Meta-commands consist of a backslash symbol (\) followed by one or more letters.
To list all the databases on the server via the psql CLI in Windows, follow these steps:
1. Open the SQL Shell (psql) app.
2. Connect to the server by providing the relevant information about your PostgreSQL installation. Alternatively, press Enter five times to use default values. The postgres prompt appears.
Note: In Linux, use the terminal to switch to an authorized PostgreSQL user and execute the psql command to get the postgres prompt.
Step 3: Run the following command:
\lThe output shows a list of all databases currently on the server, including the database name, the owner, encoding, collation, ctype, and access privileges.
Note: If you want additional information about size, tablespace, and database descriptions in the output, use \l+.
List PostgreSQL Databases Using SELECT Statement
Another method to list databases in PostgreSQL is to query database names from the pg_database catalog via the SELECT statement. Follow these steps:
1. Log in to the PostgreSQL server.
2. Run the following query:
SELECT datname FROM pg_database;psql queries the server and lists existing databases in the output.
List PostgreSQL Databases Using Database Client
Database clients are applications that can connect to a database server and provide a convenient user interface for viewing and editing databases. The following section provides steps to view all databases on your PostgreSQL server using two popular database clients, pgAdmin and DBeaver.
pgAdmin
pgAdmin is the leading open-source GUI tool for managing PostgreSQL databases. Follow these steps to see all databases on the server using pgAdmin:
1. Open pgAdmin and enter your password to connect to the database server.
2. Expand the Servers section in the menu on the left side of the screen.
3. Expand the Databases section. The tree now shows a list of all databases on the server. Click the Properties tab to see more information about each database.
Note: If you are using Linux, see how to connect to a PostgreSQL database from a Linux command line.
DBeaver
DBeaver is a cross-platform database manager that supports multiple database systems, such as PostgreSQL, MySQL, SQLite, Oracle, DB2, etc.
Note: Learn the difference between PostgreSQL and MySQL in our comparison article.
Follow the steps below to view your PostgreSQL databases using DBeaver:
1. Go to Database > New Database Connection.
2. Choose PostgreSQL from the list of available databases and select Next.
3. Select the PostgreSQL tab at the top of the dialog window.
4. Activate the Show all databases option.
5. Return to the Main tab and provide credentials for the database in the Authentication section.
6. Select Finish when done.
7. Expand the Databases item in the server tree to view all the databases on the server.
8. To access a database, double-click it in the menu.
Note: For more tutorials on PostgreSQL, check out our guides:
- Overview of Different Data Types in PostgreSQL
- How to Create a Postgres User
- How to Delete a Postgres User (Drop User)
- PostgreSQL Drop Database with Examples {2 Methods}
- Postgres Create Database {3 Different Methods}
- How to Check Your PostgreSQL Version
- How to Connect to a PostgreSQL Database From Command Line in Linux
Conclusion
The guide provided the instructions for listing all databases on your PostgreSQL server. Choose pgAdmin or DBeaver for a GUI approach, or use psql to administer your database via the terminal.
