create custom template database and create new database using custom template database in postgres

Here you can see one demo on below requirement:

create custom template database and create new database using custom template database in postgres

-- connect to to your database

$ psql postgres

postgres# \c template1

template1=# select datname from pg_database where datistemplate=true;
  datname  
-----------
 template1
 template0
(2 rows)

-- Note: you can't use template0 database
e.g.,

template1=# \c template0 postgres
FATAL:  database "template0" is not currently accepting connections
Previous connection kept
template1=# 

-- create new database e.g., template2

template1=# create database template2;
CREATE DATABASE
template1=# 
template1=# \c template2 postgres
You are now connected to database "template2" as user "postgres".
template2=# 

-- Create required objects in this new database
e.g.,
template2=# create table tblx as select * from pg_class;
SELECT 341
template2=# 
template2=# create table tblz as select * from pg_description;
SELECT 4560
template2=# 
template2=# \dt
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | tblx | table | postgres
 public | tblz | table | postgres
(2 rows)

-- Now convert this database as a template database

template2=# update pg_database set datistemplate=true where datname='template2';
UPDATE 1
template2=# 
template2=# select datname from pg_database where datistemplate=true;
  datname  
-----------
 template1
 template0
 template2
(3 rows)

-- Now you can create new custom database using custom template database e.g., template2

template2=# create database testdb template template2;
CREATE DATABASE
template2=# 
template2=# \c testdb postgres
You are now connected to database "testdb" as user "postgres".
testdb=# 
testdb=# \dt
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | tblx | table | postgres
 public | tblz | table | postgres
(2 rows)

testdb=# \dt+
                    List of relations
 Schema | Name | Type  |  Owner   |  Size  | Description 
--------+------+-------+----------+--------+-------------
 public | tblx | table | postgres | 80 kB  | 
 public | tblz | table | postgres | 320 kB | 
(2 rows)