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

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

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

[转到页底]

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


数据库总体概况

● KADB集群配置信息

● 按照实例排列
dbid content role preferred_role mode status port hostname address datadir
1 -1 p p n u 5432 kmdw kmdw /home/mppadmin/dbdata/master/mppseg-1
2 0 p p s u 40000 ksdw1 ksdw1 /home/mppadmin/dbdata/primary/mppseg0
6 0 m m s u 50000 ksdw2 ksdw2 /home/mppadmin/dbdata/mirror/mppseg0
3 1 p p s u 40001 ksdw1 ksdw1 /home/mppadmin/dbdata/primary/mppseg1
7 1 m m s u 50001 ksdw2 ksdw2 /home/mppadmin/dbdata/mirror/mppseg1
4 2 p p s u 40000 ksdw2 ksdw2 /home/mppadmin/dbdata/primary/mppseg2
8 2 m m s u 50000 ksdw1 ksdw1 /home/mppadmin/dbdata/mirror/mppseg2
5 3 p p s u 40001 ksdw2 ksdw2 /home/mppadmin/dbdata/primary/mppseg3
9 3 m m s u 50001 ksdw1 ksdw1 /home/mppadmin/dbdata/mirror/mppseg3

● 按照主机排列
dbid content role preferred_role mode status port hostname address datadir
1 -1 p p n u 5432 kmdw kmdw /home/mppadmin/dbdata/master/mppseg-1
2 0 p p s u 40000 ksdw1 ksdw1 /home/mppadmin/dbdata/primary/mppseg0
3 1 p p s u 40001 ksdw1 ksdw1 /home/mppadmin/dbdata/primary/mppseg1
8 2 m m s u 50000 ksdw1 ksdw1 /home/mppadmin/dbdata/mirror/mppseg2
9 3 m m s u 50001 ksdw1 ksdw1 /home/mppadmin/dbdata/mirror/mppseg3
4 2 p p s u 40000 ksdw2 ksdw2 /home/mppadmin/dbdata/primary/mppseg2
5 3 p p s u 40001 ksdw2 ksdw2 /home/mppadmin/dbdata/primary/mppseg3
6 0 m m s u 50000 ksdw2 ksdw2 /home/mppadmin/dbdata/mirror/mppseg0
7 1 m m s u 50001 ksdw2 ksdw2 /home/mppadmin/dbdata/mirror/mppseg1

● KADB基本信息
pg_start_time run_time config_file_load_time server_ip server_port client_ip client_port server_version primary_or_standby now_date all_db_hit
2023-07-06 16:05:56.021295+08 1886.021745 2023-07-06 16:06:34.21837+08 192.168.16.4 5432 192.168.27.9 55112 PostgreSQL 9.4.24 (Greenplum Database 6.0.0 build dev.V003R002C001B0181.d354cc9215) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit compiled on Dec 30 2021 10:02:17 primary 2023-07-06 16:37:22.04304+08 99.02%

● 数据库基本信息（数据库大小）
Name Owner Encoding Collate Ctype datconnlimit Access privileges Size Tablespace Description
lhrgpdb mppadmin UTF8 en_US.utf8 en_US.utf8 -1   238 MB pg_default  
postgres mppadmin UTF8 en_US.utf8 en_US.utf8 -1   108 MB pg_default default administrative connection database
template0 mppadmin UTF8 en_US.utf8 en_US.utf8 -1 =c/mppadmin
mppadmin=CTc/mppadmin		 106 MB pg_default unmodifiable empty database
template1 mppadmin UTF8 en_US.utf8 en_US.utf8 -1 =c/mppadmin
mppadmin=CTc/mppadmin		 108 MB pg_default default template for new databases
总计         -1   560 MB    

● 查看各数据库数据创建时间
datname size access modification change creation isdir
template1 4 2023-07-06 16:05:23+08 2023-07-06 16:05:23+08 2023-07-06 16:05:23+08   f
template0 4 2023-07-06 16:05:28+08 2023-07-06 16:05:28+08 2023-07-06 16:05:28+08   f
postgres 4 2023-07-06 16:05:30+08 2023-07-06 16:05:29+08 2023-07-06 16:05:29+08   f
lhrgpdb 4 2023-07-06 16:08:41+08 2023-07-06 16:05:58+08 2023-07-06 16:05:58+08   f

● 所有表空间
oid Name Owner Location Access privileges Options Size Description
1663 pg_default mppadmin       560 MB  
1664 pg_global mppadmin       20 MB  

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

