创建和删除数据表

最近更新时间:2019-12-03 17:10:15

不指定 shard key 建表

不指定 shard key 建表方式,系统默认使用第一个字段作为表的 shard key。

postgres=# create table t_first_col_share(id serial not null,nickname text);
CREATE TABLE
postgres=# \d+ t_first_col_share
                                              Table "public.t_first_col_share"
  Column  |  Type   |                           Modifiers                            | Storage  | Stats target | Description 
----------+---------+----------------------------------------------------------------+----------+--------------+-------------
 id       | integer | not null default nextval('t_first_col_share_id_seq'::regclass) | plain    |    | 
 nickname | text    |                                                                | extended |    | 
Has OIDs: no
Distribute By SHARD(id)
        Location Nodes: ALL DATANODES

指定 shard key 建表

postgres=# create table t_appoint_col(id serial not null,nickname text) distribute by shard(nickname);
CREATE TABLE
postgres=# \d+ t_appoint_col
                                              Table "public.t_appoint_col"
  Column  |  Type   |                         Modifiers                          | Storage  | Stats target | Description 
----------+---------+------------------------------------------------------------+----------+--------------+-------------
 id       | integer | not null default nextval('t_appoint_col_id_seq'::regclass) | plain    |              | 
 nickname | text    |                                                            | extended |              | 
Has OIDs: no
Distribute By SHARD(nickname)
        Location Nodes: ALL DATANODES

指定 group 建表

postgres=# create table t (id integer,nc text) distribute by shard (id) to group default_group;
CREATE TABLE

postgres=# \d+ t
                           Table "public.t"
 Column |  Type   | Modifiers | Storage  | Stats target | Description 
--------+---------+-----------+----------+--------------+-------------
 id     | integer |           | plain    |              | 
 nc     | text    |           | extended |              | 
Has OIDs: no
Distribute By SHARD(id)
        Location Nodes: ALL DATANODES

创建范围分区表

postgres=#create table t_range (f1 bigint,f2 timestamp default now(), f3 integer) partition by range (f3) begin (1) step (50) partitions (3) distribute by shard(f1) to group default_group;
CREATE TABLE
postgres=# insert into t_range(f1,f3) values(1,1),(2,50),(3,100),(2,110);
INSERT 0 4

1.36版本前数字分区只能支持 integer 类型,请不要使用 bigint 和 smalint,否则后面无法进行 trucate 分区表操作。

如下示例中,执行会出错,提示超出范围。

postgres=# insert into t_range(f1,f3) values(1,151);
ERROR:  node:16385, error inserted value is not in range of partitioned table, please check the value of paritition key

解决办法为扩展一些分区即可使用。

postgres=# ALTER TABLE t_range ADD PARTITIONS 2;                
ALTER TABLE
postgres=# insert into t_range(f1,f3) values(1,151);
INSERT 0 1
postgres=# 

创建时间范围分区表

postgres=# create table t_time_range(f1 bigint, f2 timestamp ,f3 bigint) partition by range (f2) begin (timestamp without time zone '2017-09-01 0:0:0') step (interval '1 month') partitions (12) distribute by shard(f1) to group default_group;
CREATE TABLE
postgres=# \d+ t_time_range 
                                Table "public.t_time_range"
 Column |            Type             | Modifiers | Storage | Stats target | Description 
--------+-----------------------------+-----------+---------+--------------+-------------
 f1     | bigint                      |           | plain   |              | 
 f2     | timestamp without time zone |           | plain   |              | 
 f3     | bigint                      |           | plain   |              | 
Has OIDs: no
Distribute By SHARD(f1)
        Location Nodes: ALL DATANODES
Partition By: RANGE(f2)
    \# Of Partitions: 12
    Start With: 2017-09-01
Interval Of Partition: 1 MONTH

创建冷热分区表

postgres=#  create table t_cold_hot_table(f1 bigint, f2 timestamp ,f3 bigint) partition by range (f2) begin (timestamp without time zone '2017-09-01 0:0:0') step (interval '1 month') partitions (12) distribute by shard(f1,f2) to group default_group ext_group;
CREATE TABLE

postgres=# \d+ t_cold_hot_table
                              Table "pgxz.t_cold_hot_table"
 Column |            Type             | Modifiers | Storage | Stats target | Description 
--------+-----------------------------+-----------+---------+--------------+-------------
 f1     | bigint                      |           | plain   |              | 
 f2     | timestamp without time zone |           | plain   |              | 
 f3     | bigint                      |           | plain   |              | 
