How to List Databases Using the psql command line tool

Introduction

PostgreSQL’s psql command-line tool offers a variety of built-in commands and SQL interfaces to interact with databases. In this tutorial, we will look at some basic and more advanced psql commands: \, \+, \du, and \db, as well as demonstrate how to retrieve detailed database information using the pg_catalog.pg_database system catalog.

Prerequisites

• PostgreSQL server installed and running.
• Access to a terminal or command-line interface.
• Appropriate permissions to view databases on the PostgreSQL server

Step 1: Accessing the psql Terminal

First, ensure that you have access to the PostgreSQL command line utility, psql. To start psql, open your terminal and enter:

psql -U username -d dbname

Replace username with your PostgreSQL role username and dbname with any database to which the user has access. If you’re accessing the default PostgreSQL database for initial setup, you can use postgres as the dbname.

Step 2: Listing Databases

Once inside the psql interface, listing all databases is straightforward. Use the following command:

\l

or

\list

These commands display a list of all databases along with their owners, encoding, collation configurations, and access privileges. Here is an example of what the output might look 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 | 

Understanding the Output

• Name: The name of the database.
• Owner: The role that owns the database.
• Encoding: Character encoding scheme used by the database.
• Collate: Collation order (i.e., how strings are sorted).
• Ctype: Character classification (e.g., letter, digit).
• Access privileges: Custom access privileges if any.

Enhanced List Display with ‘+’

The \+ command in psql is used in conjunction with other commands to provide an expanded display of the command’s output. It is particularly useful when you want more detailed information or when the default output is too compact or clipped.

Usage Example

To see more detailed information about databases, you can combine ‘\l’ with ‘+’:

\l+

This command displays additional details about each database, such as:

• Size: The physical disk space used by the database.
• Description: Any comments that have been added to the database.

Listing Roles and Permissions with ‘\du’

The \du command displays the list of roles in the PostgreSQL database along with their associated privileges.

Usage Example

Simply type the following in your psql session:

\du

The output will include columns like:

• Role name
• Attributes: Lists global attributes like login permissions, superuser status, role inheritances, etc.
• Member of: Other roles this role is a part of.

Listing Tablespace Information with ‘\db’

The \db command provides information about tablespaces, which are storage locations where PostgreSQL databases store their files.

Usage Example

Enter the following command in psql:

\db

This will display details about each tablespace, including:

• Name: The name of the tablespace.
• Owner: The PostgreSQL role that owns the tablespace.
• Location: The directory path of the tablespace on the disk.
• Size: The size of the tablespace.

Retrieving Detailed Database Information from ‘pg_catalog.pg_database’

PostgreSQL stores metadata about all databases in the system catalog pg_catalog.pg_database. Querying this catalog can yield comprehensive details about databases.

SQL Query Example

To retrieve detailed information about databases:

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;

Understanding the Output

• database_name: Name of the database.
• size: Human-readable format of the database size.
• allow_connections: Indicates if the database allows connections (true or false).
• connection_limit: The maximum number of concurrent connections allowed (-1 means no limit).
• encoding: Numeric encoding ID of the database.
• datcollate: Collation setting of the database.
• datctype: Character type setting of the database.

This query provides insights into the database’s configurations and settings.

Just point and click with Beekeeper Studio

Beekeeper Studio lists all databases in a dropdown at the top of the interface, switch between databases at any time.

PostgreSQL listing databases summary

In summary, to list databases in Postgres, you can use the \l, \list, or \l+ commands using the psql command-line interface, or you can query the pg_catalog.pg_database system catalog. These methods will provide you with a list of databases on the server, along with information about each database.

In upcoming tutorials, we will delve deeper into advanced psql functionalities and PostgreSQL administration techniques.