Greenplum小把戏 - 快速复制无数据SCHEMA

最新推荐文章于 2023-12-09 11:35:29 发布

weixin_33712881

最新推荐文章于 2023-12-09 11:35:29 发布

阅读量173

点赞数

文章标签：数据库

原文链接：https://my.oschina.net/javacy/blog/2998074

版权

为什么80%的码农都做不了架构师？>>>

今天分享一条简单的命令，帮助大家尽快的复制一个新的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 SCHEMA
postgres=# 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 TABLE
postgres=# insert into test1.t1 select generate_series(1,100);
INSERT 0 100
postgres=# 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 TABLE
postgres=# 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 TABLE
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)

使用命令快速复制一个无数据模式：命令类似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 postgres
SET
SET
SET
SET
SET
SET
CREATE SCHEMA
ALTER SCHEMA
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
[hgimdb@ps1 ~]$ psql
psql (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 DATABASE
postgres=# \q
[hgimdb@ps1 ~]$ pg_dump -n test1 postgres |sed '1,${s/test1/test2/}'|psql tpch
SET
SET
SET
SET
SET
SET
CREATE SCHEMA
ALTER SCHEMA
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
[hgimdb@ps1 ~]$ psql -d tpch
psql (8.3.23)
Type "help" for help.

tpch=# \d
No 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=#

转载于:https://my.oschina.net/javacy/blog/2998074