How to List All Databases in PostgreSQL

November 23, 2023

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:

This tutorial shows you how to list databases in PostgreSQL using all three methods.

Learn how to list all databases in PostgreSQL.

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.

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.

Connect to the database server using psql terminal.

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:

\l

The output shows a list of all databases currently on the server, including the database name, the owner, encoding, collation, ctype, and access privileges.

Output showing a list of all databases in PostgreSQL.

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 all databases in psql using the SELECT statement.

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.

Open pgAdmin and 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.

Click the Properties tab to see more information about each database

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.

Selecting PostgreSQL in DBeaver.

3. Select the PostgreSQL tab at the top of the dialog window.

The location of the PostgreSQL tab in the Connect to a database dialog.

4. Activate the Show all databases option.

Activating the Show all databases option in DBeaver.

5. Return to the Main tab and provide credentials for the database in the Authentication section.

6. Select Finish when done.

Finalizing database connection.

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.

Viewing databases in the server tree menu.

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.

Was this article helpful?
YesNo
Bosko Marijan
Having worked as an educator and content writer, combined with his lifelong passion for all things high-tech, Bosko strives to simplify intricate concepts and make them user-friendly. That has led him to technical writing at PhoenixNAP, where he continues his mission of spreading knowledge.
Next you should read
How to Check Your PostgreSQL Version
December 4, 2023

Use this guide to check your PostgreSQL version with a few straightforward commands. You can retrieve the PostgresSQL version directly from your command line or use an effective SQL statement within the PostgreSQL shell.
Read more
How to Export a PostgreSQL Table to CSV
March 17, 2020

Learn how to export a PostgreSQL table to a .csv file. This feature is especially helpful when transferring the table to a different system or importing it to another database application.
Read more
How to Install SQL Workbench for PostgreSQL
March 13, 2020

Save time and effort by managing different database systems with a single tool. Find out how to set up SQL Workbench to connect to a PostgreSQL database with four (4) easy steps.
Read more
How to Deploy PostgreSQL on Docker Container
November 23, 2023

PostgreSQL is the third most popular Docker image used for deploying containers. Run PostgreSQL on Docker by pulling the Postgres image from Docker's official repository and set up the database service for your application.
Read more