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

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

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

[转到页底]

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


数据库总体概况

● 集群信息
dbid content role preferred_role mode status port hostname address datadir
1 -1 p p n u 5432 lhrgp40 lhrgp40 /opt/greenplum/data/master/gpseg-1
2 0 p p n u 6000 lhrgp41 lhrgp41 /opt/greenplum/data/primary/gpseg0
3 1 p p n u 6000 lhrgp42 lhrgp42 /opt/greenplum/data/primary/gpseg1

● 实例信息
pg_start_time server_ip server_port client_ip client_port server_version primary_or_standby now_date
2022-03-08 11:06:44.514805+08 172.72.6.40 5432 192.168.66.64 22214 PostgreSQL 9.4.26 (Greenplum Database 6.19.3 build commit:33e10eef63ae25aeb33afbefdda46b6367353800 Open Source) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Feb 24 2022 23:24:35 primary 2022-03-08 15:20:27.216876+08

● 数据库基本信息
Name Owner Encoding Collate Ctype Access privileges Size Tablespace Description
lhrdb gpadmin UTF8 en_US.utf8 en_US.utf8   49 MB pg_default  
lhrgpdb gpadmin UTF8 en_US.utf8 en_US.utf8   86 MB pg_default  
postgres gpadmin UTF8 en_US.utf8 en_US.utf8   49 MB pg_default default administrative connection database
template0 gpadmin UTF8 en_US.utf8 en_US.utf8 =c/gpadmin
gpadmin=CTc/gpadmin		 49 MB pg_default unmodifiable empty database
template1 gpadmin UTF8 en_US.utf8 en_US.utf8 =c/gpadmin
gpadmin=CTc/gpadmin		 49 MB pg_default default template for new databases
● 查看各数据库数据创建时间
datname size access modification change creation isdir
template1 4 2022-03-08 10:56:34+08 2022-03-08 10:56:34+08 2022-03-08 10:56:34+08   f
template0 4 2022-03-08 10:56:40+08 2022-03-08 10:56:40+08 2022-03-08 10:56:40+08   f
postgres 4 2022-03-08 10:56:41+08 2022-03-08 10:56:40+08 2022-03-08 10:56:40+08   f
lhrgpdb 4 2022-03-08 11:03:34+08 2022-03-08 10:57:04+08 2022-03-08 10:57:04+08   f
lhrdb 4 2022-03-08 15:18:58+08 2022-03-08 15:18:58+08 2022-03-08 15:18:58+08   f

● 所有表空间
oid Name Owner Location Access privileges Options Size Description
1663 pg_default gpadmin       282 MB  
1664 pg_global gpadmin       12 MB  

● 当前客户端连接信息
current_user current_database pg_backend_pid
gpadmin lhrgpdb 21992

● 前几张大表
db schemaname relname rowcount table_size indexes_size total_size
lhrgpdb gp_toolkit gp_disk_free 1000000 0 bytes 0 bytes 0 bytes
lhrgpdb gp_toolkit __gp_log_segment_ext 1000000 0 bytes 0 bytes 0 bytes
lhrgpdb gp_toolkit __gp_log_master_ext 1000000 0 bytes 0 bytes 0 bytes
lhrgpdb public big 30000 36 MB 0 bytes 36 MB

● 当前库的所有数据库对象
schemaname objecttype cnt
public TABLE 1
gp_toolkit VIEW 45
gp_toolkit TABLE 3
gp_toolkit composite type 4

[回到目录]

锁情况

● 所有进程

● 总计
最大连接数 当前连接数 剩余连接数
250 2 248

● 详情
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
16384 lhrgpdb 21002 34 10 gpadmin psql 192.168.66.64   21072 2022-03-08 15:00:31.10226+08   2022-03-08 15:19:24.838957+08 2022-03-08 15:19:24.839534+08 f idle     select * from gp_toolkit;   0 unknown  
16384 lhrgpdb 21992 40 10 gpadmin psql 192.168.66.64   22214 2022-03-08 15:20:27.006844+08 2022-03-08 15:20:27.73798+08 2022-03-08 15:20:27.73798+08 2022-03-08 15:20:27.737983+08 f active   722 select a.* from pg_stat_activity a ;   0 unknown  

