磐维2.0数据库日常维护

磐维数据库简介

“中国移动磐维数据库”（ChinaMobileDB），简称“磐维数据库”（PanWeiDB）。是中国移动信息技能中心首个基于中国本土开源数据库打造的面向ICT基础设施的自研数据库产品。
其产品内核能力基于华为 OpenGauss 开源软件，并进一步提升了系统稳定性。
磐维数据库 V1.0 基于 openGauss 3.0 研发，在2022年12月29日，openGauss Summit 2022大议上正式发布。
磐维数据库 V2.0 基于 openGauss 5.0 研发，在2023年12月28日，openGauss Summit 2023大会上重磅发布。
磐维数据库2.0，具有高性能、高可靠、高安全、高兼容等特点，可以或许为集中式、分布式、云原生、一体机等多种应用场景提供强大支持。
1.磐维数据库查询效果按列展示

         磐维数据库中怎样实现查询效果按列展示？类似与MySQL数据库的\G效果，可以使用\x下令，以扩展方式显示查询效果

1. [omm@pw01 data]$ gsql -d postgres
2. gsql ((PanWeiDB 2.0.0 (Build0)) compiled at 2024-01-05 17:19:18 commit 9fbca90 last mr  )
3. Non-SSL connection (SSL connection is recommended when requiring high-security)
4. Type "help" for help.
6. postgres=# \x
7. Expanded display is on.
8. postgres=# \l
9. List of databases
10. -[ RECORD 1 ]-----+------------
11. Name              | panweidb
12. Owner             | omm
13. Encoding          | UTF8
14. Collate           | C
15. Ctype             | C
16. Access privileges |
17. -[ RECORD 2 ]-----+------------
18. Name              | postgres
19. Owner             | omm
20. Encoding          | UTF8
21. Collate           | C
22. Ctype             | C
23. Access privileges |
24. -[ RECORD 3 ]-----+------------
25. Name              | template0
26. Owner             | omm
27. Encoding          | UTF8
28. Collate           | C
29. Ctype             | C
30. Access privileges | =c/omm
31.                   | omm=CTc/omm
32. -[ RECORD 4 ]-----+------------
33. Name              | template1
34. Owner             | omm
35. Encoding          | UTF8
36. Collate           | C
37. Ctype             | C
38. Access privileges | =c/omm
39.                   | omm=CTc/omm
41. postgres=# select * from pg_user limit 1;
42. -[ RECORD 1 ]----+-------------
43. usename          | pwaudit
44. usesysid         | 34
45. usecreatedb      | f
46. usesuper         | f
47. usecatupd        | f
48. userepl          | f
49. passwd           | ********
50. valbegin         |
51. valuntil         |
52. respool          | default_pool
53. parent           | 0
54. spacelimit       |
55. useconfig        |
56. nodegroup        |
57. tempspacelimit   |
58. spillspacelimit  |
59. usemonitoradmin  | f
60. useoperatoradmin | f
61. usepolicyadmin   | f
63. postgres=#
65. postgres=# \x
66. Expanded display is off.
67. postgres=#

复制代码

2.gsql常用下令介绍

本文介绍磐维数据库里后台psql工具在一些常用场景下的快捷使用下令。
2.1 使用-r便捷编辑模式

1. [omm@pw01 data]$ gsql -d postgres -r
2. gsql ((PanWeiDB 2.0.0 (Build0)) compiled at 2024-01-05 17:19:18 commit 9fbca90 last mr  )
3. Non-SSL connection (SSL connection is recommended when requiring high-security)
4. Type "help" for help.
6. postgres=#

复制代码

否则当输入出错时，不能直接使用删除键。

2.2 快速编辑SQL并执行

元下令\e自动打开上次的查询举行编辑，编辑完成之后立刻执行

1. [omm@pw01 data]$ gsql -d postgres -r
2. gsql ((PanWeiDB 2.0.0 (Build0)) compiled at 2024-01-05 17:19:18 commit 9fbca90 last mr  )
3. Non-SSL connection (SSL connection is recommended when requiring high-security)
4. Type "help" for help.
6. postgres=# select * from pg_users limit 1;ERROR:  relation "pg_users" does not exist on dn_6001_6002_6003LINE 1: select * from pg_users limit 1;                      ^postgres=# \eselect * from pg_users limit 1;~

复制代码

上面使用\e直接在上一个SQL语句里举行编辑修改，然后wq保存退出编辑模式之后，自动执行新SQL语句。
2.3 定制客户端提示符

客户端提示符变量，我们可以定制PROMPT1(等待新下令时的提示符)，.gsqlrc文件可以举行下面的设置：

1. \set PROMPT1 '%`date +%H:%M:%S` (%n@%M:%>)%/%R%#%x'
4. postgres=# \set PROMPT1 '%`date +%H:%M:%S` (%n@%M:%>)%/%R%#%x'
5. 17:40:30 (omm@local:/database/panweidb/tmp:17700)postgres=#
6. 17:40:32 (omm@local:/database/panweidb/tmp:17700)postgres=#select * from dual;
7. dummy
8. -------
9. X
10. (1 row)
12. 17:40:50 (omm@local:/database/panweidb/tmp:17700)postgres=#

复制代码

PROMPT1变量可以定制操作系统的下令，比方显示时间。
2.4 使用边框美化查询效果

在.gsqlrc文件增加下面的设置，可以对查询效果设置边框，美化输出。

1. \pset border 2
2. \pset linestyle unicode

复制代码

2.5 查询效果自动适配屏幕宽度

在psql里面使用如下pset下令

1. pset format wrapped \pset columns 0

复制代码

1. 或者psql选项里加这两个设置$ psql -r --pset=format=wrapped --pset=columns=0

复制代码

2.6 输出差别的查询格式

文本紧凑模式

1. [omm@pw01 data]$ psql -qtA  -c "select * from pg_user limit 2;"
2. pwaudit|34|0|0|0|0|********|||default_pool|0||||||0|0|0
3. pwsso|35|0|0|0|0|********|||default_pool|0||||||0|0|0
4. [omm@pw01 data]$

复制代码

