241 阿裏雲技術社區[雲棲]

Deepgreen & Greenplum DBA小白普及課之一（一般問題解答）

不積跬步無以至千裏，要想成為一名合格的數據庫管理員，首先應該具備紮實的基礎知識及問題處理能力。本文參考Pivotal官方FAQ，對一些在使用和管理Deepgreen & Greenplum時經常會遇到的普通問題進行解答。希望對大家有所幫助，如果有朋友有更多的問題分享，請留言，我將一並整理。

下麵單刀直入，開始問題瀏覽及解決思路梳理：

1.如何檢查一張表的分區策略？

測試表：region

表的詳細描述信息可以展示其分區策略：Distributed by: (r_regionkey)

tpch=# \d region
                             Append-Only Columnar Table "public.region"
   Column    |          Type          |                          Modifiers                           
-------------+------------------------+--------------------------------------------------------------
 r_regionkey | integer                | not null default nextval('region_r_regionkey_seq'::regclass)
 r_name      | character(25)          |
 r_comment   | character varying(152) |
Checksum: t
Distributed by: (r_regionkey)

2.如何查看數據庫中有多少用戶模式？

在psql中使用\dn進行查看

tpch=# \dn
       List of schemas
        Name        |  Owner 
--------------------+---------
 gp_toolkit         | dgadmin
 information_schema | dgadmin
 pg_aoseg           | dgadmin
 pg_bitmapindex     | dgadmin
 pg_catalog         | dgadmin
 pg_toast           | dgadmin
 public             | dgadmin
(7 rows)

3.如何查看我的表上一次ANALYZE的時間？

測試表：region

通過pg_stat_operations視圖可以查看任何對象的所有操作

tpch=# select objname,actionname,statime from pg_stat_operations  where objname like 'region';
 objname | actionname |            statime           
---------+------------+-------------------------------
 region  | CREATE     | 2017-05-21 00:32:28.672208+08
 region  | ANALYZE    | 2017-06-30 06:55:57.658525+08
(2 rows)

4.如何查看表的大小？

測試表：customer，在模式public下

查看表的大小：
tpch=# select pg_size_pretty(pg_relation_size('public.customer'));
 pg_size_pretty
----------------
 122 MB
(1 row)
查看表和索引的大小：
tpch=# select pg_size_pretty(pg_total_relation_size('public.customer'));
 pg_size_pretty
----------------
 155 MB
(1 row)

5.如何查看模式（schema）大小？

測試模式：public，查詢時隻需將下麵where條件schemaname=後的public替換成你要查詢的schema名稱即可。

tpch=# select schemaname ,round(sum(pg_total_relation_size(schemaname||'.'||tablename))/1024/1024) "Size_MB" from pg_tables where schemaname='public' group by 1;
 schemaname | Size_MB
------------+---------
 public     |   10698
(1 row)

6.如何查看數據庫大小？

測試數據庫：tpch，你查詢時隻需要替換括號裏的tpch為你需要的數據庫即可

tpch=# select pg_size_pretty(pg_database_size('tpch'));
 pg_size_pretty
----------------
 10 GB
(1 row)

查看所有數據庫的大小：

tpch=# select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;
  datname  | pg_size_pretty
-----------+----------------
 tpch      | 10 GB
 postgres  | 111 MB
 tpch_1g   | 1100 MB
 template1 | 29 MB
 template0 | 27 MB
(5 rows)

7.如何查看分區表大小（包括分區和索引）？

測試表：employee_daily

select schemaname,tablename,round(sum(pg_total_relation_size(schemaname "" '.' "" partitiontablename))/1024/1024) "MB"
from pg_partitions where tablename='employee_daily' group by 1,2;
schemaname " tablename      " MB
-----------+----------------+-----
public     " employee_daily " 254

8.如何查看修改表的語法幫助？

在psql中使用【\h +待查語法】：

tpch=# \h alter table
Command:     ALTER TABLE
Description: change the definition of a table
Syntax:
ALTER TABLE [ONLY] name RENAME [COLUMN] column TO new_column

ALTER TABLE name RENAME TO new_name

ALTER TABLE name SET SCHEMA new_schema

ALTER TABLE [ONLY] name SET
     DISTRIBUTED BY (column, [ ... ] )
   | DISTRIBUTED RANDOMLY
   | WITH (REORGANIZE=true|false)

ALTER TABLE [ONLY] name action [, ... ]

