# What are PostgreSQL Templates?

[Supabase](https://yomu.fyi/company/supabase) · Angelico de los Reyes · Jul 9, 2020

**Type:** Explainer

## Summary

PostgreSQL bases every new database creation on an existing template database within the cluster, defaulting to template1. While administrators can directly modify template1 with tables, data, extensions, or procedural languages, altering it risks breaking future database creation commands if mistakes happen. Setting an existing database as a custom template using the ALTER DATABASE command allows users with the CREATEDB privilege to instantiate customized databases without polluting system templates. Any database creation from a template requires zero active connections on that template at execution time, making pg\_dump the preferred tool for replicating active production environments. The immutable template0 system database serves as a fallback to recreate corrupted templates, restore clean dumps, or initialize databases with alternative character encodings.

## Context

Directly modifying the default template1 database to pre-configure new databases risks breaking the CREATE DATABASE command if errors occur during manual modifications or database recreation.

## Approach / What changed

Configuring custom template databases with ALTER DATABASE template\_db\_name WITH is\_template TRUE isolates custom schemas across multiple use cases, while keeping template0 as an immutable fallback for recovery, clean dumps, and custom encodings.

## Takeaways

- Setting is\_template to TRUE on an existing database allows any role with the CREATEDB privilege to use it as a template, whereas unflagged databases restrict template usage to superusers or owners.
- The CREATE DATABASE command fails immediately if any active connections exist on the target template database at the start of execution, making pg\_dump preferable for live database replication.
- The pristine template0 database must remain unmodified after cluster initialization to enable recovery of corrupted template1 databases and allow database creation with alternative encodings such as SQL\_ASCII.

**Tags:** [Postgres](https://yomu.fyi/topic/postgres)

[Read original post](https://supabase.com/blog/postgresql-templates)