How to List All Databases in PostgreSQL (psql and More)

Before creating a database, it’s important to verify that the name you want to use doesn’t already exist. You don’t want to create a database and then get a “database already exists” error.

Listing all of your databases can help you determine whether to move forward with your preferred name or choose a different one. This is just one of the many reasons why you’d want to list your databases. 

Whatever reason you may have, this guide will explore six methods you can use to list all of your databases in PostgreSQL.

Prerequisites

As with most processes, there are several requirements you must meet:

• You’ve installed and set up PostgreSQL on your system.
• Your user account has the necessary permissions to view databases. 
• You understand how tools like psql, pgAdmin, and DBeaver work. If you don’t, here are links to introductory guides for psql, pgAdmin, and DBeaver.

1. Using the psql Command-Line Interface

This method leverages the "\l" meta-command. 

1. Launch the PostgreSQL application and connect using the following command: "psql -U your_username." By default, this connects to the "postgres" database. If you want to connect to a specific database other than the default, use the "-d" flag to specify the database. name: psql -U your_username -d postgres
2. Enter your password when prompted. After successful authentication, you’ll receive the “postgres=#” prompt. 
3. Use the "\l" command.

If you want additional information on the description, tablespace, or size, you can use the "\l+" command. 

2. Using SQL Queries

This method leverages the "SELECT" statement to query database names from "pg_database." It allows you to specify which databases to retrieve and which ones to leave out.

From the PostgreSQL environment, you can use the below "SELECT" statement to list all databases:

SELECT datname FROM pg_database WHERE datistemplate = false;

This query excludes template databases (datistemplate = false) used for internal purposes but lists user-created databases. It does not exclude other databases that may have configurations similar to templates but are not flagged as "datistemplate." Therefore, if you have custom databases that mimic templates but don't have the "datistemplate" attribute set to "true," they will still appear in the result.

3. Listing Databases via pgAdmin

Once you log in to your PostgreSQL server on pgAdmin, here’s how to go about this method:

1. On the left-hand pane of your dashboard, find the “Servers” node and expand it by clicking on the arrow next to it.
2. Expand the “Databases” node under your server. This will list all of the databases in PostgreSQL. 
3. Click on the properties tab to view the databases’ details, such as the owner, connection status, and comments (if any). Note: The properties tab does not display details for all databases simultaneously. You will need to select each database individually to view its details. 

4. Using 3rd-Party Tools

If you want advanced features beyond just listing databases, you can leverage third-party tools like DBeaver and DataGrip. These tools provide features such as advanced data visualization, cross-platform compatibility, data analysis, and entity relationship (ER) diagrams. 

Here’s a step-by-step guide for listing all databases in PostgreSQL using DBeaver:

1. On the DBeaver dashboard, navigate to the “Database Navigator” pane and select “New Database Connection.” 
2. Select "PostgreSQL" and click “Next.”
3. In the new dialog window, select the “PostgreSQL” tab at the top of the dashboard. 
4. Toggle the “Show all databases” setting. This step is tool-specific and may not apply to all versions of DBeaver. It applies only to configurations in which database visibility is restricted by default.
5. Select the “Main” tab and enter your server details, such as the host, port, username, and password. 
6. Click “Finish.” 

5. Automating Database Listing With Scripts

Scripts in Bash or Python let you programmatically list databases and incorporate this functionality into larger workflows or scheduled tasks. 

For our example, we’ll focus on a sample Python script that uses the "psycopg2" library:

import psycopg2

# Establish connection to the PostgreSQL server

conn = psycopg2.connect(
  dbname='postgres', 
  user='yourusername',
  password='yourpassword',
  host='localhost'
) 
cursor = conn.cursor()

# Execute SQL query to list databases

cursor.execute("SELECT datname FROM pg_database WHERE datistemplate = false;")

# Print each database name

for db in cursor.fetchall():

    print(db[0])

# Close the connection

cursor.close() 
 conn.close()  

The script above will retrieve all non-template databases from "pg_database."

The connection above assumes that the database name is "postgres." You should replace "postgres" with the name of an accessible database.

6. Using Docker and PostgreSQL Containers

If you work with containerized environments in which PostgreSQL runs inside a Docker container, you can still list all of your databases.

First, run psql using the following command:

docker exec -it 
  
    psql -U 
   
  

Next, use the "\l" command to list all databases, just like in a standard PostgreSQL setup.

Common Issues and Tips

You may experience several issues when trying to list databases in PostgreSQL. For instance, if you receive a “permission denied” error, this means you don’t have the required privileges to perform that activity. 

To remedy this, you can edit the "pg_hba.conf" file, which controls the client authentication settings. However, this file is critical for database security. Changing it without careful consideration could expose your database to unauthorized access. Resort to editing it only if your basic role and privilege management doesn't resolve the issue, and ensure you follow security best practices.

Ensure you’ve correctly configured the authentication method (such as md5, scram-sha-256, or trust) for your network or IP range. Then, restart PostgreSQL to apply any changes.

If you experience connection issues, there are several things you can do, including:

• Verify that the PostgreSQL service is running using the "systemctl status postgresql" command. 
• Ensure you’ve configured PostgreSQL to listen for the correct IP addresses in the "postgresql.conf" file (listen_addresses parameter).

How Delinea Simplifies Secure PostgreSQL Database Management

The best method for listing your databases will depend on your preferences and experience.

With Delinea, you can simplify your database management even more. This solution gives you:

• Centralized control: You don’t have to switch between multiple tools or interfaces. Delinea provides a single unified platform so you can view all of your databases without the added complexities.
• Simplified auditing: Delinea logs every query, action, and interaction with your databases, which makes compliance and security audits seamless.
• Role-based access control (RBAC): You can assign precise permissions using role-based access control so that users can access only what they need. This minimizes the risk of unauthorized access and adheres to the principle of least privilege.
• Integration with monitoring tools: Delinea effortlessly integrates with your existing monitoring and logging tools so you don’t have to disrupt your current workflows.

Sign up for a free trial today to see Delinea in action.