Has OIDs: no
Distribute By SHARD(f1,f2)
        Hotnodes:dn02, dn01 Coldnodes:dn03, dn04
Partition By: RANGE(f2)
    \# Of Partitions: 12
    Start With: 2017-09-01
Interval Of Partition: 1 MONTH

创建时间范围冷热分区表需要有两个 group,冷数据的 ext_group 对应的节点 dn03 和 dn04 需要标识为冷节点,如下所示:

[pgxz@VM_0_29_centos install]$ psql -p 23003
psql (PGXC , based on PG 9.4beta1)
Type "help" for help.

postgres=#  select pg_set_node_cold_access();
 pg_set_node_cold_access 
\-------------------------
 success
(1 row)

postgres=# \q
[pgxz@VM_0_29_centos install]$ psql -p 23004
psql (PGXC , based on PG 9.4beta1)
Type "help" for help.

postgres=#  select pg_set_node_cold_access();
 pg_set_node_cold_access 
\-------------------------
 success
(1 row)

postgres=# 

使用冷热分区表需要在 postgresql.conf 中配置冷热分区时间参数,如下所示:

manual_hot_date = '2017-12-01' 

逻辑分区表

range 分区表

创建主分区

postgres=# create table t_native_range (f1 bigint,f2 timestamp default now(), f3 integer) partition by range ( f2 ) distribute by shard(f1) to group default_group;   
NOTICE:  Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE

建立两个子表

postgres=# create table t_native_range_201709 partition of t_native_range (f1 ,f2 , f3 ) for values from ('2017-09-01') to ('2017-10-01');
NOTICE:  Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE
postgres=# create table t_native_range_201710 partition of t_native_range (f1 ,f2 , f3 ) for values from ('2017-10-01') to ('2017-11-01');
NOTICE:  Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE
postgres=# 

查看表结构

postgres=# \d+ t_native_range
                                         Table "tbase.t_native_range"
 Column |            Type             | Collation | Nullable | Default | Storage | Stats target | Description 
--------+-----------------------------+-----------+----------+---------+---------+--------------+-------------
 f1     | bigint                      |           |          |         | plain   |              | 
 f2     | timestamp without time zone |           |          | now()   | plain   |              | 
 f3     | integer                     |           |          |         | plain   |              | 
Partition key: RANGE (f2)
Partitions: t_native_range_201709 FOR VALUES FROM ('2017-09-01 00:00:00') TO ('2017-10-01 00:00:00'),
            t_native_range_201710 FOR VALUES FROM ('2017-10-01 00:00:00') TO ('2017-11-01 00:00:00')
Distribute By: SHARD(f1)
Location Nodes: ALL DATANODES

list 分区表

创建主分区

postgres=# create table t_native_list(f1 bigserial not null,f2 text, f3 integer,f4 date) partition by list( f2 ) distribute by shard(f1) to group default_group;   

NOTICE:  Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE

建立两个子表,分别存入“广东”和“北京”

postgres=# create table t_list_gd partition of t_native_list(f1 ,f2 , f3,f4) for values in ('广东'); 
NOTICE:  Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE
postgres=# create table t_list_bj partition of t_native_list(f1 ,f2 , f3,f4) for values in ('北京');  
NOTICE:  Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE

查看表结构

postgres=# \d+ t_native_list
                                                 Table "tbase.t_native_list"
 Column |  Type   | Collation | Nullable |                  Default                  | Storage  | Stats target | Description 
--------+---------+-----------+----------+-------------------------------------------+----------+--------------+-------------
 f1     | bigint  |           | not null | nextval('t_native_list_f1_seq'::regclass) | plain    |              | 
 f2     | text    |           |          |                                           | extended |              | 
 f3     | integer |           |          |                                           | plain    |              | 
 f4     | date    |           |          |                                           | plain    |              | 
Partition key: LIST (f2)
Partitions: t_list_bj FOR VALUES IN ('北京'),
            t_list_gd FOR VALUES IN ('广东')
Distribute By: SHARD(f1)
Location Nodes: ALL DATANODES

postgres=# 

多级分区表

创建主表

postgres=# create table t_native_mul_list(f1 bigserial not null,f2 integer,f3 text,f4 text, f5 date) partition by list ( f3 ) distribute by shard(f1) to group default_group;
NOTICE:  Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE

创建二级表

postgres=# create table t_native_mul_list_gd partition of t_native_mul_list for values in ('广东') partition by range(f5); 
NOTICE:  Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE

postgres=# create table t_native_mul_list_bj partition of t_native_mul_list for values in ('北京') partition by range(f5); 
NOTICE:  Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE

