天天看点
返回

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

Greenplum连接登录测试

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

1、psql的帮助信息 

 psql -help
 [[email protected] 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

 [[email protected] ~]$ 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
 [[email protected] ~]$ 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
 [[email protected] ~]$ 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
 ​
 查看配置文件文件
 ​
 [[email protected] ~]$ cd /greenplum/gpdata/master/gpseg-1
 [[email protected] 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
 [[email protected] gpseg-1]$         
 ​
 修改用户测试一下
 psql -d postgres -U gpadmin -p 5432 -h mdw
 ​
 [[email protected] 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节点登录到本地

直接登录报错

 [[email protected] ~]# su - gpadmin
 Last login: Sun Apr 19 23:12:27 CST 2020 from 10.102.254.27 on pts/1
 [[email protected] ~]$ ls
 gpAdminLogs
 [[email protected] ~]$ psql
 bash: psql: command not found...
 [[email protected] ~]$ source /usr/local/greenplum-db/greenplum_path.sh
 [[email protected] ~]$ 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"?
 [[email protected] ~]$ 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"?
 [[email protected] ~]$ 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
 [[email protected] ~]$ ps -ef|grep postgrel
 gpadmin   6479  6309  0 21:08 pts/1    00:00:00 grep --color=auto postgrel
 [[email protected] ~]$ 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
 ​
 [[email protected] 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"?
 [[email protected] gpseg0]$ 
 ​
 ​
 说明不能直接从segment上直接本地登录

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

  PGOPTIONS='-c gp_session_role=utility' psql -p 40000
  
  [[email protected] gpseg0]$ PGOPTIONS='-c gp_session_role=utility' psql -p 40000
 psql: FATAL:  database "gpadmin" does not exist
 [[email protected] 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
 [[email protected] 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参数重新加载后才能使之生效。
 [[email protected] 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
 . 
 [[email protected] gpseg-1]
 ​
 ​

因此尝试

 psql -h mdw -p 5432 -U gpadmin -d postgres
 ​
 [[email protected] 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
greenplum # 集群管理
上一篇: linux集群是啥,linux集群是什么
下一篇: 在Windows上的虚拟机安装Hadoop伪分布式系统（三）

继续阅读