Greenplum实战---Greenplum连接登录测试

Greenplum连接登录测试


本文章记录Greenplum数据库的登录测试，主要利用的psql客户端工具



1、psql的帮助信息


psql -help
[gpadmin@mdw bin]$ psql --help
This is psql 8.3.23, the PostgreSQL interactive terminal (Greenplum version).

Usage:
psql [OPTION]... [DBNAME [USERNAME]]

General options:
-c, --command=COMMAND run only single command (SQL or internal) and exit
-d, --dbname=DBNAME database name to connect to (default: "archdata")
-f, --file=FILENAME execute commands from file, then exit
-l, --list list available databases, then exit
-v, --set=, --variable=NAME=VALUE
set psql variable NAME to VALUE
-X, --no-psqlrc do not read startup file (~/.psqlrc)
-1 ("one"), --single-transaction
execute command file as a single transaction
--help show this help, then exit
--version output version information, then exit

Input and output options:
-a, --echo-all echo all input from script
-e, --echo-queries echo commands sent to server
-E, --echo-hidden display queries that internal commands generate
-L, --log-file=FILENAME send session log to file
-n, --no-readline disable enhanced command line editing (readline)
-o, --output=FILENAME send query results to file (or |pipe)
-q, --quiet run quietly (no messages, only query output)
-s, --single-step single-step mode (confirm each query)
-S, --single-line single-line mode (end of line terminates SQL command)

Output format options:
-A, --no-align unaligned table output mode
-F, --field-separator=STRING
set field separator (default: "|")
-H, --html HTML table output mode
-P, --pset=VAR[=ARG] set printing option VAR to ARG (see \pset command)
-R, --record-separator=STRING
set record separator (default: newline)
-t, --tuples-only print rows only
-T, --table-attr=TEXT set HTML table tag attributes (e.g., width, border)
-x, --expanded turn on expanded table output

Connection options:
-h, --host=HOSTNAME database server host or socket directory (default: "local socket")
-p, --port=PORT database server port (default: "5432")
-U, --username=USERNAME database user name (default: "gpadmin")
-w, --no-password never prompt for password
-W, --password force password prompt (should happen automatically)

For more information, type "\?" (for internal commands) or "\help" (for SQL
commands) from within psql, or consult the psql section in the PostgreSQL
documentation.

主要常用的参数有：

-d，指定要连接的数据库，基本每次登录GreenPlum数据库都需要使用这个参数。

-l，列出可用的所有数据库，如果忘记了要登录数据库的名字，可以使用这个参数查看。

-h，指定要连接的数据库服务器的IP地址，默认是本机(localhost)。

-p，指定数据库的端口号，默认是5432.

-U，连接数据库的用户名，默认是gpadmin。



例如：

$ psql -d gpdatabase -h master_host -p 5432 -U gpadmin
检查一下GP当前有多少个databse

[gpadmin@mdw ~]$ more .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH
export MASTER_DATA_DIRECTORY=/greenplum/gpdata/master/gpseg-1
source /usr/local/greenplum-db/greenplum_path.sh
export PGPORT=5432
export PGDATABASE=archdata

因此psql直接就可以登录到archdata

psql
[gpadmin@mdw ~]$ more .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH
export MASTER_DATA_DIRECTORY=/greenplum/gpdata/master/gpseg-1
source /usr/local/greenplum-db/greenplum_path.sh
export PGPORT=5432
export PGDATABASE=archdata



尝试在master上本地登录测试


下面在本地通过远程的方式登录数据库。

psql -d postgre -U postgre -p 5432 -h mdw
[gpadmin@mdw ~]$ psql -d postgre -U postgre -p 5432 -h mdw
psql: FATAL: no pg_hba.conf entry for host "10.102.254.27", user "postgre", database "postgre", SSL off

查看配置文件文件

[gpadmin@mdw ~]$ cd /greenplum/gpdata/master/gpseg-1
[gpadmin@mdw gpseg-1]$ more pg_hba.conf

host all gpadmin 10.102.254.27/32 trust
host replication gpadmin 10.102.254.27/32 trust
host all gpadmin 10.102.254.26/32 trust
host replication gpadmin 10.102.254.26/32 trust
local all gpadmin ident
host all gpadmin 127.0.0.1/28 trust
host all gpadmin 10.102.254.27/32 trust
host all gpadmin ::1/128 trust
host all gpadmin fe80::5b53:4d81:e39f:856c/128 trust
local replication gpadmin ident
host replication gpadmin samenet trust
host gpperfmon gpmon 10.102.254.27/32 trust
host all gpmon 127.0.0.1/28 md5
host all gpmon ::1/128 md5
[gpadmin@mdw gpseg-1]$