ALTER TABLE name
   [ ALTER PARTITION { partition_name | FOR (RANK(number))
                     | FOR (value) } partition_action [...] ]
   partition_action

where action is one of:
  ADD [COLUMN] column_name type
      [ ENCODING ( storage_directive [,...] ) ]
      [column_constraint [ ... ]]
  DROP [COLUMN] column [RESTRICT | CASCADE]
  ALTER [COLUMN] column TYPE type [USING expression]
  ALTER [COLUMN] column SET DEFAULT expression
  ALTER [COLUMN] column DROP DEFAULT
  ALTER [COLUMN] column { SET | DROP } NOT NULL
  ALTER [COLUMN] column SET STATISTICS integer
  ADD table_constraint
  DROP CONSTRAINT constraint_name [RESTRICT | CASCADE]
  DISABLE TRIGGER [trigger_name | ALL | USER]


tpch=# \h create index
Command:     CREATE INDEX
Description: define a new index
Syntax:
CREATE [UNIQUE] INDEX name ON table
       [USING btree|bitmap|gist]
       ( {column | (expression)} [opclass] [, ...] )
       [ WITH ( FILLFACTOR = value ) ]
       [TABLESPACE tablespace]
       [WHERE predicate]

9.如何從Master主機連接到Master或者Segment實例的係統模式？

dgadmin@flash:~$ PGOPTIONS='-c gp_session_role=utility' psql -p 25432 -h flash -d postgres
psql (8.2.15)
Type "help" for help.
postgres=# \q

其中-p後麵接master或者segment的端口號，-h後麵接對應的master或者segment主機名，-d後麵接數據庫名

10.從哪裏看數據庫日誌信息？

Master主機：

Master節點的數據庫日誌存在$MASTER_DATA_DIRECTORY/pg_log/目錄下，文件名根據數據庫的log_filename參數生成。
dgadmin@flash:~$ gpconfig -s log_filename
Values on all segments are consistent
GUC          : log_filename
Master  value: gpdb-%Y-%m-%d_%H%M%S.csv
Segment value: gpdb-%Y-%m-%d_%H%M%S.csv
     默認安裝的日誌文件格式為：$MASTER_DATA_DIRECTORY/pg_log/gpdb-yyyy-mm-dd_000000.csv
     gpstart\gpstop\gpstate和另外utility生成的日誌存儲在：~gpadmin/gpAdminLogs/目錄下

Segment主機：

     Primary Segment日誌位置用下麵語句查詢：select dbid,hostname,datadir||'/pg_log' from gp_configuration where content not in (-1) and isprimary is true;
     Mirror Segment日誌位置用下麵語句查詢：Primary Segment日誌位置用下麵語句查詢：select dbid,hostname,datadir||'/pg_log' from gp_configuration where content not in (-1) and isprimary is false;

11.如何查看函數列表？

在psql界麵執行：\df schemaname.functionname (schemaname and function name support wildcard characters)

例如：

tpch=# \df pub*.*test*
                       List of functions
 Schema "   Name      " Result data type " Argument data types
--------+-------------+------------------+---------------------
 public " bugtest     " integer          "
 public " test        " boolean          " integer
 public " test        " void             "
(3 rows)

12.如何檢查數據庫運行狀態是否正常？

gpstate是數據庫狀態檢查的一個組件，可以使用gpstate -Q快速查看數據庫狀態。

dgadmin@flash:/dgdata/primary/dg0/pg_log$ gpstate -Q
20170701:07:09:37:027224 gpstate:flash:dgadmin-[INFO]:-Starting gpstate with args: -Q
20170701:07:09:37:027224 gpstate:flash:dgadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 4.3.99.00 build Deepgreen DB'
20170701:07:09:37:027224 gpstate:flash:dgadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 8.2.15 (Greenplum Database 4.3.99.00 build Deepgreen DB) on x86_64-unknown-linux-gnu, compiled by GCC gcc (Ubuntu 4.9.4-2ubuntu1~14.04.1) 4.9.4 compiled on May 18 2017 05:19:19'
20170701:07:09:37:027224 gpstate:flash:dgadmin-[INFO]:-Obtaining Segment details from master...
20170701:07:09:37:027224 gpstate:flash:dgadmin-[INFO]:--Quick Greenplum database status from Master instance only
20170701:07:09:37:027224 gpstate:flash:dgadmin-[INFO]:-----------------------------------------------------------
20170701:07:09:37:027224 gpstate:flash:dgadmin-[INFO]:-# of up segments, from configuration table     = 4
20170701:07:09:37:027224 gpstate:flash:dgadmin-[INFO]:-----------------------------------------------------------

