合 Greenplum 7 新特性整理

发布日期 2023年10月9日 · 已更新 2024年1月26日

0 276 2

Tags： GreenPlum 整理自网络新特性 GPDB 7

👉 本文共约11293个字，系统预计阅读时间或需43分钟。

新特性总结
快速拥有GPDB 7和gpcc 7环境
重要的新特性
内核
自动启用vacuuum
支持仅索引扫描和覆盖索引
引入监控视图可以监控长运行的操作
新增pg_backend_memory_contexts的系统视图
增加列不再需要重写表
分区表
其它
Example 1: Creating a Range Partitioned Table
Example 2: Classic Range Partitioned Table
Example 3: Creating a List Partitioned Table
Example 4: Classic List Partitioned Table
Example 5: Creating a Hash Partitioned Table
Example 6: Adding Hash Partitions
Example 7: Creating a Composite Partitioned Table
Example 8: Adding Composite Partitions
Example 9: Adding Default Partition
Example 10: Splitting Default Partition
Example 11: Detaching Partitions
Example 12: Attaching Existing Table
Example 13: Checking Partition Information
Example 14: Getting Partition Statistics
Example 15: Truncate a Partition
Example 16: Exchange Partition Data
Example 17: Applying Constraints on Partitions
Example 18: Creating an indexed Partitioned Tables
Example 19: Rename Partitioned Tables
Example 20: Dropping Partitions
支持hash索引
支持生成列
AO表特性
AO表分析可以更快
AO表支持唯一索引、唯一约束和主键
引入gppkg v2
数据库维护
性能
优化器 Greenplum Query Optimizer (GPORCA)
AO表 Append-Optimized Tables
GP7移除的功能
GP7系统视图
GP7调优参数
GP7动态分区裁剪
用户或角色不允许以“pg_”开头
参考

新特性总结

快速拥有GPDB 7和gpcc 7环境

参考：https://www.dbaup.com/zaidockerzhongkuaisutiyangreenplum-7-0-0.html

此docker包括1个master，1个standby master，2个segment，2个mirror实例；还包括gpcc 7.0.0

docker rm -f gpdb7
docker run -itd --name gpdb7 -h gpdb7 \
  -p 5437:5432 -p 28087:28080  \
  -v /sys/fs/cgroup:/sys/fs/cgroup \
  --privileged=true lhrbest/greenplum:7.0.0_v2 \
  /usr/sbin/init

docker exec -it gpdb7 bash
su - gpadmin
gpstart -a
gpcc start

gpcc status
gpstate

[gpadmin@gpdb7 ~]$ psql
psql (12.12)
Type "help" for help.

postgres=# select version();
                                                                                                                      version                                                                                                                       
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.12 (Greenplum Database 7.0.0 build commit:0a7a3566873325aca1789ae6f818c80f17a9402d Open Source) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit compiled on Sep 20 2023 23:29:19 Bhuvnesh C.
(1 row)

postgres=# select * from gp_segment_configuration ;
 dbid | content | role | preferred_role | mode | status | port | hostname | address |                  datadir                   
------+---------+------+----------------+------+--------+------+----------+---------+--------------------------------------------
    1 |      -1 | p    | p              | n    | u      | 5432 | gpdb7    | gpdb7   | /opt/greenplum/data/master/gpseg-1
    6 |      -1 | m    | m              | s    | u      | 5433 | gpdb7    | gpdb7   | /opt/greenplum/data/master_standby/gpseg-1
    2 |       0 | p    | p              | n    | u      | 6000 | gpdb7    | gpdb7   | /opt/greenplum/data/primary/gpseg0
    4 |       0 | m    | m              | n    | d      | 7000 | gpdb7    | gpdb7   | /opt/greenplum/data/mirror/gpseg0
    3 |       1 | p    | p              | n    | u      | 6001 | gpdb7    | gpdb7   | /opt/greenplum/data/primary/gpseg1
    5 |       1 | m    | m              | n    | d      | 7001 | gpdb7    | gpdb7   | /opt/greenplum/data/mirror/gpseg1
(6 rows)

docker rm -f gpdb7

docker run -itd --name gpdb7 -h gpdb7 \

-p 5437:5432 -p 28087:28080 \

-v /sys/fs/cgroup:/sys/fs/cgroup \

--privileged=true lhrbest/greenplum:7.0.0_v2 \

/usr/sbin/init

docker exec -it gpdb7 bash

su - gpadmin

gpstart -a

gpcc start

gpcc status

gpstate

[gpadmin@gpdb7 ~]$ psql

psql (12.12)

Type "help" for help.

postgres=# select version();

version

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

PostgreSQL 12.12 (Greenplum Database 7.0.0 build commit:0a7a3566873325aca1789ae6f818c80f17a9402d Open Source) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit compiled on Sep 20 2023 23:29:19 Bhuvnesh C.

(1 row)

postgres=# select * from gp_segment_configuration ;

------+---------+------+----------------+------+--------+------+----------+---------+--------------------------------------------

1 | -1 | p | p | n | u | 5432 | gpdb7 | gpdb7 | /opt/greenplum/data/master/gpseg-1

6 | -1 | m | m | s | u | 5433 | gpdb7 | gpdb7 | /opt/greenplum/data/master_standby/gpseg-1

2 | 0 | p | p | n | u | 6000 | gpdb7 | gpdb7 | /opt/greenplum/data/primary/gpseg0

4 | 0 | m | m | n | d | 7000 | gpdb7 | gpdb7 | /opt/greenplum/data/mirror/gpseg0

3 | 1 | p | p | n | u | 6001 | gpdb7 | gpdb7 | /opt/greenplum/data/primary/gpseg1

5 | 1 | m | m | n | d | 7001 | gpdb7 | gpdb7 | /opt/greenplum/data/mirror/gpseg1

(6 rows)

http://192.168.8.8/28080

gpmon/lhr

重要的新特性

内核

Greenplum 7的内核从9.4.26升级到了12.12，其性能、功能均领先于Greenplum 6

自动启用vacuuum

缺省打开了autovacuum，会自动对系统表进行VACUUM和ANALYZE操作，并对用户表进行ANALYZE操作。

INSERT,UPDATE和DELETE影响的数据量大于下面的计算结果时，会自动对操作的表进行ANALYZE操作：

autovacuum_analyze_scale_factor × reltuples + autovacuum_analyze_threshold

-- 默认
0.1 * 行数 + 50

autovacuum_analyze_scale_factor × reltuples + autovacuum_analyze_threshold

-- 默认

0.1 * 行数 + 50

Automatic Vacuum is now enabled by default for all databases, which automatically performs VACUUM and ANALYZE operations against all catalog tables, as well as runs ANALYZE for all users tables in those databases.

参数含义参考： https://docs.vmware.com/en/VMware-Greenplum/7/greenplum-database/ref_guide-config_params-guc-list.html#autovacuum

