Tuesday, November 26, 2019

How to list the databases in a PostgreSQL instance


Connect to the default database:
psql

List your databases:
select oid as database_id,
       datname as database_name,
       datallowconn as allow_connect,
       datconnlimit as connection_limit
from pg_database
order by oid;

Example output:
database_id | database_name | allow_connect | connection_limit
-------------+---------------+---------------+------------------
           1 | template1     | t             |               -1
       13880 | template0     | f             |               -1
       13881 | postgres      | t             |               -1
       16426 | proddb01      | t             |               -1
(4 rows)

Or use the psql meta-command "list+" (or "\l+") to list the available databases;
prod-# \l+
                                                                      List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |      Access privileges       |  Size   | Tablespace |                Description
-----------+----------+----------+------------+------------+------------------------------+---------+------------+--------------------------------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =Tc/postgres                +| 9269 kB | pg_default | default administrative connection database
           |          |          |            |            | postgres=CTc/postgres       +|         |            |
           |          |          |            |            | postgres_exporter=c/postgres |         |            |
 proddb01  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | postgres=CTc/postgres       +| 25 GB   | pg_default |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres                 +| 7965 kB | pg_default | unmodifiable empty database
           |          |          |            |            | postgres=CTc/postgres        |         |            |
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres                 +| 8221 kB | pg_default | default template for new databases
           |          |          |            |            | postgres=CTc/postgres        |         |            |
(4 rows)
Once again, thanks to Bart Gawrych for providing some very useful listings on his site.

No comments:

Post a Comment