Docker 启动单机版 GreenPlum (GPDB) 数据库
下载镜像
# docker pull pivotaldata/gpdb-devel
运行容器
# docker run -it -p 15430:15432 --name gpdb0 -v /root/greenplum-db-gpdb-sandbox-tutorials-d734733/:/workspace/gpdb/tutorials -d pivotaldata/gpdb-devel
进入容器
# docker exec -it gpdb0 bash
首次切换 gpadmin 用户，会自动启动 gp 数据库
bash-4.1# su gpadmin
/workspace/gpdb/gpAux/gpdemo /workspace/gpdb
======================================================================
______ _____ ______ _______ _______ _____
| ____ |_____] | \ |______ | | | | |
|_____| | |_____/ |______ | | | |_____|
----------------------------------------------------------------------
This is a demo of the Greenplum Database system. We will create
a cluster installation with master and 6 segment instances
(3 primary & 3 mirror).
GPHOME ................. : /usr/local/gpdb
MASTER_DATA_DIRECTORY .. : /workspace/gpdb/gpAux/gpdemo/datadirs/qddir/demoDataDir-1
MASTER PORT (PGPORT) ... : 15432
SEGMENT PORTS .......... : 25432 25433 25434 25435 25436 25437 25438 25439 25440 25441 25442 25443
NOTE(s):
* The DB ports identified above must be available for use.
* An environment file gpdemo-env.sh has been created for your use.
======================================================================
==========================================================================================
executing:
/usr/local/gpdb/bin/gpinitsystem -a -c clusterConfigFile -l /workspace/gpdb/gpAux/gpdemo/datadirs/gpAdminLogs ""
==========================================================================================
。。。省略部分输出
gp_opt_version
---------------------------------------
Server has been compiled without ORCA
(1 row)
======================================================================
/workspace/gpdb
创建用户
bash-4.1$ createuser -P dlw
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n
NOTICE: resource queue required -- using default resource queue "pg_default"
创建数据库
bash-4.1$ createdb dlwdb
bash-4.1$ psql dlwdb
psql (8.3.23)
Type "help" for help.
执行 help 查看命令帮助
dlwdb=# help
You are using psql, the command-line interface to PostgreSQL.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
在数据库中创建用户
dlwdb=# CREATE USER cyj WITH PASSWORD 'cyj' NOSUPERUSER;
NOTICE: resource queue required -- using default resource queue "pg_default"
CREATE ROLE
创建用户组并添加用户进去
dlwdb=# CREATE ROLE dcyls;
NOTICE: resource queue required -- using default resource queue "pg_default"
CREATE ROLE
dlwdb=# GRANT dcyls TO dlw,cyj;
GRANT ROLE
dlwdb=# \du
List of roles
Role name | Attributes | Member of
-----------+-----------------------------------+-----------
cyj | | {dcyls}
dcyls | Cannot login | {}
dlw | Create DB | {dcyls}
gpadmin | Superuser, Create role, Create DB | {}
重启容器
容器暂停后，下次启动会出现报错，提示 ssh 连接问题
# docker stop gpdb0
gpdb0
# docker start gpdb0
gpdb0
# docker exec -it gpdb0 bash
bash-4.1# su gpadmin
bash-4.1$ createuser -P dlw
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) y
createuser: could not connect to database postgres: could not connect to server: Connection refused
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.15432"?
bash-4.1$ gpstart
20190827:02:42:25:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-Starting gpstart with args:
20190827:02:42:25:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-Gathering information and validating the environment...
20190827:02:42:25:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-Greenplum Binary Version: 'postgres (Greenplum Database) 4.3.99.00 build dev'
20190827:02:42:25:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-Greenplum Catalog Version: '301605129'
20190827:02:42:25:000018 gpstart:3ce34f890d16:gpadmin-[WARNING]:-postmaster.pid file exists on Master, checking if recovery startup required
20190827:02:42:25:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-Commencing recovery startup checks
20190827:02:42:25:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-Have lock file /tmp/.s.PGSQL.15432 but no process running on port 15432
20190827:02:42:25:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-No Master instance process, entering recovery startup mode
20190827:02:42:25:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-Clearing Master instance lock files
20190827:02:42:25:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-Clearing Master instance pid file
20190827:02:42:25:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-Starting Master instance in admin mode
20190827:02:42:26:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information
20190827:02:42:26:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-Obtaining Segment details from master...
20190827:02:42:26:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-Setting new master era
20190827:02:42:26:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-Commencing forced instance shutdown
20190827:02:42:28:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-Starting Master instance in admin mode
20190827:02:42:29:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information
20190827:02:42:29:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-Obtaining Segment details from master...
20190827:02:42:29:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-Setting new master era
20190827:02:42:29:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-Master Started...
20190827:02:42:29:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-Shutting down master
20190827:02:42:30:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:---------------------------
20190827:02:42:30:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-Master instance parameters
20190827:02:42:30:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:---------------------------
20190827:02:42:30:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-Database = template1
20190827:02:42:30:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-Master Port = 15432
20190827:02:42:30:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-Master directory = /workspace/gpdb/gpAux/gpdemo/datadirs/qddir/demoDataDir-1
20190827:02:42:30:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-Timeout = 600 seconds
20190827:02:42:30:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-Master standby = Off
20190827:02:42:30:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:---------------------------------------
20190827:02:42:30:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-Segment instances that will be started
20190827:02:42:30:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:---------------------------------------
20190827:02:42:30:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:- Host Datadir Port Role
20190827:02:42:30:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:- 3ce34f890d16 /workspace/gpdb/gpAux/gpdemo/datadirs/dbfast1/demoDataDir0 25432 Primary
20190827:02:42:30:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:- 3ce34f890d16 /workspace/gpdb/gpAux/gpdemo/datadirs/dbfast_mirror1/demoDataDir0 25435 Mirror
20190827:02:42:30:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:- 3ce34f890d16 /workspace/gpdb/gpAux/gpdemo/datadirs/dbfast2/demoDataDir1 25433 Primary
20190827:02:42:30:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:- 3ce34f890d16 /workspace/gpdb/gpAux/gpdemo/datadirs/dbfast_mirror2/demoDataDir1 25436 Mirror
20190827:02:42:30:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:- 3ce34f890d16 /workspace/gpdb/gpAux/gpdemo/datadirs/dbfast3/demoDataDir2 25434 Primary
20190827:02:42:30:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:- 3ce34f890d16 /workspace/gpdb/gpAux/gpdemo/datadirs/dbfast_mirror3/demoDataDir2 25437 Mirror
Continue with Greenplum instance startup Yy|Nn (default=N):
> y
20190827:02:42:35:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-Commencing parallel primary and mirror segment instance startup, please wait...
.
20190827:02:42:36:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-Process results...
20190827:02:42:36:000018 gpstart:3ce34f890d16:gpadmin-[ERROR]:-No segment started for content: 0.
20190827:02:42:36:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-dumping success segments: []
20190827:02:42:36:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-----------------------------------------------------
20190827:02:42:36:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-DBID:2 FAILED host:'3ce34f890d16' datadir:'/workspace/gpdb/gpAux/gpdemo/datadirs/dbfast1/demoDataDir0' with reason:'cmd had rc=255 completed=True halted=False
stdout=''
stderr='ssh: connect to host 3ce34f890d16 port 22: Connection refused
''
20190827:02:42:36:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-DBID:5 FAILED host:'3ce34f890d16' datadir:'/workspace/gpdb/gpAux/gpdemo/datadirs/dbfast_mirror1/demoDataDir0' with reason:'cmd had rc=255 completed=True halted=False
stdout=''
stderr='ssh: connect to host 3ce34f890d16 port 22: Connection refused
''
20190827:02:42:36:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-DBID:3 FAILED host:'3ce34f890d16' datadir:'/workspace/gpdb/gpAux/gpdemo/datadirs/dbfast2/demoDataDir1' with reason:'cmd had rc=255 completed=True halted=False
stdout=''
stderr='ssh: connect to host 3ce34f890d16 port 22: Connection refused
''
20190827:02:42:36:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-DBID:6 FAILED host:'3ce34f890d16' datadir:'/workspace/gpdb/gpAux/gpdemo/datadirs/dbfast_mirror2/demoDataDir1' with reason:'cmd had rc=255 completed=True halted=False
stdout=''
stderr='ssh: connect to host 3ce34f890d16 port 22: Connection refused
''
20190827:02:42:36:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-DBID:4 FAILED host:'3ce34f890d16' datadir:'/workspace/gpdb/gpAux/gpdemo/datadirs/dbfast3/demoDataDir2' with reason:'cmd had rc=255 completed=True halted=False
stdout=''
stderr='ssh: connect to host 3ce34f890d16 port 22: Connection refused
''
20190827:02:42:36:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-DBID:7 FAILED host:'3ce34f890d16' datadir:'/workspace/gpdb/gpAux/gpdemo/datadirs/dbfast_mirror3/demoDataDir2' with reason:'cmd had rc=255 completed=True halted=False
stdout=''
stderr='ssh: connect to host 3ce34f890d16 port 22: Connection refused
''
20190827:02:42:36:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-----------------------------------------------------
20190827:02:42:36:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-----------------------------------------------------
20190827:02:42:36:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:- Successful segment starts = 0
20190827:02:42:36:000018 gpstart:3ce34f890d16:gpadmin-[WARNING]:-Failed segment starts = 6 <<<<<<<<
20190827:02:42:36:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:- Skipped segment starts (segments are marked down in configuration) = 0
20190827:02:42:36:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-----------------------------------------------------
20190827:02:42:36:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-
20190827:02:42:36:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-Successfully started 0 of 6 segment instances <<<<<<<<
20190827:02:42:36:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-----------------------------------------------------
20190827:02:42:36:000018 gpstart:3ce34f890d16:gpadmin-[WARNING]:-Segment instance startup failures reported
20190827:02:42:36:000018 gpstart:3ce34f890d16:gpadmin-[WARNING]:-Failed start 6 of 6 segment instances <<<<<<<<
20190827:02:42:36:000018 gpstart:3ce34f890d16:gpadmin-[WARNING]:-Review /home/gpadmin/gpAdminLogs/gpstart_20190827.log
20190827:02:42:36:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-----------------------------------------------------
20190827:02:42:36:000018 gpstart:3ce34f890d16:gpadmin-[INFO]:-Commencing parallel segment instance shutdown, please wait...
.
20190827:02:42:37:000018 gpstart:3ce34f890d16:gpadmin-[ERROR]:-gpstart error: Do not have enough valid segments to start the array.
这个问题很好处理，它是 ssh 自己，看下 ssh 服务，第一次启动的时候应该是启动脚本里附带了启动 sshd 服务，之后再启动，需要自己手动启动下 sshd 服务，此处是 centos6.8 的系统
bash-4.1$ hostname
3ce34f890d16
bash-4.1$ ssh 3ce34f890d16
ssh: connect to host 3ce34f890d16 port 22: Connection refused
bash-4.1$ cat /etc/redhat-release
CentOS release 6.8 (Final)
bash-4.1$ service sshd start
/etc/init.d/sshd: line 33: /etc/sysconfig/sshd: Permission denied
Starting sshd: /etc/ssh/sshd_config: Permission denied
[FAILED]
bash-4.1$ exit
exit
bash-4.1# service sshd start
Starting sshd: [ OK ]
启动 sshd 后再启动数据库，正常运行
bash-4.1# su gpadmin
bash-4.1$ gpstart
20190827:02:43:49:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:-Starting gpstart with args:
20190827:02:43:49:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:-Gathering information and validating the environment...
20190827:02:43:49:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:-Greenplum Binary Version: 'postgres (Greenplum Database) 4.3.99.00 build dev'
20190827:02:43:49:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:-Greenplum Catalog Version: '301605129'
20190827:02:43:49:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:-Starting Master instance in admin mode
20190827:02:43:50:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information
20190827:02:43:50:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:-Obtaining Segment details from master...
20190827:02:43:51:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:-Setting new master era
20190827:02:43:51:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:-Master Started...
20190827:02:43:51:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:-Shutting down master
20190827:02:43:52:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:---------------------------
20190827:02:43:52:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:-Master instance parameters
20190827:02:43:52:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:---------------------------
20190827:02:43:52:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:-Database = template1
20190827:02:43:52:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:-Master Port = 15432
20190827:02:43:52:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:-Master directory = /workspace/gpdb/gpAux/gpdemo/datadirs/qddir/demoDataDir-1
20190827:02:43:52:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:-Timeout = 600 seconds
20190827:02:43:52:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:-Master standby = Off
20190827:02:43:52:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:---------------------------------------
20190827:02:43:52:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:-Segment instances that will be started
20190827:02:43:52:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:---------------------------------------
20190827:02:43:52:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:- Host Datadir Port Role
20190827:02:43:52:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:- 3ce34f890d16 /workspace/gpdb/gpAux/gpdemo/datadirs/dbfast1/demoDataDir0 25432 Primary
20190827:02:43:52:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:- 3ce34f890d16 /workspace/gpdb/gpAux/gpdemo/datadirs/dbfast_mirror1/demoDataDir0 25435 Mirror
20190827:02:43:52:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:- 3ce34f890d16 /workspace/gpdb/gpAux/gpdemo/datadirs/dbfast2/demoDataDir1 25433 Primary
20190827:02:43:52:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:- 3ce34f890d16 /workspace/gpdb/gpAux/gpdemo/datadirs/dbfast_mirror2/demoDataDir1 25436 Mirror
20190827:02:43:52:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:- 3ce34f890d16 /workspace/gpdb/gpAux/gpdemo/datadirs/dbfast3/demoDataDir2 25434 Primary
20190827:02:43:52:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:- 3ce34f890d16 /workspace/gpdb/gpAux/gpdemo/datadirs/dbfast_mirror3/demoDataDir2 25437 Mirror
Continue with Greenplum instance startup Yy|Nn (default=N):
> y
20190827:02:43:54:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:-Commencing parallel primary and mirror segment instance startup, please wait...
....
20190827:02:43:58:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:-Process results...
20190827:02:43:58:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:-----------------------------------------------------
20190827:02:43:58:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:- Successful segment starts = 6
20190827:02:43:58:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:- Failed segment starts = 0
20190827:02:43:58:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:- Skipped segment starts (segments are marked down in configuration) = 0
20190827:02:43:58:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:-----------------------------------------------------
20190827:02:43:58:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:-
20190827:02:43:58:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:-Successfully started 6 of 6 segment instances
20190827:02:43:58:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:-----------------------------------------------------
20190827:02:43:58:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:-Starting Master instance 3ce34f890d16 directory /workspace/gpdb/gpAux/gpdemo/datadirs/qddir/demoDataDir-1
20190827:02:43:59:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:-Command pg_ctl reports Master 3ce34f890d16 instance active
20190827:02:43:59:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:-No standby master configured. skipping...
20190827:02:43:59:000169 gpstart:3ce34f890d16:gpadmin-[INFO]:-Database successfully started
bash-4.1$ createuser -P dlw
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) y
bash-4.1$
bash-4.1$ createdb dlwdb
bash-4.1$ psql dlwdb
psql (8.3.23)
Type "help" for help.
dlwdb=#
客户端连接
连接之前需要修改 pgdb 的配置文件，允许客户端所在网络可以连接 配置文件目录可以在初始化 pgdb 的时候看到
bash-4.1$ cd /workspace/gpdb/gpAux/gpdemo/datadirs/qddir/demoDataDir-1
bash-4.1$ vi pg_hba.conf
添加 host all all 192.16.126.0/24 trust
重启 gpdb
bash-4.1$ gpstop -r
注意：网络要配置客户端的 ip 地址，比如我的 ip 地址是 10.114.200.23 云主机的 ip 是 10.114.10.33 此时要配置成 host all all 10.114.200.0/24 trust 是可以的
下载 windows 客户端
https://www.postgresql.org/ftp/pgadmin/pgadmin3/v1.22.2/win32/
安装之后启动连接
成功连接
参考连接
https://yq.aliyun.com/articles/57656
