Kiến Thức Linux

How to List All Databases in PostgreSQL

Learn how to list all databases in PostgreSQL.

Introduction

One of the essential operations in PostgreSQL server management is listing the databases that currently exist on the server. There are three methods to view all PostgreSQL databases:

This tutorial shows you how to list databases in PostgreSQL using all three methods.

Learn how to list all databases in PostgreSQL.Learn how to list all databases in PostgreSQL.

Prerequisites:

  • Administrator privileges.
  • PostgreSQL installed and set up.

List PostgreSQL Databases Using psql

The psql CLI is a PostgreSQL frontend that allows users to interact with the server by issuing queries to PostgreSQL and displaying the results.

psql allows users to use meta-commands to perform routine tasks, such as connecting to a database, viewing all databases, etc. Meta-commands consist of a backslash symbol () followed by one or more letters.

To list all the databases on the server via the psql CLI in Windows, follow these steps:

1. Open the SQL Shell (psql) app.

Open the SQL Shell (psql) app.Open the SQL Shell (psql) app.

2. Connect to the server by providing the relevant information about your PostgreSQL installation. Alternatively, press Enter five times to use default values. The postgres prompt appears.

Connect to the database server using psql terminal.Connect to the database server using psql terminal.

Note: In Linux, use the terminal to switch to an authorized PostgreSQL user and execute the psql command to get the postgres prompt.

Step 3: Run the following command:

l

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

Output showing a list of all databases in PostgreSQL.Output showing a list of all databases in PostgreSQL.

Note: If you want additional information about size, tablespace, and database descriptions in the output, use l+.

List PostgreSQL Databases Using SELECT Statement

Another method to list databases in PostgreSQL is to query database names from the pg_database catalog via the SELECT statement. Follow these steps:

1. Log in to the PostgreSQL server.

2. Run the following query:

SELECT datname FROM pg_database;

psql queries the server and lists existing databases in the output.

List all databases in psql using the SELECT statement.List all databases in psql using the SELECT statement.

List PostgreSQL Databases Using Database Client

Database clients are applications that can connect to a database server and provide a convenient user interface for viewing and editing databases. The following section provides steps to view all databases on your PostgreSQL server using two popular database clients, pgAdmin and DBeaver.

pgAdmin

pgAdmin is the leading open-source GUI tool for managing PostgreSQL databases. Follow these steps to see all databases on the server using pgAdmin:

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

Open pgAdmin and connect to the database server.Open pgAdmin and connect to the database server.

2. Expand the Servers section in the menu on the left side of the screen.

3. Expand the Databases section. The tree now shows a list of all databases on the server. Click the Properties tab to see more information about each database.

Click the Properties tab to see more information about each databaseClick the Properties tab to see more information about each database

DBeaver

DBeaver is a cross-platform database manager that supports multiple database systems, such as PostgreSQL, MySQL, SQLite, Oracle, DB2, etc.

Note: Learn the difference between PostgreSQL and MySQL in our comparison article.

Follow the steps below to view your PostgreSQL databases using DBeaver:

1. Go to Database > New Database Connection.

2. Choose PostgreSQL from the list of available databases and select Next.

Selecting PostgreSQL in DBeaver.Selecting PostgreSQL in DBeaver.

3. Select the PostgreSQL tab at the top of the dialog window.

The location of the PostgreSQL tab in the Connect to a database dialog.The location of the PostgreSQL tab in the Connect to a database dialog.

4. Activate the Show all databases option.

Activating the Show all databases option in DBeaver.Activating the Show all databases option in DBeaver.

5. Return to the Main tab and provide credentials for the database in the Authentication section.

6. Select Finish when done.

Finalizing database connection.Finalizing database connection.

7. Expand the Databases item in the server tree to view all the databases on the server.

8. To access a database, double-click it in the menu.

Viewing databases in the server tree menu.Viewing databases in the server tree menu.

Conclusion

The guide provided the instructions for listing all databases on your PostgreSQL server. Choose pgAdmin or DBeaver for a GUI approach, or use psql to administer your database via the terminal.

Đăng ký liền tay Nhận Ngay Bài Mới

Subscribe ngay

Cám ơn bạn đã đăng ký !

Lỗi đăng ký !

Add Comment

Click here to post a comment

Đăng ký liền tay
Nhận Ngay Bài Mới

Subscribe ngay

Cám ơn bạn đã đăng ký !

Lỗi đăng ký !