文本紧凑模式，使用多字符分隔符@@

1. [omm@pw01 data]$ psql -qtA -F '@@'  -c  "select * from pg_user limit 2;"
2. pwaudit@@34@@0@@0@@0@@0@@********@@@@@@default_pool@@0@@@@@@@@@@@@0@@0@@0
3. pwsso@@35@@0@@0@@0@@0@@********@@@@@@default_pool@@0@@@@@@@@@@@@0@@0@@0
4. [omm@pw01 data]$

复制代码

HTML格式

1. [omm@pw01 data]$ psql -qtH  -c "select * from pg_user limit 2;"
2. <table border="1">
3.   <tr valign="top">
4.     <td align="left">pwaudit</td>
5.     <td align="right">34</td>
6.     <td align="left">0</td>
7.     <td align="left">0</td>
8.     <td align="left">0</td>
9.     <td align="left">0</td>
10.     <td align="left">********</td>
11.     <td align="left">&nbsp; </td>
12.     <td align="left">&nbsp; </td>
13.     <td align="left">default_pool</td>
14.     <td align="right">0</td>
15.     <td align="left">&nbsp; </td>
16.     <td align="left">&nbsp; </td>
17.     <td align="left">&nbsp; </td>
18.     <td align="left">&nbsp; </td>
19.     <td align="left">&nbsp; </td>
20.     <td align="left">0</td>
21.     <td align="left">0</td>
22.     <td align="left">0</td>
23.   </tr>
24.   <tr valign="top">
25.     <td align="left">pwsso</td>
26.     <td align="right">35</td>
27.     <td align="left">0</td>
28.     <td align="left">0</td>
29.     <td align="left">0</td>
30.     <td align="left">0</td>
31.     <td align="left">********</td>
32.     <td align="left">&nbsp; </td>
33.     <td align="left">&nbsp; </td>
34.     <td align="left">default_pool</td>
35.     <td align="right">0</td>
36.     <td align="left">&nbsp; </td>
37.     <td align="left">&nbsp; </td>
38.     <td align="left">&nbsp; </td>
39.     <td align="left">&nbsp; </td>
40.     <td align="left">&nbsp; </td>
41.     <td align="left">0</td>
42.     <td align="left">0</td>
43.     <td align="left">0</td>
44.   </tr>
45. </table>
47. [omm@pw01 data]$

复制代码

2.7检察元下令的query语句

使用-E或者–echo-hidden检察db的执行语句

1. $ psql -E -c "\l"
3. [omm@pw01 data]$ psql -E -c "\l"
4. ********* QUERY **********
5. SELECT d.datname as "Name",
6.        pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
7.        pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
8.        d.datcollate as "Collate",
9.        d.datctype as "Ctype",
10.        pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
11. FROM pg_catalog.pg_database d
12. ORDER BY 1;
13. **************************
15.                          List of databases
16.    Name    | Owner | Encoding | Collate | Ctype | Access privileges
17. -----------+-------+----------+---------+-------+-------------------
18. panweidb  | omm   | UTF8     | C       | C     |
19. postgres  | omm   | UTF8     | C       | C     |
20. template0 | omm   | UTF8     | C       | C     | =c/omm           +
21.            |       |          |         |       | omm=CTc/omm
22. template1 | omm   | UTF8     | C       | C     | =c/omm           +
23.            |       |          |         |       | omm=CTc/omm
24. (4 rows)
26. [omm@pw01 data]$

复制代码

检察自界说函数函数列表

1. $ psql -E -c "\df"

复制代码

2.8 同时显示query语句及效果

使用-e，–echo-queries回显语句

1. $ psql -e -c "select 'x' from dual;select now();"

复制代码

2.9 单步调试

使用-s，–single-step单步调试

1. $ psql -s -c "select 'x' from dual;select now();"

复制代码

3.常用gsql的元下令

3.1 检察客户端工具版权信息：\copyright

3.2 检察当前登录信息：\conninfo

3.3 检察有哪些数据库：\l

3.4 检察有哪些数据表：\dt

3.5 \d检察表界说

1. postgres=# \dt
2.                   List of relations
3. Schema |           Name           | Type  |  Owner
4. --------+--------------------------+-------+----------
5. public | cloud_sql_static         | table | postgres
6. public | cloud_subscriber_devices | table | postgres
7. public | cloud_subscribers        | table | postgres
8. public | cloud_table_static       | table | postgres
9. public | sxacc-device-types       | table | postgres
10. public | sxacc-devices            | table | postgres
11. (6 rows)
13. postgres=# \d cloud_table_static
14.              Table "public.cloud_table_static"
15.        Column        |           Type           | Modifiers
16. ---------------------+--------------------------+-----------
17. relname             | character varying(255)   |
18. seq_scan            | bigint                   |
19. seq_tup_read        | bigint                   |
20. idx_scan            | bigint                   |
21. idx_tup_fetch       | bigint                   |
22. n_tup_ins           | bigint                   |
23. n_tup_upd           | bigint                   |
24. n_tup_del           | bigint                   |
25. n_tup_hot_upd       | bigint                   |
26. n_live_tup          | bigint                   |
27. n_dead_tup          | bigint                   |
28. n_mod_since_analyze | bigint                   |
29. last_vacuum         | timestamp with time zone |
30. last_autovacuum     | timestamp with time zone |
31. last_analyze        | timestamp with time zone |
32. last_autoanalyze    | timestamp with time zone |
33. vacuum_count        | bigint                   |
34. autovacuum_count    | bigint                   |
35. analyze_count       | bigint                   |
36. autoanalyze_count   | bigint                   |
37. import_date         | timestamp with time zone |
39. postgres=#

复制代码

3.6 检察表/索引占用空间大小 

