4 Ways to List Databases and Tables in PostgreSQL

list databases

Whether you’re just starting out or have been using it for a while, finding your way around PostgreSQL databases can often be challenging, especially in large, multi-database environments.

Listing available databases is a frequent action that every user, regardless of their skill, should know. While it seems mundane, it is essential because you should find the right database before executing further commands.

Executing commands (especially critical commands such as dropping a table) on the wrong database can have long-term disastrous consequences for an organization and its data storage architecture.

In this article, we will discuss several ways to list all PostgreSQL databases on a system. We’ll cover quick command-line options and more user-friendly GUI tools.

Table  Of Contents

  1. How to List Databases and Tables in PostgreSQL
    1. The Prerequisites
    2. Method #1: List PostgreSQL Databases Using psql
    3. Method #2: List PostgreSQL Databases with database Query
    4. Method #3: List PostgreSQL Databases With pgAdmin
    5. Method #4: List PostgreSQL Databases With DBeaver
  2. Conclusion
  3. FAQs

How to List Databases and Tables in PostgreSQL

Before going into the details of listing databases and tables in PostgreSQL, let’s see the prerequisites for the process:

The Prerequisites

Before trying the methods mentioned in this tutorial, make sure you have the following:

  • PostgreSQL is installed on your system.
  • Your user account has the necessary permissions to access and manage the databases.
  • Familiarity with basic SQL commands and PostgreSQL’s command-line interface.

Method #1: List PostgreSQL Databases Using psql

The psql is a frontend tool for PostgreSQL, enabling users to interact with the server through a command line. You can run commands and execute queries on the PostgreSQL database and see the results in the terminal.

You can perform regular maintenance tasks like connecting to a database or viewing all databases through meta-commands. These special commands start with a backslash symbol (\) followed by the command syntax.

For this method, we’ll run the psql CLI in Windows.

Step #1: Launch the psql Command-line Interface.

using psql

Step #2: Connect to the PostgreSQL Server

Connect to the server by entering the necessary details of your PostgreSQL credentials. Alternatively, press Enter five times to opt for the default settings. Once connected, you’ll see the Postgres prompt (postgres=#) .

connect postgresql

Important: On Linux, launch the terminal and switch to a Postgres user (user with PostgreSQL database privileges) with the psql command. This will bring up the postgres prompt (postgres=#).

# psql

Step #3: Run the list Command

Run the following PostgreSQL meta-command:

postgres=# \l

The output of this meta-command will include detailed information on all databases present on the server. This list will include details such as database name, owner, encoding information, collation, Ctype, as well as the access privileges for the database.

PostgreSQL meta-command

You can use the \l+ command to get more details in the output, like the size of the database, tablespace, and descriptions.

Method #2: List PostgreSQL Databases with database Query

An alternative way to view databases in PostgreSQL involves querying the database names from the pg_database catalog using the SELECT statement.

The process has the following steps.

Step #1: Access the PostgreSQL Server

Launch the terminal or the command-line interface for your system and access the psql shell.

Step #2: Execute the Query

In the shell, run the following statement:

postgre=# SELECT datname FROM pg_database;

The psql sends a request to the server and displays a list of the existing databases in the output.

list existing databases

List PostgreSQL Databases Using a Database Client

Database clients are GUI tools designed to connect to a database server, offering an easy-to-use interface for viewing and managing databases. We’ll now outline how to see PostgreSQL server databases using two widely used database clients: pgAdmin and DBeaver.

Method #3: List PostgreSQL Databases With pgAdmin

pgAdmin, a top open-source graphical interface tool for PostgreSQL database management, makes it easy to view all server databases. Here’s how you can do this.

Step #1: Log in to the PostgreSQL Server

Open pgAdmin and enter the password to connect to your PostgreSQL server.

open pgadmin

Step #2: View the Databases

In the left-side menu, click Servers to expand the section. To list the databases on the server, access the Databases section.

You can now see a hierarchical tree view showing a list of Postgres databases on the server. You can get further information about a database by clicking the Properties tab.

pgadmin view DB

Method #4: List PostgreSQL Databases With DBeaver

DBeaver is a versatile database management tool. It is popular among sysadmins because of its compatibility with database systems, such as PostgreSQL, MySQL, SQLite, Oracle, and DB2.

To see your Postgres database using DBeaver, follow these instructions:

Step #1: Select PostgreSQL

Navigate to Database > New Database Connection. Select PostgreSQL from the array of database options and click on Next.

list db with dbeaver

At the top of the dialog box, click on the PostgreSQL tab.

postgresql tab

Step #2: Show All Databases

Check the option for showing all databases.

show all DB postgresql

Return to the Main tab and enter your database credentials in the Authentication section. Click Finish to complete.

postgresql authentication

Step #3: Display the Databases

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

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

expand db

Conclusion

Whether you’re a database administrator or a developer, knowing how to list all databases in PostgreSQL is a fundamental skill. Each method discussed offers its unique advantages.

For high-performance hosting solutions, consider providers like RedSwitches Bare Metal Hosting, which offers robust and reliable services that are ideal for managing extensive PostgreSQL databases.

We offer the best dedicated server pricing and deliver instant dedicated servers, usually on the same day the order gets approved. Whether you need a dedicated server, a traffic-friendly 10Gbps dedicated server, or a powerful bare metal server, we are your trusted hosting partner.

FAQs

Q. How can I list all databases using psql?

psql is a command-line interface for PostgreSQL that allows you to interact with the database server. You can use the command \l or \list in psql for listing databases. After connecting to a database, simply type \l and press enter. This command will display a list of available databases on the database server.

Q. What is the command to show all tables in a PostgreSQL database?

To show all tables in a database using psql, you can use the \dt command. This command will display a list of relations (tables, views, and sequences) in the schema you are currently connected to.

Q. How can I display all databases and their tables in PostgreSQL using the psql command?

psql allows you to get the list of databases and tables in PostgreSQL using the \l and \dt commands, respectively. First, you can list all databases with \l and then list all tables with \dt in the schema of the selected database.

Q. What is the procedure for backing up a PostgreSQL database with the psql tool?

While psql itself doesn’t directly back up databases, you can use the pg_dump utility to back up a database. The command pg_dump dbname > dbname_backup.sql will create a backup of the database named ‘dbname’.

Q. What are the steps to create a new database in PostgreSQL using the psql command line?

Use the command CREATE DATABASE dbname; to create a new database. Replace ‘dbname’ with your desired database name.

Q. How can I determine the version of my PostgreSQL server with the psql tool?

The command SELECT version(); will display the PostgreSQL server version.

Q. How do I inspect the structure or schema of a specific table in PostgreSQL using psql commands?

Use \d table_name to view the structure of a specific table.

Q. How do I view all user accounts in PostgreSQL through the psql interface?

Use the command \du to list all user accounts and their permissions.

Q. How can I check the size of a database in PostgreSQL?

To find out the size of a database in PostgreSQL, you can use the SQL command:

SELECT pg_size_pretty(pg_database_size('your_database_name'));