Greenplum 学习笔记

/etc/hosts配置：

 

[root@dw-host10-if1 ~]# cat /etc/hosts;
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1               localhost.localdomain localhost
::1             localhost6.localdomain6 localhost6

192.168.42.10  dw-host10-if1
192.168.42.11  dw-host11-if1
192.168.42.12  dw-host12-if1

 

 

防火墙配置:

 

[root@dw-host10-if1 ~]# vi /etc/sysconfig/iptables
# Firewall configuration written by system-config-securitylevel
# Manual customization of this file is not recommended.
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
:RH-Firewall-1-INPUT - [0:0]
-A INPUT -j RH-Firewall-1-INPUT
-A FORWARD -j RH-Firewall-1-INPUT
-A RH-Firewall-1-INPUT -i lo -j ACCEPT
-A RH-Firewall-1-INPUT -p icmp --icmp-type any -j ACCEPT
-A RH-Firewall-1-INPUT -p 50 -j ACCEPT
-A RH-Firewall-1-INPUT -p 51 -j ACCEPT
-A RH-Firewall-1-INPUT -p udp --dport 5353 -d 224.0.0.251 -j ACCEPT
-A RH-Firewall-1-INPUT -p udp -m udp --dport 631 -j ACCEPT
-A RH-Firewall-1-INPUT -p tcp -m tcp --dport 631 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
-A RH-Firewall-1-INPUT -j REJECT --reject-with icmp-host-prohibited

 

 

GreenPlum主节点配置(.bash_profile)：

 

export PATH
export LANG=en_US.utf8
export GPHOME=/opt/greenplum-db
export PATH=$GPHOME/bin:$GPHOME/ext/python/bin:$PATH
export LD_LIBRARY_PATH=$GPHOME/lib:$GPHOME/ext/python/lib:$LD_LIBRARY_PATH
export PYTHONPATH=$GPHOME/lib/python
export PYTHONHOME=$GPHOME/ext/python
export PGPORT=1921
export MASTER_DATA_DIRECTORY=/database/gp_master/gp-1
alias rm='rm -i'

 

 

 

进行安装

[root@dw-host10-if1 ~]# ./greenplum-db-3.3.5.0-build-3-RHEL5-i386.bin

 

输入安装的位置

********************************************************************************
Provide the installation path for Greenplum Database or press ENTER to
accept the default installation path: /usr/local/greenplum-db-3.3.5.0
************************************************************ ********************

/opt/greenplum-db-3.3.5.0

自动创建符号链接/opt/greenplum-db ->/opt/greenplum-db-3.3.5.0(.bash_profile中进行过配置)

 

 

 

 

[gpadmin@dw-host10-if1 greenplum-db-3.3.5.0]$ mkdir cfg

[gpadmin@dw-host10-if1 cfg]$ vi sshkey_hostname

dw-host10-if1
dw-host11-if1
dw-host12-if1

 

[gpadmin@dw-host10-if1 cfg]$ gpssh-exkeys -f /opt/greenplum-db/cfg/sshkey_hostname
[STEP 1 of 5] create local ID and authorize on local host

[STEP 2 of 5] keyscan all hosts and update known_hosts file

[STEP 3 of 5] authorize current user on remote hosts
  ... send to dw-host11-if1
  ***
  *** Enter password for dw-host11-if1:

[STEP 4 of 5] determine common authentication file content

[STEP 5 of 5] copy authentication files to all remote hosts
  ... finished key exchange with dw-host11-if1
  ... finished key exchange with dw-host12-if1

 

 

对root用户也进行密码交换:

 

[root@dw-host10-if1 ~]# . /home/gpadmin/.bash_profile
[root@dw-host10-if1 ~]# gpssh-exkeys  -f /opt/greenplum-db/cfg/sshkey_hostname
[STEP 1 of 5] create local ID and authorize on local host

[STEP 2 of 5] keyscan all hosts and update known_hosts file

[STEP 3 of 5] authorize current user on remote hosts
  ... send to dw-host11-if1
  ***
  *** Enter password for dw-host11-if1:
  ... send to dw-host12-if1