postgres=# create table t_native_mul_list_sh partition of t_native_mul_list for values in ('上海');
NOTICE:  Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE

创建三级表

postgres=# create table t_native_mul_list_gd_201701 partition of t_native_mul_list_gd(f1,f2,f3,f4,f5) for values from ('2017-01-01') to ('2017-02-01'); 
NOTICE:  Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE

postgres=# create table t_native_mul_list_gd_201702 partition of t_native_mul_list_gd(f1,f2,f3,f4,f5) for values from ('2017-02-01') to ('2017-03-01');
NOTICE:  Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE

postgres=# create table t_native_mul_list_bj_201701 partition of t_native_mul_list_bj(f1,f2,f3,f4,f5) for values from ('2017-01-01') to ('2017-02-01'); 
NOTICE:  Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE

postgres=# create table t_native_mul_list_bj_201702 partition of t_native_mul_list_bj(f1,f2,f3,f4,f5) for values from ('2017-02-01') to ('2017-03-01'); 
NOTICE:  Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE

TBase 支持存在1级和2级分区混用,用户不需要都平级。

复制表

复制表是所有 dn 节点都存储一份相同的数据。

postgres=# create table t_rep (id int,mc text) distribute by replication to group default_group;
CREATE TABLE
postgres=# insert into t_rep values(1,'TBase'),(2,'pgxz');
INSERT 0 2
postgres=# EXECUTE DIRECT ON (dn001) 'select * from t_rep';
 id |  mc   
----+-------
  1 | TBase
  2 | pgxz
(2 rows)

postgres=# EXECUTE DIRECT ON (dn002) 'select * from t_rep'; 
 id |  mc   
----+-------
  1 | TBase
  2 | pgxz
(2 rows)

可以看到所有节点都保存了一份相同的数据。

分区表性能测试

逻辑分区表 range 分区表

postgres=# \d+ t_native_range
                                         Table "tbase.t_native_range"
 Column |            Type             | Collation | Nullable | Default | Storage | Stats target | Description 
--------+-----------------------------+-----------+----------+---------+---------+--------------+-------------
 f1     | bigint                      |           |          |         | plain   |              | 
 f2     | timestamp without time zone |           |          | now()   | plain   |              | 
 f3     | integer                     |           |          |         | plain   |              | 
Partition key: RANGE (f2)
Partitions: t_native_range_201709 FOR VALUES FROM ('2017-09-01 00:00:00') TO ('2017-10-01 00:00:00'),
            t_native_range_201710 FOR VALUES FROM ('2017-10-01 00:00:00') TO ('2017-11-01 00:00:00'),
            t_native_range_201711 FOR VALUES FROM ('2017-11-01 00:00:00') TO ('2017-12-01 00:00:00'),
            t_native_range_201712 FOR VALUES FROM ('2017-12-01 00:00:00') TO ('2018-01-01 00:00:00'),
            t_native_range_201801 FOR VALUES FROM ('2018-01-01 00:00:00') TO ('2018-02-01 00:00:00'),
            t_native_range_201802 FOR VALUES FROM ('2018-02-01 00:00:00') TO ('2018-03-01 00:00:00'),
            t_native_range_201803 FOR VALUES FROM ('2018-03-01 00:00:00') TO ('2018-04-01 00:00:00'),
            t_native_range_201804 FOR VALUES FROM ('2018-04-01 00:00:00') TO ('2018-05-01 00:00:00'),
            t_native_range_201805 FOR VALUES FROM ('2018-05-01 00:00:00') TO ('2018-06-01 00:00:00'),
            t_native_range_201806 FOR VALUES FROM ('2018-06-01 00:00:00') TO ('2018-07-01 00:00:00'),
            t_native_range_201807 FOR VALUES FROM ('2018-07-01 00:00:00') TO ('2018-08-01 00:00:00'),
            t_native_range_201808 FOR VALUES FROM ('2018-08-01 00:00:00') TO ('2018-09-01 00:00:00')
Distribute By: SHARD(f1)
Location Nodes: ALL DATANODES

[tbase@VM_0_37_centos pgbench]$ pgbench -h 127.0.0.1 -p 11008 -d postgres -U tbase -c 4 -j 1 -n -M prepared -T 60 -r -f insert_t_native_range.sql > insert_t_native_range.log 2>&1
[tbase@VM_0_37_centos pgbench]$ tail insert_t_native_range.log
duration: 60 s
number of transactions actually processed: 13220
latency average = 18.162 ms
tps = 220.241817 (including connections establishing)
tps = 220.260935 (excluding connections establishing)
script statistics:
 \- statement latencies in milliseconds:
         0.013  \set f1 random(1, 10000000)
         0.006  \set f2 random(1, 364)
        18.130  insert into t_native_range values(:f1,('2017-09-01'::date+:f2::integer)::timestamp,:f1);

