不积跬步无以至千里，要想成为一名合格的数据库管理员，首先应该具备扎实的基础知识及问题处理能力。本文参考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官网得到，免费用于测试，生产中使用建议联系官方授权 --> http://vitessedata.com/deepgreen-db-downloadGreenplum安装包可以从Pivotal官网得到，免费用于测试，生产中使用建议联系官方授权 --> https://network.pivotal.io/products/pivotal-gpdbGreenplum开源软件源码可以从Github免费得到 --> https://github.com/greenplum-db/gpdb 同系列相关文章：

Deepgreen & Greenplum DBA小白普及课之二（管理问题解答）

Deepgreen & Greenplum DBA小白普及课之三（备份问题解答）

Deepgreen & Greenplum DBA小白普及课之四（性能问题解答）