How to List All Databases in PostgreSQL?

1. What is PostgreSQL?

In this guide, You will Learn to List All Databases in PostgreSQL; It is an open-source object-relational database management system (ORDBMS) that combines the robustness of SQL with advanced capabilities. Initially released in 1996, PostgreSQL has grown to be one of the most trusted databases used by major organizations worldwide. It is designed to handle complex data operations and extensive workloads, making it suitable for applications requiring high data integrity and robust concurrency control.

Features of PostgreSQL

• ACID Compliance: PostgreSQL follows ACID (Atomicity, Consistency, Isolation, Durability) principles, which makes sure of reliable transactions and maintains data accuracy even in cases of hardware failure or crashes.
• Extensibility: PostgreSQL is an extremely extensible system, and it allows users to extend the system with their own data types, operators, functions, and even access possibilities.
• MVCC (Multi-Version Concurrency Control): This feature allows concurrent data access by keeping track of multiple versions of data, ensuring seamless and efficient transaction processing.
• Advanced Data Types: PostgreSQL has many data types like JSON, arrays, XML, and custom types and this resourcefulness in data types is required by different application use cases.
• Cross-Platform Compatibility: It runs on all major operating systems, including Linux, Windows, and macOS, with support for replication and backup.

In PostgreSQL, It is important to learn how to view and manage databases, and knowing how to list all available databases is often the first step in familiarizing oneself with the database environment.

2. How to Login in PostgreSQL on Linux?

To List All Databases in PostgreSQL First, you have to access the PostgreSQL shell to begin managing databases in PostgreSQL. Various login methods are supported by PostgreSQL, and users are provided with a secure connection through system configuration, permissions, and user environment.

Commands for Login to PostgreSQL:

1. Login Using the PostgreSQL Command Line Interface (CLI)

The primary method for accessing PostgreSQL is through the command line interface, known as psql. Using this tool, users are not supposed to be on the GUI but are able to use the SQL commands, run the scripts, or manage the database environment directly from the terminal itself. psql is a very powerful command that gives you total control over handling database matters without any graphical interface.

To log into PostgreSQL, open your terminal and enter the following command:

To log in, enter the following command in your Linux terminal:

psql -U [username] -d [database]

• -U [username]:  Specifies the PostgreSQL username for login. Replace [username] with your actual PostgreSQL username (e.g., postgres for the default admin).
• -d [database]: Specifies the database name you want to connect to. Replace [database] with an accessible database name.

Replace [username] with your PostgreSQL username (often postgres for the default admin) and [database] with the name of an accessible database. After pressing Enter, PostgreSQL will prompt you for a password. Once authenticated, you’ll be in the PostgreSQL shell, where you can start executing commands to List All Databases in PostgreSQL.

2. Login Without Prompting for a Password

If you’re logging into the PostgreSQL database frequently, you may have a .pgpass file to make it an easier process. It stores your login credentials directly in this file so that reconnecting doesn’t require entering your password manually every time.

Steps to set up a .pgpass file:

In your home directory, create a .pgpass file.

nano ~/.pgpass

1. Add login credentials in the following format:

hostname:port:database:username:password

• hostname: IP address or hostname of your PostgreSQL server (e.g., localhost).
• port: Port on which PostgreSQL listens (default is 5432).
• database: Name of the database (use * to allow for all databases).
• username: PostgreSQL username.
• password: Password for the specified user.

2. Secure the file to prevent unauthorized access:

chmod 600 ~/.pgpass

Now if you try to log in without putting in your password, PostgreSQL will use this file to do automatic auth.

3. Basic Commands to List PostgreSQL Databases

Learning how to List All Databases in PostgreSQL is a must when working with PostgreSQL because of database management. To do so, we first log in to PostgreSQL, and some common commands can be used to see a list of databases. Database information is displayed each with varying detail to give you the command you need to suit your needs.

Commands to List All Databases in PostgreSQL

1. Using the \l Command

The \l command in PostgreSQL is one of the quickest methods ways to List All Databases in PostgreSQL. This command provides a structured output, displaying important details about each database in a table format. To use this command, enter:

\l

The output includes:

• Database name: The name of each database in the PostgreSQL instance.
• Owner: The person who created or owns the database.
• Encoding: Specifies the character encoding format for each database.
• Collation and Ctype: The collation and character type settings determine how Text is sorted and compared.
• Access privileges: Displays permissions associated with each database, showing who can connect, modify, or query each database.

This structured output allows administrators and developers to quickly check key database information, making it an efficient tool for managing database environments.

2. Using the \list Command

The \list command in PostgreSQL is an alternative to the \l command. It provides the same detailed overview of all databases within the PostgreSQL instance, displaying information in a structured table format. To execute this command, enter:

\list

The output includes:

• Database name: The name of each database in the PostgreSQL instance.
• Owner: The person who created or owns the database.
• Encoding: Specifies the character encoding format for each database.
• Collation and Ctype: The collation and character type settings determine how Text is sorted and compared.
• Access privileges: Displays permissions associated with each database, showing who can connect, modify, or query each database.

Both \l and \list commands provide a quick, organized way to view all databases, Easy on the eyes for administrators and users to understand the structure and permission in the PostgreSQL environment.

3. Querying the pg_database Table