修改用户测试一下
psql -d postgres -U gpadmin -p 5432 -h mdw

[gpadmin@mdw gpseg-1]$ psql -d postgres -U gpadmin -p 5432 -h mdw
psql (8.3.23)
Type "help" for help.

postgres=# \q





host all gpadmin 10.102.254.27/32 trust

说明使用10.102.254.27就是本地地址，利用安装的os用户gpadmin可以不用输入password就可以登录到GP master上的databases


测试从segment节点登录到本地


直接登录报错

[root@sdw1 ~]# su - gpadmin
Last login: Sun Apr 19 23:12:27 CST 2020 from 10.102.254.27 on pts/1
[gpadmin@sdw1 ~]$ ls
gpAdminLogs
[gpadmin@sdw1 ~]$ psql
bash: psql: command not found...
[gpadmin@sdw1 ~]$ source /usr/local/greenplum-db/greenplum_path.sh
[gpadmin@sdw1 ~]$ psql
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
[gpadmin@sdw1 ~]$ psql -d postgres
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
[gpadmin@sdw1 ~]$ ls -l /tmp
total 0
drwx------. 2 root root 25 Sep 5 2019 ssh-Qt2PJJYknybc
drwx------. 3 root root 17 Sep 5 2019 systemd-private-d8ccfe1ba7474f4eb4307360c57db0dd-colord.service-aRs2li
drwx------. 3 root root 17 Sep 5 2019 systemd-private-d8ccfe1ba7474f4eb4307360c57db0dd-cups.service-sVWnV4
drwx------. 3 root root 17 Sep 5 2019 systemd-private-d8ccfe1ba7474f4eb4307360c57db0dd-rtkit-daemon.service-dWRaRc
[gpadmin@sdw1 ~]$ ps -ef|grep postgrel
gpadmin 6479 6309 0 21:08 pts/1 00:00:00 grep --color=auto postgrel
[gpadmin@sdw1 ~]$ ps -ef|grep postgre
gpadmin 6464 1 0 Apr24 ? 00:00:06 /usr/local/greenplum-db-5.10.2/bin/postgres -D /greenplum/gpdata/primary1/gpseg0 -p 40000 --gp_dbid=2 --gp_num_contents_in_cluster=6 --silent-mode=true -i -M quiescent --gp_contentid=0
gpadmin 6465 1 0 Apr24 ? 00:00:00 /usr/local/greenplum-db-5.10.2/bin/postgres -D /greenplum/gpdata/mirror2/gpseg5 -p 50001 --gp_dbid=13 --gp_num_contents_in_cluster=6 --silent-mode=true -i -M quiescent --gp_contentid=5
gpadmin 6466 1 0 Apr24 ? 00:00:00 /usr/local/greenplum-db-5.10.2/bin/postgres -D /greenplum/gpdata/mirror1/gpseg4 -p 50000 --gp_dbid=12 --gp_num_contents_in_cluster=6 --silent-mode=true -i -M quiescent --gp_contentid=4
gpadmin 6467 1 0 Apr24 ? 00:00:06 /usr/local/greenplum-db-5.10.2/bin/postgres -D /greenplum/gpdata/primary2/gpseg1 -p 40001 --gp_dbid=3 --gp_num_contents_in_cluster=6 --silent-mode=true -i -M quiescent --gp_contentid=1

[gpadmin@sdw1 gpseg0]$ psql
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
[gpadmin@sdw1 gpseg0]$


说明不能直接从segment上直接本地登录

直连greenplum segment节点的方法, utility模式 ,但是不建议这么做，特别是连接到database之后进行修改操作，这样会破坏整个GP的完整性



PGOPTIONS='-c gp_session_role=utility' psql -p 40000

[gpadmin@sdw1 gpseg0]$ PGOPTIONS='-c gp_session_role=utility' psql -p 40000
psql: FATAL: database "gpadmin" does not exist
[gpadmin@sdw1 gpseg0]$ PGOPTIONS='-c gp_session_role=utility' psql -p 40000 -d postgres
psql (8.3.23)
Type "help" for help.

postgres=#

s


从sdw1 远程登录到master--也不建议