● 当前库的前50张大表
db schemaname relname rowcount table_size indexes_size total_size
postgres zhparser zhprs_custom_word 0 160 kB 160 kB 320 kB
postgres identity_auth authfailed 0 0 bytes 0 bytes 0 bytes
postgres identity_auth userpwdexp 0 0 bytes 0 bytes 0 bytes
postgres identity_auth userlogin 0 160 kB 0 bytes 160 kB
postgres cachedata cache_info 0 160 kB 0 bytes 160 kB

● 当前库的所有数据库对象
schemaname objecttype cnt
cachedata TABLE 1
identity_auth TABLE 3
public pg_proc 7
session_state VIEW 1
zhparser INDEX 1
zhparser TABLE 1

● 查看当前库的所有schema占用的磁盘空间
sosdnsp schema_tb_size_mb schema_idx_size_mb
cachedata 0.16 0.00
session_state 0.00 0.00
zhparser 0.16 0.16
public 0.00 0.00
identity_auth 0.16 0.00

● 磁盘剩余空间查询

● segment剩余空间查询
address free_disk_gb
ksdw1 1800.00
ksdw2 1836.00

● 磁盘剩余空间查询
dfsegment dfhostname dfdevice free_disk_gb
0  ksdw1  overlay 1800.00
1  ksdw1  overlay 1800.00
2  ksdw2  overlay 1836.00
3  ksdw2  overlay 1836.00

● 详情
dfsegment dfhostname address dfdevice datadir free_disk_gb
0  ksdw1 ksdw1  overlay /home/mppadmin/dbdata/primary/mppseg0 1800.00
1  ksdw1 ksdw1  overlay /home/mppadmin/dbdata/primary/mppseg1 1800.00
2  ksdw2 ksdw2  overlay /home/mppadmin/dbdata/primary/mppseg2 1836.00
3  ksdw2 ksdw2  overlay /home/mppadmin/dbdata/primary/mppseg3 1836.00

● 没有统计信息且可能需要ANALYZE的表
smischema smitable smisize smicols smirecs
cachedata cache_info f 7 0
identity_auth userlogin f 4 0
identity_auth authfailed f 3 0
identity_auth userpwdexp f 2 0
zhparser zhprs_custom_word f 4 0

[回到目录]

锁情况

● 所有进程

● 总计
最大连接数 当前连接数 剩余连接数
250 6 244

● 用户统计
usename cnt
lhr 2
mppadmin 4

● 状态统计
state cnt
active 1
idle 5

● 详情
datid datname pid sess_id usesysid usename application_name client_addr client_hostname client_port backend_start xact_start query_start state_change waiting state backend_xid backend_xmin query waiting_reason rsgid rsgname rsgqueueduration
14569 postgres 13212 56 16433 lhr psql 192.168.27.9   55112 2023-07-06 16:37:21.73868+08 2023-07-06 16:37:23.802662+08 2023-07-06 16:37:23.802662+08 2023-07-06 16:37:23.80267+08 f active   744 select a.* from pg_stat_activity a order by a.state,query_start limit 100;   0 unknown  
14569 postgres 11645 22 10 mppadmin psql     -1 2023-07-06 16:07:06.394764+08   2023-07-06 16:08:03.126011+08 2023-07-06 16:08:03.17127+08 f idle     create user lhr password 'lhr' superuser;   0 unknown  
14569 postgres 12077 39 10 mppadmin Navicat 172.17.28.12   58984 2023-07-06 16:14:36.375167+08   2023-07-06 16:14:53.48607+08 2023-07-06 16:14:53.490785+08 f idle     select version()   0 unknown  
14569 postgres 12241 43 10 mppadmin Navicat 192.168.27.9   63157 2023-07-06 16:17:51.87191+08   2023-07-06 16:17:58.058964+08 2023-07-06 16:17:58.727468+08 f idle     SELECT r.routine_schema, r.routine_name , string_agg(p.udt_name, ', ' ORDER BY p.ordinal_position) AS object_info FROM information_schema.routines r LEFT JOIN information_schema.parameters p ON r.specific_name = p.specific_name GROUP BY p.specific_name, r.routine_schema, r.routine_name ORDER BY r.routine_schema   0 unknown  
16432 lhrgpdb 12248 44 10 mppadmin Navicat 192.168.27.9   63172 2023-07-06 16:17:58.000212+08   2023-07-06 16:17:58.827257+08 2023-07-06 16:17:59.085215+08 f idle     SELECT r.routine_schema, r.routine_name , string_agg(p.udt_name, ', ' ORDER BY p.ordinal_position) AS object_info FROM information_schema.routines r LEFT JOIN information_schema.parameters p ON r.specific_name = p.specific_name GROUP BY p.specific_name, r.routine_schema, r.routine_name ORDER BY r.routine_schema   0 unknown  
14569 postgres 13215 56 16433 lhr       -1 2023-07-06 16:37:22.864347+08   2023-07-06 16:37:23.406081+08 2023-07-06 16:37:23.408512+08 f idle     select dfsegment,dfhostname,sc.address,dfdevice,sc.datadir,round(dfspace/1024/1024,2) as free_disk_GB
from gp_toolkit.gp_disk_free df , gp_segment_configuration sc
where df.dfsegment= sc.content and role='p'
ORDER BY address,dfsegment ;		   0 unknown  

