Please find below steps in creating a new db from template 0 and assign it a new owner during creation step itself.
Requirement for such need: [from postgresql guide]
https://www.postgresql.org/docs/current/manage-ag-templatedbs.html
"By instructing
CREATE DATABASE
to copy template0
instead of template1
, you can create a “pristine” user database (one where no user-defined objects exist and where the system objects have not been altered) that contains none of the site-local additions in template1
. This is particularly handy when restoring a pg_dump
dump: the dump script should be restored in a pristine database to ensure that one recreates the correct contents of the dumped database, without conflicting with objects that might have been added to template1
later on"Before:
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8769 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 8617 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 8617 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(3 rows)
Creating the database:
postgres=# create database pgbenchdb owner pgbenchusr template template0;
CREATE DATABASE
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8769 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 8617 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 8617 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(3 rows)
Creating the database:
postgres=# create database pgbenchdb owner pgbenchusr template template0;
CREATE DATABASE
Post create validation:
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+------------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
pgbenchdb | pgbenchusr | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8617 kB | pg_default |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8769 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 8617 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 8617 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(4 rows)
postgres=#
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+------------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
pgbenchdb | pgbenchusr | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8617 kB | pg_default |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8769 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 8617 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 8617 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(4 rows)
postgres=#
Thanks
No comments:
Post a Comment