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.
- installed and configured
- Administrator privileges
- to PostgreSQL,
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
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
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:
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
Another method of enumerating databases in PostgreSQL is to query database names from the pg_database catalog through the SELECT statement. Follow these steps
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
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.
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]
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
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.