Chapter 5 part 2 - The Databases

The Databases

In order to establish the connection, PostgreSQL requires a target database. When the connection happens via the client psql, the database can be omitted. In this case the environment variable $PGDATABASE is used. If the variable is not set then the target database defaults to the login user.
This can be confusing because even if the pg_hba.conf authorises the connection, this aborts with the message

postgres@tardis:~$ psql -U test -h localhost
Password for user test: 
psql: FATAL:  database "test" does not exist

In this case if the database to connect to is unknown, the connection should contain the template1 as last parameter. This way the connection will establish to the always present template1 database . After the connection is established a query to the pg_database system table will return the database list.

postgres@tardis:~$ psql -U test -h localhost template1
Password for user test: 
psql (9.3.4)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

template1=> SELECT datname FROM pg_database;
    datname    
---------------
 template1
 template0
 postgres
(3 rows)

A brief note for the database administrators coming from MS SQL or MySql. The database postgres can be confused for the system database, like the master db or the mysql database.
That's not correct, the postgres database have nothing special and it's created by default only in the recent major PostgreSQL versions because is required by specific tools like pg_bench.
The template0 and template1 are the template databases. A template database is used to build new database copies via the physical file copy.
During the initdb the template1 is initialised with the correct references to the WAL records. The system views and the procedural language PL/PgSQL are then loaded into the template1. Finally the template0 and postgres databases are created from template1.
The database template0 doesn't allow the connections and is used to rebuild template1 if gets corrupted or to build a new database with character encoding or ctype an different from the cluster wide values.

postgres=# CREATE DATABASE db_test WITH ENCODING 'UTF8' LC_CTYPE 'en_US.UTF-8';
ERROR:  new LC_CTYPE (en_US.UTF-8) is incompatible with the LC_CTYPE of the 
template database (en_GB.UTF-8)
HINT:  Use the same LC_CTYPE as in the template database, or use template0 as 
template.

postgres=# CREATE DATABASE db_test WITH ENCODING 'UTF8' LC_CTYPE 'en_US.UTF-8' 
TEMPLATE template0;
CREATE DATABASE
postgres=#

If not specified, the CREATE DATABASE statement will use template1. A database can be renamed or dropped with the ALTER DATABASE and DROP DATABASE statements. Those operations require the exclusive access to the database. If any connection except the one performing the operation is present on the database the operation will abort.

postgres=# ALTER DATABASE db_test RENAME TO db_to_drop;
ALTER DATABASE

postgres=# DROP DATABASE db_to_drop;
DROP DATABASE