Blogs

How to List All Databases in PostgreSQL | phoenixNAP KB

Posted on by admin

Introduction

One of the important tasks when managing PostgreSQL servers is to enumerate existing databases and their tables. There are three ways to

enumerate all databases:

  • Using metacommands
  • Running a query on a server
  • Through the pgAdmin GUI tool

.

This tutorial will show you how to enumerate all databases in PostgreSQL and inspect which tables a database contains.

Learn how to enumerate all databases in PostgreSQL.

Prerequisites:

PostgreSQL

    installed and configured

  • Administrator privileges

    • List of databases via psql Terminal The psql terminal is a PostgreSQL front end, allowing users to interact with the server by executing queries, issuing them

  • to PostgreSQL,

and showing the results.

psql allows users to use meta-commands, useful commands that begin with a backslash . Use these commands to perform routine tasks, such as connecting to a database, viewing all databases, and so on.

To enumerate all databases on the server through the psql terminal, follow these steps

: Step

1: Open the SQL Shell (psql) application

.

Step 2: Press ENTER four times to connect to the database server. Enter your password if prompted. If you did not set a password, press ENTER again to connect.

Step 3: Run the following command:

\l

The output displays a list of all databases currently on the server, including database name, owner, encoding, collation, ctype, and access privileges.

List of databases

using SQL

query

Another method of enumerating databases in PostgreSQL is to query database names from the pg_database catalog through the SELECT statement. Follow these steps

: Step

1: Log on to the server with the SQL Shell (psql) application.

Step 2: Run the following query:

SELECT datname FROM pg_database;

PSQL executes the query on the server and displays a list of existing databases in the output.

List of databases

through

pgAdmin The third method of viewing databases on the server is to use pgAdmin. pgAdmin

is the leading open source GUI tool for managing PostgreSQL databases

.

Follow these steps to view all databases on

the server using pgAdmin: Step

1: Open the pgAdmin application and enter your password to connect to the database server

.

Step 2: Expand the Servers tree, and then expand the Databases tree. The tree expands to display a list of all databases on the server. Click the Properties tab to view more information about each database.

Enumerate

tables

After you enumerate all the existing databases on the server, you can view the tables that a database contains. You can achieve this using psql or using pgAdmin.

View tables in psql

Step 1: While you’re logged in, connect to the database you want to inspect. The syntax is

: \c [database_name]

For example

: Step

2: List all

database tables by running: \dt

The output includes table names and their schema, type, and owner

. If there are no tables in

a database, the result indicates that no relationships were found. View tables

in pgAdmin: Step 1: After logging

in to pgAdmin

, expand the

Servers tree, expand Database Tree, and click the database that you want to inspect. Step 2: In the expanded database tree, click Schemas,

followed by Tables. The Properties tab displays a list of all tables, and they also appear in the Tables tree.

<img src="https://phoenixnap.com/kb/wp-content/uploads/2021/06/list-tables-in-pgadmin.png" alt="View all tables in

a database in pgAdmin.” />

Conclusion

The guide provides instructions for listing all databases and their tables on your PostgreSQL server. Choose pgAdmin for a GUI approach or use psql if you prefer to manage your database through a terminal.

For more tutorials on PostgreSQL, be sure to read our article and find out how to delete a PostgreSQL database.

admin