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 | 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
