Deepgreen & Greenplum DBA One of the small white popularization classes (General FAQ)

Original link

Absrtact: Not accumulate Kuibu not even thousands of miles, want to become a qualified database administrator, first of all should have solid basic knowledge and problem processing ability. This article refers to the pivotal official FAQ to explain some common issues that are often encountered when using and managing Deepgreen & Greenplum.

Do not accumulate Kuibu not even thousands of miles, to become a qualified database administrator, first of all should have a solid basic knowledge and problem-handling capabilities. This article refers to the pivotal official FAQ for answers to some common questions that you often encounter when using and managing Deepgreen & Greenplum. Hope to have some help, if there are more friends have more questions to share, please leave a message, I will tidy up together. The following straightforward, start the problem to browse and solve the idea of carding: 1. How do I check a table's partitioning policy? Test table: Detailed description of the region table can show its partitioning strategy: 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: tDistributed by: (r_regionkey)

2. How do I see how many user modes are in the database? View with \DN in Psql

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. How do I check the time of my table last analyze? Test table: Region can view all actions of any object through the Pg_stat_operations view

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. How do I view the size of a table? Test table: Customer, under Mode 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. How do I view the schema size? Test mode: Public, simply replace the following where condition Schemaname= public with the schema name you want to query.

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. How do I view the database size? Test database: TPCH, you only need to replace the parentheses in the tpch for the database you need

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

To view the size of all databases:

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. How do I see the partition table size (including partitions and indexes)? Test table: 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. How do I view the syntax Help for a modified table? Use "\h + unknown Origin syntax" in Psql:

tpch=# \h Alter Tablecommand:alter tabledescription:change the definition of a tablesyntax:alter TABLE [only] name R ename [column] column to new_columnalter table name RENAME to new_namealter table name SET SCHEMA new_schemaalter table [O   NLY] 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_actionwhere 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 Indexcommand:create indexdescription:define a new indexsyntax:create [UNIQUE] INDEX name on TA ble [USING btree|bitmap|gist] ({column | (expression)} [Opclass] [, ...] ) [WITH (FILLFACTOR = value)] [tablespace tablespace] [WHERE predicate]

9. How can I connect to the system mode of master or segment instances from Master host?

[email protected]:~$ PGOPTIONS=‘-c gp_session_role=utility‘ psql -p 25432 -h flash -d postgrespsql (8.2.15)Type "help" for help.postgres=# \q

Where-P is followed by the port number of master or segment, followed by the corresponding master or segment host name,-D followed by the database name 10. Where do I look at database log information? Master Host:

Master节点的数据库日志存在$MASTER_DATA_DIRECTORY/pg_log/目录下，文件名根据数据库的log_filename参数生成。[email protected]:~$ gpconfig -s log_filenameValues on all segments are consistentGUC          : log_filenameMaster  value: gpdb-%Y-%m-%d_%H%M%S.csvSegment 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 Host:

     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. How do I view the list of functions? Performed in the Psql interface: \DF schemaname.functionname (SchemaName and function name support wildcard characters) for example:

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. How do I check if the database is running properly? Gpstate is a component of database state checking, and you can use GPSTATE-Q to quickly view the status of a database.

[email protected]:/dgdata/primary/dg0/pg_log$ gpstate-q20170701:07:09:37:027224 Gpstate:flash:dgadmin-[INFO ]:-starting gpstate with args:-q20170701: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 the May 05:19:19 ' 20170701:07:09:37:027224 gpst Ate:flash:dgadmin-[info]:-O btaining Segment details from master ... 20170701:07:09:37:027224 Gpstate:flash:dgadmin-[info]:--quick greenplum Database status from Master instance only20170701:07:09:37:027224 Gpstate:flash:dgadmin-[info]:----------------------------------------------------- ------20170701:07:09:37:027224 gpstate:flash:dgadmin-[info]:-# of up segments, from configuration table = 420,170,701:07 : 09:37:027224 Gpstate:flash:dgadmin-[info]:----------------------------------------------------------- 

13. How do I view the total list of databases? EXECUTE \l in the Psql interface (lowercase letter 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)

You can also use the following SQL to query for more detailed information:

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. How can I view the current user who is connected to you? Original link

Deepgreen & Greenplum DBA One of the small white popularization classes (General FAQ)