● 锁
locktype database relation page tuple virtualxid transactionid classid objid objsubid virtualtransaction pid mode granted fastpath mppsessionid mppiswriter gp_segment_id
relation 16384 11667               4/140 21992 AccessShareLock t t 40 t -1
virtualxid         4/140         4/140 21992 ExclusiveLock t t 40 t -1
virtualxid         1/631         1/631 17461 ExclusiveLock t t 40 t 0
virtualxid         1/631         1/631 17351 ExclusiveLock t t 40 t 1

● 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
16384 lhrgpdb 21002 34 10 gpadmin psql 192.168.66.64   21072 2022-03-08 15:00:31.10226+08   2022-03-08 15:19:24.838957+08 2022-03-08 15:19:24.839534+08 f idle     select * from gp_toolkit;   0 unknown  

[回到目录]

SQL情况

[回到目录]

高可用情况

● 主从流复制情况

● 主库查看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
12809 template0 0 0 0 0 0
12812 postgres 0 0 0 0 0
16384 lhrgpdb 0 0 0 0 0
16392 lhrdb 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    
listen_addresses *   Connections and Authentication / Connection Settings Sets the host name or IP address(es) to listen to.   postmaster string configuration file       localhost * /opt/greenplum/data/master/gpseg-1/postgresql.conf 665
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 /opt/greenplum/data/master/gpseg-1/postgresql.conf 666
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 /opt/greenplum/data/master/gpseg-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
51 4 3659 876 63 0 0 10 0 1148 2022-03-08 10:56:41.685317+08

[回到目录]

其它

● 当前库的所有schema
catalog_name schema_name schema_owner default_character_set_catalog default_character_set_schema default_character_set_name sql_path
lhrgpdb pg_toast gpadmin        
lhrgpdb pg_aoseg gpadmin        
lhrgpdb pg_bitmapindex gpadmin        
lhrgpdb pg_catalog gpadmin        
lhrgpdb public gpadmin        
lhrgpdb information_schema gpadmin        
lhrgpdb gp_toolkit gpadmin        

● 当前库的所有角色(用户)
rolname rolsuper rolinherit rolcreaterole rolcreatedb rolcatupdate rolcanlogin rolreplication rolconnlimit rolpassword rolvaliduntil rolconfig rolresqueue oid rolcreaterextgpfd rolcreaterexthttp rolcreatewextgpfd rolresgroup
gpadmin t t t t t t t -1 ********     6055 10 t t t 6438

usename usesysid usecreatedb usesuper usecatupd userepl passwd valuntil useconfig
gpadmin 10 t t t t ********    

● 所有语言
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
gp_pitr 1.0   Distributed point-in-time-recovery functions
plpgsql 1.0 1.0 PL/pgSQL procedural language
plpython2u 1.0   PL/Python2U untrusted procedural language
hstore 1.3   data type for storing sets of (key, value) pairs
sslinfo 1.0   information about SSL certificates
gp_distribution_policy 1.0   check distribution policy in a GPDB cluster
plperlu 1.0   PL/PerlU untrusted procedural language
gp_internal_tools 1.0.0   Different internal tools for Greenplum
plperl 1.0   PL/Perl procedural language
citext 1.0   data type for case-insensitive character strings
pgcrypto 1.1   cryptographic functions
plpythonu 1.0   PL/PythonU untrusted procedural language
amcheck 1.0   functions for verifying relation integrity
gp_sparse_vector 1.0.1   SParse vector implementation for GreenPlum
dblink 1.1   connect to other PostgreSQL databases from within a database
fuzzystrmatch 1.0   determine similarities and distance between strings
pageinspect 1.2   inspect the contents of database pages at a low level
gp_legacy_string_agg 1.0.0   Legacy one-argument string_agg implementation for Greenplum
postgres_fdw 1.0   foreign-data wrapper for remote PostgreSQL servers

● 已安装的插件
Name Version Schema Description
plpgsql 1.0 pg_catalog PL/pgSQL procedural language

[回到目录]