A single PostgreSQL server can contain many databases, and there are several ways to list them all. Explore three different approaches to get the list of databases in a PostgreSQL server.

When working with PostgreSQL, developers, and DBAs are certainly interested in listing the databases available on your server. This is one of the most common operations performed by developers working on PostgreSQL. There are several ways to achieve that, depending on preferences, requirements, and experience.

In this article, you will find out more about three different approaches to showing PostgreSQL databases:

• With a command-line command

• With a query

• In a database client

Let’s dig into PostgreSQL database listing!

Listing Databases With the psql Command-Line Tool

psql allows you to interact with a PostgreSQL server via the command line. “psql” stands for "PostgreSQL interactive terminal" and allows you to:

• Create databases, tables, and users.

• Run queries on a Postgres database.

• Launch commands to get metadata about your databases.

In detail, psql provides the \l command to get the list of databases in a PostgreSQL server. Follow the steps below to learn how to use psql to list databases in the terminal:

Step 1. Connect to the PostgreSQL database server with psql command-line tool:

$ psql -U username

• Replace username with the username of the PostgreSQL user you want to log in with. psql will ask you to enter the password associated with the user. Type it and press Enter to connect to the database.

• Keep in mind that users can observe executed commands by observing the history of commands in the CLI. This will allow them to see the password you typed. If you want to prevent entering the password for security reasons, you can:

• Set the password in the PGPASSWORD environment variable

• Add the password to the .pgpass file

Step 2. List the databases with:

$ \l

["This will return a table containing the databases of the PostgreSQL server in the format below:"]

The result of the "\l" command

Note that \l is the shortened version of the \list command. So, \list will return the same result.

Use \l+ or \list+ to get additional information about each database, such as the size in Kb and its description:

The result of the "\l+" command. Note that the table now involves more columns

Listing PostgreSQL Databases With a Query

If the command line is not your thing, keep in mind that you can also list databases in Postgres by querying pg_catalog.pg_database. Specifically, that Postgres catalog contains a row for each database in the server.

Retrieve the list of databases in PostgreSQL with the query below:

SELECT * FROM pg_catalog.pg_database

Executing the query in DbVisualizer

The datname column stores the name of each database. Note that this table also includes the database templates used by PostgreSQL to initialize a new database when a CREATE DATABASE query is launched. Filter them out with the query below:

SELECT * FROM pg_catalog.pg_database
WHERE datistemplate = false;

Note that the template databases are no longer part of the resulting table

View Databases in a Postgres Client

The last and easiest way to show all databases in PostgreSQL is through a database client, such as DbVisualizer. This powerful tool allows you to visually connect and manage your Postgres databases.

In detail, DbVisualizer is a PostgreSQL client with extended support for pgSQL specific object types and features. All you have to do to view the databases available in a PostgreSQL server in DbVisualizer is:

• Connect to your PostgreSQL server as explained in the official doc.

• Select your target server in the “Connections” menu on the left, right-click on it, and choose “Connect.”

Connecting to a new database in DbVisualizer

• Open the “Databases” dropdown to see the list of databases.

The list of databases available in DbVisualizer

Fantastic! DbVisualizer allows you to view all PostgreSQL databases with just a couple of clicks.

Conclusion

In this article, you understood that listing databases in PostgreSQL is a simple task, and there are at least three methods to achieve that. The first involves a command-line command, the second a query, and the third a GUI tool. In particular, a database client with full support for PostgreSQL such as DbVisualizer allows you to visually deal with databases and provide several advanced features, such as query optimization. Download DbVisualizer for free!

FAQ

How to list PostgreSQL databases with a single command?

You can get the list of databases available in a Postgres server with a single command with:

$ psql -U -l

Replace with an actual username. This command will connect to the PostgreSQL server and then directly launch the \l command to get the list of databases.

How to get the list of tables in a database with psql?

If you want to get the list of tables contained in a specific database, first connect to a database in psql with:

$ \c

Then, use the \dt command:

$ \dt

This will return all the tables in the selected database.

What is the easiest way to list databases in PostgreSQL?

The easiest way to show databases in PostgreSQL is through a database client. This is because a PostgreSQL client gives you the ability to see the databases available on the server in a user-friendly interface. Such an approach does not involve queries or commands in the terminal and makes it a perfect solution for both experienced and non-experienced users.

How to use pgAdmin to view the list of databases in PostgreSQL?

To use pgAdmin to view a list of databases in PostgreSQL, open pgAdmin and connect to your PostgreSQL server. Then, expand the Servers group in the left panel to see a list of servers. Click on the server that you want to list the databases for, and then expand the Databases group to see a list of databases.

Are there any security concerns when listing databases in PostgreSQL?

If the permissions on the PostgreSQL server have been not set correctly, a user can see databases they should not have access to. To mitigate this risk, it is important to assign the right groups and limit the use of superuser accounts to only when necessary.

About the author

Antonello Zanini is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.