There are three databases in postgresql by default: postgres, template0, and template1.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =T/postgres +
| | | | | postgres=CTc/postgres
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
postgres=#
The client will connect to the postgres library by default. This library can be deleted, but it will affect the default client connection.
After deleting the postgres library, you can use the template library template1 to create the postgres library:
$ psql template1
psql (11.9)
Type "help" for help.
template1=# drop database postgres;
DROP DATABASE
template1=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(2 rows)
template1=# create database postgres;
CREATE DATABASE
template1=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
template1=#
In fact, when creating a new library using the create database db_name statement, it is to create a copy of the template library template1.
So what happens if I modify the template1 library?
$ psql template1
psql (11.9)
Type "help" for help.
template1=# create table my_test_tab(a int);
CREATE TABLE
template1=# create extension hstore;
CREATE EXTENSION
template1=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+--------------------------------------------------
hstore | 1.5 | public | data type for storing sets of (key, value) pairs
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
template1=#
After modification, when creating a new library, the new library will also contain the above tables and extensions:
template1=# create database db_test;
CREATE DATABASE
template1=# \c db_test
You are now connected to database "db_test" as user "postgres".
db_test=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+--------------------------------------------------
hstore | 1.5 | public | data type for storing sets of (key, value) pairs
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
db_test=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------+-------+----------
public | my_test_tab | table | postgres
(1 row)
db_test=#
No matter what is added to template1, it will be in the new library created later.
So what is the purpose of template0?
db_test=# 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) db_test=#
From here, we can see that only the datallowconn field corresponding to the template0 library is F. This is why the above login to template1 instead of template0 when rebuilding postgres.
template0 is the default non-modified database. It is not recommended that users make any changes to template0. In the initialized empty instance, template0 and template1 are exactly the same.
Why do you need two template libraries? Suppose you messed up with template1, you can also restore template1 through template0.
If you want to create your own template library, just set the database corresponding to the library (column in pg_database) to T.
Of course, when creating a new library, you can also choose other libraries as the source library:
db_test=# create database db_test_2 template db_test; CREATE DATABASE db_test=#
However, it is required that no other connections are connected to the template library, otherwise an error will be reported:
db_test=# create database db_test_2 template db_test; ERROR: source database "db_test" is being accessed by other users DETAIL: There is 1 other session using the database. db_test=#
Supplement: Rebuild postgresql template database template1
$ psql -U postgres postgres postgres=# update pg_database set datistemplate = false where datname='template1'; UPDATE 1 postgres=# drop database template1; DROP DATABASE postgres=# create database template1 template=template0; CREATE DATABASE postgres=# update pg_database set datistemplate = true where datname='template1'; UPDATE 1 postgres=#
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.