Listing databases on the database server

When we connect to PostgreSQL, we always connect to just one specific database on any database server. If there are many databases on a single server, it can get confusing, so sometimes you may just want to find out which databases are parts of the database server.

This is also confusing because we can use the word database in two different, but related, contexts. Initially, we start off by thinking that PostgreSQL is a database in which we put data, referring to the whole database server by just the word database. In PostgreSQL, a database server (also known as a cluster) is potentially split into multiple, individual databases, so, as you get more used to working with PostgreSQL, you'll start to separate the two concepts.

How to do it…

If you have access to psql, you can type the following command:

bash $ psql -l
                               List of databases
   Name    | Owner  | Encoding |   Collate   |    Ctype    | Access privileges
-----------+--------+----------+-------------+-------------+-------------------
 postgres  | sriggs | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
 template0 | sriggs | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/sriggs        +
           |        |          |             |             | sriggs=CTc/sriggs
 template1 | sriggs | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/sriggs        +
           |        |          |             |             | sriggs=CTc/sriggs
(3 rows)

You can also get the same information while running psql by simply typing \l.

The information that we just looked at is stored in a PostgreSQL catalog table named pg_database. We can issue a SQL query directly against that table from any connection to get a simpler result, as follows:

postgres=# select datname from pg_database;
datname
-----------
template1
template0
postgres
(3 rows)

How it works...

PostgreSQL starts with three databases: template0, template1, and postgres. The main user database is postgres.

You can create your own databases as well, like this:

CREATE DATABASE cookbook;

You can do the same from the command line, using the following expression:

bash $ createdb cookbook

After you've created your databases, be sure to secure them properly, as discussed in Chapter 6, Security.

From now on, we will run our examples in the cookbook database.

When you create another database, it actually takes a copy of an existing database. Once it is created, there is no further link between the two databases.

The template0 and template1 databases are known as template databases. The template1 database can be changed to allow you to create a localized template for any new databases that you create. The template0 database exists so that, when you alter template1, you still have a pristine copy to fall back on. In other words, if you break template1, then you can drop it and recreate it from template0.

You can drop the database named postgres. But don't, okay? Similarly, don't try to touch template0, because you won't be allowed to do anything with it, except use it as a template. On the other hand, the template1 database exists to be modified, so feel free to change it.

There's more...

The information that we just saw is stored in a PostgreSQL catalog table named pg_database. We can look at this directly to get some more information. In some ways, the output is less useful as well, as we need to look up some of the code in other tables:

cookbook=# \x
cookbook=# select * from pg_database;
-[ RECORD 1 ]-+------------------------------
oid           | 1
datname       | template1
datdba        | 10
encoding      | 6
datcollate    | en_GB.UTF-8
datctype      | en_GB.UTF-8
datistemplate | t
datallowconn  | t
datconnlimit  | -1
datlastsysoid | 11620
datfrozenxid  | 644
datminmxid    | 1
dattablespace | 1663
datacl        | {=c/sriggs,sriggs=CTc/sriggs}
-[ RECORD 2 ]-+------------------------------
oid           | 13706
datname       | template0
datdba        | 10
encoding      | 6
datcollate    | en_GB.UTF-8
datctype      | en_GB.UTF-8
datistemplate | t
datallowconn  | f
datconnlimit  | -1
datlastsysoid | 11620
datfrozenxid  | 644
datminmxid    | 1
dattablespace | 1663
datacl        | {=c/sriggs,sriggs=CTc/sriggs}
-[ RECORD 3 ]-+------------------------------
oid           | 13707
datname       | postgres
datdba        | 10
encoding      | 6
datcollate    | en_GB.UTF-8
datctype      | en_GB.UTF-8
datistemplate | f
datallowconn  | t
datconnlimit  | -1
datlastsysoid | 11620
datfrozenxid  | 644
datminmxid    | 1
dattablespace | 1663
datacl        |
-[ RECORD 4 ]-+------------------------------------
oid           | 16408
datname       | cookbook
datdba        | 16384
encoding      | 6
datcollate    | en_GB.UTF-8
datctype      | en_GB.UTF-8
datistemplate | f
datallowconn  | t
datconnlimit  | -1
datlastsysoid | 13706
datfrozenxid  | 726
datminmxid    | 1
dattablespace | 1663
datacl        | 

First of all, look at the use of the \x command. It makes the output in psql appear as one column per line, rather than one row per line.

We've already discussed templates. The other interesting things are that we can turn connections on and off for a database, and we can set connection limits for them, as well.

Also, you can see that each database has a default tablespace. Therefore, data tables get created inside one specific database, and the data files for that table get placed in one tablespace.

You can also see that each database has a collation sequence, which is the way that various language features are defined. We'll cover more on that in the Choosing good names for database objects recipe in Chapter 5, Tables and Data.