How to see the list of the Postgres databases via the linux terminal?

Question

I have connected a database using the command

sudo -i -u postgres

And after I have tried \l and \d too, to view the list of all available databases, it is not working. Instead, I get the following output:

d:command not found and 
l:command not found error is showing 

What to do, how to get the list of the postgresql databases?

• user1822: You need to run the command line tool psql. sudo itself just changes your current user

Answer 1

You will have to log in to the database using the psql (see below) command line tool (or use pgadmin or similar tool). The error you are getting appears to be a bash (i.e. Linux shell) error.

This information you want is available through SQL. You can obtain it in 1 of two ways:

SELECT nspname
FROM pg_catalog.pg_namespace;

Or, you can use the standard ANSI method,

SELECT schema_name
FROM information_schema.schemata;

You can find out more about this and more in the PostgreSQL system catalogs documentation here.

Using psql (command to start this is psql [db_name] from the shell), you can also use \dn and/or \l (as explained by @a_horse_with_no_name) if you prefer psql tool commands!

In bash, the \ character is used:

• to disable aliases at the beginning of a command (explained here), or

• as an escape character (within a command - explained in bash ref 2) or

• to allow a command to span multiple lines (at the end of a line on its own - also explained in bash ref 2).

Try running:

ls \
-lista 

from the shell (cut and paste as-is). After it has run (returns the long listing of your current directory), hit the up-arrow to see what's happening! The ls -lista is expanded on a single line, however, it can be great for helping make long commands legible!

• user1822: The information is available in psql, \l and \d will do that. But apparently Abhinaw is not executing those commands in psql but on the linux command line
• Abhinaw Anand: thanks @a_horse_with_no_name i got solution is 'psql \l'
• ypercubeᵀᴹ: \dn shows schemata. \l shows databases.
• JoeLeBaron: The question is how to list databases. This shows schemas SELECT datname FROM pg_database

Answer 2

If you want to execute the command from the shell, you can use the -c option in psql:

psql -c '\l'

Example:

peter@blackrain:~$ sudo su postgres
postgres@blackrain:/home/peter$ psql -c '\l'
 List of databases
 Name | Owner | Encoding | Collate | Ctype | Access privileges 
------------+----------+----------+-------------+-------------+-----------------------
 blackrain | peter | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | 
 postgres | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | 
 redpepper | peter | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | 
 template0 | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres +
 | | | | | postgres=CTc/postgres
 template1 | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres +
 | | | | | postgres=CTc/postgres
 x | peter | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | 
(6 rows)
postgres@blackrain:/home/peter$

Answer 3

If you only want to see the list of databases and have psql available to you, you can simply use -l to list and exit.

psql -l

https://www.postgresql.org/docs/current/app-psql.html

Answer 4

This lists all databases:

SELECT datname FROM pg_database;

These list all databases in detail:

\list

\l

These list all databases in more detail:

\list+

\l+

Answer 5

For getting the database names only:

echo "SELECT datname FROM pg_database;" | psql | tail -n +3 | head -n -2 | egrep -v 'template0|template1|postgres'

• peterh: I believe it is an answer in its current form, but a snippet-only one.