巡检人:小麦苗　QQ:646634621　微信公众号：DB宝　提供OCP、OCM、高可用（rac+dg）、PostgreSQL和MySQL培训　BLOG地址: https://www.dbaup.com 若需要脚本可私聊我
MogDB数据库巡检报告

Copyright (c) 2015-2100 (https://www.dbaup.com) dbaup. All rights reserved.

巡 检 人：小麦苗([QQ：646634621] [微信公众号：DB宝] [提供OCP、OCM、高可用、MySQL和PostgreSQL最实用的培训])
版 本 号：v7.0.0
修改日期：2022-04-08 18:28:00

[转到页底]

目录
总体概况 实例信息数据库实例信息 数据库基本信息数据库的总体概况、版本、主机情况、数据库负载情况、数据库属性等 所有表空间所有表空间 当前客户端连接信息当前客户端连接信息 前几张大表前几张大表
当前库的所有数据库对象当前库的所有数据库对象
锁情况 所有进程
SQL部分 总执行时间最长的SQL 最耗IO的SQL 最耗共享内存
索引部分
高可用情况 主从流复制情况主从流复制情况 逻辑复制PG逻辑复制情况
数据库性能 参数配置 pg_hba参数文件 后台写进程统计信息
其它 用户和角色 所有插件 获取已安装的PostgreSQL的信息获取已安装的PostgreSQL的信息，表pg_config


数据库总体概况

● 实例信息
pg_start_time server_ip server_port client_ip client_port server_version primary_or_standby now_date
2022-04-15 07:21:39.525475+00 172.17.0.4 5432 192.168.66.64 17043 (MogDB 2.1.1 build b5f25b20) compiled at 2022-03-21 14:42:30 commit 0 last mr on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit primary 2022-04-18 12:31:10.34664+00

● 数据库基本信息
Name Owner Encoding Collate Ctype Access privileges Size Tablespace Description
lhrdb lhr UTF8 en_US.UTF-8 en_US.UTF-8   13 MB pg_default  
mogdb omm UTF8 en_US.UTF-8 en_US.UTF-8   13 MB pg_default  
mogila omm UTF8 en_US.UTF-8 en_US.UTF-8   15 MB pg_default  
postgres omm UTF8 en_US.UTF-8 en_US.UTF-8   32 MB pg_default default administrative connection database
template0 omm UTF8 en_US.UTF-8 en_US.UTF-8 =c/omm
omm=CTc/omm		 12 MB pg_default default template for new databases
template1 omm UTF8 en_US.UTF-8 en_US.UTF-8 =c/omm
omm=CTc/omm		 11 MB pg_default unmodifiable empty database

● 所有表空间
oid Name Owner Location Access privileges Options Size Description
1663 pg_default omm       95 MB  
1664 pg_global omm       514 MB  

● 当前客户端连接信息
current_user current_database pg_backend_pid
lhr postgres 139876974458624

● 前几张大表
db schemaname relname rowcount table_size indexes_size total_size
postgres utl_file utl_file_dir 0 8192 bytes 8192 bytes 16 kB
postgres compat_tools compat_version 0 48 kB 40 kB 88 kB
postgres compat_tools compat_testing 0 96 kB 64 kB 160 kB
postgres db4ai snapshot 0 8192 bytes 16 kB 24 kB

● 当前库的所有数据库对象
schemaname objecttype cnt
oracle VIEW 11
repack VIEW 2
compat_tools VIEW 103
db4ai INDEX 2
public VIEW 1
dbe_perf VIEW 174
public composite type 1
compat_tools INDEX 2
dbms_pipe VIEW 1
utl_file TABLE 1
db4ai composite type 1
utl_file INDEX 1
db4ai TABLE 1
compat_tools TABLE 2
public func 130

[回到目录]

锁情况

● 所有进程

● 总计
最大连接数 当前连接数 剩余连接数
200 10 190

● 详情
datid datname pid sessionid usesysid usename application_name client_addr client_hostname client_port backend_start xact_start query_start state_change waiting enqueue state resource_pool query_id query connection_info
14440 postgres 139877685393152 139877685393152 10 omm JobScheduler       2022-04-15 07:21:40.33344+00     2022-04-18 12:31:10.48618+00 f   active default_pool 0    
14440 postgres 139877577389824 139877577389824 10 omm WDRSnapshot       2022-04-15 07:21:40.358105+00     2022-04-18 12:31:11.112501+00 f   idle default_pool 0    
14440 postgres 139877555369728 139877555369728 10 omm PercentileJob       2022-04-15 07:21:40.365506+00     2022-04-18 12:31:02.738573+00 f   active default_pool 0    
14440 postgres 139877523912448 139877523912448 10 omm Asp       2022-04-15 07:21:40.451652+00     2022-04-18 12:31:10.394152+00 f   active default_pool 0    
14440 postgres 139877489833728 139877489833728 10 omm statement flush thread       2022-04-15 07:21:40.473914+00     2022-04-15 07:21:40.473959+00 f   idle default_pool 0    
14440 postgres 139877321012992 139877321012992 10 omm WorkloadMonitor       2022-04-15 07:21:40.297082+00       f     default_pool 0    
14440 postgres 139877286409984 139877286409984 10 omm WLMArbiter       2022-04-15 07:21:40.297264+00       f     default_pool 0    
14440 postgres 139877352339200 139877352339200 10 omm workload       2022-04-15 07:21:40.297347+00 2022-04-15 07:21:40.30154+00 2022-04-15 07:21:40.30154+00 2022-04-15 07:21:40.349739+00 f   active default_pool 0 WLM fetch collect info from data nodes  
14440 postgres 139877141182208 139877141182208 24577 lhr psql 192.168.66.64   16733 2022-04-18 12:26:27.669011+00   2022-04-18 12:30:06.723137+00 2022-04-18 12:30:06.735444+00 f   idle default_pool 0 with tmp_file as (
select t1.file,
t1.file_ls,
(pg_stat_file(t1.file)).size as size,
(pg_stat_file(t1.file)).access as access,
(pg_stat_file(t1.file)).modification as last_update_time,
(pg_stat_file(t1.file)).change as change,
(pg_stat_file(t1.file)).creation as creation,
(pg_stat_file(t1.file)).isdir as isdir
from (select dir||'/'||pg_ls_dir(t0.dir) as file,
pg_ls_dir(t0.dir) as file_ls
from ( select '/pg13/pgdata/pg_wal'::text as dir
) t0
) t1
where 1=1
order by (pg_stat_file(file)).modification desc
)
select to_char(date_trunc('day',tf0.last_update_time),'yyyymmdd') as day_id,
sum(case when date_part('hour',tf0.last_update_time) >=0 and date_part('hour',tf0.last_update_time) <24 then 1 else 0 end) as wal_num_all,
sum(case when date_part('hour',tf0.last_update_time) >=0 and date_part('hour',tf0.last_update_time) <1 then 1 else 0 end) as wal_num_00_01,
sum(case when date_part('hour',tf0.last_update_time) >=		  
14440 postgres 139876974458624 139876974458624 24577 lhr psql 192.168.66.64   17043 2022-04-18 12:31:10.198909+00 2022-04-18 12:31:11.128139+00 2022-04-18 12:31:11.128139+00 2022-04-18 12:31:11.128144+00 f   active default_pool 562949953490778 select a.* from pg_stat_activity a ;  

● 锁
locktype database relation page tuple bucket virtualxid transactionid classid objid objsubid virtualtransaction pid sessionid mode granted fastpath locktag global_sessionid
relation 14440 11986                 12/277 139876974458624 139876974458624 AccessShareLock t t 3868:2ed2:0:0:0:0 0:0#0
virtualxid           12/277         12/277 139876974458624 139876974458624 ExclusiveLock t t c:115:0:0:0:7 0:0#0
virtualxid           10/55557         10/55557 139877352339200 139877352339200 ExclusiveLock t t a:d905:0:0:0:7 0:0#0

● state_change字段长时间没有更新过的连接信息
datid datname pid sessionid usesysid usename application_name client_addr client_hostname client_port backend_start xact_start query_start state_change waiting enqueue state resource_pool query_id query connection_info
14440 postgres 139877489833728 139877489833728 10 omm statement flush thread       2022-04-15 07:21:40.473914+00     2022-04-15 07:21:40.473959+00 f   idle default_pool 0    
14440 postgres 139877141182208 139877141182208 24577 lhr psql 192.168.66.64   16733 2022-04-18 12:26:27.669011+00   2022-04-18 12:30:06.723137+00 2022-04-18 12:30:06.735444+00 f   idle default_pool 0 with tmp_file as (
select t1.file,
t1.file_ls,
(pg_stat_file(t1.file)).size as size,
(pg_stat_file(t1.file)).access as access,
(pg_stat_file(t1.file)).modification as last_update_time,
(pg_stat_file(t1.file)).change as change,
(pg_stat_file(t1.file)).creation as creation,
(pg_stat_file(t1.file)).isdir as isdir
from (select dir||'/'||pg_ls_dir(t0.dir) as file,
pg_ls_dir(t0.dir) as file_ls
from ( select '/pg13/pgdata/pg_wal'::text as dir
) t0
) t1
where 1=1
order by (pg_stat_file(file)).modification desc
)
select to_char(date_trunc('day',tf0.last_update_time),'yyyymmdd') as day_id,
sum(case when date_part('hour',tf0.last_update_time) >=0 and date_part('hour',tf0.last_update_time) <24 then 1 else 0 end) as wal_num_all,
sum(case when date_part('hour',tf0.last_update_time) >=0 and date_part('hour',tf0.last_update_time) <1 then 1 else 0 end) as wal_num_00_01,
sum(case when date_part('hour',tf0.last_update_time) >=		  
14440 postgres 139877577389824 139877577389824 10 omm WDRSnapshot       2022-04-15 07:21:40.358105+00     2022-04-18 12:31:11.21309+00 f   idle default_pool 0    

[回到目录]

SQL情况

[回到目录]

高可用情况

● 主从流复制情况

● 主库查看wal日志发送状态
pid usesysid usename application_name client_addr client_hostname client_port backend_start state sender_sent_location receiver_write_location receiver_flush_location receiver_replay_location sync_priority sync_state

● 从库查看wal日志接收状态

● 主从流复制冲突统计
datid datname confl_tablespace confl_lock confl_snapshot confl_bufferpin confl_deadlock
1 template1 0 0 0 0 0
14435 template0 0 0 0 0 0
18890 mogdb 0 0 0 0 0
18891 mogila 0 0 0 0 0
24581 lhrdb 0 0 0 0 0
14440 postgres 0 0 0 0 0

● 数据库统计（是否有坏块）
datid datname numbackends xact_commit xact_rollback blks_read blks_hit tup_returned tup_fetched tup_inserted tup_updated tup_deleted conflicts temp_files temp_bytes deadlocks blk_read_time blk_write_time stats_reset
1 template1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0  
14435 template0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0  
18890 mogdb 0 24019 0 260 1314396 1830722 1052968 0 0 0 0 0 0 0 0 0 2022-04-15 07:22:40.407906+00
18891 mogila 0 24019 0 266 1331946 1918502 1064980 0 0 0 0 0 0 0 0 0 2022-04-15 07:22:40.474715+00
24581 lhrdb 0 42 0 189 3581 4102 2584 0 0 0 0 0 0 0 0 0 2022-04-18 12:23:18.237094+00
14440 postgres 10 51931 40 4665 2801116 2755031 2525294 130017 324 43882 0 0 0 0 0 0 2022-04-15 07:21:40.456247+00

[回到目录]

● 逻辑复制

● 查看复制槽
slot_name plugin slot_type datoid database active xmin catalog_xmin restart_lsn dummy_standby

[回到目录]

数据库性能

● 相关参数 ● 重要参数
name setting unit category short_desc extra_desc context vartype source min_val max_val enumvals boot_val reset_val sourcefile sourceline
archive_mode off   Write-Ahead Log / Archiving Allows archiving of WAL files using archive_command.   sighup bool default       off off    
client_encoding UTF8   Client Connection Defaults / Locale and Formatting Sets the client's character set encoding.   user string default       SQL_ASCII UTF8    
config_file /var/lib/mogdb/data/postgresql.conf   File Locations Sets the server's main configuration file.   postmaster string override         /var/lib/mogdb/data/postgresql.conf    
data_directory /var/lib/mogdb/data   File Locations Sets the server's data directory.   postmaster string override         /var/lib/mogdb/data    
hba_file /var/lib/mogdb/data/pg_hba.conf   File Locations Sets the server's "hba" configuration file.   postmaster string override         /var/lib/mogdb/data/pg_hba.conf    
ident_file /var/lib/mogdb/data/pg_ident.conf   File Locations Sets the server's "ident" configuration file.   postmaster string override         /var/lib/mogdb/data/pg_ident.conf    
listen_addresses *   Connections and Authentication / Connection Settings Sets the host name or IP address(es) to listen to.   postmaster string configuration file       localhost * /var/lib/mogdb/data/postgresql.conf 776
log_directory pg_log   Reporting and Logging / Where to Log Sets the destination directory for log files. Can be specified as relative to the data directory or as absolute path. sighup string default       pg_log pg_log    
log_filename postgresql-%Y-%m-%d_%H%M%S.log   Reporting and Logging / Where to Log Sets the file name pattern for log files.   sighup string configuration file       postgresql-%Y-%m-%d_%H%M%S.log postgresql-%Y-%m-%d_%H%M%S.log /var/lib/mogdb/data/postgresql.conf 393
logging_collector on   Reporting and Logging / Where to Log Starts a subprocess to capture stderr output and/or csvlogs into log files.   postmaster bool configuration file       off on /var/lib/mogdb/data/postgresql.conf 385
log_min_duration_statement 1800000 ms Reporting and Logging / When to Log Sets the minimum execution time above which statements will be logged. Zero prints all queries. -1 turns this feature off. superuser integer configuration file -1 2147483647   -1 1800000 /var/lib/mogdb/data/postgresql.conf 448
log_statement none   Reporting and Logging / What to Log Sets the type of statements logged.   superuser enum default     {none,ddl,mod,all} none none    
log_truncate_on_rotation off   Reporting and Logging / Where to Log Truncates existing log files of same name during log rotation.   sighup bool default       off off    
max_connections 200   Connections and Authentication / Connection Settings Sets the maximum number of concurrent connections for clients.   postmaster integer configuration file 10 262143   200 200 /var/lib/mogdb/data/postgresql.conf 74
port 5432   Connections and Authentication / Connection Settings Sets the TCP port the server listens on.   postmaster integer default 1 65535   5432 0    

● 后台写进程统计信息
checkpoints_timed checkpoints_req checkpoint_write_time checkpoint_sync_time buffers_checkpoint buffers_clean maxwritten_clean buffers_backend buffers_backend_fsync buffers_alloc stats_reset
4625 4 161 1107 0 0 0 10915 0 0 2022-04-15 07:21:40.29263+00

● 查看表膨胀信息
table_name table_size n_dead_tup n_live_tup dead_tup_ratio
pg_catalog.gs_asp 16 MB 10005 86107 10.41

[回到目录]

其它

● 当前库的所有schema
catalog_name schema_name schema_owner default_character_set_catalog default_character_set_schema default_character_set_name sql_path
postgres pg_toast omm        
postgres cstore omm        
postgres pkg_service omm        
postgres dbe_perf omm        
postgres snapshot omm        
postgres blockchain omm        
postgres pg_catalog omm        
postgres public omm        
postgres sqladvisor omm        
postgres dbe_pldebugger omm        
postgres information_schema omm        
postgres db4ai omm        
postgres dbms_output omm        
postgres dbms_utility omm        
postgres utl_file omm        
postgres dbms_random omm        
postgres oracle omm        
postgres dbms_pipe omm        
postgres dbms_alert omm        
postgres plvdate omm        
postgres plvstr omm        
postgres plvchr omm        
postgres plvsubst omm        
postgres plunit omm        
postgres plvlex omm        
postgres dbms_assert omm        
postgres orafce omm        
postgres pgbulkload omm        
postgres repack omm        
postgres compat_tools omm        
postgres dbms_metadata omm        
postgres dbms_job omm        
postgres dbms_lock omm        
postgres dbms_application_info omm        
postgres dbms_obfuscation_toolkit omm        
postgres utl_url omm        
postgres utl_encode omm        
postgres utl_raw omm        
postgres mogdb mogdb        
postgres lhr lhr        

● 当前库的所有角色(用户)
rolname rolsuper rolinherit rolcreaterole rolcreatedb rolcatupdate rolcanlogin rolreplication rolauditadmin rolsystemadmin rolconnlimit rolpassword rolvalidbegin rolvaliduntil rolrespool rolparentid roltabspace rolconfig oid roluseft rolkind nodegroup roltempspace rolspillspace rolmonitoradmin roloperatoradmin rolpolicyadmin
gs_role_copy_files f t f f f f f f f -1 ********     default_pool 0     1044 f n       f f f
gs_role_account_lock f t f f f f f f f -1 ********     default_pool 0     1048 f n       f f f
gs_role_replication f t f f f f f f f -1 ********     default_pool 0     1047 f n       f f f
gs_role_tablespace f t f f f f f f f -1 ********     default_pool 0     1046 f n       f f f
omm t t t t t t t t t -1 ********     default_pool 0     10 t n       t t t
gs_role_pldebugger f t f f f f f f f -1 ********     default_pool 0     1055 f n       f f f
gs_role_signal_backend f t f f f f f f f -1 ********     default_pool 0     1045 f n       f f f
mogdb f t f f f t f f t -1 ********     default_pool 0     18886 f n       t f f
lhr f t f f f t f f t -1 ********     default_pool 0     24577 f n       f f f

usename usesysid usecreatedb usesuper usecatupd userepl passwd valbegin valuntil respool parent spacelimit useconfig nodegroup tempspacelimit spillspacelimit usemonitoradmin useoperatoradmin usepolicyadmin
mogdb 18886 f f f f ********     default_pool 0           t f f
omm 10 t t t t ********     default_pool 0           t t t
lhr 24577 f f f f ********     default_pool 0           f f f

● 所有语言
lanname lanowner lanispl lanpltrusted lanplcallfoid laninline lanvalidator lanacl
internal 10 f f 0 0 2246  
c 10 f f 0 0 2247  
sql 10 f t 0 0 2248  
java 10 f f 0 0 0  
plpgsql 10 t t 11897 11898 11899  

● 所有可用插件

● 所有插件（已编译）
name default_version installed_version comment
plpgsql 1.0 1.0 PL/pgSQL procedural language
log_fdw 1.0 1.0 Foreign Data Wrapper for accessing logging data
hstore 1.1 1.1 data type for storing sets of (key, value) pairs
hdfs_fdw 1.0 1.0 foreign-data wrapper for flat file access
mot_fdw 1.0 1.0 foreign-data wrapper for MOT access
security_plugin 1.0 1.0 provides security functionality
file_fdw 1.0 1.0 foreign-data wrapper for flat file access
dist_fdw 1.0 1.0 foreign-data wrapper for distfs access
postgres_fdw 1.0   foreign-data wrapper for remote PostgreSQL servers
pg_trgm 1.0 1.0 text similarity measurement and index searching based on trigrams
pg_prewarm 1.1 1.1 prewarm relation data
orafce 3.17 3.17 Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS
dblink 1.0 1.0 connect to other PostgreSQL databases from within a database
pg_repack 1.4.6 1.4.6 Reorganize tables in PostgreSQL databases with minimal locks
pg_bulkload 1.0 1.0 pg_bulkload is a high speed data loading utility for PostgreSQL

● 已安装的插件
Name Version Schema Description
dblink 1.0 public connect to other PostgreSQL databases from within a database
dist_fdw 1.0 pg_catalog foreign-data wrapper for distfs access
file_fdw 1.0 pg_catalog foreign-data wrapper for flat file access
hdfs_fdw 1.0 pg_catalog foreign-data wrapper for flat file access
hstore 1.1 pg_catalog data type for storing sets of (key, value) pairs
log_fdw 1.0 pg_catalog Foreign Data Wrapper for accessing logging data
mot_fdw 1.0 pg_catalog foreign-data wrapper for MOT access
orafce 3.17 public Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS
pg_bulkload 1.0 public pg_bulkload is a high speed data loading utility for PostgreSQL
pg_prewarm 1.1 public prewarm relation data
pg_repack 1.4.6 public Reorganize tables in PostgreSQL databases with minimal locks
pg_trgm 1.0 public text similarity measurement and index searching based on trigrams
plpgsql 1.0 pg_catalog PL/pgSQL procedural language
security_plugin 1.0 pg_catalog provides security functionality

[回到目录]