时间范围分区表

postgres=# \d+ t_time_range
                                          Table "tbase.t_time_range"
 Column |            Type             | Collation | Nullable | Default | Storage | Stats target | Description 
--------+-----------------------------+-----------+----------+---------+---------+--------------+-------------
 f1     | bigint                      |           |          |         | plain   |              | 
 f2     | timestamp without time zone |           |          |         | plain   |              | 
 f3     | bigint                      |           |          |         | plain   |              | 
Distribute By: SHARD(f1)
Location Nodes: ALL DATANODES
Partition By: RANGE(f2)
         \# Of Partitions: 12
         Start With: 2017-09-01
         Interval Of Partition: 1 MONTH

postgres=# 
[tbase@VM_0_37_centos pgbench]$ pgbench -h 127.0.0.1 -p 11008 -d postgres -U tbase -c 4 -j 1 -n -M prepared -T 60 -r -f insert_t_time_range.sql > insert_t_time_range.log 2>&1
[tbase@VM_0_37_centos pgbench]$ tail -20 insert_t_time_range.log
client 3 receiving
client 0 receiving
client 1 receiving
client 2 receiving
pghost: 127.0.0.1 pgport: 11008 nclients: 4 duration: 60 dbName: postgres
transaction type: insert_t_time_range.sql
scaling factor: 1
query mode: prepared
number of clients: 4
number of threads: 1
duration: 60 s
number of transactions actually processed: 55464
latency average = 4.328 ms
tps = 924.229841 (including connections establishing)
tps = 924.343708 (excluding connections establishing)
script statistics:
 \- statement latencies in milliseconds:
         0.011  \set f1 random(1, 10000000)
         0.006  \set f2 random(1, 364)
         4.307  insert into t_time_range values(:f1,('2017-09-01'::date+:f2::integer)::timestamp,:f1);

使用 IF NOT EXISTS

带 IF NOT EXISTS 关键字作用表示表不存在时才创建。

postgres=# create table t(id int,mc text);              
CREATE TABLE
postgres=# create table t(id int,mc text);
ERROR:  relation "t" already exists
postgres=# create table IF NOT EXISTS t(id int,mc text);
NOTICE:  relation "t" already exists, skipping
CREATE TABLE
postgres=# 

指定模式创建表

postgres=# create table public.t(id int,mc text);
NOTICE:  Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE 

使用查询结果创建数据表

postgres=# create table t(id int,mc text) distribute by shard(mc);
NOTICE:  Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE
postgres=# insert into t values(1,'tbase');
INSERT 0 1
postgres=#  create table t_as as select * from t;
NOTICE:  Replica identity is needed for shard table, please add to this table through "alter table" command.
INSERT 0 1
postgres=# select * from t_as;
 id |  mc   
----+-------
  1 | tbase
(1 row)
postgres=# \d+ t
                                      Table "tbase.t"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------+-----------+----------+---------+----------+--------------+-------------
 id     | integer |           |          |         | plain    |              | 
 mc     | text    |           |          |         | extended |              | 
Distribute By: SHARD(mc)
Location Nodes: ALL DATANODES

postgres=# \d+ t_as
                                    Table "tbase.t_as"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------+-----------+----------+---------+----------+--------------+-------------
 id     | integer |           |          |         | plain    |              | 
 mc     | text    |           |          |         | extended |              | 
Distribute By: SHARD(id)
Location Nodes: ALL DATANODES

postgres=#  

删除数据表

删除当前模式下的数据表

postgres=# drop table t;
DROP TABLE

删除某个模式下数据表

postgres=# drop table public.t;
DROP TABLE

删除数据表,不存在时不执行,不报错

postgres=# drop table IF EXISTS t;    
NOTICE:  table "t" does not exist, skipping
DROP TABLE

使用 CASCADE 无条件删除数据表

postgres=# create view tbase_schema.t1_view as select * from tbase_schema.t1 ;
CREATE VIEW
postgres=# drop table tbase_schema.t1 ;
ERROR:  cannot drop table tbase_schema.t1 because other objects depend on it
DETAIL:  view tbase_schema.t1_view depends on table tbase_schema.t1
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

postgres=# drop table tbase_schema.t1 CASCADE;
NOTICE:  drop cascades to view tbase_schema.t1_view
DROP TABLE
postgres=#