(一)數(shù)據(jù)庫對(duì)象.doc

GPDB管理員筆記（一）數(shù)據(jù)庫對(duì)象 分類： Greenplum 2014-03-10 13:25 1363人閱讀 評(píng)論(0) 收藏 舉報(bào) 數(shù)據(jù)庫databasegreenplum數(shù)據(jù)庫對(duì)象管理 1、創(chuàng)建數(shù)據(jù)庫 create database new_dbname; createdb -h localhost -p 5432 mydb2、克隆數(shù)據(jù)庫3、查看數(shù)據(jù)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、數(shù)據(jù)庫屬性變更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創(chuàng)建文件系統(tǒng)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 創(chuàng)建表空間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查看現(xiàn)有表空間和空間文件：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)查看當(dāng)前的shemalibo=# select current_schema();current_schema-public(1 row)創(chuàng)建表聲明分布鍵= 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;選擇表的存儲(chǔ)模式GPDB提供幾種靈活的存儲(chǔ)處理模式(或者混合模式)。在創(chuàng)建一張新的TABLE時(shí)，有幾個(gè)選項(xiàng)來決定數(shù)據(jù)如何儲(chǔ)存在磁盤上。本節(jié)介紹這幾種選項(xiàng)，以及出于工作負(fù)載的考慮如何實(shí)現(xiàn)最佳的儲(chǔ)存模式。l 選擇堆存儲(chǔ)(Heap)或只追加(Append-Only/AO)存儲(chǔ)l 選擇行存儲(chǔ)(Row-Orientation)或列存儲(chǔ)(Column-Orientation)l 使用壓縮(只可以是AO表)l 檢查只追加(AO)表的壓縮和分布情況創(chuàng)建列存儲(chǔ)表= CREATE TABLE bar (a int, b text) WITH (appendonly=true, orientation=column)DISTRIBUTED BY (a);檢查AO表的壓縮與分布情況GP提供了內(nèi)置的函數(shù)用以檢查AO表的壓縮率和分布情況。這兩個(gè)函數(shù)可以使用對(duì)象ID或者TABLE的NAME作為參數(shù)。表名可能需要帶模式名限定。壓縮率得到的是一個(gè)常見的比值類型。比如，3.19的返回值或者3.19:1，意味著該TABLE未壓縮狀態(tài)下的儲(chǔ)存尺寸是壓縮下的儲(chǔ)存尺寸的3倍多。分布信息展示的是每個(gè)Instance存儲(chǔ)該TABLE的ROW數(shù)量。例如，在一個(gè)有著4個(gè)Instance的系統(tǒng)，其dbid范圍為0 3，該函數(shù)返回類似下面的結(jié)果集：=# SELECT get_ao_distribution(lineitem_comp);get_ao_distribution-(0,7500721)(1,7501365)(2,7499978)(3,7497731)(4 rows)通過TYPE命令的方式設(shè)置壓縮配置一個(gè)TYPE可以包含3個(gè)壓縮參數(shù)。關(guān)于添加這些參數(shù)到TYPE的語法和限制，參考相關(guān)的CREATE TYPE命令。下面的命令使用精簡的方式創(chuàng)建壓縮CREATE TABLE t2 (c1 comptype) WITH (APPENDONLY=true, ORIENTA