● 锁
locktype database relation page tuple virtualxid transactionid classid objid objsubid virtualtransaction pid mode granted fastpath mppsessionid mppiswriter gp_segment_id
relation 14569 11667               4/131 13212 AccessShareLock t t 56 t -1
virtualxid         4/131         4/131 13212 ExclusiveLock t t 56 t -1
virtualxid         2/233         2/233 6713 ExclusiveLock t t 56 t 0
virtualxid         2/234         2/234 6714 ExclusiveLock t t 56 t 1
virtualxid         2/234         2/234 6714 ExclusiveLock t t 56 t 1
virtualxid         2/237         2/237 6713 ExclusiveLock t t 56 t 2

● 锁2
lorlocktype lordatabase lorrelname lorrelation lortransaction lorpid lormode lorgranted lorcurrentquery
relation 14569 gp_locks_on_relation 14172   13212 AccessShareLock t select * from gp_toolkit.gp_locks_on_relation ;
relation 0 pg_authid 1260   13212 AccessShareLock t select * from gp_toolkit.gp_locks_on_relation ;
relation 0 pg_authid_oid_index 2677   13212 AccessShareLock t select * from gp_toolkit.gp_locks_on_relation ;
relation 0 pg_authid_rolname_index 2676   13212 AccessShareLock t select * from gp_toolkit.gp_locks_on_relation ;
relation 0 pg_authid_rolresgroup_index 6440   13212 AccessShareLock t select * from gp_toolkit.gp_locks_on_relation ;
relation 0 pg_authid_rolresqueue_index 6029   13212 AccessShareLock t select * from gp_toolkit.gp_locks_on_relation ;
relation 14569 pg_class 1259   13212 AccessShareLock t select * from gp_toolkit.gp_locks_on_relation ;
relation 14569 pg_class_oid_index 2662   13212 AccessShareLock t select * from gp_toolkit.gp_locks_on_relation ;
relation 14569 pg_class_relname_nsp_index 2663   13212 AccessShareLock t select * from gp_toolkit.gp_locks_on_relation ;
relation 14569 pg_class_tblspc_relfilenode_index 3455   13212 AccessShareLock t select * from gp_toolkit.gp_locks_on_relation ;
relation 0 pg_database 1262   13212 AccessShareLock t select * from gp_toolkit.gp_locks_on_relation ;
relation 0 pg_database_datname_index 2671   13212 AccessShareLock t select * from gp_toolkit.gp_locks_on_relation ;
relation 0 pg_database_oid_index 2672   13212 AccessShareLock t select * from gp_toolkit.gp_locks_on_relation ;
relation 14569 pg_locks 11667   13212 AccessShareLock t select * from gp_toolkit.gp_locks_on_relation ;
relation 14569 pg_stat_activity 11755   13212 AccessShareLock t select * from gp_toolkit.gp_locks_on_relation ;

● 锁详情
pid transaction_id db_name schemaname object_name lock_type lock_mode lock_satus waitting_satus query_start query_text

