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)
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)
No comments:
Post a Comment