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