今天分享一条简单的命令，帮助大家尽快的复制一个新的SCHEMA。话不多说，看例子：

首先创建一个有三个表（t1\t2\t3）的新模式(test1):

postgres=# \d               List of relations Schema |   Name    | Type  | Owner  | Storage--------+-----------+-------+--------+--------- public | hgimdbtab | table | hgimdb | heap(1 row)postgres=# create schema test1;CREATE SCHEMApostgres=# create table test1.t1(id int);NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.CREATE TABLEpostgres=# insert into test1.t1 select generate_series(1,100);INSERT 0 100postgres=# select count(*) from test1.t1; count-------   100(1 row)postgres=# create table test1.t2(id int);NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.CREATE TABLEpostgres=# create table test1.t3(id int);NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.CREATE TABLEpostgres=# \dt test1.*            List of relations Schema | Name | Type  | Owner  | Storage--------+------+-------+--------+--------- test1  | t1   | table | hgimdb | heap test1  | t2   | table | hgimdb | heap test1  | t3   | table | hgimdb | heap(3 rows)

使用命令快速复制一个无数据模式：命令类似pg_dump -n test1 postgres |sed '1,${s/test1/test2/}'|psql postgres

[hgimdb@ps1 ~]$ pg_dump -n test1 postgres |sed '1,${s/test1/test2/}'|psql postgresSETSETSETSETSETSETCREATE SCHEMAALTER SCHEMASETSETCREATE TABLEALTER TABLECREATE TABLEALTER TABLECREATE TABLEALTER TABLE[hgimdb@ps1 ~]$ psqlpsql (8.3.23)Type "help" for help.postgres=# \dt test2.*            List of relations Schema | Name | Type  | Owner  | Storage--------+------+-------+--------+--------- test2  | t1   | table | hgimdb | heap test2  | t2   | table | hgimdb | heap test2  | t3   | table | hgimdb | heap(3 rows)postgres=# \dt test1.*            List of relations Schema | Name | Type  | Owner  | Storage--------+------+-------+--------+--------- test1  | t1   | table | hgimdb | heap test1  | t2   | table | hgimdb | heap test1  | t3   | table | hgimdb | heap(3 rows)postgres=# select count(*) from test1.t1; count-------   100(1 row)postgres=# select count(*) from test2.t1; count-------   100(1 row)postgres=#

这种方式不仅可以在同一个数据库下操作，也可以在不同的数据库中实现SCHEMA复制：

postgres=# create database tpch;CREATE DATABASEpostgres=# \q[hgimdb@ps1 ~]$ pg_dump -n test1 postgres |sed '1,${s/test1/test2/}'|psql tpchSETSETSETSETSETSETCREATE SCHEMAALTER SCHEMASETSETCREATE TABLEALTER TABLECREATE TABLEALTER TABLECREATE TABLEALTER TABLE[hgimdb@ps1 ~]$ psql -d tpchpsql (8.3.23)Type "help" for help.tpch=# \dNo relations found.tpch=# \dt test2.*            List of relations Schema | Name | Type  | Owner  | Storage--------+------+-------+--------+--------- test2  | t1   | table | hgimdb | heap test2  | t2   | table | hgimdb | heap test2  | t3   | table | hgimdb | heap(3 rows)tpch=# select count(*) from test2.t1; count-------   100(1 row)tpch=#