[STEP 4 of 5] determine common authentication file content

[STEP 5 of 5] copy authentication files to all remote hosts
  ... finished key exchange with dw-host11-if1
  ... finished key exchange with dw-host12-if1

[INFO] completed successfully
[root@dw-host10-if1 ~]#

 

 

如果ssh的端口不是默认的22，那么会产生下面的错误:

 

[root@dw-host10-if1 /]# vi /etc/ssh/sshd_config

 

[STEP 1 of 5] create local ID and authorize on local host
  ... /home/gpadmin/.ssh/id_rsa file exists ... key generation skipped
[ERROR dw-host10-if1] authentication check failed:
     ssh: connect to host dw-host10-if1 port 22: Connection refused
[ERROR] cannot establish ssh access into the local host

 

 

[root@dw-host10-if1 ~]# gpssh -f /opt/greenplum-db/cfg/sshkey_hostname
=> date
[dw-host12-if1] Fri Sep 24 20:50:48 PDT 2010
[dw-host11-if1] Fri Sep 24 20:50:48 PDT 2010
[dw-host10-if1] Fri Sep 24 20:50:48 PDT 2010
=>exit

 

 

配置主节点数据分发到子节点(如果有一台机器有两台网卡的，只配置一台):

 

[gpadmin@dw-host10-if1 cfg]$ cp sshkey_hostname  gpscp_hostname
[gpadmin@dw-host10-if1 cfg]$ vi gpscp_hostname
dw-host11-if1
dw-host12-if1

 

 

 

[gpadmin@dw-host10-if1 cfg]$ vi gp_init_hostname(包含所有的segment,如果一台机器有两个网卡，都要包含)
dw-host11-if1
dw-host12-if1

 

 

---------------------------------------------SEGMENT安装---------------------------------------

1.对master节点进行压缩

tar -jcvf greenplum-db-3.3.5.0.tar.bz2 greenplum-db-3.3.5.0

 

2.把压缩的文件拷贝到各个节点

[root@dw-host10-if1 opt]# . /home/gpadmin/.bash_profile
[root@dw-host10-if1 opt]# gpscp -f /opt/greenplum-db/cfg/gpscp_hostname greenplum-db-3.3.5.0.tar.bz2 =:/opt

3.对节点进行解压

 

[root@dw-host10-if1 opt]# gpssh -f /opt/greenplum-db/cfg/gpscp_hostname
=> cd /opt
[dw-host12-if1]
[dw-host11-if1]
=> ls
[dw-host12-if1] greenplum-db-3.3.5.0.tar.bz2
[dw-host11-if1] greenplum-db-3.3.5.0.tar.bz2
=> tar --directory /opt -jxvf /opt/greenplum-db-3.3.5.0.tar.bz2

4.创建每个节点的存储数据的位置(一个segment一个位置)

 => mkdir -p /database/gp_subdir_0
[dw-host12-if1]
[dw-host11-if1]

=> chown -R gpadmin:gpadmin /database
[dw-host12-if1]
[dw-host11-if1]
=>

 

 

 

 

[gpadmin@dw-host10-if1 cfg]$ vi gp_init_config
ARRAY_NAME="Greenplum Database"
MACHINE_LIST_FILE=/opt/greenplum-db/cfg/init_host
SEG_PREFIX=gp
PORT_BASE=500000
declare -a DATA_DIRECTORY=(/database/gp_subdir_0)
MASTER_HOSTNAME=dw-host10-if1
MASTER_DIRECTORY=/database/gp_master
MASTER_PORT=1921
TRUSTED_SHELL=ssh
CHECK_POINT_SEGMENTS=64
ENCODING=UNICODE

 

 

 