psql -h mdw -p 5432 -U gpadmin -d postgres
[gpadmin@sdw1 gpseg0]$ psql -h mdw -p 5432 -U gpadmin -d postgres
psql: FATAL: no pg_hba.conf entry for host "10.102.254.24", user "gpadmin", database "postgres", SSL off

说明 pg_hba.conf条目需要修改

host all gpadmin 10.102.254.24/32 md5

host all gpadmin 10.102.254.24/32 md5

修改pg_hba.conf文件不需要重启数据库，但是需要使用gpstop –u参数重新加载后才能使之生效。
[gpadmin@mdw gpseg-1]$ gpstop -u
20200427:21:25:32:027667 gpstop:mdw:gpadmin-[INFO]:-Starting gpstop with args: -u
20200427:21:25:32:027667 gpstop:mdw:gpadmin-[INFO]:-Gathering information and validating the environment...
20200427:21:25:32:027667 gpstop:mdw:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information
20200427:21:25:32:027667 gpstop:mdw:gpadmin-[INFO]:-Obtaining Segment details from master...
20200427:21:25:32:027667 gpstop:mdw:gpadmin-[INFO]:-Greenplum Version: 'postgres (Greenplum Database) 5.10.2 build commit:b3c02f3acd880e2d676dacea36be015e4a3826d4'
20200427:21:25:32:027667 gpstop:mdw:gpadmin-[INFO]:-Signalling all postmaster processes to reload
.
[gpadmin@mdw gpseg-1]



因此尝试

psql -h mdw -p 5432 -U gpadmin -d postgres

[gpadmin@sdw1 gpseg0]$ psql -h mdw -p 5432 -U gpadmin -d postgres
Password for user gpadmin:
psql (8.3.23)
Type "help" for help.

postgres=#


psql 帮助
\h


postgres=# \h
Available help:
ABORT COMMIT DELETE EXECUTE
ALTER AGGREGATE COMMIT PREPARED DISCARD EXPLAIN
ALTER CONVERSION COPY DO FETCH
ALTER DATABASE CREATE AGGREGATE DROP AGGREGATE GRANT
ALTER DOMAIN CREATE CAST DROP CAST INSERT
ALTER EXTERNAL TABLE CREATE CONSTRAINT TRIGGER DROP CONVERSION LISTEN
ALTER FILESPACE CREATE CONVERSION DROP DATABASE LOAD
ALTER FUNCTION CREATE DATABASE DROP DOMAIN LOCK
ALTER GROUP CREATE DOMAIN DROP EXTERNAL TABLE MOVE
ALTER INDEX CREATE EXTERNAL TABLE DROP FILESPACE NOTIFY
ALTER LANGUAGE CREATE FUNCTION DROP FUNCTION PREPARE
ALTER OPERATOR CREATE GROUP DROP GROUP PREPARE TRANSACTION
ALTER OPERATOR CLASS CREATE INDEX DROP INDEX REASSIGN OWNED
ALTER OPERATOR FAMILY CREATE LANGUAGE DROP LANGUAGE REINDEX
ALTER PROTOCOL CREATE OPERATOR DROP OPERATOR RELEASE SAVEPOINT
ALTER RESOURCE GROUP CREATE OPERATOR CLASS DROP OPERATOR CLASS RESET
ALTER RESOURCE QUEUE CREATE OPERATOR FAMILY DROP OPERATOR FAMILY REVOKE

\h create table