2. 给测试表test插入500万数据：
4. postgres=# create table test(id int primary key, name varchar(100));
5. CREATE TABLE
6. postgres=# insert into test(id,name) select n,n||'_francs' from generate_series(1,5000000) n;
7. INSERT 0 5000000
8. postgres=# \di+ test_pkey
9.                           List of relations
10. Schema |   Name    | Type  |  Owner   | Table |  Size  | Description
11. --------+-----------+-------+----------+-------+--------+-------------
12. public | test_pkey | index | postgres | test  | 107 MB |
13. (1 row)
15. postgres=# \dt+ test
16.                     List of relations
17. Schema | Name | Type  |  Owner   |  Size  | Description
18. --------+------+-------+----------+--------+-------------
19. public | test | table | postgres | 249 MB |
20. (1 row)

复制代码

3.7 切换数据库：\c 数据库名

3.8 切换目次

1. cd 如果不带参数，则切换到当前用户的主目录。\! pwd用来显示当前工作路径（叹号后有空格）
2. postgres=# \! pwd
3. /home/postgres
4. postgres=# \!pwd
5. Invalid command \!pwd. Try \? for help.
6. postgres=# \cd /pgtbs
7. postgres=# \! pwd
8. /pgtbs
9. postgres=#

复制代码

3.9 显示执行时间（\timing on | off）

1. mydb=> \timing on
2. Timing is on.
3. mydb=> select id,name from t1 limit 10;

复制代码

3.10 退出下令行客户端：\q

1. [omm@panwei ~]$ gsql -d escspdb -r
2. gsql ((PanWeiDB 2.0.0 (Build0)) compiled at 2024-01-05 17:19:18 commit 9fbca90 last mr  )
3. 非SSL连接（安全性要求高时，建议使用SSL连接）
4. 输入 "help" 来获取帮助信息。
6. escspdb=# \q
7. [omm@panwei ~]$

复制代码

3.11 其他

1. \db 列出表空间信息
2. \du 列出用户、角色
3. \dt 列出数据库表
4. \di 列出索引
5. \dx 列出插件
6. \df 列出函数
7. \dv 列出view
8. \i FILE 执行文件中的命令
9. \h [NAME]  sql命令的help信息
11. 使用psql执行sql命令，-c :指定psql执行一个给定的命令字符串command
12. psql -c '\x' -c 'SELECT * FROM pg_user;'
14. 使用psql执行sql文件，-f :从文件filename而不是标准输入中读取命令
15. [postgres@pgserver12 ~]$ cat test.sql
16. select now();
17. select current_date;
18. select 1;
19. [postgres@pgserver12 ~]$ psql -f test.sql
22. 把所有查询输出放到文件filename中。这等效于命令\o。
23. [postgres@pgserver12 ~]$ psql -o /tmp/2.log

复制代码

4.死锁

4.1 怎样统计死锁的条数：

1. postgres=# select datname,deadlocks from pg_stat_database;
2.   datname  | deadlocks
3. -----------+-----------
4. template1 |         0
5. template0 |         0
6. panweidb  |         0
7. postgres  |         0
8. (4 rows)
10. postgres=#

复制代码

4.2 怎样找到导致死锁的sql

方法1、通过报错找到对应的sql:

1. select * from pg_stat_activity where datname = 'db01';
3. select datname,pid,query,connection_info from  pg_stat_activity where pid='47183102150400' or pid='47183356102400';

复制代码

以上的两个pid，是从死锁的报错中获取的。
方法2、直接去pg_log日志里，检索deadlock detected，下边有执行死锁的sql。

5.杀死会话


对于磐维CMDB数据库的会话查杀，建议优先使用pg_terminate_backend函数，如果查杀失败，需要使用更强力的下令时，可以使用kill -9或者kill下令查杀客户端进程。
在执行查杀的会话窗口中，查询pg_stat_activity获取连接会话的PID

1. select datid,pid,state,query from pg_stat_activity;
3. postgres=# select datid,pid,state,query from pg_stat_activity;
4. datid |      pid       | state  |                        query                        
5. -------+----------------+--------+-----------------------------------------------------
6. 20538 | 47456966424320 | idle   |
7. 20538 | 47456949643008 | idle   |
8. 20538 | 47457226524416 | idle   | show default_transaction_read_only;
9. 20538 | 47457377584896 | active | select datid,pid,state,query from pg_stat_activity;
10. (4 rows)
12. postgres=#

复制代码

使用pg_terminate_backend杀会话

1. select * from pg_terminate_backend(47440526968576);
3. postgres=# select * from pg_terminate_backend(47440526968576);
4. WARNING:  PID 47440526968576 is not a gaussdb server thread
5. pg_terminate_backend
6. ----------------------
7. f
8. (1 row)
10. postgres=#

复制代码

 查杀会话举例：

1. --先查会话的pid
2. postgres=# select datid,pid,state,query from pg_stat_activity where query like '%pw_version%';
3. datid |      pid       | state  |                                        query                                       
4. -------+----------------+--------+-------------------------------------------------------------------------------------
5. 20538 | 47922937857792 | active | select datid,pid,state,query from pg_stat_activity where query like '%pw_version%';
6. 20538 | 47922981963520 | idle   | select * from pw_version();
7. (2 rows)
8. --基于上一步查到的pid杀会话
9. postgres=# select * from pg_terminate_backend(47922981963520);
10. pg_terminate_backend
11. ----------------------
12. t
13. (1 row)
14. --再次查询，会话已消失
15. postgres=# select datid,pid,state,query from pg_stat_activity where query like '%pw_version%';
16. datid |      pid       | state  |                                        query                                       
17. -------+----------------+--------+-------------------------------------------------------------------------------------
18. 20538 | 47922937857792 | active | select datid,pid,state,query from pg_stat_activity where query like '%pw_version%';
19. (1 row)

复制代码

6.sql执行筹划

SQL执行筹划概述：
SQL执行筹划是一个节点树，显示磐维数据库执行一条SQL语句时执行的详细步骤。每一个步骤为一个数据库运算符。
怎样获取执行筹划：
使用EXPLAIN下令可以检察优化器为每个查询生成的具体执行筹划。EXPLAIN给每个执行节点都输出一行，显示基本的节点类型和优化器为执行这个节点预计的开销值。具体用法如下：

• EXPLAIN statement：只生成执行筹划，不实际执行。其中statement代表SQL语句。
  
