Making a duplicate copy of a schema in the same database without data in Greenplum

posted Apr 28, 2017, 8:08 AM by Sachchida Ojha
Easiest way to make a duplicate copy of a schema without data in the same database. Lets say I have an schema in dbadmin in sachi database. I want another schema dbadmin2 in the same database without data to preserve the schema object DDL. 
Step: Run a simple checksum to count the tables and views.
sachi=# \dt dbadmin.*
                     List of relations
 Schema  |         Name         | Type  |  Owner  | Storage 
---------+----------------------+-------+---------+---------
 dbadmin | smart_analyze_config | table | gpadmin | heap
(1 row)
sachi=# \dv dbadmin.*
                      List of relations
 Schema  |          Name          | Type |  Owner  | Storage 
---------+------------------------+------+---------+---------
 dbadmin | v_dist_key             | view | gpadmin | none
 dbadmin | v_distribution_key     | view | gpadmin | none
 dbadmin | v_distribution_key1    | view | gpadmin | none
 dbadmin | v_distribution_key2    | view | gpadmin | none
 dbadmin | v_distribution_key3    | view | gpadmin | none
 dbadmin | v_smart_analyze_confif | view | gpadmin | none
 dbadmin | v_smart_analyze_config | view | gpadmin | none
(7 rows)
Step 2:
[gpadmin@sachi ~]$ pg_dump -n dbadmin sachi |sed '1,${s/dbadmin/dbadmin2/}'|psql sachi

Step 3: Revalidate the count
sachi=# \dt dbadmin2.*
                      List of relations
  Schema  |         Name         | Type  |  Owner  | Storage 
----------+----------------------+-------+---------+---------
 dbadmin2 | smart_analyze_config | table | gpadmin | heap
(1 row)

sachi=# \dv dbadmin2.*
                      List of relations
  Schema  |          Name          | Type |  Owner  | Storage 
----------+------------------------+------+---------+---------
 dbadmin2 | v_dist_key             | view | gpadmin | none
 dbadmin2 | v_distribution_key     | view | gpadmin | none
 dbadmin2 | v_distribution_key1    | view | gpadmin | none
 dbadmin2 | v_distribution_key2    | view | gpadmin | none
 dbadmin2 | v_distribution_key3    | view | gpadmin | none
 dbadmin2 | v_smart_analyze_confif | view | gpadmin | none
 dbadmin2 | v_smart_analyze_config | view | gpadmin | none
(7 rows)

sachi=# 
Comments