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.
databases in PostgreSQL.” />
Prerequisites:
PostgreSQL installed and configured Administrator privileges
List of databases through the 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
displaying the results
.
psql allows users to use meta-commands, useful commands that start 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 connected, 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.
Conclusion
The guide provided 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.