当前位置: 首页 > GreenPlum > 正文

GreenPlum数据库登录和基本的帮助手册信息

本篇文章主要记录GreenPlum数据库的登录和基本的帮助手册相关信息,GreenPlum数据库提供psql客户端程序访问数据库,下面是psql的帮助信息。

[gpadmin@mdw gpdb]$ psql --help
This is psql 8.2.15, 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: "gpadmin")
  -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.
Report bugs to <pgsql-bugs@postgresql.org>.

常用的有以下几个参数:

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

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

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

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

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

下面演示下如何登录GreenPlum数据库,首先使用psql –l参数列出都有哪些数据库。

[gpadmin@mdw gpdb]$ psql -l
                  List of databases
   Name    |  Owner  | Encoding |  Access privileges  
-----------+---------+----------+---------------------
 postgres  | gpadmin | UTF8     | 
 template0 | gpadmin | UTF8     | =c/gpadmin          
                                : gpadmin=CTc/gpadmin
 template1 | gpadmin | UTF8     | =c/gpadmin          
                                : gpadmin=CTc/gpadmin
(3 rows)

可以看到当前有3个数据库,下面演示在本地登录postgres数据库,登录本地数据库可以不指定-h参数,如果端口使用默认的5432,也不需要指定-p参数,默认使用gpadmin管理员用户登录数据库,如果使用gpadmin用户登录,也可以不指定-U参数。

[gpadmin@mdw gpseg-1]$ psql -d postgres
psql (8.2.15)
Type "help" for help.

postgres=#

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

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

postgres=#

可以成功登录数据库,下面测试Segment节点远程访问数据库。

[gpadmin@sdw3 gpdb]$ psql -h mdw -p 5432 -U gpadmin -d postgres
psql:FATAL: no pg_hba_conf entry for host “10.9.15.28”,user “gpadmin”,database “postgres”,SSL off

这是因为没有开启segment节点sdw3的访问权限,如果需要远程访问数据库,还需要为用户设置密码,登录Master节点,为gpadmin用户设置密码。

postgres=# alter role gpadmin with password ‘gpadmin’;
ALTER ROLE
在pg_hba.conf文件中,增加sdw3节点的权限,使sdw3节点可以访问数据库。
[gpadmin@mdw gpdb]$ cd /gpdb/gpdata/gpseg-1
[gpadmin@mdw gpseg-1]$ vi pg_hba.conf
增加以下内容:
host    all     gpadmin         10.9.15.28/32   md5

修改pg_hba.conf文件不需要重启数据库,但是需要使用gpstop –u参数重新加载后才能使之生效。

[gpadmin@mdw gpseg-1]$ gpstop -u
20160115:16:08:56:014623 gpstop:mdw:gpadmin-[INFO]:-Starting gpstop with args: -u
20160115:16:08:56:014623 gpstop:mdw:gpadmin-[INFO]:-Gathering information and validating the environment...
20160115:16:08:56:014623 gpstop:mdw:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information
20160115:16:08:56:014623 gpstop:mdw:gpadmin-[INFO]:-Obtaining Segment details from master...
20160115:16:08:56:014623 gpstop:mdw:gpadmin-[INFO]:-Greenplum Version: 'postgres (Greenplum Database) 4.3.6.2 build 1'
20160115:16:08:56:014623 gpstop:mdw:gpadmin-[INFO]:-Signalling all postmaster processes to reload

然后sdw3节点就可以远程访问数据库了。

[gpadmin@sdw3 gpdb]$ psql -h mdw -p 5432 -U gpadmin -d postgres
Password for user gpadmin:                    --输入gpadmin的密码gpadmin
psql (8.2.15)
Type "help" for help.

GreenPlum数据库的密码是区分大小写的,在设置密码和输入密码时需要注意。登录数据库后,该怎么使用呢?GreenPlum数据库提供了较详细的帮助手册,不知道怎么看帮助手册不要紧,help命令基本是通用的,直接输入help命令,就可以看到如何查看帮助手册了。

postgres=# 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

\h,SQL相关的手册。

\?,psql客户端的常用命令手册。

\q,退出数据库。

先看下\h帮助信息。