[gpadmin@gpdb7 ~]$ gpconfig -s autovacuum
Values on all segments are consistent
GUC              : autovacuum
Coordinator value: on
Segment     value: on
[gpadmin@gpdb7 ~]$ gpconfig -s autovacuum_analyze_scale_factor  
Values on all segments are consistent
GUC              : autovacuum_analyze_scale_factor
Coordinator value: 0.1
Segment     value: 0.1
[gpadmin@gpdb7 ~]$ 
[gpadmin@gpdb7 ~]$ gpconfig -s autovacuum_analyze_threshold
Values on all segments are consistent
GUC              : autovacuum_analyze_threshold
Coordinator value: 50
Segment     value: 50
[gpadmin@gpdb7 ~]$ 
[gpadmin@gpdb7 ~]$ gpconfig -s autovacuum_max_workers
Values on all segments are consistent
GUC              : autovacuum_max_workers
Coordinator value: 3
Segment     value: 3
[gpadmin@gpdb7 ~]$ gpconfig -s gp_autovacuum_scope
Values on all segments are consistent
GUC              : gp_autovacuum_scope
Coordinator value: catalog
Segment     value: catalog
[gpadmin@gpdb7 ~]$ 
[gpadmin@gpdb7 ~]$ gpconfig -s autovacuum_naptime
Values on all segments are consistent
GUC              : autovacuum_naptime
Coordinator value: 1min
Segment     value: 1min
[gpadmin@gpdb7 ~]$ 
[gpadmin@gpdb7 ~]$ ps -ef|grep cuum
gpadmin     1252    1236  0 12:17 ?        00:00:00 postgres:  6000, autovacuum launcher   
gpadmin     1259    1241  0 12:17 ?        00:00:00 postgres:  6001, autovacuum launcher   
gpadmin     1279    1273  0 12:17 ?        00:00:00 postgres:  5432, autovacuum launcher   
gpadmin     2903     461  0 12:41 pts/1    00:00:00 grep --color=auto cuum
[gpadmin@gpdb7 ~]$ 

gpconfig -c gp_autostats_mode -v on_change
gpconfig -c gp_autostats_on_change_threshold -v 100000

[gpadmin@gpdb7 ~]$ gpconfig -s autovacuum

Values on all segments are consistent

GUC : autovacuum

Coordinator value: on

Segment value: on

[gpadmin@gpdb7 ~]$ gpconfig -s autovacuum_analyze_scale_factor

Values on all segments are consistent

GUC : autovacuum_analyze_scale_factor

Coordinator value: 0.1

Segment value: 0.1

[gpadmin@gpdb7 ~]$

[gpadmin@gpdb7 ~]$ gpconfig -s autovacuum_analyze_threshold

Values on all segments are consistent

GUC : autovacuum_analyze_threshold

Coordinator value: 50

Segment value: 50

[gpadmin@gpdb7 ~]$

[gpadmin@gpdb7 ~]$ gpconfig -s autovacuum_max_workers

Values on all segments are consistent

GUC : autovacuum_max_workers

Coordinator value: 3

Segment value: 3

[gpadmin@gpdb7 ~]$ gpconfig -s gp_autovacuum_scope

Values on all segments are consistent

GUC : gp_autovacuum_scope

Coordinator value: catalog

Segment value: catalog

[gpadmin@gpdb7 ~]$

[gpadmin@gpdb7 ~]$ gpconfig -s autovacuum_naptime

Values on all segments are consistent

GUC : autovacuum_naptime

Coordinator value: 1min

Segment value: 1min

[gpadmin@gpdb7 ~]$

[gpadmin@gpdb7 ~]$ ps -ef|grep cuum

gpadmin 1252 1236 0 12:17 ? 00:00:00 postgres: 6000, autovacuum launcher

gpadmin 1259 1241 0 12:17 ? 00:00:00 postgres: 6001, autovacuum launcher

gpadmin 1279 1273 0 12:17 ? 00:00:00 postgres: 5432, autovacuum launcher

gpadmin 2903 461 0 12:41 pts/1 00:00:00 grep --color=auto cuum

[gpadmin@gpdb7 ~]$

gpconfig -c gp_autostats_mode -v on_change

gpconfig -c gp_autostats_on_change_threshold -v 100000

示例：


create table t8 as select * from pg_class limit 5;
create table t9 as select * from pg_class limit 50;

select * from pg_stat_all_tables  where schemaname='public'  and relname = 't9';

SELECT * from pg_stats d where d.tablename='t9';

create table t8 as select * from pg_class limit 5;

create table t9 as select * from pg_class limit 50;

select * from pg_stat_all_tables where schemaname='public' and relname = 't9';

SELECT * from pg_stats d where d.tablename='t9';

支持仅索引扫描和覆盖索引

增加了对覆盖索引（covering index）的支持。覆盖索引允许用户使用INCLUDE 子句将额外的列添加到索引中，对于执行索引 (index-only)扫描非常有用，特别是对于那些不能被B-tree所索引的数据类型。 Greenplum 查询优化器部分支持索引扫描和覆盖索引。

注意：不能使用GitHub上的开源版本，否则不能使用仅索引扫描和覆盖索引！！！

Index-only scans can answer queries from an index alone without accessing the table’s heap, which significantly improves query performance. In addition, covering indexes allow you to add additional columns to an index using the INCLUDE clause, in order to make the use of index-only scans more effective. See Understanding Index-Only Scans and Covering Indexes for more details.

支持Index only的索引扫描，并支持CREATE INDEX的INCLUDE子句，允许在索引查询时不再回表，从而提升索引查询的性能。

测试环境：

drop table t1;
drop table t2;
drop table t3;

create table t1 (id int, c1 int, c2 int, c3 int, info text, crt_time timestamp); 
create table t2(like t1);
create table t3(like t1);  
create index idx_t1_1 on t1 (id) include(c1,c2,c3,info,crt_time);  
create index idx_t2_1 on t2 (id,c1,c2,c3,info,crt_time);  
create index idx_t3_1 on t3(id);

\timing on
insert into t1 select (1000*random())::int,1,1,1,'test',now() from generate_series(1,10000000); 
insert into t2 select (1000*random())::int,1,1,1,'test',now() from generate_series(1,10000000); 
insert into t3 select (1000*random())::int,1,1,1,'test',now() from generate_series(1,10000000); 

explain (analyze,verbose,timing,costs,buffers) select id,c1,c2,c3,info,crt_time from t1 where id=10000; 
explain (analyze,verbose,timing,costs,buffers) select id,c1,c2,c3,info,crt_time from t2 where id=10000; 
explain (analyze,verbose,timing,costs,buffers) select id,c1,c2,c3,info,crt_time from t3 where id=10000; 

analyze t1;
analyze t2;
analyze t3;

select id,c1,c2,c3,info,crt_time from t1 where id=1000; 
select id,c1,c2,c3,info,crt_time from t2 where id=1000; 
select id,c1,c2,c3,info,crt_time from t3 where id=1000; 

create table t66(id int PRIMARY key, c1 text, crt_time timestamp); 

insert into t66
SELECT id, md5(id::text),now()
FROM generate_series(1, 2000000) AS id;

create index idx_t66_1 on t66 (id) include(c1);

explain select id,c1 from t66 where id =100;

