How to List All Databases in PostgreSQL

PostgreSQL is a powerful, open-source relational database management system (RDBMS) that provides robust data storage, processing, and retrieval capabilities. It is designed to handle high volumes of data, emphasizing reliability, data integrity, and data security.

PostgreSQL operates as a client-server model where the client communicates with the PostgreSQL server to manage data. The PostgreSQL server processes incoming queries, executes them, and returns the result to the client. It uses a multi-version concurrency control (MVCC) system to manage multiple transactions simultaneously on the same data. Each transaction sees a snapshot of the data, and all transactions run concurrently without locking each other. MVCC provides excellent concurrency while maintaining data consistency, making PostgreSQL a superb choice for high-transaction environments.

PostgreSQL has robust indexing options that allow for efficient data retrieval. Indexes are data structures that help the database engine locate data quickly, essential for high-performance applications. PostgreSQL supports several indexes, including B-tree, hash, GiST, SP-GiST, GIN, and BRIN.

In this quick tutorial, we will quickly learn how to list all the databases in a PostgreSQL Server using simple commands.

Requirements

1. PostgreSQL Server
2. Permissions to View and List databases

Method 1 - PostgreSQL List Databases Using the PSQL Tool

psql is a command-line tool for managing and interacting with PostgreSQL databases. It allows users to execute SQL commands and queries, manage database objects such as tables, indexes, and views, and perform administrative tasks such as user management, backups, and restores.

psql provides robust features, including tab completion, syntax highlighting, and history management. It also supports customizing the output format of queries and importing and exporting data in various formats, such as CSV and JSON.

On Windows, launch the psql tool by searching SQL Shell from the start menu.

This should open the SQL Shell allowing you to connect to the PostgreSQL Server. You can press ENTER to accept the default values as shown in the prompt below:

Server [localhost]:
Database [postgres]:
Port [5432]:
Username [postgres]:
Password for user postgres:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

Note: Ensure to provide the password for the postgres user as defined during the installation.

Once logged in, you should find yourself in your PostgreSQL Shell ready to execute commands to the server.

To list of all databases currently on the server, including the database name, the owner, encoding, collation, ctype, and access privileges, run the command:

\l

The \l command tells psql to show all the databases on the server and the corresponding information. An example output is as shown:

   Name    |  Owner   | Encoding |          Collate           |           Ctype            | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+----------------------------+----------------------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | English_United States.1252 | English_United States.1252 |            | libc            |
 template0 | postgres | UTF8     | English_United States.1252 | English_United States.1252 |            | libc            | =c/postgres          +
           |          |          |                            |                            |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | English_United States.1252 | English_United States.1252 |            | libc            | =c/postgres          +
           |          |          |                            |                            |            |                 | postgres=CTc/postgres
(3 rows)

The above output shows all the available database names, owner, encoding, collation, and privileges.

We can also show additional information about a given database using the \l+ command.

\l+

Method 2 - PostgreSQL List Databases Using PLSQL Query

We can also use a SQL Query to list all the databases in a PostgreSQL Server as shown in the command below:

postgres=# select datname, encoding from pg_database;

The command should list all the database names and their respective encoding as shown in the example output below:

  datname  | encoding
-----------+----------
 postgres  |        6
 template1 |        6
 template0 |        6
(3 rows)

Method 3 - PostgreSQL List Databases Using GUI

You can also use the graphical interface for PostgreSQL to show all the databases in the server.

https://www.geekbits.io/how-to-install-postgresql-and-pgadmin-4-on-windows/

Start by logging into the server from pgAdmin.

Once logged in, expand the servers section -> Databases to view all the available databases.

Conclusion

In this post, you learned how to list all the available databases on a PostgreSQL Server using psql, SQL Queries, and pgAdmin.