• EXPLAIN ANALYZE statement：生成执行筹划，举行执行，并显示执行的概要信息。显示中加入了实际的运行时间统计，包括在每个规划节点内部花掉的总时间（以毫秒计）和它实际返回的行数。
  
• EXPLAIN PERFORMANCE statement：生成执行筹划，举行执行，并显示执行期间的全部信息。
  

1. escspdb=# explain select business_id from cert where business_id >100000;
2.                        QUERY PLAN                        
3. ---------------------------------------------------------
4. Seq Scan on cert  (cost=0.00..395.90 rows=620 width=23)
5.    Filter: ((business_id)::bigint > 100000)
6. (2 行记录)
8. escspdb=#
11. escspdb=# EXPLAIN ANALYZE select business_id from cert where business_id ='Test_20230720_009';
12.                                                       QUERY PLAN                                                      
13. ----------------------------------------------------------------------------------------------------------------------
14. [Bypass]
15. Index Only Scan using cert_pkey on cert  (cost=0.00..8.27 rows=1 width=23) (actual time=0.059..0.059 rows=1 loops=1)
16.    Index Cond: (business_id = 'Test_20230720_009'::text)
17.    Heap Fetches: 1
18. Total runtime: 0.105 ms
19. (5 行记录)
21. escspdb=#
23. escspdb=# EXPLAIN PERFORMANCE select business_id from cert where business_id ='Test_20230720_009';
24.                                                          QUERY PLAN                                                         
25. ----------------------------------------------------------------------------------------------------------------------------
26. [Bypass]
27. Index Only Scan using cert_pkey on escsp.cert  (cost=0.00..8.27 rows=1 width=23) (actual time=0.026..0.027 rows=1 loops=1)
28.    Output: business_id
29.    Index Cond: (cert.business_id = 'Test_20230720_009'::text)
30.    Heap Fetches: 1
31.    (Buffers: shared hit=3)
32.    (CPU: ex c/r=43646396419570728, ex row=1, ex cyc=43646396419570728, inc cyc=43646396419570728)
33. Total runtime: 0.110 ms
34. (8 行记录)
36. escspdb=#

复制代码

7.数据库日志网络

1. gs_collector --begin-time="BEGINTIME" --end-time="ENDTIME" [-h HOSTNAME |
2. -f HOSTFILE] [--keyword=KEYWORD] [--speed-limit=SPEED] [-o OUTPUT] [-l LO
3. GFILE] [-C CONFIGFILE]
5. 示例：gs_collector --begin-time="20240707 14:00" --end-time="20240707 19:00"
6. 说明：不加-h参数时，收集的是集群中所有节点的信息
8. [omm@pw01 data]$ gs_collector --begin-time="20240707 14:00" --end-time="20240707 19:00"
9. Successfully parsed the configuration file.
10. create Dir.
11. Successfully create dir.
12. do system check interval 0 : count 1
13. Collecting OS information.
14. The cmd is source /home/omm/.bashrc; python3 '/database/panweidb/tool/script/local/LocalCollect.py' -t system_check -U omm -l /database/panweidb/log/omm/om/gs_local.log -C '{#TypeName#: #System#, #Content#: #ps,ioStat,netFlow,spaceUsage,cpuInfo,memInfo,disk,#, #Interval#: #0#, #Count#: #1#}'
15. Failed to collect OS information.
16. do database check interval 0 : count 1
17. Collecting catalog statistics.
18. Successfully collected catalog statistics.
19. do log check interval 0 : count 1
20. Collecting Log files.
21. Successfully collected Log files.
22. do Config check 0:1
23. Collecting Config files.
24. Successfully collected Config files.
25. Collecting files.
26. Successfully collected files.
27. All results are stored in /database/panweidb/log/omm/collector_20240707_190115.tar.gz.
28. [omm@pw01 data]$

复制代码

 8.创建用户和数据库

1. 需求描述
2. 库名：pwejb
3. 用户名：pwejb
4. 端口：17700
5. 密码：1qlIk8L2aaWW
6. 客户端白名单：192.168.122.231
8. a.创建用户USER
9. CREATE USER pwejb IDENTIFIED BY '1qlIk8L2aaWW';
10. grant all privileges to pwejb;
12. b. 使用如下命令创建一个新的表空间pwejb_tbs，并将表空间pwejb_tbs授权给用户pwejb
13. CREATE TABLESPACE pwejb_tbs OWNER pwejb RELATIVE LOCATION 'tablespace/pwejb_tbs1';
15. c. 使用如下命令创建一个新的数据库pwejb，放在使用pwejb_tbs表空间中,使用UTF8编码，兼容性为B
16. CREATE DATABASE pwejb WITH TABLESPACE = pwejb_tbs ENCODING='UTF8';
18. d.使用以下命令为数据库设置默认的模式搜索路径。
19. ALTER DATABASE pwejb SET search_path TO pa_catalog,public;
20. grant all privileges on database pwejb to pwejb;
22. e.使用如下命令修改数据库表空间
23. ALTER DATABASE pwejb SET TABLESPACE pwejb_tbs;
25. f.在数据库本地测试数据库连接
26. gsql -d pwejb -U pwejb -W '1qlIk8L2aaWW' -r
29. g.添加客户端白名单，允许192.168.122.231远程访问磐维数据库
30. gs_guc reload -N all -I all -h 'host all all 192.168.122.231/24 sha256'
32. h.通过客户端连接数据库
33. gsql -h IP -p 17700 -d pwejb -U pwejb -r

复制代码

9.修改数据库暗码有效期

数据库用户的暗码都有暗码有效期（password_effect_time），当达到暗码到期提示天数
（password_notify_time）时，系统会在用户登录数据库时提示用户修改暗码。

