In PostgreSQL, we have two default databases, template0 and template1. Sometimes users are confused about why there are two default databases. In this post, we will learn why these two databases are important and their usage in PostgreSQL.
template1 database
Whenever we create a new database in PostgreSQL, the system needs to find a default database from which all the default tables, views, and functions are to be copied. template1 is used as the default database. Whenever a new database is created, it will clone the template1 database and have all the objects of the template1 database.
This database is also helpful whenever we need to create common tables, views, and functions for all new databases. For example, suppose you want to add the table “db_info” to all new databases. You simply create that table in the template1 database. When any user creates a new database, this default table “db_info” will also be copied into that new database.
template0 database
template0 database in PostgreSQL has two uses.
- Fix the template1 database when it is corrupted by the user.
- Restoring a database dump.
- As you can create or modify objects in the template1 database, there is a possibility of corruption of the template1 database. The user can create or use an existing extension, to modify the template1 database. So, template0 is always there to restore template 1 to its original state. By default, connection to template0 is blocked, which means you cannot modify or create new objects in the template0 database. So, the template0 database is always there to restore the template1 database.
In the above image, you can see that when we are creating a connection to the template0 database, PostgreSQL is showing an error that the template0 database is not currently accepting connections.
2. When a user restores a database, all the PostgreSQL extensions are also restored. There may be a possibility that the same extension has already been created in the template1 database or that the name extension has already been modified in the template1 database. So, if PostgreSQL uses the template1 database to restore the database, it will fail to restore the database. That’s why template0 is used whenever a database is restored. So that all the objects and extensions will be successfully restored without failure.