● state_change字段长时间没有更新过的连接信息
datid datname pid sess_id usesysid usename application_name client_addr client_hostname client_port backend_start xact_start query_start state_change waiting state backend_xid backend_xmin query waiting_reason rsgid rsgname rsgqueueduration
14569 postgres 11645 22 10 mppadmin psql     -1 2023-07-06 16:07:06.394764+08   2023-07-06 16:08:03.126011+08 2023-07-06 16:08:03.17127+08 f idle     create user lhr password 'lhr' superuser;   0 unknown  
14569 postgres 12077 39 10 mppadmin Navicat 172.17.28.12   58984 2023-07-06 16:14:36.375167+08   2023-07-06 16:14:53.48607+08 2023-07-06 16:14:53.490785+08 f idle     select version()   0 unknown  
14569 postgres 12241 43 10 mppadmin Navicat 192.168.27.9   63157 2023-07-06 16:17:51.87191+08   2023-07-06 16:17:58.058964+08 2023-07-06 16:17:58.727468+08 f idle     SELECT r.routine_schema, r.routine_name , string_agg(p.udt_name, ', ' ORDER BY p.ordinal_position) AS object_info FROM information_schema.routines r LEFT JOIN information_schema.parameters p ON r.specific_name = p.specific_name GROUP BY p.specific_name, r.routine_schema, r.routine_name ORDER BY r.routine_schema   0 unknown  
16432 lhrgpdb 12248 44 10 mppadmin Navicat 192.168.27.9   63172 2023-07-06 16:17:58.000212+08   2023-07-06 16:17:58.827257+08 2023-07-06 16:17:59.085215+08 f idle     SELECT r.routine_schema, r.routine_name , string_agg(p.udt_name, ', ' ORDER BY p.ordinal_position) AS object_info FROM information_schema.routines r LEFT JOIN information_schema.parameters p ON r.specific_name = p.specific_name GROUP BY p.specific_name, r.routine_schema, r.routine_name ORDER BY r.routine_schema   0 unknown  
14569 postgres 13215 56 16433 lhr       -1 2023-07-06 16:37:22.864347+08   2023-07-06 16:37:23.406081+08 2023-07-06 16:37:23.408512+08 f idle     select dfsegment,dfhostname,sc.address,dfdevice,sc.datadir,round(dfspace/1024/1024,2) as free_disk_GB
from gp_toolkit.gp_disk_free df , gp_segment_configuration sc
where df.dfsegment= sc.content and role='p'
ORDER BY address,dfsegment ;		   0 unknown  

[回到目录]

SQL情况