1. postgres=# show password_effect_time;
2. password_effect_time
3. ----------------------
4. 90
5. (1 row)
7. postgres=# show password_notify_time;
8. password_notify_time
9. ----------------------
10. 7
11. (1 row)
13. -- 修改用户密码有效期时间为36500(集群内所有节点执行)
14. alter system set password_effect_time to 36500;
17. -- 用户密码有效期查询校验(集群内所有节点查询确认)
18. select b.usename,a.passwordtime,a.passwordtime+numtodsinterval(to_number((select setting from pg_settings where name='password_effect_time')),'DAY') as passwordexpiredtime  from
19. (select roloid,max(passwordtime) as passwordtime from pg_catalog.pg_auth_history group by roloid) a
20. right join
21. (select usename,usesysid from pg_user) b
22. on a.roloid=b.usesysid;
24. usename |         passwordtime          |      passwordexpiredtime      
25. ---------+-------------------------------+-------------------------------
26. omm     | 2024-07-07 17:03:19.04627+08  | 2124-06-13 17:03:19.04627+08
27. pwadmin | 2024-07-07 17:03:19.063156+08 | 2124-06-13 17:03:19.063156+08
28. pwsso   | 2024-07-07 17:03:19.079373+08 | 2124-06-13 17:03:19.079373+08
29. pwaudit | 2024-07-07 17:03:19.095594+08 | 2124-06-13 17:03:19.095594+08
30. (4 rows)
32. postgres=#
33. -- 用户密码有效期查询校验(集群内所有节点查询确认)  
34. show password_effect_time;

复制代码

 10.数据库添加白名单

1. gs_guc reload -N all -I all -h 'host all all 192.168.122.230/24 sha256'
3. --以上命令执行完成之后，会在3台主机的pg_hba.conf增加如下内容。
5. [omm@pw01 data]$ cat /database/panweidb/data/pg_hba.conf|grep 230
6. host all all 192.168.122.230/24 sha256
7. [omm@pw01 data]$

复制代码

11.检察数据库大小（降序）

1. select datname,pg_size_pretty(pg_database_size(datname)) as dbsize from pg_database order by 2 desc;
4. escspdb=# select datname,pg_size_pretty(pg_database_size(datname)) as dbsize from pg_database order by 2 desc;
5.     datname     | dbsize  
6. ----------------+---------
7. testzaq        | 9015 MB
8. caoss          | 60 MB
9. template1      | 24 MB
10. panweidb       | 24 MB
11. template0      | 24 MB
12. (5 行记录)
14. escspdb=#

复制代码

13.检察表大小（降序）

1. select tableowner,schemaname,tablename,pg_size_pretty(pg_table_size(schemaname||'.'||tablename)) as table_size from pg_tables order by pg_table_size(schemaname||'.'||tablename) desc;
3. escspdb=# select tableowner,schemaname,tablename,pg_size_pretty(pg_table_size(schemaname||'.'||tablename)) as table_size from pg_tables order by pg_table_size(schemaname||'.'||tablename) desc;
4. tableowner |     schemaname     |           tablename           | table_size
5. ------------+--------------------+-------------------------------+------------
6. escsp      | escsp              | operation_info                | 448 MB
7. omm        | pg_catalog         | pg_attribute                  | 5552 kB
8. omm        | pg_catalog         | pg_rewrite                    | 3360 kB
9. escsp      | escsp              | cert                          | 2976 kB
10. omm        | pg_catalog         | pg_proc                       | 2520 kB
11. ...

复制代码

14.磐维数据库集群管理组件介绍

       磐维数据库作为面向OLTP业务的关系型数据库，在主备集群模式下，怎样实现对节点的监控，感知故障，实现自动主备切换，保证集群的高可用，是至关重要的，信赖大家也非常感爱好，下面我们就一起来揭秘磐维数据集群管理组件Cluster Manager。
14.1 集群介绍

Cluster Manager（CM）是磐维数据库集群管理组件，是磐维数据库集群高可用的保障。CM的重要功能有：

• 数据库主备节点状态监控，故障自动主备切换
  
• 网络通讯故障、文件系统故障等故障监控
  
• 支持集群、节点查询、设置、操作等
  

此外，还支持自界说资源纳管，如分布式中心件ShardingSphere等。
14.1.1 高可用组网  

CM组件包含CM Agent（CMA），CM Server（CMS），OM Monitor（CMM）三个核心模块。为保证磐维数据库节点（DN）的高可用，需要满足以下组网条件：

• 每个节点都需要部署CMM、CMA：CMM监控本节点CMA实例；CMA监控本节点的DN、CMS实例，采集信息上报CMS主
  
• CMS、DN不需要每个节点都部署
  
• CMS、DN实例组成各自的集群
  
• CMS集群实个数>=3，基于Quorum协议实现自仲裁，以及DN仲裁，需要保证一半以上实例存活
  

磐维数据库推荐的高可用部署方案为一主两备，组网如下图所示。除此之外，CM还支持双中心组网方案，并支持优先同中心选主。         

14.1.2进程保活机制  

CM组件包含cm_server，cm_agent，以及om_monitor三个进程，分别为CMS、CMA，以及CMM模块。磐维数据库（DN）进程为gaussdb。如下图所示：

DN、CMS保活：由CMA来负责
CMA每秒查抄一次DN、CMS健康状态。若进程不存在，拉起进程；通过查抄DN、CMS进程状态，以及时间阈值内多次查询检测进程是否僵死，并负责杀死僵死进程，重新拉起。
CMA保活：由CMM来负责
CMM每秒查抄一次CMA健康状态。若进程不存在，拉起进程；若进程僵死，则杀死僵死进程，重新拉起。
CMM保活：由系统定时任务来兜底
系统定时任务每分钟第0秒执行一次。若进程不存在，下一轮定时任务执行时拉起进程；若进程僵死，则下一轮定时任务执行时杀死僵死进程，再下一轮重新拉起。

14.2 重要模块及功能 

CM组件团体架构，以及模块核心功能如下图所示：

14.2.1 CM Agent  

