How to List All Database in PostgreSQL via Command Line

Posted on

This article is describing on how to list all database in PostgreSQL Database Server via command line. So, the environment used in order to execute the command to list all database in PostgreSQL Database Server is an environment equipped with a CLI (Command Line Interface). An example of a CLI tool in this context is a terminal or any other interface equipped with a bash prompt.

Steps for listing all database in PostgreSQL via command line will be described in the following steps :

1. Check the PostgreSQL Database Server’s service. It is a necessity for connecting or accessing service of PostgreSQL Database Server. For further reference, read the article titled ‘Check PostgreSQL Service Status’ in this link.

2. Connect to PostgreSQL Database Server’s command console. It can be done by referring to this article as an option which is titled ‘PostgreSQL Database Access from Command Line in Linux’ and it can be found in the following link. Connecting to PostgreSQL Database Server can be done without having to specify the database which is going to be accessed. It is done since the purpose is just to list all databases available in MySQL Database Server as shown below :

[root@hostname ~]# psql -Upostgres
Password for user postgres: 
psql (9.2.18)
Type "help" for help.

postgres=# 

3. After connecting to PostgreSQL Database Server to its command console, the following command can be executed in order to list all database in PostgreSQL :

postgres=# \l

The following is the output of the above command execution :

postgres=# \l
                                   List of databases
     Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
--------------+----------+----------+-------------+-------------+-----------------------
 mydb1        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 mydb2        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
              |          |          |             |             | postgres=CTc/postgres
 template1    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
              |          |          |             |             | postgres=CTc/postgres
(5 rows)

postgres=# \q

So, the above command shown an output which is listing databases where there are 5(five) databases available. It is done using the super user or the default user name called ‘postgres’. Depends on the username and also the privileges given to the user itself, the output of the available database listed will also be vary.

Leave a Reply