Template databases

By default, when a database is created, it is cloned from a template database called template1. The template database contains a set of tables, views, and functions, which is used to model the relation between the user-defined database objects. These tables, views, and functions are a part of the system catalog schema called pg_catalog.

The schema is very close to the namespace concept in object-oriented languages. It is often used to organize the database objects, functionality, security access, or to eliminate name collision.

The PostgreSQL server has two template databases:

• template1: The default database to be cloned. It can be modified to allow global modification to all the newly created databases. For example, if someone intends to use a certain extension in all the databases, then they can install this extension in the template1 database. Certainly, installing an extension in template1 will not be cascaded to the already existing databases, but it will affect the databases that will be created after this installation.
• template0: A safeguard or version database that has several purposes:
  • If template1 is corrupted by a user, then it can be used to fix template1.
  • It is handy in restoring a database dump. When a developer dumps a database, all the extensions are also dumped. If the extension is already installed in template1, this will lead to a collision, because the newly created database already contains the extensions. Unlike template1, template0 does not contain encoding-specific or locale-specific data.

One can create a database using a user database as a template. This is very handy for testing, database refactoring purposes, deployment plans, and more.