[gpadmin@dw-host10-if1 cfg]$ gpinitsystem -c ./gp_init_config
20100924:23:43:40:gpinitsystem:dw-host10-if1:gpadmin-[INFO]:-Checking configuration parameters, please wait...
20100924:23:43:41:gpinitsystem:dw-host10-if1:gpadmin-[INFO]:-Reading Greenplum configuration file ./gp_init_config
20100924:23:43:41:gpinitsystem:dw-host10-if1:gpadmin-[INFO]:-Locale has not been set in ./gp_init_config, will set to default value
20100924:23:43:41:gpinitsystem:dw-host10-if1:gpadmin-[INFO]:-Locale set to en_US.utf8
20100924:23:43:42:gpinitsystem:dw-host10-if1:gpadmin-[INFO]:-No DATABASE_NAME set, will exit following template1 updates
20100924:23:43:42:gpinitsystem:dw-host10-if1:gpadmin-[INFO]:-MASTER_MAX_CONNECT not set, will set to default value 25
20100924:23:43:42:gpinitsystem:dw-host10-if1:gpadmin-[INFO]:-Master IP address array = 192.168.42.10
20100924:23:43:43:gpinitsystem:dw-host10-if1:gpadmin-[INFO]:-Checking configuration parameters, Completed
20100924:23:43:43:gpinitsystem:dw-host10-if1:gpadmin-[INFO]:-Commencing multi-home checks, please wait...
..
20100924:23:43:44:gpinitsystem:dw-host10-if1:gpadmin-[INFO]:-Configuring build for standard array
20100924:23:43:44:gpinitsystem:dw-host10-if1:gpadmin-[INFO]:-Commencing multi-home checks, Completed
20100924:23:43:44:gpinitsystem:dw-host10-if1:gpadmin-[INFO]:-Building primary segment instance array, please wait...
..
20100924:23:43:48:gpinitsystem:dw-host10-if1:gpadmin-[INFO]:-Checking Master host
20100924:23:43:48:gpinitsystem:dw-host10-if1:gpadmin-[INFO]:-Checking new segment hosts, please wait...
20100924:23:43:52:gpinitsystem:dw-host10-if1:gpadmin-[FATAL]:-No /opt/greenplum-db/lib on segment instance dw-host11-if1 Script Exiting!
[gpadmin@dw-host10-if1 cfg]$ su - root
Password:
[root@dw-host10-if1 ~]# gpssh -f /opt/greenplum-db/cfg/gpscp_hostname
-bash: gpssh: command not found
[root@dw-host10-if1 ~]# . /home/gpadmin/.bash_profile
[root@dw-host10-if1 ~]# gpssh -f /opt/greenplum-db/cfg/gpscp_hostname
=> ln -s /opt/greenplum-db-3.3.5.0  /opt/greenplum-db
[dw-host12-if1]
[dw-host11-if1]
=>

 

初始化数据库

[gpadmin@dw-host10-if1 cfg]$ gpinitsystem  -c ./gp_init_config

[gpadmin@dw-host10-if1 cfg]$ ipcs

                 

------ Semaphore Arrays --------
key        semid      owner      perms      nsems    
0x001d4fe9 2031616    gpadmin   600        17       
0x001d4fea 2064385    gpadmin   600        17       

 

 

 

 [gpadmin@dw-host10-if1 gp-1]$ vi pg_hba.conf 

 

加入一行(本地可信)

local    all         all             trust

 

 [gpadmin@dw-host10-if1 gp-1]$ gpstart;

 

[gpadmin@dw-host10-if1 gp-1]$ psql postgres gpadmin
psql (8.2.13)
Type "help" for help.

postgres=# \h

 

 --------------------------------------------------------------------创建数据库-------------------------------------------------------------------------------

[gpadmin@dw-host10-if1 gp-1]$ psql postgres gpadmin
psql (8.2.13)
Type "help" for help.

