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
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.
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=#) .
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.
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 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.
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.
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.
At the top of the dialog box, click on the PostgreSQL tab.
Step #2: Show All Databases
Check the option for showing all databases.
Return to the Main tab and enter your database credentials in the Authentication section. Click Finish to complete.
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.
Also Read: How to Delete a Postgres User (Drop User) [5 Methods Inside!]
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'));