CM Agent在多线程模式下，实现对本地DN、CMS的看护，以及CMS与DN的交互。重要功能如下：
查抄保持与CMS主的连接
CMA的ConnCmsPMain线程负责循环查抄与CMS主的连接状态，查抄的间隔AGENT_RECV_CYCLE为200ms。若连接不存在，则重新建立连接。
查抄与所有peer DN的连接
CMA的DNConnectionStatusCheckMain线程负责循环ping所有peer DN节点，查抄的间隔由agent_report_interval参数控制，默认为1s。若与所有peer DN节点断连，且本节点为DN主，则上报CMS，并杀死本节点DN，CMS将启动DN仲裁。
查抄上报DN的状态
CMA的DNStatusCheckMain线程负责循环查抄DN状态，查抄的间隔由agent_report_interval参数控制，默认为1s。若进程不存在，则上报CMS，并拉起进程，CMS仲裁模块将启动DN仲裁。
查抄上报CMS的状态
CMA同样循环检测本节点CMS状态，每次查抄的间隔为1s。若进程不存在，则上报CMS，并拉起进程，CMS HA模块将启动主备自仲裁。若进程出现T状态，则判断为进程僵死，则将僵死情况上报CMS，并杀死进程，重新拉起，CMS HA模块也将启动主备自仲裁。
检测磁盘使用率、磁盘故障
CMA通过在数据、日志路径下创建临时文件，举行读写测试（fopen，fwrite，fread），来检测DN、CMS磁盘故障，若出现失败，则将disc damage状态上报CMS。
检测CPU、MEM、磁盘IO使用率
CMA还负责检测节点cpu、mem，以及所有磁盘io使用是否达阈值。
检测进程僵死
CMA的DNPhonyDeadStatusCheckMain线程负责循环检测DN、CMS进程是否僵死，检测的间隔由agent_phony_dead_check_interval参数控制，默认为10s，其中包含检测时间。
若DN进程出现T、D、Z状态，则判断为进程僵死，将T、Z状态上报CMS，若为T状态，则将检测间隔更新为36s。若非以上状态，则连接数据库，执行查询语句，若执行失败，则判断为进程僵死。当上报次数达到阈值，CMS仲裁模块将启动僵死处理。
向CMS主上报消息
CMA的SendCmsMsgMain线程负责循环将DN、CMS状态上报CMS主，每次上报的间隔为200ms。
处理CMS主下发的下令
CMA同时负责接收并处理CMS下发的下令，根据下令类型，执行对DN的操作。


14.2.2 CM Server  

CM Server通过CMA是实现与DN的交互。CMS集群基于Quorum协议，实现对DN集群的仲裁选主，以及自仲裁。核心功能如下：
处理DN节点仲裁
服务线程接收CMA定时上报的DN节点状态，感知DN异常。若为主故障，则启动仲裁，确定候选DN后，启动升主流程。若进程被判断为僵死，则举行僵死处理，若僵死进程为主节点，处理完成后，进入仲裁选主。
处理CMS节点自仲裁
服务线程接收CMA定时上报的CMS节点状态，感知CMS故障，若为主故障，则根据Quorum协议实现CMS集群选主。
处理存储达阈值、磁盘故障
服务线程接收CMA定时上报的DN数据、日志路径磁盘使用情况，循环处理，间隔由datastorage_threshold_check_interval参数控制，默认为10s，每分钟输出检测日志。
当磁盘用量凌驾只读阈值的80%（只读阈值由datastorage_threshold_value_check控制，默认为85%）时，发送预报警，否则清除预报警。当凌驾只读阈值时，则向所有DN实例发送只读下令（通过CMA），发送只读告警，否则清除所有DN实例只读下令状态（通过CMA），清除只读告警。
需要注意的是，只读状态为集群级设置，因此集群中任何一个节点磁盘使用达到阈值，集群都会被设置为只读模式，导致写哀求返回失败。

 15.集群维护


15.1启停数据库集群

1. --启停整个集群
2. gs_om -t start
3. gs_om -t stop
4. gs_om -t restart  
5. 或者
6. cm_ctl start
7. cm_ctl stop
9. [root@pw01 soft]# su - omm
10. Last login: Sun Jul  7 17:05:04 CST 2024 on pts/0
11. [omm@pw01 ~]$ cm_ctl stop
12. cm_ctl: stop cluster.
13. cm_ctl: stop nodeid: 1
14. cm_ctl: stop nodeid: 2
15. cm_ctl: stop nodeid: 3
16. ............
17. cm_ctl: stop cluster successfully.
18. [omm@pw01 ~]$
21. [omm@pw01 ~]$ cm_ctl start
22. cm_ctl: checking cluster status.
23. cm_ctl: checking cluster status.
24. cm_ctl: checking finished in 432 ms.
25. cm_ctl: start cluster.
26. cm_ctl: start nodeid: 1
27. cm_ctl: start nodeid: 2
28. cm_ctl: start nodeid: 3
29. ................
30. cm_ctl: start cluster successfully.
31. [omm@pw01 ~]$

复制代码

15.2 检察数据库集群的状态

1. gs_om -t status --detail
2. 或者
3. cm_ctl query -Cvid
5. [omm@pw01 data]$ gs_om -t status --detail
6. [  CMServer State   ]
8. node    node_ip         instance                             state
9. --------------------------------------------------------------------
10. 1  pw01 192.168.122.142 1    /database/panweidb/cm/cm_server Primary
11. 2  pw02 192.168.122.120 2    /database/panweidb/cm/cm_server Standby
12. 3  pw03 192.168.122.235 3    /database/panweidb/cm/cm_server Standby
14. [   Cluster State   ]
16. cluster_state   : Normal
17. redistributing  : No
18. balanced        : No
19. current_az      : AZ_ALL
21. [  Datanode State   ]
23. node    node_ip         instance                     state            
24. ----------------------------------------------------------------------
25. 1  pw01 192.168.122.142 6001 /database/panweidb/data P Standby Normal
26. 2  pw02 192.168.122.120 6002 /database/panweidb/data S Standby Normal
27. 3  pw03 192.168.122.235 6003 /database/panweidb/data S Primary Normal
28. [omm@pw01 data]$ cm_ctl query -Cvid
29. [  CMServer State   ]
31. node    node_ip         instance                             state
32. --------------------------------------------------------------------
33. 1  pw01 192.168.122.142 1    /database/panweidb/cm/cm_server Primary
34. 2  pw02 192.168.122.120 2    /database/panweidb/cm/cm_server Standby
35. 3  pw03 192.168.122.235 3    /database/panweidb/cm/cm_server Standby
37. [   Cluster State   ]
39. cluster_state   : Normal
40. redistributing  : No
41. balanced        : No
42. current_az      : AZ_ALL
44. [  Datanode State   ]
46. node    node_ip         instance                     state            | node    node_ip         instance                     state            | node    node_ip         instance                     state
47. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
48. 1  pw01 192.168.122.142 6001 /database/panweidb/data P Standby Normal | 2  pw02 192.168.122.120 6002 /database/panweidb/data S Standby Normal | 3  pw03 192.168.122.235 6003 /database/panweidb/data S Primary Normal
49. [omm@pw01 data]$

