Deepgreen & Greenplum DBA小白普及课之一（一般问题解答） -

爱哭的小魔女

浏览: 71496 次
性别:
来自: 北京

最近访客更多访客>>

yflwz

星云联动

Youkes

smart_yb

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

2017-11 ( 13)
2017-07 ( 78)
2017-06 ( 16)
更多存档...

Deepgreen & Greenplum DBA小白普及课之一（一般问题解答）

ubuntu sql

原文链接：http://click.aliyun.com/m/24833/
摘要：不积跬步无以至千里，要想成为一名合格的数据库管理员，首先应该具备扎实的基础知识及问题处理能力。本文参考Pivotal官方FAQ，对一些在使用和管理Deepgreen & Greenplum时经常会遇到的普通问题进行讲解。

不积跬步无以至千里，要想成为一名合格的数据库管理员，首先应该具备扎实的基础知识及问题处理能力。本文参考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.如何创建一个数据库？
原文链接：http://click.aliyun.com/m/24833/

分享到：