How to output a list of PostgreSQL databases and tables using psql

Preamble

​​

When administering PostgreSQL database servers, one of the most common tasks you will probably perform is enumerating databases and their tables.
PostgreSQL comes with an interactive tool, psql, that allows you to connect to a server and execute queries to it. When using psql, you can also use its meta-commands. These commands are useful for scripting and administering the command line. All meta-commands start with a backslash without quotes and are also known as backslash commands.

Database listing

You can connect to the PostgreSQL server using PSQL command like any system user. Depending on the server configuration, the user may need to enter his password to connect to the psql terminal. To access the psql terminal on behalf of the user you are logged in to, just enter psql.

When the PostgreSQL package is installed, an administrative user named “postgres” is created. By default, this user can connect to the local PostgreSQL server without a password.

To access the psql terminal as a “postgres” user, run it:

The sudo command allows you to run the commands as another user.
From the psql terminal, run the meta-command \l or \list to display a list of all databases:

\l

The output shall include the number of databases, the name of each database, its owner, encryption and access privileges:

List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+---------+-----------------------
odoo | odoo | UTF8 | C.UTF-8 |.
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
template0 | postgres | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | postgres=CTc/postgres
template1 | postgres | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | postgres=CTc/postgres
(4 rows)

The PostgreSQL server has three default databases: template0, template1 and postgres. The first two are templates, which are used when creating new databases.

If you want information about database sizes, default tablespaces, and descriptions, use \l+ or \list+. The database size shall be shown only if the current user can connect to it.

To obtain a list of all databases without access to the psql shell, use the -c switch as shown below:

sudo -u postgres psql -c "\l".

Another way to create a list of databases is to use the following SQL statement:

SELECT datname FROM pg_database;

In contrast to the \l meta command, the above query will only show the names of databases:

datname
-----------
postgres
odoo
template1
template0
(4 rows)

Lists Tables

To get a list of all tables of a particular database, first you need to connect to it using the meta-command \c or \connect. A user logged in as a psql terminal shall be able to connect to the database.  For example, to connect to a database named “odoo”, you must type:

\c odoo

Once the database is switched, use the \dt meta command to display a list of all database tables:

The output will include the number of tables, the name of each table and its scheme, type and owner:

List of relations
Schema | Name | Type | Owner
--------+-----------------------------------------------------+-------+-------
public | base_import_import | table | odoo
public | base_import_mapping | table | odoo
public | base_import_tests_models_char | table | odoo
...
public | web_editor_converter_test_sub | table | odoo
public | web_tour_tour | table | odoo
public | wizard_ir_model_menu_create | table | odoo
(107 rows)

If the database is empty, the output shall look like this:

No relations found.

For information on the size of tables and descriptions, use \dt+.

Conclusion.

You learned how to compile a list of PostgreSQL databases and tables using the psql command.

Exporting and Importing PostgreSQL Databases

Enteros

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of RDBMS, NoSQL, and machine learning database platforms.