Greenplum学习笔记（1）远程访问数据库

psql远程访问数据库

Greeenplum默认只能通过控制节点（Master Host）访问数据库，不允许其他主机访问数据库。如果需要远程访问，需要配置Greenplum的权限控制。修改Master数据目录$MASTER_DATA_DIRECYORY下的pg_hba.conf和postgresql.conf，其中pg_hba.conf是用于配置PostgreSQL数据库的访问权限，postgresql.conf用于配置PostgreSQL数据库服务器的相应参数。

数据库用户gpadmin创建密码

[gpadmin@sdw ~]psql -h 10.47.160.206 -p 5432 testdb gpadmin
Password for user gpadmin:
psql: FATAL:  password authentication failed for user "gpadmin"

报错说明未设置数据库用户密码，alter role设置密码

testdb=# alter role gpadmin with password 'gpadmin';
ALTER ROLE

修改postgresql.conf

[gpadmin@mdw ~]$ cd /gpdata/master/default/gpseg-1/
[gpadmin@mdw gpseg-1]$ vi postgresql.conf

# - Connection Settings -

listen_addresses = '*'                  # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to '*', '*' = all
                                        # (change requires restart)

修改pg_hba.conf

#TYPE	DATABASE	USER	ADDRESS			METHOD
# "local" is for Unix domain socket connections only
# IPv4 local connections:
# IPv6 local connections:
# standby master host ip addresses

host	all			all		0.0.0.0/0       md5

重启greenplum数据库

通过gpstop -u 命令使配置生效

[gpadmin@mdw ~]$ gpstop -u

psql远程连接数据库

Greenplum集群的Segment主机sdw的gpadmin用户通过psql连接Master主机mdw远程访问数据库

[gpadmin@sdw ~]$ psql -h 10.47.160.206 -p 5432 testdb gpadmin
Password for user gpadmin:
psql (10.6, server 8.3.23)
Type "help" for help.

testdb=#