Template databases

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

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, they can install this extension in the template1 database. Installing an extension in template1 won't 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, this can be used to fix template1.
  • It's 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 doesn't contain encoding-specific or locale-specific data.