How to List All PostgreSQL Databases on Linux?

1. What is PostgreSQL?

Commonly known as ‘Postgres,’ PostgreSQL is a free and open-source 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 really shines; you can create custom data types, custom operators, and custom functions that all ensure you’re perfectly satisfied with your database.
• MVCC (Multi-Version Concurrency Control): A feature that tracks multiple versions of data to provide concurrent data access for easy flow and quick transaction processing.
• Advanced Data Types: There are many data types with PostgreSQL, including JSON, arrays, XML, and custom types, and PostgreSQL is quite flexible for various types of applications.
• 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?

Before you start managing databases of PostgreSQL, you need to access the PostgreSQL shell. There are different login methods available for PostgreSQL to secure users’ login based on system configuration, permission, 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. With this tool, users can even execute the SQL commands, run the script, and also manage the database environment from the terminal. While the psql command power is unquestionable, keeping the database executing with absolute control to interact without using a graphical interface is a very good idea.

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.

2. Login Without Prompting for a Password

If you frequently access the database, you can add your user and password information into a .pgpass file, which will add a layer of security and make the database login process a lot easier. Having a line like this file will securely store your login credentials so you can connect without manually entering your password each time.

Steps to set up a .pgpass file:

To Set the .pgpass file in your home directory:

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, PostgreSQL will use this file for automatic authentication, allowing you to log in without entering your password manually.

3. Basic Commands to List PostgreSQL Databases

When you log into PostgreSQL, some commands to view the list of databases are available. Various commands show you different levels of information about the database each command displays the information you require in detail.

Commands to List Databases

1. Using the \l Command

The \l command is one of the quickest methods to view a list of databases. 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 one who created or owned 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 one who created orowneds 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.

The \l and \list commands allow users and administrators of a Postgresql environment to get a quick and organized view of all databases and see what structure they have and what access permissions are granted.

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. With this table, you can query it to display which columns you want; you don’t have to bring back whatever the column is defined to be, only the information that you need. However, for those database administrators looking for detailed insights or well-tailored outputs, this approach is very handy.

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: A database that shows the privileges for each of its databases.

These columns provide a broad view of eacdatabase’s’s characteristics and are helpful to administrators looking to check or modify various 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 database lists based on specific criteria, such as pg_catalog.pg_database, which allows 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

Listing databases can be automated by a database administrator servicing multiple servers. Monitoring tasks can be assigned and scheduled with shell scripts by the Cron daemon.

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

Permission Denied error 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 database environment, follow these best practices.

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 database installation up to date for security patches and performance improvements.
5. Monitor Access Logs:
   Regularly check database logs to detect unauthorized access attempts.

8. Conclusion

One of the important skills in managing and maintaining your PostgreSQL database environment is to list PostgreSQL databases. In this guide you will learn from basic listing commands to customized SQL queries and automation techniques to provide you with multiple methods to handle database listings better. If you follow this approach, we can ensure that your PostgreSQL environment stays secure, efficient, and organized with good practices for everyday work and long-term goals.

About the writer

This article was written by Vinayak Baranwal, For more insightful content or collaboration opportunities, feel free to connect with Vinayak on LinkedIn through the provided link.