greenplum 常用整理

创建表：

DROP TABLE IF EXISTS "ff"."newtable";
CREATE TABLE "ff"."newtable" (
"id" int4,
"name" varchar(64) COLLATE "pg_catalog"."default",
"fdate" varchar(64) COLLATE "pg_catalog"."default"
)
;
COMMENT ON COLUMN "ff"."newtable"."name" IS 'aaaa';

插入数据：

INSERT INTO "ff"."newtable" VALUES (1, 'new', NULL);

！！！GreenPlum查看表和数据库大小
-- 表大小

select pg_size_pretty(pg_relation_size('daas.ff.newtable_1_prt_default_p'));

pg_size_pretty 
----------------
256 kB

-- 表和索引

select pg_size_pretty(pg_total_relation_size('daas.ff.newtable_1_prt_default_p'));

pg_size_pretty 
----------------
256 kB

-- 查看指定数据库
select pg_size_pretty(pg_database_size('daas'));

pg_size_pretty 
----------------
62  MB

-- 所有数据库

select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;

datname   | pg_size_pretty 

template1	|49 MB
template0	|49 MB
postgres	|49 MB
daas			|62 MB
test			|49 MB

-- 查看数据分布情况和磁盘空间

select gp_segment_id,count(*) from pg_database group by gp_segment_id order by 1;

gp_segment_id |  count  
---------------+---------
	-1					 |5

-- 检查磁盘空间使用，GP里面就可以查看到对应分区的使用情况

select dfhostname, dfspace,dfdevice from gp_toolkit.gp_disk_free order by dfhostname;

dfhostname | dfspace  |  dfdevice  
------------+----------+------------
dev-hwc-gy1-2048-daas-test-220-deepexi	814937232	 /dev/vda1
dev-hwc-gy1-2048-daas-test-220-deepexi	814937232	 /dev/vda1