13.如何查看數據庫總列表？

在psql界麵執行\l （英文字母L的小寫）:

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

也可以通過下麵sql查詢更詳細的信息：

postgres=# select * from pg_database;
  datname  | datdba | encoding | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace | datconfig |              datacl             
-----------+--------+----------+---------------+--------------+--------------+---------------+--------------+---------------+-----------+----------------------------------
 tpch      |     10 |        6 | f             | t            |           -1 |         10898 |          888 |          1663 |           |
 postgres  |     10 |        6 | t             | t            |           -1 |         10898 |          888 |          1663 |           |
 tpch_1g   |     10 |        6 | f             | t            |           -1 |         10898 |          888 |          1663 |           |
 template1 |     10 |        6 | t             | t            |           -1 |         10898 |          888 |          1663 |           | {=c/dgadmin,dgadmin=CTc/dgadmin}
 template0 |     10 |        6 | t             | f            |           -1 |         10898 |          888 |          1663 |           | {=c/dgadmin,dgadmin=CTc/dgadmin}
(5 rows)

14.如何查看當前你的連接用戶是誰？

在psql界麵執行\c：

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

15.如何查看psql命令的語法幫助？

在psql界麵通過執行\h或者\?：

\h     顯示任何SQL語法幫助
\?     顯示所有psql命令語法幫助

16.如何創建一個數據庫？

有兩種創建數據庫的方式：1）在psql界麵用sql語句創建 2）在shell命令行使用createdb工具命令創建

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

----------------------------------------------------

dgadmin@flash:~$ createdb --help
createdb creates a PostgreSQL database.

Usage:
  createdb [OPTION]... [DBNAME] [DESCRIPTION]

Options:
  -D, --tablespace=TABLESPACE  default tablespace for the database
  -e, --echo                   show the commands being sent to the server
  -E, --encoding=ENCODING      encoding for the database
  -O, --owner=OWNER            database user to own the new database
  -T, --template=TEMPLATE      template database to copy
  --help                       show this help, then exit
  --version                    output version information, then exit

Connection options:
  -h, --host=HOSTNAME          database server host or socket directory
  -p, --port=PORT              database server port
  -U, --username=USERNAME      user name to connect as
  -w, --no-password            never prompt for password
  -W, --password               force password prompt

By default, a database with the same name as the current user is created.

Report bugs to <pgsql-bugs@postgresql.org>.

17.如何刪除一個數據庫？

對應上條，刪除數據庫也有兩種方式：psql界麵和shell組件

postgres=# \h drop database
Command:     DROP DATABASE
Description: remove a database
Syntax:
DROP DATABASE [ IF EXISTS ] name

-------------------------------------------------

dgadmin@flash:~$ dropdb --help
dropdb removes a PostgreSQL database.

Usage:
  dropdb [OPTION]... DBNAME

Options:
  -e, --echo                show the commands being sent to the server
  -i, --interactive         prompt before deleting anything
  --help                    show this help, then exit
  --version                 output version information, then exit

Connection options:
  -h, --host=HOSTNAME       database server host or socket directory
  -p, --port=PORT           database server port
  -U, --username=USERNAME   user name to connect as
  -w, --no-password         never prompt for password
  -W, --password            force password prompt

Report bugs to <pgsql-bugs@postgresql.org>.

18.從哪可以下載Deepgreen和Greenplum的安裝包？

Deepgreen安裝包可以從VitesseData官網得到，免費用於測試，生產中使用建議聯係官方授權 --> https://vitessedata.com/deepgreen-db-download
Greenplum安裝包可以從Pivotal官網得到，免費用於測試，生產中使用建議聯係官方授權 --> https://network.pivotal.io/products/pivotal-gpdb
Greenplum開源軟件源碼可以從Github免費得到 --> https://github.com/greenplum-db/gpdb

最後更新：2017-07-02 11:26:45

Deepgreen & Greenplum DBA小白普及課之一（一般問題解答）

上一篇： centos5.x格式化ext4文件係統

下一篇： OSS Web直傳方案在iOS中上傳視頻時需要注意的一個小坑

相關內容

熱門內容

最新內容