Wednesday, March 22, 2023

How to create a postgresql database from template0 with owner assigned to another user than the creator

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

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=#

Thanks

No comments:

Post a Comment

Flashback data archive steps

 Objective: Ways to track DML changes in a table Detailed objective: We should be able to track the changes in the table at any point in tim...