# 1. 检查数据库服务状态
[fgedu@fgedu.net.cn ~]$ gs_ctl status -D /gauss/fgdata
gs_ctl: server is running (PID: 12345)
/gauss/app/bin/gaussdb “-D” “/gauss/fgdata”

# 2. 检查连接数
[fgedu@fgedu.net.cn ~]$ psql -h localhost -p 5432 -U fgedu -d fgedudb -c “SELECT count(*) FROM pg_stat_activity;”
count
——-
50

# 3. 检查资源使用情况
# CPU使用率
[fgedu@fgedu.net.cn ~]$ top -b -n 1 | grep Cpu
%Cpu(s): 5.0 us, 2.0 sy, 0.0 ni, 93.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st

# 内存使用情况
[fgedu@fgedu.net.cn ~]$ free -h
total used free shared buff/cache available
Mem: 32G 10G 10G 2G 12G 20G

# 磁盘空间使用情况 学习交流加群风哥QQ113257174
[fgedu@fgedu.net.cn ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda1 100G 50G 50G 50% /
/dev/sdb1 500G 200G 300G 40% /gauss

# 4. 检查表空间使用情况
[fgedu@fgedu.net.cn ~]$ psql -h localhost -p 5432 -U fgedu -d fgedudb -c ”
SELECT
tablespace_name,
round(sum(size) / 1024 / 1024 / 1024, 2) AS size_gb
FROM (
SELECT
tablespace_name,
pg_total_relation_size(quote_ident(schemaname) || ‘.’ || quote_ident(tablename)) AS size
FROM
pg_tables
WHERE
schemaname NOT IN (‘pg_catalog’, ‘information_schema’)
UNION ALL
SELECT
tablespace_name,
pg_indexes_size(quote_ident(schemaname) || ‘.’ || quote_ident(tablename)) AS size
FROM
pg_tables
WHERE
schemaname NOT IN (‘pg_catalog’, ‘information_schema’)
) AS sizes
GROUP BY
tablespace_name
ORDER BY 更多视频教程www.fgedu.net.cn
size_gb DESC;
”
tablespace_name | size_gb
—————-+———-
pg_default | 10.5
fgedutbs | 5.2

# 5. 检查备份状态
[fgedu@fgedu.net.cn ~]$ ls -la /gauss/backup/
total 204800
-rw-r–r– 1 fgedu fgedu 209715200 Sep 1 00:00 fgedudb_full_backup_20240901.backup

# 6. 检查日志文件
[fgedu@fgedu.net.cn ~]$ tail -n 50 /gauss/fgdata/log/postgresql-$(date +%Y-%m-%d).log
# 检查是否有错误或警告信息

# 7. 检查慢查询
[fgedu@fgedu.net.cn ~]$ psql -h localhost -p 5432 -U fgedu -d fgedudb -c ”
SELECT
pid,
usename,
datname,
query_start,
now() – query_start AS duration,
query
FROM
pg_stat_activity
WHERE
state = ‘active’ AND
now() – query_start > interval ‘5 seconds’ 更多学习教程公众号风哥教程itpux_com
ORDER BY
duration DESC;
”
# 检查是否有慢查询

# 1. 检查数据库版本
[fgedu@fgedu.net.cn ~]$ psql -h localhost -p 5432 -U fgedu -d fgedudb -c “SELECT version();”
version
———————————————————————————————————-
PostgreSQL 14.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.0, 64-bit
(1 row)

# 2. 检查数据库参数配置
[fgedu@fgedu.net.cn ~]$ psql -h localhost -p 5432 -U fgedu -d fgedudb -c “SHOW shared_buffers;”
shared_buffers
—————-
8GB
(1 row)

from DB视频:www.itpux.com
[fgedu@fgedu.net.cn ~]$ psql -h localhost -p 5432 -U fgedu -d fgedudb -c “SHOW work_mem;”
work_mem
———-
16MB
(1 row)

[fgedu@fgedu.net.cn ~]$ psql -h localhost -p 5432 -U fgedu -d fgedudb -c “SHOW maintenance_work_mem;”
maintenance_work_mem
———————
1GB
(1 row)

# 3. 检查索引使用情况
[fgedu@fgedu.net.cn ~]$ psql -h localhost -p 5432 -U fgedu -d fgedudb -c ”
SELECT
schemaname,
tablename,
indexname,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM
pg_stat_user_indexes
JOIN
pg_stat_user_tables ON pg_stat_user_indexes.schemaname = pg_stat_user_tables.schemaname AND pg_stat_user_indexes.tablename = pg_stat_user_tables.tablename
ORDER BY
idx_scan DESC
LIMIT 10;
”
schemaname | tablename | indexname | idx_scan | idx_tup_read | idx_tup_fetch
————+———–+———–+———-+————–+—————
public | fgedu_users | fgedu_users_pkey | 100000 | 100000 | 100000
public | fgedu_orders | fgedu_orders_pkey | 50000 | 50000 | 50000

# 4. 检查表统计信息
[fgedu@fgedu.net.cn ~]$ psql -h localhost -p 5432 -U fgedu -d fgedudb -c ”
SELECT
schemaname,
tablename,
n_live_tup,
n_dead_tup,
last_vacuum,
last_analyze
FROM
pg_stat_user_tables
ORDER BY
n_dead_tup DESC
LIMIT 10;
”
schemaname | tablename | n_live_tup | n_dead_tup | last_vacuum | last_analyze
————+———–+————+————+————-+—————
public | fgedu_orders | 100000 | 5000 | 2024-09-01 | 2024-09-01
public | fgedu_users | 50000 | 2000 | 2024-09-01 | 2024-09-01

# 5. 检查死锁情况
[fgedu@fgedu.net.cn ~]$ psql -h localhost -p 5432 -U fgedu -d fgedudb -c ”
SELECT
blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_query,
blocking_activity.query AS blocking_query
FROM
pg_catalog.pg_locks blocked_locks
JOIN
pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN
pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN
pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE
NOT blocked_locks.GRANTED;
”
# 检查是否有死锁

# 6. 检查安全配置
[fgedu@fgedu.net.cn ~]$ psql -h localhost -p 5432 -U fgedu -d fgedudb -c ”
SELECT
usename,
usesuper,
passwd IS NOT NULL AS has_password
FROM
pg_user;
”
usename | usesuper | has_password
———+———-+————–
postgres | t | t
fgedu | f | t
fgedu01 | f | t

# 1. 确认问题
[fgedu@fgedu.net.cn ~]$ psql -h localhost -p 5432 -U fgedu -d fgedudb -c ”
SELECT
tablespace_name,
round(sum(size) / 1024 / 1024 / 1024, 2) AS size_gb,
round(sum(size) / (SELECT pg_tablespace_size(tablespace_name)) * 100, 2) AS usage_percent
FROM (
SELECT
tablespace_name,
pg_total_relation_size(quote_ident(schemaname) || ‘.’ || quote_ident(tablename)) AS size
FROM
pg_tables
WHERE
schemaname NOT IN (‘pg_catalog’, ‘information_schema’)
UNION ALL
SELECT
tablespace_name,
pg_indexes_size(quote_ident(schemaname) || ‘.’ || quote_ident(tablename)) AS size
FROM
pg_tables
WHERE
schemaname NOT IN (‘pg_catalog’, ‘information_schema’)
) AS sizes
GROUP BY
tablespace_name
ORDER BY
usage_percent DESC;
”
tablespace_name | size_gb | usage_percent
—————-+———-+—————
fgedutbs | 45.0 | 90.0
pg_default | 10.5 | 21.0

# 2. 分析问题
# 查看占用空间最大的表
[fgedu@fgedu.net.cn ~]$ psql -h localhost -p 5432 -U fgedu -d fgedudb -c ”
SELECT
schemaname,
tablename,
round(pg_total_relation_size(quote_ident(schemaname) || ‘.’ || quote_ident(tablename)) / 1024 / 1024 / 1024, 2) AS size_gb
FROM
pg_tables
WHERE
tablespace_name = ‘fgedutbs’
ORDER BY
size_gb DESC
LIMIT 5;
”
schemaname | tablename | size_gb
————+———–+———-
public | fgedu_logs | 40.0
public | fgedu_data | 5.0

# 3. 制定解决方案
# 方案1：清理fgedu_logs表中的历史数据
# 方案2：扩展表空间

# 4. 执行解决方案
# 清理历史数据
[fgedu@fgedu.net.cn ~]$ psql -h localhost -p 5432 -U fgedu -d fgedudb -c “DELETE FROM fgedu_logs WHERE log_time < '2024-08-01';" DELETE 1000000
# 执行VACUUM回收空间
[fgedu@fgedu.net.cn ~]$ psql -h localhost -p 5432 -U fgedu -d fgedudb -c “VACUUM FULL fgedu_logs;”
VACUUM

# 5. 验证结果
[fgedu@fgedu.net.cn ~]$ psql -h localhost -p 5432 -U fgedu -d fgedudb -c ”
SELECT
tablespace_name,
round(sum(size) / 1024 / 1024 / 1024, 2) AS size_gb,
round(sum(size) / (SELECT pg_tablespace_size(tablespace_name)) * 100, 2) AS usage_percent
FROM (
SELECT
tablespace_name,
pg_total_relation_size(quote_ident(schemaname) || ‘.’ || quote_ident(tablename)) AS size
FROM
pg_tables
WHERE
schemaname NOT IN (‘pg_catalog’, ‘information_schema’)
UNION ALL
SELECT
tablespace_name,
pg_indexes_size(quote_ident(schemaname) || ‘.’ || quote_ident(tablename)) AS size
FROM
pg_tables
WHERE
schemaname NOT IN (‘pg_catalog’, ‘information_schema’)
) AS sizes
WHERE
tablespace_name = ‘fgedutbs’
GROUP BY
tablespace_name;
”
tablespace_name | size_gb | usage_percent
—————-+———-+—————
fgedutbs | 15.0 | 30.0

日常巡检脚本：

#!/bin/bash
# daily_inspection.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

# 日常巡检脚本

# 数据库信息
DB_HOST=”localhost”
DB_PORT=”5432″
DB_USER=”fgedu”
DB_NAME=”fgedudb”

# 日志文件
LOG_FILE=”/gauss/logs/inspection_$(date +%Y%m%d).log”

echo “开始日常巡检…” > $LOG_FILE
echo “巡检时间: $(date)” >> $LOG_FILE
echo “====================================” >> $LOG_FILE

# 1. 检查数据库服务状态
echo “1. 检查数据库服务状态…” >> $LOG_FILE
gs_ctl status -D /gauss/fgdata >> $LOG_FILE 2>&1
echo “” >> $LOG_FILE

# 2. 检查连接数
echo “2. 检查连接数…” >> $LOG_FILE
psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c “SELECT count(*) FROM pg_stat_activity;” >> $LOG_FILE 2>&1
echo “” >> $LOG_FILE

# 3. 检查资源使用情况
echo “3. 检查资源使用情况…” >> $LOG_FILE

# CPU使用率
echo “CPU使用率:” >> $LOG_FILE
top -b -n 1 | grep Cpu >> $LOG_FILE

# 内存使用情况
echo “内存使用情况:” >> $LOG_FILE
free -h >> $LOG_FILE

# 磁盘空间使用情况
echo “磁盘空间使用情况:” >> $LOG_FILE
df -h >> $LOG_FILE
echo “” >> $LOG_FILE

# 4. 检查表空间使用情况
echo “4. 检查表空间使用情况…” >> $LOG_FILE
psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c ”
SELECT
tablespace_name,
round(sum(size) / 1024 / 1024 / 1024, 2) AS size_gb
FROM (
SELECT
tablespace_name,
pg_total_relation_size(quote_ident(schemaname) || ‘.’ || quote_ident(tablename)) AS size
FROM
pg_tables
WHERE
schemaname NOT IN (‘pg_catalog’, ‘information_schema’)
UNION ALL
SELECT
tablespace_name,
pg_indexes_size(quote_ident(schemaname) || ‘.’ || quote_ident(tablename)) AS size
FROM
pg_tables
WHERE
schemaname NOT IN (‘pg_catalog’, ‘information_schema’)
) AS sizes
GROUP BY
tablespace_name
ORDER BY
size_gb DESC;
” >> $LOG_FILE 2>&1
echo “” >> $LOG_FILE

# 5. 检查备份状态
echo “5. 检查备份状态…” >> $LOG_FILE
ls -la /gauss/backup/ >> $LOG_FILE
echo “” >> $LOG_FILE

# 6. 检查日志文件
echo “6. 检查日志文件…” >> $LOG_FILE
tail -n 50 /gauss/fgdata/log/postgresql-$(date +%Y-%m-%d).log | grep -E ‘ERROR|WARNING’ >> $LOG_FILE
echo “” >> $LOG_FILE

# 7. 检查慢查询
echo “7. 检查慢查询…” >> $LOG_FILE
psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c ”
SELECT
pid,
usename,
datname,
query_start,
now() – query_start AS duration,
query
FROM
pg_stat_activity
WHERE
state = ‘active’ AND
now() – query_start > interval ‘5 seconds’
ORDER BY
duration DESC;
” >> $LOG_FILE 2>&1
echo “” >> $LOG_FILE

echo “====================================” >> $LOG_FILE
echo “日常巡检完成!” >> $LOG_FILE

# 发送邮件通知
echo “发送巡检报告邮件…”
mail -s “GaussDB日常巡检报告 $(date +%Y%m%d)” admin@fgedu.net.cn < $LOG_FILE
echo “日常巡检脚本执行完成!”

运行日常巡检脚本：

[fgedu@fgedu.net.cn ~]$ chmod +x daily_inspection.sh
[fgedu@fgedu.net.cn ~]$ ./daily_inspection.sh
开始日常巡检…
发送巡检报告邮件…
日常巡检脚本执行完成!