复制代码

15.3 启停单节点进程（包括cm和数据库进程）

1. cm_ctl start -n <nodeid>
2. cm_ctl stop -n <nodeid>

复制代码

15.4 启停单节点数据库

1. gs_ctl start [-D <DATADIR>]
2. gs_ctl stop  [-D <DATADIR>]
3. gs_ctl restart  [-D <DATADIR>]

复制代码

15.5 检察单节点数据库状态

1. gs_ctl query [-D <DATADIR>]
3. [omm@pw01 data]$ gs_ctl query -D /database/panweidb/data
4. [2024-07-07 18:43:29.792][18929][][gs_ctl]: gs_ctl query ,datadir is /database/panweidb/data
5. HA state:           
6.         local_role                     : Standby
7.         static_connections             : 2
8.         db_state                       : Normal
9.         detail_information             : Normal
11. Senders info:      
12. No information
13. Receiver info:      
14.         receiver_pid                   : 2995
15.         local_role                     : Standby
16.         peer_role                      : Primary
17.         peer_state                     : Normal
18.         state                          : Normal
19.         sender_sent_location           : 0/7006F40
20.         sender_write_location          : 0/7006F40
21.         sender_flush_location          : 0/7006F40
22.         sender_replay_location         : 0/7006F40
23.         receiver_received_location     : 0/7006F40
24.         receiver_write_location        : 0/7006F40
25.         receiver_flush_location        : 0/7006F40
26.         receiver_replay_location       : 0/7006F40
27.         sync_percent                   : 100%
28.         channel                        : 192.168.122.142:42764<--192.168.122.235:17701
30. [omm@pw01 data]$

复制代码

15.6 检察数据库静态设置

1. gs_om -t view
4. [omm@pw01 ~]$ gs_om -t view
6. NodeHeader:
7. version:301
8. time:1720342985
9. nodeCount:3
10. node:1
11. ============================================================
12. azName:AZ1
13. azPriority:1
14. node :1
15. nodeName:pw01
16. ssh channel :
17. sshChannel 1:192.168.122.142
18. datanodeCount :1
19. datanodeInstanceType :primary
20. datanode 1:
21. datanodeLocalDataPath :/database/panweidb/data
22. datanodeXlogPath :
23. datanodeListenIP 1:192.168.122.142
24. datanodePort :17700
25. datanodeLocalHAIP 1:192.168.122.142
26. datanodeLocalHAPort :17701
27. dn_replication_num: 3
28. datanodePeer0DataPath :/database/panweidb/data
29. datanodePeer0HAIP 1:192.168.122.120
30. datanodePeer0HAPort :17701
31. datanodePeer1DataPath :/database/panweidb/data
32. datanodePeer1HAIP 1:192.168.122.235
33. datanodePeer1HAPort :17701
34. ============================================================
35. azName:AZ1
36. azPriority:1
37. node :2
38. nodeName:pw02
39. ssh channel :
40. sshChannel 1:192.168.122.120
41. datanodeCount :1
42. datanodeInstanceType :standby
43. datanode 1:
44. datanodeLocalDataPath :/database/panweidb/data
45. datanodeXlogPath :
46. datanodeListenIP 1:192.168.122.120
47. datanodePort :17700
48. datanodeLocalHAIP 1:192.168.122.120
49. datanodeLocalHAPort :17701
50. dn_replication_num: 3
51. datanodePeer0DataPath :/database/panweidb/data
52. datanodePeer0HAIP 1:192.168.122.142
53. datanodePeer0HAPort :17701
54. datanodePeer1DataPath :/database/panweidb/data
55. datanodePeer1HAIP 1:192.168.122.235
56. datanodePeer1HAPort :17701
57. ============================================================
58. azName:AZ1
59. azPriority:1
60. node :3
61. nodeName:pw03
62. ssh channel :
63. sshChannel 1:192.168.122.235
64. datanodeCount :1
65. datanodeInstanceType :standby
66. datanode 1:
67. datanodeLocalDataPath :/database/panweidb/data
68. datanodeXlogPath :
69. datanodeListenIP 1:192.168.122.235
70. datanodePort :17700
71. datanodeLocalHAIP 1:192.168.122.235
72. datanodeLocalHAPort :17701
73. dn_replication_num: 3
74. datanodePeer0DataPath :/database/panweidb/data
75. datanodePeer0HAIP 1:192.168.122.142
76. datanodePeer0HAPort :17701
77. datanodePeer1DataPath :/database/panweidb/data
78. datanodePeer1HAIP 1:192.168.122.120
79. datanodePeer1HAPort :17701
80. ============================================================
81. [omm@pw01 ~]$

复制代码

15.7 数据库主备切换（主备机均正常时）

主备机均正常的状态下，主备机之间可以通过switchover下令举行角色切换,

1. cm_ctl switchover -n <nodeid> -D <datadir>
2. 示例：cm_ctl switchover -n 2 -D /data/panweidb/data
4. --从节点上执行，执行后，从节点变成主节点
5. gs_ctl switchover -D /opt/huawei/install/data/dn
6. 注意：执行switchover或failover后，需要执行gs_om -t refreshconf 命令记录当前主备机信息。

复制代码

15.8 数据库主备切换（主机故障时）

主机故障后可以通过failover下令对备机举行升主。

1. gs_ctl failover   [-D DATADIR]

复制代码

15.9 重建备库

1. gs_ctl build -b auto -D <datadir>

复制代码

15.10 数据库节点扩缩容

详情可以参考文档：数据库节点扩缩容