postgres=# \h create table
Command: CREATE TABLE
Description: define a new table
Syntax:
CREATE [[GLOBAL | LOCAL] {TEMPORARY | TEMP}] TABLE table_name (
[ { column_name data_type [ DEFAULT default_expr ] [column_constraint [ ... ]
[ ENCODING ( storage_directive [,...] ) ]
]
| table_constraint
| LIKE other_table [{INCLUDING | EXCLUDING}
{DEFAULTS | CONSTRAINTS}] ...}
[, ... ] ]
[column_reference_storage_directive [, ... ]
)
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH ( storage_parameter=value [, ... ] )
[ ON COMMIT {PRESERVE ROWS | DELETE ROWS | DROP} ]
[ TABLESPACE tablespace ]
[ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ]
[ PARTITION BY partition_type (column)
[ SUBPARTITION BY partition_type (column) ]
[ SUBPARTITION TEMPLATE ( template_spec ) ]
[...]
( partition_spec )
| [ SUBPARTITION BY partition_type (column) ]
[...]
( partition_spec
[ ( subpartition_spec
[(...)]
) ]

psql 常用命令
列出database\l

postgres=# \l
List of databases
Name | Owner | Encoding | Access privileges
-----------+---------+----------+---------------------
archdata | gpadmin | UTF8 |
gpperfmon | gpadmin | UTF8 | gpadmin=CTc/gpadmin
: =c/gpadmin
postgres | gpadmin | UTF8 |
template0 | gpadmin | UTF8 | =c/gpadmin
: gpadmin=CTc/gpadmin
template1 | gpadmin | UTF8 | =c/gpadmin
: gpadmin=CTc/gpadmin
(5 rows)

postgres=#


切换用户，database；\c

postgres=# \c archdata
You are now connected to database "archdata" as user "gpadmin".
archdata=#


\d，列出当前数据库所有的表，如果后面加表名，则列出表的字段及索引信息。
archdata=# \d
List of relations
Schema | Name | Type | Owner | Storage
--------+------+-------+---------+---------
public | tb1 | table | gpadmin | heap
public | tb2 | table | gpadmin | heap
public | test | table | gpadmin | heap
(3 rows)

archdata=# \d+
List of relations
Schema | Name | Type | Owner | Storage | Description
--------+------+-------+---------+---------+-------------
public | tb1 | table | gpadmin | heap |
public | tb2 | table | gpadmin | heap |
public | test | table | gpadmin | heap |
(3 rows)

archdata=# \d tb1
Table "public.tb1"
Column | Type | Modifiers
--------+---------+-----------
a | integer | not null
Indexes:
"tb1_pkey" PRIMARY KEY, btree (a)
Distributed by: (a)

archdata=# \di tb1
No matching relations found.
archdata=#

列出数据库的连接信息。
\conninfo
archdata=# \conninfo
You are connected to database "archdata" as user "gpadmin" on host "mdw" at port "5432".
archdata=#




/x 切换扩展行格式．当打开时，每一行将在左边打印列/字段名而在右边打印列/字段值．这对于那些不能在一行输出的超长行是很有用的．HTML 行输出模式也支持这个标记

archdata=# select * from pg_stat_activity ;
-[ RECORD 1 ]----+---------------------------------
datid | 16388
datname | gpperfmon
procpid | 25532
sess_id | 150
usesysid | 16941
usename | gpmon
current_query | <IDLE>
waiting | f
query_start | 2020-04-24 18:58:13.033133+08
backend_start | 2020-04-24 18:50:12.162497+08
client_addr | 10.102.254.27
client_port | 29721
application_name | gpcc
xact_start |
waiting_reason |
rsgid | 0
rsgname | unknown

再次\x就关闭了


信息选项
\d [名字] 描述表, 索引, 序列, 或者视图
\d{t|i|s|v|S} [模式] (加 "+" 获取更多信息)
列出表/索引/序列/视图/系统表
\da [模式] 列出聚集函数
\db [模式] 列出表空间 (加 "+" 获取更多的信息)
\dc [模式] 列出编码转换
\dC 列出类型转换
\dd [模式] 显示目标的注释
\dD [模式] 列出域
\df [模式] 列出函数 (加 "+" 获取更多的信息)
\dg [模式] 列出组
\dn [模式] 列出模式 (加 "+" 获取更多的信息)
\do [名字] 列出操作符
\dl 列出大对象, 和 lo_list 一样
\dp [模式] 列出表, 视图, 序列的访问权限
\dT [模式] 列出数据类型 (加 "+" 获取更多的信息)
\du [模式] 列出用户
\l 列出所有数据库 (加 "+" 获取更多的信息)
\z [模式] 列出表, 视图, 序列的访问权限 (和 dp 一样)
\dS
列出系统表和索引．
\dt
只列出非系统表

一般选项
\c[onnect] [数据库名|- [用户名称]]
联接到新的数据库 (当前为 "test")
\cd [目录名] 改变当前的工作目录
\copyright 显示 PostgreSQL 用法和发布信息
\encoding [编码] 显示或设置客户端编码
\h [名字] SQL 命令的语法帮助, 用 * 可以看所有命令的帮助
\q 退出 psql
\set [名字 [值]] 设置内部变量, 如果没有参数就列出所有
\timing 查询计时开关切换 (目前是 关闭)
\unset 名字 取消(删除)内部变量
\! [命令] 在 shell 里执行命令或者开始一个交互的 shell

本文来源：https://blog.csdn.net/murkey/article/details/105800514