Database Administration - PostgreSQL: Up and Running - page 27

This creates a copy, owned by the login role that issued the command, of the tem
plate1 default. Any role with CREATEDB rights can create new databases.
Template Databases
A template database is, as the name suggests, a database that serves as a model for other
databases. When you create a new database, PostgreSQL copies all the database settings
and data from the template database into yours.
The default PostgreSQL installation comes with two template databases: template0 and
template1 . If you don't specify a template database to follow when you create a database,
the template1 database is used as the template for the new database.
You should never alter template0 because it is the immaculate mod‐
el that you'll need to copy from if you screw up your templates. Make
your customizations to template1 or a new template database you
create. You can't change the encoding and collation of a database you
create from template1 or any other template database you create. So
if you need a different encoding or collation from those in tem
plate1 , create the database from template0 .
The basic syntax to create a database modeled after a template is:
CREATE DATABASE my_db TEMPLATE my_template_db ;
You can pick any database to serve as the template. Additionally, you can mark a database
as a template database. When you do, PostgreSQL restricts the database from being
edited or deleted. Any role with CREATEDB rights can use the database. To make any
database a template, run the following SQL as a superuser:
UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'mydb' ;
If ever you need to make edits to a template database or drop it entirely, first set datis
template to FALSE to enable changes. Don't forget to change the value back.
Using Schemas
Schemas organize your database into logical groups. If you have more than two dozen
databases on your server, consider cubbyholing them into schemas in a single database.
Objects must have unique names within a schema but need not be unique across the
database. If you cram all your tables into the default public schema, you'll run into
name clashes sooner or later. It's up to you how to organize your schemas. For example,
if you are an airline, you can place all tables of planes you own and their maintenance
records into a plane schema. Place all your crew and their personnel information into
another. And create another schema to house passenger-related information.