Key points of template0, template1 and Postges databases

1. You can not drop a database which users are currently connected to.
2. The “postgres” database is meant as a default database for connections. It is not required, you can drop it but probably a lot of tools you use will need to be adjusted because they assume that the “postgres” database is there by default.
3. When you create a new database by using the syntax “create database [DB_NAME]” you get an exact copy of template1.
   Whatever you put into template1 will be available in a new database if you use the following syntax: “create database [DB_NAME];” This can simplify your deployments a lot if you rely on pre-installed objects for e.g. monitoring or development.
4. For creating new databases you can use whatever database you like as the source when you specify then template explicitly.
5. When you want to drop a database or when you want to create a database there must be no connections to the database you either want to drop or you want to create a new database from.
6. “datistemplate” and “datallowcon” settings are in pg_database table, which stores information about the available databases. Unlike most system catalogs, pg_database is shared across all databases of a cluster: there is only one copy of pg_database per cluster, not one per database.
   [database_name] > select datname,datallowconn,datistemplate from pg_database order by 3;
     datname  | datallowconn | datistemplate
   -----------+--------------+---------------
    postgres  | t            | f
    db_test   | t            | f
    template1 | t            | t
    template0 | f            | t
   (4 rows)
7. template0 is the only database that has “datallowcon” set to false, why? Because template0 is meant as the default unmodifiable database.
   Imagine that you have modified template1 to include the stuff you rely on but at some point in the future you need a new database which shall be without your modifications (e.g. restoring a dump). For this you always can use template0 as a template because template0 is always clean. And this is why connections are not allowed to template0.
8. You can create your own template database(s) by setting the “datistemplate” to true.

Ref: https://blog.dbi-services.com/what-the-hell-are-these-template0-and-template1-databases-in-postgresql/?unapproved=11903&moderation-hash=d2787e26b5b1380763c7a0ee4cac468f
https://www.postgresql.org/docs/9.4/catalog-pg-database.html

 

 

• postgres
• PostgreSQL
• template0
• template0 vs template1
• template1