drop table t1;

drop table t2;

drop table t3;

create table t1 (id int, c1 int, c2 int, c3 int, info text, crt_time timestamp);

create table t2(like t1);

create table t3(like t1);

create index idx_t1_1 on t1 (id) include(c1,c2,c3,info,crt_time);

create index idx_t2_1 on t2 (id,c1,c2,c3,info,crt_time);

create index idx_t3_1 on t3(id);

\timing on

insert into t1 select (1000*random())::int,1,1,1,'test',now() from generate_series(1,10000000);

insert into t2 select (1000*random())::int,1,1,1,'test',now() from generate_series(1,10000000);

insert into t3 select (1000*random())::int,1,1,1,'test',now() from generate_series(1,10000000);

explain (analyze,verbose,timing,costs,buffers) select id,c1,c2,c3,info,crt_time from t1 where id=10000;

explain (analyze,verbose,timing,costs,buffers) select id,c1,c2,c3,info,crt_time from t2 where id=10000;

explain (analyze,verbose,timing,costs,buffers) select id,c1,c2,c3,info,crt_time from t3 where id=10000;

analyze t1;

analyze t2;

analyze t3;

select id,c1,c2,c3,info,crt_time from t1 where id=1000;

select id,c1,c2,c3,info,crt_time from t2 where id=1000;

select id,c1,c2,c3,info,crt_time from t3 where id=1000;

create table t66(id int PRIMARY key, c1 text, crt_time timestamp);

insert into t66

SELECT id, md5(id::text),now()

FROM generate_series(1, 2000000) AS id;

create index idx_t66_1 on t66 (id) include(c1);

explain select id,c1 from t66 where id =100;

视频：https://www.youtube.com/watch?v=J7_xLoq3E20&t=1383s

db2=# explain select id,c1,c2,c3,info,crt_time from t1 where id=1000; 
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Gather Motion 1:1  (slice1; segments: 1)  (cost=0.00..9.82 rows=9883 width=29)
   ->  Index Scan using idx_t1_1 on t1  (cost=0.00..8.26 rows=4942 width=29)
         Index Cond: (id = 1000)
 Optimizer: GPORCA
(4 rows)

Time: 2.374 ms
db2=# explain select id,c1,c2,c3,info,crt_time from t2 where id=1000; 
                                    QUERY PLAN                                    
----------------------------------------------------------------------------------
 Gather Motion 1:1  (slice1; segments: 1)  (cost=0.00..14.31 rows=10933 width=29)
   ->  Index Scan using idx_t2_1 on t2  (cost=0.00..12.59 rows=5467 width=29)
         Index Cond: (id = 1000)
 Optimizer: GPORCA
(4 rows)

Time: 3.194 ms
db2=# explain select id,c1,c2,c3,info,crt_time from t3 where id=1000;
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Gather Motion 1:1  (slice1; segments: 1)  (cost=0.00..9.40 rows=9775 width=29)
   ->  Index Scan using idx_t3_1 on t3  (cost=0.00..7.86 rows=4888 width=29)
         Index Cond: (id = 1000)
 Optimizer: GPORCA
(4 rows)

Time: 2.755 ms

db2=# explain (analyze,verbose,timing,costs,buffers) select id,c1,c2,c3,info,crt_time from t1 where id=10000; 
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Gather Motion 1:1  (slice1; segments: 1)  (cost=0.00..6.00 rows=1 width=29) (actual time=1.067..1.068 rows=0 loops=1)
   Output: id, c1, c2, c3, info, crt_time
   ->  Index Scan using idx_t1_1 on public.t1  (cost=0.00..6.00 rows=1 width=29) (actual time=0.000..0.778 rows=0 loops=1)
         Output: id, c1, c2, c3, info, crt_time
         Index Cond: (t1.id = 10000)
 Optimizer: GPORCA
 Planning Time: 1.878 ms
   (slice0)    Executor memory: 18K bytes.
   (slice1)    Executor memory: 42K bytes (seg1).
 Memory used:  128000kB
 Execution Time: 1.372 ms
(11 rows)

Time: 3.683 ms
db2=# explain (analyze,verbose,timing,costs,buffers) select id,c1,c2,c3,info,crt_time from t2 where id=10000; 
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Gather Motion 1:1  (slice1; segments: 1)  (cost=0.00..6.00 rows=1 width=29) (actual time=0.308..0.308 rows=0 loops=1)
   Output: id, c1, c2, c3, info, crt_time
   ->  Index Scan using idx_t2_1 on public.t2  (cost=0.00..6.00 rows=1 width=29) (actual time=0.000..0.082 rows=0 loops=1)
         Output: id, c1, c2, c3, info, crt_time
         Index Cond: (t2.id = 10000)
 Optimizer: GPORCA
 Planning Time: 1.940 ms
   (slice0)    Executor memory: 18K bytes.
   (slice1)    Executor memory: 42K bytes (seg1).
 Memory used:  128000kB
 Execution Time: 0.550 ms
(11 rows)

Time: 2.890 ms
db2=# explain (analyze,verbose,timing,costs,buffers) select id,c1,c2,c3,info,crt_time from t3 where id=10000; 
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Gather Motion 1:1  (slice1; segments: 1)  (cost=0.00..6.00 rows=1 width=29) (actual time=0.329..0.330 rows=0 loops=1)
   Output: id, c1, c2, c3, info, crt_time
   ->  Index Scan using idx_t3_1 on public.t3  (cost=0.00..6.00 rows=1 width=29) (actual time=0.000..0.066 rows=0 loops=1)
         Output: id, c1, c2, c3, info, crt_time
         Index Cond: (t3.id = 10000)
 Optimizer: GPORCA
 Planning Time: 1.971 ms
   (slice0)    Executor memory: 18K bytes.
   (slice1)    Executor memory: 42K bytes (seg1).
 Memory used:  128000kB
 Execution Time: 0.626 ms
(11 rows)

Time: 3.070 ms
db2=# 

db2=# explain select id,c1 from t66 where id =100;
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Gather Motion 1:1  (slice1; segments: 1)  (cost=0.00..6.00 rows=1 width=37)
   ->  Index Scan using t66_pkey on t66  (cost=0.00..6.00 rows=1 width=37)
         Index Cond: (id = 100)
 Optimizer: GPORCA
(4 rows)

Time: 3.489 ms

db2=# explain select id,c1,c2,c3,info,crt_time from t1 where id=1000;

QUERY PLAN

--------------------------------------------------------------------------------

Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..9.82 rows=9883 width=29)

-> Index Scan using idx_t1_1 on t1 (cost=0.00..8.26 rows=4942 width=29)

Index Cond: (id = 1000)

Optimizer: GPORCA

(4 rows)

Time: 2.374 ms

db2=# explain select id,c1,c2,c3,info,crt_time from t2 where id=1000;

QUERY PLAN

----------------------------------------------------------------------------------

Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..14.31 rows=10933 width=29)

-> Index Scan using idx_t2_1 on t2 (cost=0.00..12.59 rows=5467 width=29)

Index Cond: (id = 1000)