postgres=# \h
Available help:
  ABORT                       CREATE GROUP                DROP TABLE
  ALTER AGGREGATE             CREATE INDEX                DROP TABLESPACE
  ALTER CONVERSION            CREATE LANGUAGE             DROP TRIGGER
  ALTER DATABASE              CREATE OPERATOR             DROP TYPE
  ALTER DOMAIN                CREATE OPERATOR CLASS       DROP USER
  ALTER EXTERNAL TABLE        CREATE RESOURCE QUEUE       DROP USER MAPPING
  ALTER FILESPACE             CREATE ROLE                 DROP VIEW
  ALTER FOREIGN DATA WRAPPER  CREATE RULE                 END
  ALTER FUNCTION              CREATE SCHEMA               EXECUTE
  ALTER GROUP                 CREATE SEQUENCE             EXPLAIN
  ALTER INDEX                 CREATE SERVER               FETCH
  ALTER LANGUAGE              CREATE TABLE                GRANT
  ALTER OPERATOR              CREATE TABLE AS             INSERT
  ALTER OPERATOR CLASS        CREATE TABLESPACE           LISTEN
  ALTER RESOURCE QUEUE        CREATE TRIGGER              LOAD
  ALTER ROLE                  CREATE TYPE                 LOCK
  ALTER SCHEMA                CREATE USER                 MOVE
  ALTER SEQUENCE              CREATE USER MAPPING         NOTIFY
  ALTER SERVER                CREATE VIEW                 PREPARE
  ALTER TABLE                 DEALLOCATE                  PREPARE TRANSACTION
  ALTER TABLESPACE            DECLARE                     REASSIGN OWNED
  ALTER TRIGGER               DELETE                      REINDEX
  ALTER TYPE                  DROP AGGREGATE              RELEASE SAVEPOINT
  ALTER USER                  DROP CAST                   RESET
  ALTER USER MAPPING          DROP CONVERSION             REVOKE
  ANALYZE                     DROP DATABASE               ROLLBACK
  BEGIN                       DROP DOMAIN                 ROLLBACK PREPARED
  CHECKPOINT                  DROP EXTERNAL TABLE         ROLLBACK TO SAVEPOINT
  CLOSE                       DROP FILESPACE              SAVEPOINT
  CLUSTER                     DROP FOREIGN DATA WRAPPER   SELECT
  COMMENT                     DROP FUNCTION               SELECT INTO
  COMMIT                      DROP GROUP                  SET
  COMMIT PREPARED             DROP INDEX                  SET CONSTRAINTS
  COPY                        DROP LANGUAGE               SET ROLE
  CREATE AGGREGATE            DROP OPERATOR               SET SESSION AUTHORIZATION
  CREATE CAST                 DROP OPERATOR CLASS         SET TRANSACTION
  CREATE CONSTRAINT TRIGGER   DROP OWNED                  SHOW
  CREATE CONVERSION           DROP RESOURCE QUEUE         START TRANSACTION
  CREATE DATABASE             DROP ROLE                   TRUNCATE
  CREATE DOMAIN               DROP RULE                   UNLISTEN
  CREATE EXTERNAL TABLE       DROP SCHEMA                 UPDATE
  CREATE FOREIGN DATA WRAPPER DROP SEQUENCE               VACUUM
  CREATE FUNCTION             DROP SERVER                 VALUES

\h命令会列出大部分的命令,\h命令后面输入以上命令信息即可看到详细的帮助信息,以CREATE DATABASE为例。

postgres=# \h CREATE DATABASE         
Command:     CREATE DATABASE
Description: create a new database
Syntax:
CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] dbowner ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ TABLESPACE [=] tablespace ]
           [ CONNECTION LIMIT [=] connlimit ] ]

\?命令输出的结果太多,这里就不列举了,介绍几个个常用的psql客户端命令。

\l,列出所有数据库信息。

postgres=# \l
List of databases
Name|Owner|Encoding|Access privileges
postgres|gpadmin|UTF8|
template0|gpadmin|UTF8|=c/gpadmin     gpadmin=CTc/gpadmin
template1|gpadmin|UTF8|=c/gpadmin     gpadmin=CTc/gpadmin
(3 rows)

\c,切换用户、数据库。

postgres=# \c template1 
Password for user gpadmin:             --输入gpadmin用户的密码
You are now connected to database "template1" as user "gpadmin".

\conninfo,列出数据库的连接信息。

template1=# \conninfo
You are connected to database "template1" as user "gpadmin" on host "mdw" at port "5432".

\d,列出当前数据库所有的表,如果后面加表名,则列出表的字段及索引信息。

postgres -# \d
No relations found.

这是因为postgres数据库没有表,所以\d命令没有结果,创建一张表后,即可列出当前数据库的所有表信息。

template1=# create table tt (id int,name varchar(10)) DISTRIBUTED BY(ID);
CREATE TABLE

以上建表语句是创建一张基于ID字段hash分布的表,GreenPlum是分布式存储的数据库,表会创建到所有Segment节点上。

template1=# \d
List of relations
Schema    |    Name    |    Type     |    Owner    |    Storage
Public    |    tt      |    table    |    gpadmin  |    heap
(1 row)

\d命令后面加上表名,会列出表的详细信息。

template1=# \d tt
Table "public.tt"
Column    |    Type                |    Modifiers
Id        |    integer             |
Name      |   character varying(10)|
Distributed by: (id)

下面看下pg_database数据字典信息。

postgres-# \d pg_database
    Table "pg_catalog.pg_database"
    Column     |   Type    | Modifiers 
---------------+-----------+-----------
 datname       | name      | not null
 datdba        | oid       | not null
 encoding      | integer   | not null
 datistemplate | boolean   | not null
 datallowconn  | boolean   | not null
 datconnlimit  | integer   | not null
 datlastsysoid | oid       | not null
 datfrozenxid  | xid       | not null
 dattablespace | oid       | not null
 datconfig     | text[]    | 
 datacl        | aclitem[] | 
Indexes:
    "pg_database_datname_index" UNIQUE, btree (datname), tablespace "pg_global"
    "pg_database_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
Triggers:
    pg_sync_pg_database AFTER INSERT OR DELETE OR UPDATE ON pg_database FOR EACH STATEMENT EXECUTE PROCEDURE flatfile_update_trigger()

Tablespace: "pg_global"

\d命令不但可以列出表的列信息,还可以列出索引、触发器和表空间信息。

本文固定链接: http://www.dbdream.com.cn/2016/01/greenplum%e6%95%b0%e6%8d%ae%e5%ba%93%e7%99%bb%e5%bd%95%e5%92%8c%e5%9f%ba%e6%9c%ac%e7%9a%84%e5%b8%ae%e5%8a%a9%e6%89%8b%e5%86%8c%e4%bf%a1%e6%81%af/ | 信春哥,系统稳,闭眼上线不回滚!

该日志由 dbdream 于2016年01月15日发表在 GreenPlum 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: GreenPlum数据库登录和基本的帮助手册信息 | 信春哥,系统稳,闭眼上线不回滚!
关键字: ,

GreenPlum数据库登录和基本的帮助手册信息:等您坐沙发呢!

发表评论

快捷键:Ctrl+Enter