1. gs_expansion -U omm -G dbgrp -h 192.168.100.14 -X ./clusterconfig.xml
2. gs_dropnode -U omm -G dbgrp -h 192.168.100.14

复制代码

16.慢sql录制开启和关闭

16.1 慢SQL开启

题目征象：业务适配或者上线前期，性能压测，优化，怎样记录业务慢sql。
解决方法：开启数据库慢sql录制开关。
gs_guc reload -Z coordinator -N all -I all -c "log_min_duration_statement =3000" #---- 慢sql录制
gs_guc reload -Z coordinator -N all -I all -c "enable_stmt_track=on"
gs_guc reload -Z coordinator -N all -I all -c "track_stmt_stat_level = 'OFF,L1'" #---- 慢sql执行筹划录制
gs_guc reload -Z coordinator -N all -I all -c "track_stmt_details_size = 40960"
gs_guc reload -Z coordinator -N all -I all -c "instr_unique_sql_count = 200000"
gs_guc reload -Z coordinator -N all -I all -c "track_stmt_parameter = 'on'"
开启后：在数据库中检察 select * from dbe_perf.statement_history ; --- 必须在postgres库中，使用超级用户检察。
16.2 慢SQL参数阐明

参数详解：
log_min_duration_statement ：记录日志的时间
参数阐明：当某条语句的连续时间大于或者等于特定的毫秒数时，log_min_duration_statement参数用于控制记录每条完成语句的连续时间。
设置log_min_duration_statement可以很方便地跟踪需要优化的查询语句。对于使用扩展查询协议的客户端，语法分析、绑定、执行每一步所花时间被独立记录。
该参数属于SUSET类型参数，请参考表1中对应设置方法举行设置。

enable_stmt_track：
参数阐明：控制是否启用Full /Slow SQL特性。
在x86平台集中式部署下，硬件设置规格为32核CPU/256GB内存，使用Benchmark SQL 5.0工具测试性能，开关此参数性能影响约1.2%。

track_stmt_stat_level ：
参数阐明：控制语句执行跟踪的级别。
该参数属于USERSET类型参数，请参考表1中对应设置方法举行设置，不区分英文字母大小写。
取值范围：字符型
该参数分为两部分，形式为'full sql stat level, slow sql stat level'
第一部分为全量SQL跟踪级别，取值范围为OFF、L0、L1、L2
第二部分为慢SQL的跟踪级别，取值范围为OFF、L0、L1、L2

track_stmt_details_size ：
参数阐明：设置单语句可以网络的最大的执行事件的大小(byte)。
该参数属于USERSET类型参数，请参考表1中对应设置方法举行设置。
取值范围：整型，0 ~ 100000000

instr_unique_sql_count ：
参数阐明：控制系统中unique sql信息及时网络功能。设置为0表示不启用unique sql信息网络功能。
该值由大变小将会清空系统中原有的数据重新统计（备机不支持此能力）；从小变大不受影响。
当系统中产生的unique sql条目数量大于instr_unique_sql_count时，若开启了unique sql自动镌汰，则系统会按unique sql的更新时间由远到近自动镌汰肯定比例的条目，使得新产生的unique sql信息可以继续被统计。若没有开启自动镌汰，则系统产生的新的unique sql信息将不再被统计。
在x86平台集中式部署下，硬件设置规格为32核CPU/256GB内存，使用Benchmark SQL 5.0工具测试性能，开关此参数性能影响约3%。

track_stmt_parameter：
参数阐明：开启track_stmt_parameter后，在statement_history中记录的执行语句不再举行归一化操作，可以显示完整SQL语句信息，辅助DBA举行题目定位。其中对于简朴查询，显示完整语句信息；对于PBE语句，显示完整语句信息的同时，追加每个变量数值信息，格式为“query string；parameters1=value1,$2=value2,...”。
该参数属于SIGHUP类型参数，请参考表1中对应设置方法举行设置。

16.3 慢SQL排除

题目来了，开启录制sql后，慢sql会渐渐累积，怎样清理避免日志过大，影响数据库性能？
方法：
gs_guc reload -Z coordinator -N all -I all -c "track_stmt_retention_time= '3600,604800'" #---- 慢sql清理参数
通过此函数监控检察 statement_history 表大小，当表的增长非常大时，就需要调整track_stmt_retention_time参数，使清理频率加速。
select * from pg_catalog.pg_size_pretty(pg_catalog.pg_table_size('statement_history'));

track_stmt_retention_time
参数阐明：组合参数，控制全量/慢SQL记录的保存时间。以60秒为周期读取该参数，并执行清理凌驾保存时间的记录，仅sysadmin用户可以访问。
该参数属于SIGHUP类型参数，请参考表1中对应设置方法举行设置。
取值范围：字符型
该参数分为两部分，形式为'full sql retention time, slow sql retention time'
full sql retention time为全量SQL保存时间，取值范围为0 ~ 86400
slow sql retention time为慢SQL的保存时间，取值范围为0 ~ 604800

默认值：3600,604800

16.4 慢sql录制开启-执行筹划

生产环境中，经常出现某些sql运行特别慢，dba获取到慢sql，单独执行非常的快，无法判断是资源异常导致，还是执行筹划与手动执行不一样。

PanweiDB 数据库提供慢sql执行筹划录制功能，帮忙dba 定位题目。
方法：
gs_guc reload -Z coordinator -N all -I all -c "track_stmt_stat_level = 'OFF,L1'" #---- 慢sql执行筹划录制

track_stmt_stat_level ：
参数阐明：控制语句执行跟踪的级别。
该参数属于USERSET类型参数，请参考表1中对应设置方法举行设置，不区分英文字母大小写。
取值范围：字符型
该参数分为两部分，形式为'full sql stat level, slow sql stat level'
第一部分为全量SQL跟踪级别，取值范围为OFF、L0、L1、L2
第二部分为慢SQL的跟踪级别，取值范围为OFF、L0、L1、L2。

慢sql可通过此视图举行查询：在数据库中检察 select * from dbe_perf.statement_history ; --- 必须在postgres库中，使用超级用户检察。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。