Optimizer: GPORCA

(4 rows)

Time: 3.194 ms

db2=# explain select id,c1,c2,c3,info,crt_time from t3 where id=1000;

QUERY PLAN

--------------------------------------------------------------------------------

Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..9.40 rows=9775 width=29)

-> Index Scan using idx_t3_1 on t3 (cost=0.00..7.86 rows=4888 width=29)

Index Cond: (id = 1000)

Optimizer: GPORCA

(4 rows)

Time: 2.755 ms

db2=# explain (analyze,verbose,timing,costs,buffers) select id,c1,c2,c3,info,crt_time from t1 where id=10000;

QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------

Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..6.00 rows=1 width=29) (actual time=1.067..1.068 rows=0 loops=1)

Output: id, c1, c2, c3, info, crt_time

-> Index Scan using idx_t1_1 on public.t1 (cost=0.00..6.00 rows=1 width=29) (actual time=0.000..0.778 rows=0 loops=1)

Output: id, c1, c2, c3, info, crt_time

Index Cond: (t1.id = 10000)

Optimizer: GPORCA

Planning Time: 1.878 ms

(slice0) Executor memory: 18K bytes.

(slice1) Executor memory: 42K bytes (seg1).

Memory used: 128000kB

Execution Time: 1.372 ms

(11 rows)

Time: 3.683 ms

db2=# explain (analyze,verbose,timing,costs,buffers) select id,c1,c2,c3,info,crt_time from t2 where id=10000;

QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------

Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..6.00 rows=1 width=29) (actual time=0.308..0.308 rows=0 loops=1)

Output: id, c1, c2, c3, info, crt_time

-> Index Scan using idx_t2_1 on public.t2 (cost=0.00..6.00 rows=1 width=29) (actual time=0.000..0.082 rows=0 loops=1)

Output: id, c1, c2, c3, info, crt_time

Index Cond: (t2.id = 10000)

Optimizer: GPORCA

Planning Time: 1.940 ms

(slice0) Executor memory: 18K bytes.

(slice1) Executor memory: 42K bytes (seg1).

Memory used: 128000kB

Execution Time: 0.550 ms

(11 rows)

Time: 2.890 ms

db2=# explain (analyze,verbose,timing,costs,buffers) select id,c1,c2,c3,info,crt_time from t3 where id=10000;

QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------

Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..6.00 rows=1 width=29) (actual time=0.329..0.330 rows=0 loops=1)

Output: id, c1, c2, c3, info, crt_time

-> Index Scan using idx_t3_1 on public.t3 (cost=0.00..6.00 rows=1 width=29) (actual time=0.000..0.066 rows=0 loops=1)

Output: id, c1, c2, c3, info, crt_time

Index Cond: (t3.id = 10000)

Optimizer: GPORCA

Planning Time: 1.971 ms

(slice0) Executor memory: 18K bytes.

(slice1) Executor memory: 42K bytes (seg1).

Memory used: 128000kB

Execution Time: 0.626 ms

(11 rows)

Time: 3.070 ms

db2=#

db2=# explain select id,c1 from t66 where id =100;

QUERY PLAN

-----------------------------------------------------------------------------

Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..6.00 rows=1 width=37)

-> Index Scan using t66_pkey on t66 (cost=0.00..6.00 rows=1 width=37)

Index Cond: (id = 100)

Optimizer: GPORCA

(4 rows)

Time: 3.489 ms

引入监控视图可以监控长运行的操作

引入了pg_stat_progress_vacuum/copy/create_index等进度视图。

Greenplum Database can report the progress of ANALYZE, CLUSTER, COPY, CREATE INDEX, REINDEX, and VACUUM commands during command execution. Greenplum can also report the progress of a running base backup (initiated during gprecoverseg -F) command invocation, allowing you to monitor the progress of these possibly long-running operations.

select * from gp_stat_progress_analyze;     
select * from gp_stat_progress_basebackup;  
select * from gp_stat_progress_cluster;     
select * from gp_stat_progress_copy   ;     
select * from gp_stat_progress_create_index;
select * from gp_stat_progress_vacuum   ;

select * from gp_stat_progress_analyze;

select * from gp_stat_progress_basebackup;

select * from gp_stat_progress_cluster;

select * from gp_stat_progress_copy ;

select * from gp_stat_progress_create_index;

select * from gp_stat_progress_vacuum ;

参考：

https://docs.vmware.com/en/VMware-Greenplum/7/greenplum-database/admin_guide-managing-progress_reporting.html

https://greenplum.org/progress-reporting-views-in-greenplum-7/

https://greenplum.org/partition-in-greenplum-7-recursion-and-inheritance/

新增pg_backend_memory_contexts的系统视图

新增了一个名为pg_backend_memory_contexts的系统视图，可以查看后台的内存使用情况。

SELECT * from pg_backend_memory_contexts d ORDER BY   d.total_bytes desc ;

1	SELECT * from pg_backend_memory_contexts d ORDER BY d.total_bytes desc ;

增加列不再需要重写表

Greenplum Database no longer rewrites the table when a column is added to a table (ALTER TABLE ... ADD COLUMN ...).

ALTER TABLE ... ADD COLUMN ...不再需要重写表：这个功能是PostgreSQL11引入的特性，通过在pg_attribute系统表中增加atthasmissing和attmissingval两个额外字段，所以GP7自然也支持了该功能，可以有效降低加字段所带来的影响。

Operation	AO Columnar (AOCO)	AO Row (AO)	Heap
DROP COLUMN	No	No	No
ALTER COLUMN TYPE [1]	No [2]	Yes	Yes
ADD COLUMN (w/ non-volative default [3])	No [2]	No	No
ADD COLUMN (w/ volative default)	No [2]	Yes	Yes
ALTER COLUMN SET ENCODING	No [2]	N/A	N/A
SET ()[4]	Yes	Yes	Yes
SET ACCESS METHOD	Yes	Yes	Yes

[1] If the new type is not binary coercible with the old. Otherwise, no rewrite for all cases.

[2] But write/rewrite single column data.
[3] Including NULL.
[4] If the options differ from the current table. Otherwise, no rewrite for all cases.

参考：https://greenplum.org/alter-table-in-greenplum-7-avoiding-table-rewrite/

分区表

PostgreSQL declarative table partitioning syntaxis now supported. See About Changes to Table Partitioning in Greenplum 7 for more details.

-- Assuming there's long-running insert
INSERT INTO sales SELECT * FROM ext_sales_data;

-- This will be blocked
ALTER TABLE sales ADD PARTITION march_sales START ('2023-03-01') END ('2023-04-01');

-- This will go through
ALTER TABLE sales ATTACH PARTITION march_sales FOR VALUES FROM ('2023-03-01') TO ('2023-04-01');  -- 这种方式添加分区不会被阻塞

-- Assuming there's long-running insert

INSERT INTO sales SELECT * FROM ext_sales_data;

-- This will be blocked

ALTER TABLE sales ADD PARTITION march_sales START ('2023-03-01') END ('2023-04-01');

-- This will go through