● 慢查询SQL
pid sess_id client_port datname usename rsqname client_addr application_name state waiting waiting_reason backend_start xact_start state_change query_start xact_stay query_stay query yc_filesize_gb yc_numfiles kill1 kill2
13212 56 55112 postgres lhr pg_default 192.168.27.9 psql active f   2023-07-06 16:37:21.73868+08 2023-07-06 16:37:24.106933+08 2023-07-06 16:37:24.106943+08 2023-07-06 16:37:24.106933+08 0 0 SELECT pgsa.pid, pgsa.sess_id, pgsa.client_port, pgsa.datname AS datname, pgsa.usename AS usename, (SELECT nb.rrrsqname from gp_toolkit.gp_resq_role nb where nb.rrrolname=pgsa.usename) rsqname, pgsa.client_addr client_addr, pgsa.application_name AS application_name, pgsa.state AS state, pgsa.waiting, pgsa.waiting_reason, pgsa.backend_start AS backend_start, pgsa.xact_start AS xact_start, state_change, pgsa.query_start AS query_start, TRUNC(EXTRACT( epoch FROM (NOW() - pgsa.xact_start) )) AS xact_stay, trunc(EXTRACT( epoch FROM (NOW() - pgsa.query_start) )) AS query_stay, REPLACE(pgsa.QUERY, chr(10), ' ') AS QUERY, yc_filesize_GB, yc_numfiles, 'select pg_terminate_backend('||pgsa.pid||');' kill1, 'select pg_cancel_backend('||pgsa.pid||');' kill2 FROM pg_stat_activity AS pgsa -- 溢出文件大小查询 LEFT JOIN (select pid,trunc(sum(     select pg_terminate_backend(13212); select pg_cancel_backend(13212);

[回到目录]

高可用情况

● 主从流复制情况

● 主库查看wal日志发送状态
pid usesysid usename application_name client_addr client_hostname client_port backend_start backend_xmin state sent_location write_location flush_location 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
14566 template0 0 0 0 0 0
14569 postgres 0 0 0 0 0
16432 lhrgpdb 0 0 0 0 0

[回到目录]

数据库性能

● 相关参数 ● 重要参数
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.   postmaster 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    
gp_resource_manager queue   Resource Usage Sets the type of resource manager. Only support "queue" and "group" for now. postmaster string default       queue queue    
listen_addresses *   Connections and Authentication / Connection Settings Sets the host name or IP address(es) to listen to.   postmaster string configuration file       localhost * /home/mppadmin/dbdata/master/mppseg-1/postgresql.conf 59
log_filename gpdb-%Y-%m-%d_%H%M%S.csv   Reporting and Logging / Where to Log Sets the file name pattern for log files.   sighup string default       gpdb-%Y-%m-%d_%H%M%S.csv gpdb-%Y-%m-%d_%H%M%S.csv    
logging_collector on   Reporting and Logging / Where to Log Start a subprocess to capture stderr output and/or csvlogs into log files.   postmaster bool default       on on    
log_min_duration_statement -1 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 default -1 2147483647   -1 -1    
log_statement all   Reporting and Logging / What to Log Sets the type of statements logged.   superuser enum configuration file     {none,ddl,mod,all} none all /home/mppadmin/dbdata/master/mppseg-1/postgresql.conf 683
log_truncate_on_rotation off   Reporting and Logging / Where to Log Truncate existing log files of same name during log rotation.   sighup bool default       off off    
max_connections 250   Connections and Authentication / Connection Settings Sets the maximum number of concurrent connections.   postmaster integer configuration file 10 8388607   200 250 /home/mppadmin/dbdata/master/mppseg-1/postgresql.conf 74
port 5432   Connections and Authentication / Connection Settings Sets the TCP port the server listens on.   postmaster integer command line 1 65535   5432 5432    

● 后台写进程统计信息
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
6 2 11488 471 253 0 0 19 0 1130 2023-07-06 16:05:30.756007+08

● 查看当前库的表膨胀信息
table_name table_size n_dead_tup n_live_tup dead_tup_ratio

● gp_toolkit.gp_bloat_diag

表oid 模式名 表名 期望页数 实际页数 real_size all_size 表大小 膨胀状态

● 缓存命中率和事务提交率
datname blks_read blks_hit xact_commit xact_rollback cache_hit commit_hit stats_reset
lhrgpdb 246 11197 51 0 97.85% 100.00% 2023-07-06 16:08:41.982468+08
postgres 348 109398 214 3 99.68% 98.61% 2023-07-06 16:05:30.85328+08
------         99.50% 98.88%  

● 数据库统计详情
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 81 6 437 12654 28498 9379 96 5 0 0 1 32 0 0 0 2023-07-06 16:05:31.220787+08
14566 template0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0  
14569 postgres 5 214 3 348 109398 225115 53519 140 9 0 0 2 96 0 0 0 2023-07-06 16:05:30.85328+08
16432 lhrgpdb 1 51 0 246 11197 187240 6963 40 1 0 0 2 3128 0 0 0 2023-07-06 16:08:41.982468+08

● 会话占用内存
datname sess_id usename query segid vmem_mb is_runaway qe_count active_qe_count dirty_qe_count runaway_vmem_mb runaway_command_cnt idle_start
postgres 56 lhr SELECT *
FROM session_state.session_level_memory_consumption d
WHERE d.datname IS NOT NULL
ORDER BY
d.vmem_mb DESC,
d.sess_id,
d.segid
LIMIT 20;		 -1 39 f 2 1 -1 0 0 2023-07-06 16:37:24.640522+08
postgres 56 lhr select dfsegment,dfhostname,sc.address,dfdevice,sc.datadir,round(dfspace/1024/1024,2) as free_disk_GB
from gp_toolkit.gp_disk_free df , gp_segment_configuration sc
where df.dfsegment= sc.content and role='p'
ORDER BY address,dfsegment ;		 -1 39 f 2 1 -1 0 0 2023-07-06 16:37:24.640522+08
postgres 56 lhr SELECT *
FROM session_state.session_level_memory_consumption d
WHERE d.datname IS NOT NULL
ORDER BY
d.vmem_mb DESC,
d.sess_id,
d.segid
LIMIT 20;		 0 27 f 2 1 -1 0 0 2023-07-06 16:36:07.780301+08
postgres 56 lhr select dfsegment,dfhostname,sc.address,dfdevice,sc.datadir,round(dfspace/1024/1024,2) as free_disk_GB
from gp_toolkit.gp_disk_free df , gp_segment_configuration sc
where df.dfsegment= sc.content and role='p'
ORDER BY address,dfsegment ;		 0 27 f 2 1 -1 0 0 2023-07-06 16:36:07.780301+08
postgres 56 lhr SELECT *
FROM session_state.session_level_memory_consumption d
WHERE d.datname IS NOT NULL
ORDER BY
d.vmem_mb DESC,
d.sess_id,
d.segid
LIMIT 20;		 1 27 f 2 1 -1 0 0 2023-07-06 16:36:07.780205+08
postgres 56 lhr select dfsegment,dfhostname,sc.address,dfdevice,sc.datadir,round(dfspace/1024/1024,2) as free_disk_GB
from gp_toolkit.gp_disk_free df , gp_segment_configuration sc
where df.dfsegment= sc.content and role='p'
ORDER BY address,dfsegment ;		 1 27 f 2 1 -1 0 0 2023-07-06 16:36:07.780205+08
postgres 56 lhr SELECT *
FROM session_state.session_level_memory_consumption d
WHERE d.datname IS NOT NULL
ORDER BY
d.vmem_mb DESC,
d.sess_id,
d.segid
LIMIT 20;		 2 27 f 2 1 -1 0 0 2023-07-06 16:36:07.61486+08
postgres 56 lhr select dfsegment,dfhostname,sc.address,dfdevice,sc.datadir,round(dfspace/1024/1024,2) as free_disk_GB
from gp_toolkit.gp_disk_free df , gp_segment_configuration sc
where df.dfsegment= sc.content and role='p'
ORDER BY address,dfsegment ;		 2 27 f 2 1 -1 0 0 2023-07-06 16:36:07.61486+08
postgres 56 lhr SELECT *
FROM session_state.session_level_memory_consumption d
WHERE d.datname IS NOT NULL
ORDER BY
d.vmem_mb DESC,
d.sess_id,
d.segid
LIMIT 20;		 3 27 f 2 1 -1 0 0 2023-07-06 16:36:07.615953+08
postgres 56 lhr select dfsegment,dfhostname,sc.address,dfdevice,sc.datadir,round(dfspace/1024/1024,2) as free_disk_GB
from gp_toolkit.gp_disk_free df , gp_segment_configuration sc
where df.dfsegment= sc.content and role='p'
ORDER BY address,dfsegment ;		 3 27 f 2 1 -1 0 0 2023-07-06 16:36:07.615953+08
postgres 39 mppadmin select version() -1 16 f 1 0 -1 0 0 2023-07-06 16:14:53.490695+08
postgres 22 mppadmin create user lhr password 'lhr' superuser; -1 15 f 1 0 -1 0 0 2023-07-06 16:08:03.171188+08
postgres 43 mppadmin SELECT r.routine_schema, r.routine_name , string_agg(p.udt_name, ', ' ORDER BY p.ordinal_position) AS object_info FROM information_schema.routines r LEFT JOIN information_schema.parameters p ON r.specific_name = p.specific_name GROUP BY p.specific_name, r.routine_schema, r.routine_name ORDER BY r.routine_schema -1 15 f 1 0 -1 0 0 2023-07-06 16:17:58.727353+08
lhrgpdb 44 mppadmin SELECT r.routine_schema, r.routine_name , string_agg(p.udt_name, ', ' ORDER BY p.ordinal_position) AS object_info FROM information_schema.routines r LEFT JOIN information_schema.parameters p ON r.specific_name = p.specific_name GROUP BY p.specific_name, r.routine_schema, r.routine_name ORDER BY r.routine_schema -1 15 f 1 0 -1 0 0 2023-07-06 16:17:59.085078+08

● 资源队列
queueid rsqname rsqcountlimit rsqcountvalue rsqcostlimit rsqcostvalue rsqmemorylimit rsqmemoryvalue rsqwaiters rsqholders
6055 pg_default 20 0 -1 0 -1 0 0 0

[回到目录]

日志

● master库近7天的错误日志
logtime loguser logdatabase logpid logthread loghost logport logsessiontime logtransaction logsession logcmdcount logsegment logslice logdistxact loglocalxact logsubxact logseverity logstate logmessage logdetail loghint logquery logquerypos logcontext logdebug logcursorpos logfunction logfile logline logstack
2023-07-07 06:35:01.974475+08 lhr postgres p13088 th-1365743488 192.168.27.9 54997 2023-07-07 06:34:49+08 0 con55 cmd2 seg-1       sx1 ERROR 42601 syntax error at or near "FROMsession_state"           SELECT*
FROMsession_state.session_level_memory_consumption d
WHEREd.datname IS NOT NULL
ORDER BY
d.vmem_mb DESC,
d.sess_id,
d.segid
LIMIT 20;		 10   scan.l 1067  
2023-07-07 06:34:35.850232+08 lhr postgres p13075 th-1365743488 192.168.27.9 54983 2023-07-07 06:34:35+08 0 con53   seg-1       sx1 FATAL 28P01 password authentication failed for user "lhr" Connection matched pg_hba.conf line 94: "host all all 0.0.0.0/0 md5"           0   auth.c 318  
2023-07-07 06:24:57.886519+08 lhr postgres p12599 th-1365743488 192.168.27.9 50377 2023-07-07 06:24:54+08 0 con50 cmd120 seg-1       sx1 ERROR 42P01 relation "session_state.session_level_memory_consumption" does not exist           SELECT *
FROM session_state.session_level_memory_consumption d
WHERE d.datname IS NOT NULL
ORDER BY
d.vmem_mb DESC,
d.sess_id,
d.segid
LIMIT 20;		 17   parse_relation.c 1008  
2023-07-07 06:23:36.071056+08 lhr postgres p12528 th-1365743488 192.168.27.9 50206 2023-07-07 06:23:32+08 0 con48 cmd120 seg-1       sx1 ERROR 42P01 relation "session_state.session_level_memory_consumption" does not exist           SELECT *
FROM session_state.session_level_memory_consumption d
WHERE d.datname IS NOT NULL
ORDER BY
d.vmem_mb DESC,
d.sess_id,
d.segid
LIMIT 20;		 17   parse_relation.c 1008  
2023-07-07 06:17:37.308231+08 postgres mppadmin p12226 th-1365743488 192.168.27.9 63113 2023-07-07 06:17:37+08 0 con41   seg-1       sx1 FATAL 28P01 password authentication failed for user "postgres" Connection matched pg_hba.conf line 94: "host all all 0.0.0.0/0 md5"           0   auth.c 318  
2023-07-07 06:06:53.248231+08 mppadmin postgres p11633 th-1365743488 192.168.27.9 54499 2023-07-07 06:06:53+08 0 con21   seg-1       sx1 FATAL 28P01 password authentication failed for user "mppadmin" Connection matched pg_hba.conf line 94: "host all all 0.0.0.0/0 md5"           0   auth.c 318  
2023-07-07 06:05:56.02365+08 mppadmin postgres p10499 th-1365743488 [local]   2023-07-07 06:05:56+08 0     seg-1         FATAL 57P03 the database system is starting up last replayed record at 0/0           0   postmaster.c 2593  
2023-07-07 06:05:54.361234+08     p10453 th1942603904       0     seg-1         FATAL 57P01 terminating background worker "data cacher process" due to administrator command             0   bgworker.c 565  
2023-07-07 06:05:54.361222+08     p10454 th1942603904       0     seg-1         FATAL 57P01 terminating background worker "sweeper process" due to administrator command             0   bgworker.c 565  
2023-07-07 06:05:53.852161+08 mppadmin postgres p10448 th1942603904 [local]   2023-07-07 06:05:53+08 0     seg-1         FATAL 57P03 the database system is starting up last replayed record at 0/0           0   postmaster.c 2593  
2023-07-07 06:05:52.384359+08     p8841 th1153742976       0     seg-1         FATAL 57P01 terminating background worker "data cacher process" due to administrator command             0   bgworker.c 565  
2023-07-07 06:05:52.384344+08     p8842 th1153742976       0     seg-1         FATAL 57P01 terminating background worker "sweeper process" due to administrator command             0   bgworker.c 565  
2023-07-07 06:05:30.744683+08 mppadmin postgres p8836 th1153742976 [local]   2023-07-07 06:05:30+08 0     seg-1         FATAL 57P03 the database system is starting up last replayed record at 0/0           0   postmaster.c 2593  

● segment库近7天的错误日志
logtime loguser logdatabase logpid logthread loghost logport logsessiontime logtransaction logsession logcmdcount logsegment logslice logdistxact loglocalxact logsubxact logseverity logstate logmessage logdetail loghint logquery logquerypos logcontext logdebug logcursorpos logfunction logfile logline logstack
2023-07-07 06:04:39.228727+08 mppadmin postgres p4445 th1870063744 [local]   2023-07-07 06:04:39+08 0     seg1         FATAL 57P03 the database system is starting up last replayed record at 0/6EB6D58           0   postmaster.c 2593  
2023-07-07 06:04:39.149214+08 mppadmin postgres p4435 th-674928512 [local]   2023-07-07 06:04:39+08 0     seg0         FATAL 57P03 the database system is starting up last replayed record at 0/0           0   postmaster.c 2593  
2023-07-07 06:04:38.836479+08 mppadmin postgres p4395 th2047658112 [local]   2023-07-07 06:04:38+08 0     seg2         FATAL 57P03 the database system is starting up last replayed record at 0/6EC27E8           0   postmaster.c 2593  
2023-07-07 06:04:38.835117+08 mppadmin postgres p4394 th1117632640 [local]   2023-07-07 06:04:38+08 0     seg3         FATAL 57P03 the database system is starting up last replayed record at 0/0           0   postmaster.c 2593  

[回到目录]

其它

● 所有角色(用户)
rolname rolsuper rolinherit rolcreaterole rolcreatedb rolcatupdate rolcanlogin rolreplication rolconnlimit rolpassword rolvaliduntil rolconfig rolresqueue oid rolcreaterextgpfd rolcreaterexthttp rolcreatewextgpfd rolresgroup
mppadmin t t t t t t t -1 ********     6055 10 t t t 6438
lhr t t f f t t f -1 ********     6055 16433 f f f 6438

● pg_user

usename usesysid usecreatedb usesuper usecatupd userepl passwd valuntil useconfig
mppadmin 10 t t t t ********    
lhr 16433 f t t f ********    

● pg_shadow

usename usesysid usecreatedb usesuper usecatupd userepl passwd valuntil useconfig
mppadmin 10 t t t t md583bdc9cd23795c947dd7844a0f2bf1ad    
lhr 16433 f t t f md53e5c401ee2b9f28db1bb075b1b99e0ad    

● 授权信息
raroleid rarolename ramemberid ramembername

● 当前库的所有schema
catalog_name schema_name schema_owner default_character_set_catalog default_character_set_schema default_character_set_name sql_path
postgres pg_toast mppadmin        
postgres pg_aoseg mppadmin        
postgres pg_bitmapindex mppadmin        
postgres pg_catalog mppadmin        
postgres public mppadmin        
postgres information_schema mppadmin        
postgres identity_auth mppadmin        
postgres cachedata mppadmin        
postgres gp_toolkit mppadmin        
postgres zhparser mppadmin        
postgres session_state lhr        

● 所有语言
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  
plpgsql 10 t t 12332 12333 12334  

● 所有可用插件

● 所有插件（已编译）
name default_version installed_version comment
plpgsql 1.0 1.0 PL/pgSQL procedural language
plperl 1.0   PL/Perl procedural language
plperlu 1.0   PL/PerlU untrusted procedural language
plpython2u 1.0   PL/Python2U untrusted procedural language
plpythonu 1.0   PL/PythonU untrusted procedural language
amcheck 1.0   functions for verifying relation integrity
citext 1.0   data type for case-insensitive character strings
fuzzystrmatch 1.0   determine similarities and distance between strings
postgres_fdw 1.0   foreign-data wrapper for remote PostgreSQL servers
pageinspect 1.2   inspect the contents of database pages at a low level
hstore 1.3   data type for storing sets of (key, value) pairs
identity_auth 1.0 1.0 extension for identity authentication
cachedata 1.0 1.0 support for caching hot data
sslinfo 1.0   information about SSL certificates
gp_sparse_vector 1.0.1   SParse vector implementation for GreenPlum
gp_distribution_policy 1.0   check distribution policy in a GPDB cluster
gp_internal_tools 1.0.0 1.0.0 Different internal tools for Greenplum
orafce 3.7   Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS
dblink 1.1   connect to other PostgreSQL databases from within a database
pgcrypto 1.1   cryptographic functions
pg_trgm 1.1 1.1 text similarity measurement and index searching based on trigrams
btree_gin 1.0 1.0 support for indexing common datatypes in GIN
pg_stat_statements 1.2 1.2 track execution statistics of all SQL statements executed
zhparser 2.1 2.1 a parser for full-text search of Chinese
uuid-ossp 1.0 1.0 generate universally unique identifiers (UUIDs)
iolimit 1.0   support extension for cgroup
pgagent 4.0.0   A PostgreSQL job scheduler
gpexpand_rw 1.0 1.0 support read or write when gpexpand
force_view 1.0 1.0 support force view
postgis 2.1.5 2.1.5 PostGIS geometry, geography, and raster spatial types and functions
postgis_tiger_geocoder 2.1.5   PostGIS tiger geocoder and reverse geocoder
postgis_topology 2.1.5   PostGIS topology spatial types and functions
pxf 2.0   Extension which allows to access unmanaged data

● 已安装的插件
Name Version Schema Description
btree_gin 1.0 pg_catalog support for indexing common datatypes in GIN
cachedata 1.0 pg_catalog support for caching hot data
force_view 1.0 pg_catalog support force view
gp_internal_tools 1.0.0 public Different internal tools for Greenplum
gpexpand_rw 1.0 public support read or write when gpexpand
identity_auth 1.0 pg_catalog extension for identity authentication
pg_stat_statements 1.2 pg_catalog track execution statistics of all SQL statements executed
pg_trgm 1.1 pg_catalog text similarity measurement and index searching based on trigrams
plpgsql 1.0 pg_catalog PL/pgSQL procedural language
postgis 2.1.5 pg_catalog PostGIS geometry, geography, and raster spatial types and functions
uuid-ossp 1.0 pg_catalog generate universally unique identifiers (UUIDs)
zhparser 2.1 public a parser for full-text search of Chinese

[回到目录]