postgres=# \h
Available help:
  ABORT                     CREATE LANGUAGE           DROP TYPE                
  ALTER AGGREGATE           CREATE OPERATOR CLASS     DROP USER                
  ALTER CONVERSION          CREATE OPERATOR           DROP VIEW                
  ALTER DATABASE            CREATE RESOURCE QUEUE     END                      
  ALTER DOMAIN              CREATE ROLE               EXECUTE                  
  ALTER FUNCTION            CREATE RULE               EXPLAIN                  
  ALTER GROUP               CREATE SCHEMA             FETCH                    
  ALTER INDEX               CREATE SEQUENCE           GRANT                    
  ALTER LANGUAGE            CREATE TABLE              INSERT                   
  ALTER OPERATOR CLASS      CREATE TABLE AS           LISTEN                   
  ALTER OPERATOR            CREATE TABLESPACE         LOAD                     
  ALTER RESOURCE QUEUE      CREATE TRIGGER            LOCK                     
  ALTER ROLE                CREATE TYPE               MOVE                     
  ALTER SCHEMA              CREATE USER               NOTIFY                   
  ALTER SEQUENCE            CREATE VIEW               PREPARE                  
  ALTER TABLE               DEALLOCATE                PREPARE TRANSACTION      
  ALTER TABLESPACE          DECLARE                   REASSIGN OWNED           
  ALTER TRIGGER             DELETE                    REINDEX                  
  ALTER TYPE                DROP AGGREGATE            RELEASE SAVEPOINT        
  ALTER USER                DROP CAST                 RESET                    
  ANALYZE                   DROP CONVERSION           REVOKE                   
  BEGIN                     DROP DATABASE             ROLLBACK                 
  CHECKPOINT                DROP DOMAIN               ROLLBACK PREPARED        
  CLOSE                     DROP EXTERNAL TABLE       ROLLBACK TO SAVEPOINT    
  CLUSTER                   DROP FUNCTION             SAVEPOINT                
  COMMENT                   DROP GROUP                SELECT                   
  COMMIT                    DROP INDEX                SELECT INTO              
  COMMIT PREPARED           DROP LANGUAGE             SET                      
  COPY                      DROP OPERATOR CLASS       SET CONSTRAINTS          
  CREATE AGGREGATE          DROP OPERATOR             SET ROLE                 
  CREATE CAST               DROP OWNED                SET SESSION AUTHORIZATION
  CREATE CONSTRAINT TRIGGER DROP RESOURCE QUEUE       SET TRANSACTION          
  CREATE CONVERSION         DROP ROLE                 SHOW                     
  CREATE DATABASE           DROP RULE                 START TRANSACTION        
  CREATE DOMAIN             DROP SCHEMA               TRUNCATE                 
  CREATE EXTERNAL TABLE     DROP SEQUENCE             UNLISTEN                 
  CREATE FUNCTION           DROP TABLE                UPDATE                   
  CREATE GROUP              DROP TABLESPACE           VACUUM                   
  CREATE INDEX              DROP TRIGGER              VALUES                   
postgres=# \h alter role
Command:     ALTER ROLE
Description: change a database role
Syntax:
ALTER ROLE name [ [ WITH ] option [ ... ] ]

where option can be:
   
      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | CREATEUSER | NOCREATEUSER
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'
    | RESOURCE QUEUE queuename

ALTER ROLE name RENAME TO newname

ALTER ROLE name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER ROLE name RESET configuration_parameter