ALTER TABLE sales ATTACH PARTITION march_sales FOR VALUES FROM ('2023-03-01') TO ('2023-04-01'); -- 这种方式添加分区不会被阻塞

分区表删除的视图：

select * from pg_partition_columns;
select * from pg_partition_encoding;
select * from pg_partition_rule;
select * from pg_partition_template;
select * from pg_stat_partition_operation;

select * from pg_partition_columns;

select * from pg_partition_encoding;

select * from pg_partition_rule;

select * from pg_partition_template;

select * from pg_stat_partition_operation;

参考：

https://greenplum.org/20-examples-of-greenplum-partition-commands/

https://greenplum.org/partition-in-greenplum-7-whats-new/

https://greenplum.org/partition-in-greenplum-7-recursion-and-inheritance/

其它

创建分区表

--GBDP7版本可以先只定义父分区表，不定义子分区表
postgres=# CREATE TABLE sales (id int, date date, amt decimal(10,2))
postgres-# DISTRIBUTED BY (id)
postgres-# PARTITION BY RANGE (date);
CREATE TABLE
--GBDP7版本也可以定义父分区表的同时定义子分区表（保留了以往的经典语法）
postgres=# drop table sales ;
DROP TABLE
postgres=# CREATE TABLE sales (id int, date date, amt decimal(10,2))
postgres-# DISTRIBUTED BY (id)
postgres-# PARTITION BY RANGE (date)
postgres-# (PARTITION jan_sales START ('2023-01-01') END ('2023-02-01'),
postgres(# PARTITION feb_sales START ('2023-02-01') END ('2023-03-01'),
postgres(# DEFAULT PARTITION other_sales);
CREATE TABLE
--GPDB6版本创建分区表时则不能只定义父分区表，否则会报错
postgres=# CREATE TABLE sales (id int, date date, amt decimal(10,2))
postgres-# DISTRIBUTED BY (id)
postgres-# PARTITION BY RANGE (date);
ERROR:  no partitions specified at depth 1
LINE 3: PARTITION BY RANGE (date);

--GBDP7版本可以先只定义父分区表，不定义子分区表

postgres=# CREATE TABLE sales (id int, date date, amt decimal(10,2))

postgres-# DISTRIBUTED BY (id)

postgres-# PARTITION BY RANGE (date);

CREATE TABLE

--GBDP7版本也可以定义父分区表的同时定义子分区表（保留了以往的经典语法）

postgres=# drop table sales ;

DROP TABLE

postgres=# CREATE TABLE sales (id int, date date, amt decimal(10,2))

postgres-# DISTRIBUTED BY (id)

postgres-# PARTITION BY RANGE (date)

postgres-# (PARTITION jan_sales START ('2023-01-01') END ('2023-02-01'),

postgres(# PARTITION feb_sales START ('2023-02-01') END ('2023-03-01'),

postgres(# DEFAULT PARTITION other_sales);

CREATE TABLE

--GPDB6版本创建分区表时则不能只定义父分区表，否则会报错

postgres=# CREATE TABLE sales (id int, date date, amt decimal(10,2))

postgres-# DISTRIBUTED BY (id)

postgres-# PARTITION BY RANGE (date);

ERROR: no partitions specified at depth 1

LINE 3: PARTITION BY RANGE (date);

添加新的子分区

--GPDB7版本创建新表并将其添加为新的子分区有两种语法，一个是使用FOR VALUES FROM ... TO ，一个是...START ... END ()
postgres=# CREATE TABLE Mar_sales PARTITION OF sales FOR VALUES FROM ('2023-03-01') TO ('2023-04-01');
NOTICE:  table has parent, setting distribution columns to match parent table
CREATE TABLE
postgres=# ALTER TABLE sales ADD PARTITION Apr_sales
START ('2023-04-01') END ('2023-04-30');
ALTER TABLE
--GPDB6版本添加新的子分区则只有使用...START ... END ()，使用FOR VALUES FROM ... TO 则报错
postgres=# ALTER TABLE sales ADD PARTITION Apr_sales
postgres-# START ('2023-04-01') END ('2023-04-30');
NOTICE:  CREATE TABLE will create partition "sales_1_prt_apr_sales" for table "sales"
ALTER TABLE
postgres=# ALTER TABLE sales ADD PARTITION Apr_sales
postgres-# FOR VALUES FROM ('2023-04-01') TO ('2023-04-30');
ERROR:  syntax error at or near "FOR"
LINE 2: FOR VALUES FROM ('2023-04-01') TO ('2023-04-30');
--GPDB7版本将现有表添加为新的子分区
postgres=# CREATE TABLE Apr_sales (LIKE sales);
NOTICE:  table doesn't have 'DISTRIBUTED BY' clause, defaulting to distribution columns from LIKE table
CREATE TABLE
postgres=# ALTER TABLE sales ATTACH PARTITION Apr_sales  FOR VALUES FROM ('2024-04-01') TO ('2024-05-01');
ALTER TABLE
--GPDB6版本则不支持该语法
postgres=#  ALTER TABLE sales ATTACH PARTITION Apr_sales  FOR VALUES FROM ('2024-04-01') TO ('2024-05-01');
ERROR:  syntax error at or near "ATTACH"
LINE 1: ALTER TABLE sales ATTACH PARTITION Apr_sales  FOR VALUES FRO...

--GPDB7版本创建新表并将其添加为新的子分区有两种语法，一个是使用FOR VALUES FROM ... TO ，一个是...START ... END ()

postgres=# CREATE TABLE Mar_sales PARTITION OF sales FOR VALUES FROM ('2023-03-01') TO ('2023-04-01');

NOTICE: table has parent, setting distribution columns to match parent table

CREATE TABLE

postgres=# ALTER TABLE sales ADD PARTITION Apr_sales

START ('2023-04-01') END ('2023-04-30');

ALTER TABLE

--GPDB6版本添加新的子分区则只有使用...START ... END ()，使用FOR VALUES FROM ... TO 则报错

postgres=# ALTER TABLE sales ADD PARTITION Apr_sales

postgres-# START ('2023-04-01') END ('2023-04-30');

NOTICE: CREATE TABLE will create partition "sales_1_prt_apr_sales" for table "sales"

ALTER TABLE

postgres=# ALTER TABLE sales ADD PARTITION Apr_sales

postgres-# FOR VALUES FROM ('2023-04-01') TO ('2023-04-30');

ERROR: syntax error at or near "FOR"

LINE 2: FOR VALUES FROM ('2023-04-01') TO ('2023-04-30');

--GPDB7版本将现有表添加为新的子分区

postgres=# CREATE TABLE Apr_sales (LIKE sales);

NOTICE: table doesn't have 'DISTRIBUTED BY' clause, defaulting to distribution columns from LIKE table

CREATE TABLE

postgres=# ALTER TABLE sales ATTACH PARTITION Apr_sales FOR VALUES FROM ('2024-04-01') TO ('2024-05-01');

ALTER TABLE

--GPDB6版本则不支持该语法

postgres=# ALTER TABLE sales ATTACH PARTITION Apr_sales FOR VALUES FROM ('2024-04-01') TO ('2024-05-01');

ERROR: syntax error at or near "ATTACH"

LINE 1: ALTER TABLE sales ATTACH PARTITION Apr_sales FOR VALUES FRO...

删除子分区

--GPDB7版本除使用DROP  PARTITION外，还可以使用DETACH PARTITION
postgres=# alter table sales DROP PARTITION other_sales;
ALTER TABLE
postgres=# ALTER TABLE sales DETACH PARTITION Mar_sales;
ALTER TABLE
--！！！需要注意的是，使用DROP  PARTITION是将分区表直接删除，而DETACH PARTITION只是将分区表从分区结构中删除，但表任然在数据库中**
postgres=# \d+ sales
                                Partitioned table "public.sales"
 Column |     Type      | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------------+-----------+----------+---------+---------+--------------+-------------
 id     | integer       |           |          |         | plain   |              | 
 date   | date          |           |          |         | plain   |              | 
 amt    | numeric(10,2) |           |          |         | main    |              | 
Partition key: RANGE (date)
Partitions: sales_1_prt_feb_sales FOR VALUES FROM ('2023-02-01') TO ('2023-03-01'),
            sales_1_prt_jan_sales FOR VALUES FROM ('2023-01-01') TO ('2023-02-01')
Distributed by: (id)
Access method: heap
postgres=# \d other_sales
Did not find any relation named "other_sales".
postgres=# \d mar_sales 
                Table "public.mar_sales"
 Column |     Type      | Collation | Nullable | Default 
--------+---------------+-----------+----------+---------
 id     | integer       |           |          |
 date   | date          |           |          |
 amt    | numeric(10,2) |           |          |
Distributed by: (id)
--GPDB6版本只能使用DROP  PARTITION
postgres=#  ALTER TABLE sales DROP  PARTITION Apr_sales ;
ALTER TABLE
postgres=# ALTER TABLE sales DETACH PARTITION Mar_sales;
ERROR:  syntax error at or near "DETACH"
LINE 1: ALTER TABLE sales DETACH PARTITION Mar_sales;

--GPDB7版本除使用DROP PARTITION外，还可以使用DETACH PARTITION

postgres=# alter table sales DROP PARTITION other_sales;

ALTER TABLE

postgres=# ALTER TABLE sales DETACH PARTITION Mar_sales;

ALTER TABLE

--！！！需要注意的是，使用DROP PARTITION是将分区表直接删除，而DETACH PARTITION只是将分区表从分区结构中删除，但表任然在数据库中**

postgres=# \d+ sales

Partitioned table "public.sales"

--------+---------------+-----------+----------+---------+---------+--------------+-------------

Partition key: RANGE (date)

Partitions: sales_1_prt_feb_sales FOR VALUES FROM ('2023-02-01') TO ('2023-03-01'),

sales_1_prt_jan_sales FOR VALUES FROM ('2023-01-01') TO ('2023-02-01')

Distributed by: (id)

Access method: heap

postgres=# \d other_sales

Did not find any relation named "other_sales".

postgres=# \d mar_sales

Table "public.mar_sales"

Column | Type | Collation | Nullable | Default

--------+---------------+-----------+----------+---------

id | integer | | |

date | date | | |

amt | numeric(10,2) | | |

Distributed by: (id)

--GPDB6版本只能使用DROP PARTITION

postgres=# ALTER TABLE sales DROP PARTITION Apr_sales ;

ALTER TABLE

postgres=# ALTER TABLE sales DETACH PARTITION Mar_sales;

ERROR: syntax error at or near "DETACH"

LINE 1: ALTER TABLE sales DETACH PARTITION Mar_sales;

将现有分区与另一个表交换

--GPDB6和GPDB7都先创建一个常规表
postgres=# create table test (like sales);
NOTICE:  table doesn't have 'DISTRIBUTED BY' clause, defaulting to distribution columns from LIKE table
CREATE TABLE

--GPDB6查看当前分区情况
postgres=# \d+ sales
                           Table "public.sales"
 Column |     Type      | Modifiers | Storage | Stats target | Description 
--------+---------------+-----------+---------+--------------+-------------
 id     | integer       |           | plain   |              | 
 date   | date          |           | plain   |              | 
 amt    | numeric(10,2) |           | main    |              | 
Child tables: sales_1_prt_feb_sales,
              sales_1_prt_jan_sales
Distributed by: (id)
Partition by: (date)
--GPDB6将现有分区feb_sales替换为test
postgres=# ALTER TABLE sales EXCHANGE PARTITION feb_sales WITH TABLE test;
ALTER TABLE
--GPDB6再次查看分区情况时，可以看到仍和原来一样
postgres=# \d+ sales
                           Table "public.sales"
 Column |     Type      | Modifiers | Storage | Stats target | Description 
--------+---------------+-----------+---------+--------------+-------------
 id     | integer       |           | plain   |              | 
 date   | date          |           | plain   |              | 
 amt    | numeric(10,2) |           | main    |              | 
Child tables: sales_1_prt_feb_sales,
              sales_1_prt_jan_sales
Distributed by: (id)
Partition by: (date)
--GPDB7查看当前分区情况
postgres=# \d+ sales
                                Partitioned table "public.sales"
 Column |     Type      | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------------+-----------+----------+---------+---------+--------------+-------------
 id     | integer       |           |          |         | plain   |              |
 date   | date          |           |          |         | plain   |              |
 amt    | numeric(10,2) |           |          |         | main    |              |
Partition key: RANGE (date)
Partitions: sales_1_prt_feb_sales FOR VALUES FROM ('2023-02-01') TO ('2023-03-01'),
            sales_1_prt_sales_1_prt_jan_sales FOR VALUES FROM ('2023-01-01') TO ('2023-01-31')
Distributed by: (id)
Access method: heap
--除去和GPDB6一样的可以用EXCHANGE PARTITION外，还可以使用DETACH PARTITION和 ATTACH PARTITION
postgres=# ALTER TABLE sales DETACH PARTITION sales_1_prt_feb_sales;
ALTER TABLE
postgres=# ALTER TABLE sales ATTACH PARTITION test FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');
ALTER TABLE
--再次查看分区情况时可以发现sales_1_prt_feb_sales已经替换为test了。
postgres=# \d+ sales
                                Partitioned table "public.sales"
 Column |     Type      | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------------+-----------+----------+---------+---------+--------------+-------------
 id     | integer       |           |          |         | plain   |              |
 date   | date          |           |          |         | plain   |              |
 amt    | numeric(10,2) |           |          |         | main    |              |
Partition key: RANGE (date)
Partitions: sales_1_prt_sales_1_prt_jan_sales FOR VALUES FROM ('2023-01-01') TO ('2023-01-31'),
            test FOR VALUES FROM ('2023-02-01') TO ('2023-03-01')
Distributed by: (id)
Access method: heap

--GPDB6和GPDB7都先创建一个常规表

postgres=# create table test (like sales);

NOTICE: table doesn't have 'DISTRIBUTED BY' clause, defaulting to distribution columns from LIKE table

CREATE TABLE

--GPDB6查看当前分区情况

postgres=# \d+ sales

Table "public.sales"

--------+---------------+-----------+---------+--------------+-------------

Child tables: sales_1_prt_feb_sales,

sales_1_prt_jan_sales

Distributed by: (id)

Partition by: (date)

--GPDB6将现有分区feb_sales替换为test

postgres=# ALTER TABLE sales EXCHANGE PARTITION feb_sales WITH TABLE test;

ALTER TABLE

--GPDB6再次查看分区情况时，可以看到仍和原来一样

postgres=# \d+ sales

Table "public.sales"

--------+---------------+-----------+---------+--------------+-------------

Child tables: sales_1_prt_feb_sales,

sales_1_prt_jan_sales

Distributed by: (id)

Partition by: (date)

--GPDB7查看当前分区情况

postgres=# \d+ sales

Partitioned table "public.sales"

--------+---------------+-----------+----------+---------+---------+--------------+-------------

Partition key: RANGE (date)

Partitions: sales_1_prt_feb_sales FOR VALUES FROM ('2023-02-01') TO ('2023-03-01'),

sales_1_prt_sales_1_prt_jan_sales FOR VALUES FROM ('2023-01-01') TO ('2023-01-31')

Distributed by: (id)

Access method: heap

--除去和GPDB6一样的可以用EXCHANGE PARTITION外，还可以使用DETACH PARTITION和 ATTACH PARTITION

postgres=# ALTER TABLE sales DETACH PARTITION sales_1_prt_feb_sales;

ALTER TABLE

postgres=# ALTER TABLE sales ATTACH PARTITION test FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');

ALTER TABLE

--再次查看分区情况时可以发现sales_1_prt_feb_sales已经替换为test了。

postgres=# \d+ sales

Partitioned table "public.sales"

--------+---------------+-----------+----------+---------+---------+--------------+-------------

Partition key: RANGE (date)

Partitions: sales_1_prt_sales_1_prt_jan_sales FOR VALUES FROM ('2023-01-01') TO ('2023-01-31'),

test FOR VALUES FROM ('2023-02-01') TO ('2023-03-01')

Distributed by: (id)

Access method: heap

！！！！这里需要注意的是分区名和表名的区别，使用旧语法创建或者添加子分区的情况下会添加指定的前缀，例如：sales*1_prt_feb_sales中sales_1_prt*是前缀，sales_1_prt_feb_sales是分区名，而表名则是feb_sales 。而在新语法中则是将指定为表名的名称视为表名，没有前缀，例如分区表test。以上例子中sales_1_prt_sales_1_prt_jan_sales和test是分别用新旧两种语法创建的分区表，虽然同为sales的分区表，但是很难将两者联系到一起。因此，建议对于同一分区表，尽量使用相同的新语法或旧语法，以避免名称歧义。

虽然新语法的专业性较低，但一旦熟悉了用法，它们的通用性将能够更轻松地实现更复杂的层次结构。当然如果习惯了旧语法，GPDB7也是兼容的，可以继续使用。
新旧语法比较表

用例	旧语法	新语法
与父分区一起创建子分区	CREATE TABLE … (PARTITION …)	CREATE TABLE … PARTITION BY和 CREATE TABLE … PARTITION OF
创建和添加分区	ALTER TABLE … ADD PARTITION	CREATE TABLE … PARTITION OF
将现有分区子级与常规表交换	ALTER TABLE … EXCHANGE PARTITION	ALTER TABLE … DETACH PARTITION和 ATTACH PARTITION
删除分区	ALTER TABLE … DROP PARTITION	DROP TABLE
拆分分区	ALTER TABLE … SPLIT PARTITION	DETACH 分区和新分区分开ATTACH

在GPDB7版本中，不再支持多列分区（GPDB6版本也不支持）

postgres=# CREATE TABLE foo (a int, b int, c int) PARTITION BY list (b,c);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' 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.
ERROR:  cannot use "list" partition strategy with more than one column

postgres=# CREATE TABLE foo (a int, b int, c int) PARTITION BY list (b,c);

NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' 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.

ERROR: cannot use "list" partition strategy with more than one column

但可以创建一个复合类型并将其用作分区键（GPDB6版本不支持）

postgres=# CREATE TYPE partkey as (b int, c int);
CREATE TYPE
postgres=# CREATE TABLE foo (a int, b int, c int)
postgres-# PARTITION BY LIST ((row(b, c)::partkey));
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' 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=# \d+ foo 
                              Partitioned table "public.foo"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+---------+--------------+-------------
 a      | integer |           |          |         | plain   |              | 
 b      | integer |           |          |         | plain   |              | 
 c      | integer |           |          |         | plain   |              | 
Partition key: LIST ((ROW(b, c)::partkey))
Number of partitions: 0
Distributed by: (a)
Access method: heap

postgres=# CREATE TYPE partkey as (b int, c int);

CREATE TYPE

postgres=# CREATE TABLE foo (a int, b int, c int)

postgres-# PARTITION BY LIST ((row(b, c)::partkey));

NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' 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=# \d+ foo

Partitioned table "public.foo"

--------+---------+-----------+----------+---------+---------+--------------+-------------

Partition key: LIST ((ROW(b, c)::partkey))

Number of partitions: 0

Distributed by: (a)

Access method: heap

另外和分区表相关的更新不止是SQL语法上的更新，虽然GPDB7是基于PostgreSQL12.12版本，但是pg_dump命令是和PostgreSQL最新版本pg_dump中的选项一样是可以支持父表和分区表同时转储的

[gpadmin@gp7_rh8_mas201 ~]$ pg_dump --version
pg_dump (PostgreSQL) 12.12
[gpadmin@gp7_rh8_mas201 ~]$ pg_dump --help |grep children
  --exclude-table-and-children=PATTERN
  --exclude-table-data-and-children=PATTERN
  --table-and-children=PATTERN dump only the specified table(s),
[gpadmin@gp7_rh8_mas201 ~]$ pg_dump  --table-and-children=sales -f 20231116.sql
[gpadmin@gp7_rh8_mas201 ~]$ more 20231116.sql 
--
-- Greenplum Database database dump
--
-- Dumped from database version 12.12
-- Dumped by pg_dump version 12.12
SET gp_default_storage_options = '';
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
SET default_table_access_method = heap;
--
-- Name: sales; Type: TABLE; Schema: public; Owner: gpadmin
--
CREATE TABLE public.sales (
    id integer,
    date date,
    amt numeric(10,2)
)
PARTITION BY RANGE (date) DISTRIBUTED BY (id);
ALTER TABLE public.sales OWNER TO gpadmin;
--
-- Name: sales_1_prt_sales_1_prt_jan_sales; Type: TABLE; Schema: public; Owner: gpadmin
--
CREATE TABLE public.sales_1_prt_sales_1_prt_jan_sales (
    id integer,
    date date,
    amt numeric(10,2)
) DISTRIBUTED BY (id);
ALTER TABLE public.sales_1_prt_sales_1_prt_jan_sales OWNER TO gpadmin;
--
-- Name: test; Type: TABLE; Schema: public; Owner: gpadmin
--
CREATE TABLE public.test (
    id integer,
    date date,
    amt numeric(10,2)
) DISTRIBUTED BY (id);
ALTER TABLE public.test OWNER TO gpadmin;
--
-- Name: sales_1_prt_sales_1_prt_jan_sales; Type: TABLE ATTACH; Schema: public; Owner: gpadmin
--
ALTER TABLE ONLY public.sales ATTACH PARTITION public.sales_1_prt_sales_1_prt_jan_sales FOR VALUES FROM ('2023-0
1-01') TO ('2023-01-31');
--
-- Name: test; Type: TABLE ATTACH; Schema: public; Owner: gpadmin
--
ALTER TABLE ONLY public.sales ATTACH PARTITION public.test FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');
--
-- Data for Name: sales_1_prt_sales_1_prt_jan_sales; Type: TABLE DATA; Schema: public; Owner: gpadmin
--
COPY public.sales_1_prt_sales_1_prt_jan_sales (id, date, amt) FROM stdin;
\.
--
-- Data for Name: test; Type: TABLE DATA; Schema: public; Owner: gpadmin
--
COPY public.test (id, date, amt) FROM stdin;
\.
--
-- Greenplum Database database dump complete
--
--从文件内容可以看到父表和子表都全部转储。

[gpadmin@gp7_rh8_mas201 ~]$ pg_dump --version

pg_dump (PostgreSQL) 12.12

[gpadmin@gp7_rh8_mas201 ~]$ pg_dump --help |grep children

--exclude-table-and-children=PATTERN

--exclude-table-data-and-children=PATTERN

--table-and-children=PATTERN dump only the specified table(s),

[gpadmin@gp7_rh8_mas201 ~]$ pg_dump --table-and-children=sales -f 20231116.sql

[gpadmin@gp7_rh8_mas201 ~]$ more 20231116.sql

-- Greenplum Database database dump

-- Dumped from database version 12.12

-- Dumped by pg_dump version 12.12

SET gp_default_storage_options = '';

SET statement_timeout = 0;

SET lock_timeout = 0;

SET idle_in_transaction_session_timeout = 0;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = on;

SELECT pg_catalog.set_config('search_path', '', false);

SET check_function_bodies = false;

SET xmloption = content;

SET client_min_messages = warning;

SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

-- Name: sales; Type: TABLE; Schema: public; Owner: gpadmin

CREATE TABLE public.sales (

id integer,

date date,

amt numeric(10,2)

)

PARTITION BY RANGE (date) DISTRIBUTED BY (id);

ALTER TABLE public.sales OWNER TO gpadmin;

-- Name: sales_1_prt_sales_1_prt_jan_sales; Type: TABLE; Schema: public; Owner: gpadmin

CREATE TABLE public.sales_1_prt_sales_1_prt_jan_sales (

id integer,

date date,

amt numeric(10,2)

) DISTRIBUTED BY (id);

ALTER TABLE public.sales_1_prt_sales_1_prt_jan_sales OWNER TO gpadmin;

-- Name: test; Type: TABLE; Schema: public; Owner: gpadmin

CREATE TABLE public.test (

id integer,

date date,

amt numeric(10,2)

) DISTRIBUTED BY (id);

ALTER TABLE public.test OWNER TO gpadmin;

-- Name: sales_1_prt_sales_1_prt_jan_sales; Type: TABLE ATTACH; Schema: public; Owner: gpadmin

ALTER TABLE ONLY public.sales ATTACH PARTITION public.sales_1_prt_sales_1_prt_jan_sales FOR VALUES FROM ('2023-0

1-01') TO ('2023-01-31');

-- Name: test; Type: TABLE ATTACH; Schema: public; Owner: gpadmin

ALTER TABLE ONLY public.sales ATTACH PARTITION public.test FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');

-- Data for Name: sales_1_prt_sales_1_prt_jan_sales; Type: TABLE DATA; Schema: public; Owner: gpadmin

COPY public.sales_1_prt_sales_1_prt_jan_sales (id, date, amt) FROM stdin;

-- Data for Name: test; Type: TABLE DATA; Schema: public; Owner: gpadmin

COPY public.test (id, date, amt) FROM stdin;

-- Greenplum Database database dump complete

--从文件内容可以看到父表和子表都全部转储。

Example 1: Creating a Range Partitioned Table

This example creates a sales table partitioned by range using the sale_date column.

-- (GP7 only)
CREATE TABLE sales(
    sale_id SERIAL,
    sale_date DATE,
    amount DECIMAL
) PARTITION BY RANGE (sale_date);

-- (GP7 only)

CREATE TABLE sales(

sale_id SERIAL,

sale_date DATE,

amount DECIMAL

) PARTITION BY RANGE (sale_date);

Then adds a new partition to the sales table for the first half of 2023.

-- (GP7 only)
CREATE TABLE sales_p1
PARTITION OF sales FOR VALUES FROM ('2023-01-01') TO ('2023-06-30');

-- (GP7 only)

CREATE TABLE sales_p1

PARTITION OF sales FOR VALUES FROM ('2023-01-01') TO ('2023-06-30');

Example 2: Classic Range Partitioned Table

For convenience, a single command can be used.

CREATE TABLE sales(
    sale_id SERIAL,
    sale_date DATE,
    amount DECIMAL
) PARTITION BY RANGE (sale_date)
(
    PARTITION p1 START ('2023-01-01') END ('2023-06-30')
);

CREATE TABLE sales(

sale_id SERIAL,

sale_date DATE,

amount DECIMAL

) PARTITION BY RANGE (sale_date)

(

PARTITION p1 START ('2023-01-01') END ('2023-06-30')

);

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

获取验证码：请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

打赏赞(2)

标签： GreenPlum 整理自网络新特性 GPDB 7

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

合 Greenplum 7 新特性整理

新特性总结

快速拥有GPDB 7和gpcc 7环境

重要的新特性

内核

自动启用vacuuum

支持仅索引扫描和覆盖索引

引入监控视图可以监控长运行的操作

新增pg_backend_memory_contexts的系统视图

增加列不再需要重写表

分区表

其它

Example 1: Creating a Range Partitioned Table

Example 2: Classic Range Partitioned Table

相关文章

您可能还喜欢...

发表回复取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

2024 年 11 月
一	二	三	四	五	六	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

合 Greenplum 7 新特性整理

新特性总结

快速拥有GPDB 7和gpcc 7环境

重要的新特性

内核

自动启用vacuuum

支持仅索引扫描和覆盖索引

引入监控视图可以监控长运行的操作

新增pg_backend_memory_contexts的系统视图

增加列不再需要重写表

分区表

其它

Example 1: Creating a Range Partitioned Table

Example 2: Classic Range Partitioned Table

相关文章

您可能还喜欢...

使用gpmon登陆gpcc报错Error: User gpmon is not allowed to login Command Center.

MinIO_S3的简介和安装使用

GreenPlum查询数据库大小

发表回复 取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

发表回复取消回复