(一)数据库对象.doc

GPDB管理员笔记（一）数据库对象 分类： Greenplum 2014-03-10 13:25 1363人阅读 评论(0) 收藏 举报 数据库databasegreenplum数据库对象管理 1、创建数据库 create database new_dbname; createdb -h localhost -p 5432 mydb2、克隆数据库3、查看数据libo=# l List of databases Name | Owner | Encoding | Access privileges-+-+-+-libo | gpadmin | UTF8 |postgres | gpadmin | UTF8 |template0 | gpadmin | UTF8 | =c/gpadmin : gpadmin=CTc/gpadmintemplate1 | gpadmin | UTF8 | =c/gpadmin : gpadmin=CTc/gpadmin(4 rows)select * from pg_database;4、数据库属性变更libo=# alter database libo owner to libo;ALTER DATABASElibo=# l List of databases Name | Owner | Encoding | Access privileges-+-+-+-libo | libo | UTF8 |postgres | gpadmin | UTF8 |template0 | gpadmin | UTF8 | =c/gpadmin : gpadmin=CTc/gpadmintemplate1 | gpadmin | UTF8 | =c/gpadmin : gpadmin=CTc/gpadmin(4 rows)5、使用gpfilespace创建文件系统gpadminmdw $ gpfilespace -o gpfilespace_config20140303:10:43:03:012223 gpfilespace:mdw:gpadmin-INFO:-A tablespace requires a file system location to store its databasefiles. A filespace is a collection of file system locations for all componentsin a Greenplum system (primary segment, mirror segment and master instances).Once a filespace is created, it can be used by one or more tablespaces.20140303:10:43:03:012223 gpfilespace:mdw:gpadmin-INFO:-getting configEnter a name for this filespace libodiskChecking your configuration:Your system has 2 hosts with 2 primary and 2 mirror segments per host.Your system has 1 hosts with 0 primary and 0 mirror segments per host.Configuring hosts: sdw2, sdw1Please specify 2 locations for the primary segments, one per line:primary location 1 /home/gpadmin/GPDB/data/d1Error sdw2: /home/gpadmin/GPDB/data/d1/gpseg2 - Directory conflicts with existing datadirprimary location 1 /home/gpadmin/GPDB/data/d1Error sdw2: /home/gpadmin/GPDB/data/d1/gpseg2 - Directory conflicts with existing datadirprimary location 1 /home/gpadmin/GPDB/data/d1Error sdw2: /home/gpadmin/GPDB/data/d1/gpseg2 - Directory conflicts with existing datadirprimary location 1 /home/gpadmin/GPDB/data/d2Error sdw2: /home/gpadmin/GPDB/data/d2/gpseg3 - Directory conflicts with existing datadirprimary location 1primary location 1primary location 1primary location 1primary location 1primary location 1 /home/gpadmin/GPDB/data/d1/gpseg0Error sdw2: /home/gpadmin/GPDB/data/d1/gpseg0 : No such file or directoryprimary location 1 /home/gpadmin/GPDB/data/d1/gpseg2Error sdw2: /home/gpadmin/GPDB/data/d1/gpseg2/gpseg2 - Subdirectory of existing datadirprimary location 1 /home/gpadmin/GPDB/data/d1/Error sdw2: /home/gpadmin/GPDB/data/d1/gpseg2 - Directory conflicts with existing datadirprimary location 1 /home/gpadmin/GPDB/data/d3Error sdw2: /home/gpadmin/GPDB/data/d3 : No such file or directoryprimary location 1 /home/gpadmin/GPDB/data/d3Error sdw1: /home/gpadmin/GPDB/data/d3 : No such file or directoryprimary location 1 /home/gpadmin/GPDB/data/d3primary location 2 /home/gpadmin/GPDB/data/d3Please specify 2 locations for the mirror segments, one per line:mirror location 1 /home/gpadmin/GPDB/data/m3mirror location 2 /home/gpadmin/GPDB/data/m3Configuring hosts: mdwEnter a file system location for the mastermaster location /home/gpadmin/GPDB/data/master20140303:10:51:07:012223 gpfilespace:mdw:gpadmin-INFO:-Creating configuration file.20140303:10:51:07:012223 gpfilespace:mdw:gpadmin-INFO:-created20140303:10:51:07:012223 gpfilespace:mdw:gpadmin-INFO:-To add this filespace to the database please run the command: gpfilespace -config /home/gpadmin/gpfilespace_configgpadminmdw $ gpfilespace -c gpfilespace_config20140303:10:51:29:012482 gpfilespace:mdw:gpadmin-INFO:-A tablespace requires a file system location to store its databasefiles. A filespace is a collection of file system locations for all componentsin a Greenplum system (primary segment, mirror segment and master instances).Once a filespace is created, it can be used by one or more tablespaces.20140303:10:51:30:012482 gpfilespace:mdw:gpadmin-INFO:-getting configReading Configuration file: gpfilespace_config20140303:10:51:30:012482 gpfilespace:mdw:gpadmin-INFO:-Performing validation on paths.20140303:10:51:30:012482 gpfilespace:mdw:gpadmin-INFO:-Connecting to database20140303:10:51:31:012482 gpfilespace:mdw:gpadmin-INFO:-Filespace libodisk successfully created 创建表空间libo=# create tablespace libospace filespace libodisk;CREATE TABLESPACElibo=# grant create on tablespace libospace to libo;GRANTlibo=# set default_tablespace=libospace;SETlibo=# create table test (id int);NOTICE: Table doesnt 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 TABLElibo=# drop table test;DROP TABLElibo=# create table test (i int);NOTICE: Table doesnt have DISTRIBUTED BY clause - Using column named i 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查看现有表空间和空间文件：SELECT spcname as tblspc, fsname as filespc, fsedbid as seg_dbid, fselocation as datadirFROM pg_tablespace pgts, pg_filespace pgfs, pg_filespace_entry pgfseWHERE pgts.spcfsoid=pgfse.fsefsoid AND pgfse.fsefsoid=pgfs.oid ORDER BY tblspc, seg_dbid; tblspc | filespc | seg_dbid | datadir-+-+-+-libospace | libodisk | 1 | /home/gpadmin/GPDB/data/master/gpseg-1libospace | libodisk | 2 | /home/gpadmin/GPDB/data/d3/gpseg0libospace | libodisk | 3 | /home/gpadmin/GPDB/data/d3/gpseg1libospace | libodisk | 4 | /home/gpadmin/GPDB/data/d3/gpseg2libospace | libodisk | 5 | /home/gpadmin/GPDB/data/d3/gpseg3libospace | libodisk | 6 | /home/gpadmin/GPDB/data/m3/gpseg0libospace | libodisk | 7 | /home/gpadmin/GPDB/data/m3/gpseg1libospace | libodisk | 8 | /home/gpadmin/GPDB/data/m3/gpseg2libospace | libodisk | 9 | /home/gpadmin/GPDB/data/m3/gpseg3pg_default | pg_system | 1 | /home/gpadmin/GPDB/data/gpseg-1pg_default | pg_system | 2 | /home/gpadmin/GPDB/data/d1/gpseg0pg_default | pg_system | 3 | /home/gpadmin/GPDB/data/d2/gpseg1pg_default | pg_system | 4 | /home/gpadmin/GPDB/data/d1/gpseg2pg_default | pg_system | 5 | /home/gpadmin/GPDB/data/d2/gpseg3pg_default | pg_system | 6 | /home/gpadmin/GPDB/data/m1/gpseg0pg_default | pg_system | 7 | /home/gpadmin/GPDB/data/m2/gpseg1pg_default | pg_system | 8 | /home/gpadmin/GPDB/data/m1/gpseg2pg_default | pg_system | 9 | /home/gpadmin/GPDB/data/m2/gpseg3pg_global | pg_system | 1 | /home/gpadmin/GPDB/data/gpseg-1pg_global | pg_system | 2 | /home/gpadmin/GPDB/data/d1/gpseg0pg_global | pg_system | 3 | /home/gpadmin/GPDB/data/d2/gpseg1pg_global | pg_system | 4 | /home/gpadmin/GPDB/data/d1/gpseg2pg_global | pg_system | 5 | /home/gpadmin/GPDB/data/d2/gpseg3pg_global | pg_system | 6 | /home/gpadmin/GPDB/data/m1/gpseg0pg_global | pg_system | 7 | /home/gpadmin/GPDB/data/m2/gpseg1pg_global | pg_system | 8 | /home/gpadmin/GPDB/data/m1/gpseg2pg_global | pg_system | 9 | /home/gpadmin/GPDB/data/m2/gpseg3(27 rows)查看当前的shemalibo=# select current_schema();current_schema-public(1 row)创建表声明分布键= CREATE TABLE products (name varchar(40), prod_id integer, supplier_id integer)DISTRIBUTED BY (prod_id);= CREATE TABLE random_stuff (things text, doodads text, etc text)DISTRIBUTED RANDOMLY;选择表的存储模式GPDB提供几种灵活的存储处理模式(或者混合模式)。在创建一张新的TABLE时，有几个选项来决定数据如何储存在磁盘上。本节介绍这几种选项，以及出于工作负载的考虑如何实现最佳的储存模式。l 选择堆存储(Heap)或只追加(Append-Only/AO)存储l 选择行存储(Row-Orientation)或列存储(Column-Orientation)l 使用压缩(只可以是AO表)l 检查只追加(AO)表的压缩和分布情况创建列存储表= CREATE TABLE bar (a int, b text) WITH (appendonly=true, orientation=column)DISTRIBUTED BY (a);检查AO表的压缩与分布情况GP提供了内置的函数用以检查AO表的压缩率和分布情况。这两个函数可以使用对象ID或者TABLE的NAME作为参数。表名可能需要带模式名限定。压缩率得到的是一个常见的比值类型。比如，3.19的返回值或者3.19:1，意味着该TABLE未压缩状态下的储存尺寸是压缩下的储存尺寸的3倍多。分布信息展示的是每个Instance存储该TABLE的ROW数量。例如，在一个有着4个Instance的系统，其dbid范围为0 3，该函数返回类似下面的结果集：=# SELECT get_ao_distribution(lineitem_comp);get_ao_distribution-(0,7500721)(1,7501365)(2,7499978)(3,7497731)(4 rows)通过TYPE命令的方式设置压缩配置一个TYPE可以包含3个压缩参数。关于添加这些参数到TYPE的语法和限制，参考相关的CREATE TYPE命令。下面的命令使用精简的方式创建压缩CREATE TABLE t2 (c1 comptype) WITH (APPENDONLY=true, ORIENTA