postgres=# \h
Available help:
  ABORT                     CREATE LANGUAGE           DROP TYPE                
  ALTER AGGREGATE           CREATE OPERATOR CLASS     DROP USER                
  ALTER CONVERSION          CREATE OPERATOR           DROP VIEW                
  ALTER DATABASE            CREATE RESOURCE QUEUE     END                      
  ALTER DOMAIN              CREATE ROLE               EXECUTE                  
  ALTER FUNCTION            CREATE RULE               EXPLAIN                  
  ALTER GROUP               CREATE SCHEMA             FETCH                    
  ALTER INDEX               CREATE SEQUENCE           GRANT                    
  ALTER LANGUAGE            CREATE TABLE              INSERT                   
  ALTER OPERATOR CLASS      CREATE TABLE AS           LISTEN                   
  ALTER OPERATOR            CREATE TABLESPACE         LOAD                     
  ALTER RESOURCE QUEUE      CREATE TRIGGER            LOCK                     
  ALTER ROLE                CREATE TYPE               MOVE                     
  ALTER SCHEMA              CREATE USER               NOTIFY                   
  ALTER SEQUENCE            CREATE VIEW               PREPARE                  
  ALTER TABLE               DEALLOCATE                PREPARE TRANSACTION      
  ALTER TABLESPACE          DECLARE                   REASSIGN OWNED           
  ALTER TRIGGER             DELETE                    REINDEX                  
  ALTER TYPE                DROP AGGREGATE            RELEASE SAVEPOINT        
  ALTER USER                DROP CAST                 RESET                    
  ANALYZE                   DROP CONVERSION           REVOKE                   
  BEGIN                     DROP DATABASE             ROLLBACK                 
  CHECKPOINT                DROP DOMAIN               ROLLBACK PREPARED        
  CLOSE                     DROP EXTERNAL TABLE       ROLLBACK TO SAVEPOINT    
  CLUSTER                   DROP FUNCTION             SAVEPOINT                
  COMMENT                   DROP GROUP                SELECT                   
  COMMIT                    DROP INDEX                SELECT INTO              
  COMMIT PREPARED           DROP LANGUAGE             SET                      
  COPY                      DROP OPERATOR CLASS       SET CONSTRAINTS          
  CREATE AGGREGATE          DROP OPERATOR             SET ROLE                 
  CREATE CAST               DROP OWNED                SET SESSION AUTHORIZATION
  CREATE CONSTRAINT TRIGGER DROP RESOURCE QUEUE       SET TRANSACTION          
  CREATE CONVERSION         DROP ROLE                 SHOW                     
  CREATE DATABASE           DROP RULE                 START TRANSACTION        
  CREATE DOMAIN             DROP SCHEMA               TRUNCATE                 
  CREATE EXTERNAL TABLE     DROP SEQUENCE             UNLISTEN                 
  CREATE FUNCTION           DROP TABLE                UPDATE                   
  CREATE GROUP              DROP TABLESPACE           VACUUM                   
  CREATE INDEX              DROP TRIGGER              VALUES                   
postgres=# \h create database
Command:     CREATE DATABASE
Description: create a new database
Syntax:
CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] dbowner ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ TABLESPACE [=] tablespace ]
           [ CONNECTION LIMIT [=] connlimit ] ]

postgres=# create database warehouse template=template0 encoding='UTF8';
CREATE DATABASE
postgres=# \l
       List of databases
   Name    |  Owner  | Encoding
-----------+---------+----------
 postgres  | gpadmin | UTF8
 template0 | gpadmin | UTF8
 template1 | gpadmin | UTF8
 warehouse | gpadmin | UTF8
(4 rows)

postgres=# \c warehour gpadmin
FATAL:  database "warehour" does not exist
Previous connection kept
postgres=# \c warehouse gpadmin
psql (8.2.13)
You are now connected to database "warehouse".
warehouse=# create table tbl_test(col1 int primary key,col2 varchar(10));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tbl_test_pkey" for table "tbl_test"
CREATE TABLE
warehouse=# \d tbl_test
          Table "public.tbl_test"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 col1   | integer               | not null
 col2   | character varying(10) |
Indexes:
    "tbl_test_pkey" PRIMARY KEY, btree (col1)
Distributed by: (col1)

warehouse=#

 

 

 

 

 

 

通过pgadmin进行访问数据库，需要在主节点，修改pg_hdb.conf文件

[gpadmin@dw-host10-if1 gp-1]$ vi pg_hba.conf

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
# "local" is for Unix domain socket connections only
# IPv4 local connections:
# IPv6 local connections:
local    all         all             trust
local    all         gpadmin         ident
host     all         gpadmin         127.0.0.1/32    trust
host     all         gpadmin         192.168.92.10/32       trust
host     all         gpadmin         192.168.42.10/32

 

host    warehouse pgadmin    0.0.0.0/0   md5

 

 

 

[gpadmin@dw-host10-if1 gp-1]$ gpstop -u

对刚修改的文件进行更新，不需要重新启动数据库

评论

2 楼 saup007 2016-03-31  

ssh端口不是22，怎么搞呢？

1 楼 bobbell 2014-12-04  

哇塞，你真厉害，整理的非常全面。我是一个java barcode的开发者，你的博客里面有很多对我有用的笔记，谢谢你的分享哦。