What are template1 and template0?

posted Sep 18, 2010, 6:34 AM by Sachchida Ojha

When you do an initdb two default databases are created: template0 and template1. Template0 is the pristine default database from which template1 is created. All other databases are created by duplicating template1 unless another database is specified in the createdb statement. You can access template1 but you cannot access template0.

When you have a common set of objects, such as languages, functions, operators, etc., you can create those objects in template1. All databases created after doing so will be created with those things in place. For example, doing createlang 'plperl' template1 will install plperl in template1. Then all databases created afterwards will have plperl available.

Beware of pilot errors. If you inadvertently load database specific functions or other schema information into template1 they will also be created in each new database.

There was a case when someone dropped and recreated a database and tried to reload the schema. There were many errors regarding duplicate objects. It turned out that, by mistake, the schema had first been loaded into template1. The newly created database already had the objects created and this caused the error messages.