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