0、kingbase常用运维命令总结

一、数据库连接与基础操作

1. 1. 连接指定服务器数据库

ksql -h 主机IP -p 端口号 -U 用户名 -d 数据库名 -W  
# 示例：连接 IP 为 192.168.1.1 的数据库  
ksql -h 192.168.1.1 -p 54321 -U system -d test -W

2. 2. 断开数据库连接\q 或 exit
3. 3. 查看数据库列表及详细信息

\l+  
SELECT datname FROM sys_database;

4. 4. 查看数据库版本信息

sys_ctl -V          # 查看服务端版本  
SELECT version();   # 查看数据库详细版本

---

二、会话与进程管理

1. 1. 终止指定数据库所有会话

SELECT pg_terminate_backend(pid)  
FROM pg_stat_activity  
WHERE datname='xk_test' AND pid <> pg_backend_pid();

2. 2. 查看活跃会话及 SQL 信息

SELECT datname, usename, client_addr, query, state  
FROM sys_stat_activity  
WHERE datname IS NOT NULL;

3. 3. 终止指定进程SELECT sys_terminate_backend(pid); – 替换为实际 PID
4. 4. 查询当前会话进程 IDSELECT sys_backend_pid;

---

三、系统信息与状态

1. 1. 查看 License 有效期SELECT get_license_validdays();
2. 2. 数据库运行时间与启动时间

SELECT sys_postmaster_start_time();  
SELECT current_timestamp - pg_postmaster_start_time() AS uptime;

3. 3. 查看客户端连接信息SELECT inet_client_addr(), inet_client_port(); – 当前会话的客户端 IP 和端口
4. 4. 查看数据库物理存储路径SELECT sys_relation_filepath(‘表名’);

---

四、空间与容量管理

1. 1. 数据库占用空间

SELECT pg_size_pretty(pg_database_size(current_database()));  
SELECT sum(pg_database_size(datname))/1024/1024 || 'MB' FROM sys_database;

2. 2. 表与索引空间统计

-- 表大小（含索引）  
SELECT pg_size_pretty(pg_total_relation_size('表名'));  
-- 表数据大小  
SELECT pg_size_pretty(pg_relation_size('表名'));  
-- 索引大小  
SELECT pg_size_pretty(pg_total_relation_size('表名') - pg_relation_size('表名'));

---

五、性能监控与调优

1. 1. 慢 SQL 分析

-- 按平均执行时间排序  
SELECT userid::regrole, dbid, query  
FROM sys_stat_statements  
ORDER BY mean_exec_time DESC LIMIT 5;  
-- 按总执行时间排序  
SELECT userid::regrole, dbid, query  
FROM sys_stat_statements  
ORDER BY total_exec_time DESC LIMIT 5;

2. 2. 长事务监控

SELECT * FROM sys_stat_activity  
WHERE xact_start + interval '1 hour' < now();

3. 3. 锁阻塞查询

SELECT blocked_locks.pid AS blocked_pid,  
       blocked_activity.query AS blocked_query,  
       blocking_locks.pid AS blocking_pid,  
       blocking_activity.query AS blocking_query  
FROM pg_locks blocked_locks  
JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid  
JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype  
JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid  
WHERE NOT blocked_locks.granted;

---

六、高可用与备份恢复

1. 1. 主备状态检查SELECT pg_is_in_recovery(); – 返回 f 为主库，t 为备库
2. 2. 主库查看备库延迟

SELECT client_addr,  
       pg_wal_lsn_diff(sent_lsn, write_lsn) AS sent_lag,  
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS apply_lag  
FROM pg_stat_replication;

3. 3. 物理备份与恢复

# 全量备份  
kbbackup -U system -D /data/kingbase -b /backup/full  
# 恢复备份  
kbrestore -U system -D /data/kingbase_new -l /backup/full/backup_label

4. 4. 逻辑备份与导入

pg_dump -U system -d test -f test.sql    # 导出  
ksql -U system -d test -f test.sql       # 导入

---

七、表维护与优化

1. 1. 表膨胀处理

-- 查询表膨胀率  
SELECT c.oid::regclass AS table_name,  
       greatest(age(c.relfrozenxid), age(t.relfrozenxid)) AS age  
FROM pg_class c  
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid  
WHERE c.relkind IN ('r', 'm')  
ORDER BY age DESC;  
-- 手动清理膨胀  
VACUUM FULL 表名;

2. 2. 统计信息更新ANALYZE 表名; – 收集统计信息

---

八、日志与故障排查

1. 1. 日志文件查看tail -f $KINGBASE_DATA/sys_log/*.csv # 实时查看错误日志
2. 2. 数据库进程检查ps aux | grep kingbase
3. 3. 连接数统计

SELECT datname, usename, COUNT(*) AS connections  
FROM sys_stat_activity  
WHERE usename IS NOT NULL  
GROUP BY datname, usename;

---

注意事项

• • 高风险操作警告：<font style="color:rgb(221, 17, 68);">VACUUM FULL</font> 会锁表，需在业务低峰期操作；终止会话前确认影响。
• • 备份验证：定期检查备份文件完整性，确保恢复流程可靠。
• • 权限控制：避免直接使用 <font style="color:rgb(221, 17, 68);">system</font> 用户操作生产环境，按需分配权限。