For users who prefer more control over their queries, the pg_database table in PostgreSQL provides a customizable way to list database names. By querying this table, you can specify which columns to display, allowing you to retrieve only the information you need. This approach is very useful for database administrators who want detailed insights or tailored outputs.

Basic Query to List Database Names:

SELECT datname FROM pg_database;

This command returns a simple list of database names. To view additional information, extend the query to include more columns from the pg_database table.

Extended Query with More Details:

For more detailed information about each database, you can run an extended query that retrieves multiple attributes from the pg_database table.

 Use the following command:

SELECT datname, datdba, encoding, datcollate, datctype, datacl FROM pg_database;

This query includes details on the following:

• datdba: ID of the database owner.
• encoding: Character encoding format.
• datcollate and datctype: Collation and character type.
• datacl: Access control list, showing the privileges associated with each database.

These columns offer a comprehensive view of each database’s properties, making it useful for administrators who need to review or manage multiple attributes at once.

4. Using psql Options from the Command Line

You can also list databases directly from the command line without entering the PostgreSQL shell using the following command:

psql -U [username] -l

This command outputs the database list directly in the terminal, making it convenient for quick checks.

4. Using SQL Queries for Custom Listings

Advanced users often need to filter or customize how you List All Databases in PostgreSQL. PostgreSQL’s system catalogs, such as pg_catalog.pg_database, allow for highly flexible SQL queries tailored to your requirements.

Commands for Custom Listings

Basic Query Using pg_catalog.pg_database

To retrieve a simple list of databases with custom column names, run this query:

SELECT datname AS "Database Name" FROM pg_catalog.pg_database;

Detailed Query for Specific Database Properties

For more detailed information, use this query to retrieve database properties such as encoding, collation, and template status:

SELECT
    datname AS "Database Name",
    pg_catalog.pg_get_userbyid(datdba) AS "Owner",
    encoding AS "Encoding",
    datcollate AS "Collate",
    datctype AS "Ctype",
    datistemplate AS "Is Template",
    datallowconn AS "Allow Connections"
FROM pg_catalog.pg_database;

Excluding System and Template Databases

If you want to focus on user-created databases, excluding system and template databases, run this query:

SELECT datname FROM pg_catalog.pg_database WHERE NOT datistemplate;

This command is particularly useful in environments with multiple databases where administrators need a clean list of actively used databases.

5. Automating Database Listings with Scripts

Automating the process to List All Databases in PostgreSQL can be helpful for database administrators managing multiple servers. Using shell scripts and scheduling them with Cron can save time and make monitoring tasks easy.

Creating a Shell Script to List Databases

Create the Script File:
Open the terminal and create a shell script file:

nano list_databases.sh

1. Add Content to the Script:
   Inside the file, add the following code, which connects to PostgreSQL using environment variables and lists databases:

#!/bin/bash
psql -U $PGUSER -h $PGHOST -c "\l"

2. Make the Script Executable:
   Save the file, close the editor, and make the script executable:

chmod +x list_databases.sh

You can now run the script at any time by typing:

./list_databases.sh

3. Schedule the Script with Cron:
   To automate this process, add the script to your Cron schedule:

crontab -e

In the cron editor, add the following line to schedule the script daily at midnight:

0 0 * * * /path/to/list_databases.sh >> /path/to/database_list.log

This configuration will run the script daily and save the output to database_list.log.

6. Troubleshooting Common Issues in PostgreSQL

Sometimes, access or network errors may prevent users from listing databases. This section addresses common issues and solutions.

6.1 “Permission Denied” Error

If you’re getting a “Permission Denied” error, it usually indicates that the user lacks privileges. Resolve this by adjusting permissions as needed:

Verify User Permissions:
Check that the user has the CONNECT privilege:

GRANT CONNECT ON DATABASE dbname TO username;

1. Adjust Database-Level Privileges:
   If broader access is needed, grant all privileges:

GRANT ALL PRIVILEGES ON DATABASE dbname TO username;

6.2 Network and Connection Issues

1. Firewall Settings:
   Make sure your firewall allows traffic on PostgreSQL’s port (5432).
2. Remote Access Configuration:
   Edit

pg_hba.conf

To allow remote access and restart PostgreSQL.

7. Best Practices for Using PostgreSQL

To maintain a secure and efficient PostgreSQL environment, follow these best practices to List All Databases in PostgreSQL.

Security Tips for PostgreSQL Management

1. Limit Access to Credentials:
   Store credentials in protected files such as .pgpass with appropriate permissions.

2. Use Limited Privileges:
   Avoid using superuser privileges for daily tasks; create accounts with specific roles.
3. Audit User Access Regularly:
   Regularly review users and privileges, removing unnecessary access.
4. Update PostgreSQL:
   Keep your PostgreSQL installation up to date for security patches and performance improvements.
5. Monitor Access Logs:
   Regularly check PostgreSQL logs to detect unauthorized access attempts.

8. Conclusion

Knowing how to List All Databases in PostgreSQL is an important skill for managing and maintaining your database environment. This guide covered everything from basic listing commands to customized SQL queries and automation techniques, equipping you with multiple tools to handle database listings. With the best practices outlined here, you’re able to secure, efficiently, and organize the PostgreSQL environment for a range of tasks, large and small.

About the writer

Vinayak Baranwal wrote this article. Use the provided link to connect with Vinayak on LinkedIn for more insightful content or collaboration opportunities.