想学会更多实用技巧，欢迎加入青学会MOP技术社区（实名社区）。
加入方法：公众号后台回复关键字“加入”获取小助手微信，添加后登记入会。
同时欢迎大家在评论区留言互动交流！社区会不定期举行相关的抽奖、公开分享活动。
如果你有想了解的知识点希望我们发文可以后台私信。
本期投稿人
刘迪，就职于东软熙康，熟悉Oracle，MySQL，Postgres等数据库，对国产数据库有着浓厚的兴趣，个人公众号 运维DBA小记，不定时分享一些数据库的技术文章；目前拥有Kingbase(KCA,KCP)、OceanBase(OBCA)、Oracle(OCP,OCM
正文开始
在现代企业中，数据库作为核心数据存储和管理系统，扮演着至关重要的角色。随着数据量的不断增长和业务需求的日益复杂，数据库的运维工作变得愈发重要。有效的数据库运维不仅能够确保数据的安全性和完整性，还能提升系统的性能和可用性。
本文将介绍一些常用的数据库运维命令，这些命令涵盖了数据库统计，查杀等工作日常的命令，方便大家粘贴使用。
1.连接指定服务器上的数据库
ksql -h -p -U -d -W
2.杀掉连接某个数据库的会话
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname='xk_test' AND pid<>pg_backend_pid();
<g_stat_activity WHERE datname='xk_nanning' AND pid<>pg_backend_pid();
pg_terminate_backend
----------------------
t
(1 row)
test=#
3.查询lincense的有效期
test=# select get_license_validdays();
get_license_validdays
-----------------------
90
(1 row)
test=#
4.查看服务器端版本信息
[kingbase@9e734187565d ~]$ sys_ctl -V
sys_ctl (Kingbase) 12.1
[kingbase@9e734187565d ~]$
[kingbase@9e734187565d ~]$ ksql -Usystem -d test -pxxxxx
test=# SELECT version();
version
------------------------------------------------------------------------------------------
----------------------------
KingbaseES V009R001C001B0025 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623
(Red Hat 4.8.5-28), 64-bit
(1 row)
test=#
5.查看数据库信息
test=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------------+--------+----------+-------------+-------------+-------------------
kingbase | system | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
security | system | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
template0 | system | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/system +
| | | | | system=CTc/system
template1 | system | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/system +
| | | | | system=CTc/system
test | system | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
xk_nanning | system | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/system +
| | | | | system=CTc/system+
| | | | | xk_dba=CTc/system
(6 rows)
test=#
test=# select datname from sys_database;
datname
------------
test
kingbase
template1
template0
security
xk_nanning
(6 rows)
test=#
6.查询版本的提交号
xk_test=# Select build_version();
build_version
---------------
fc20f39d407
(1 row)
xk_test=#
7.查看KES实例的启动时间
xk_test=# select sys_postmaster_start_time();
sys_postmaster_start_time
-------------------------------
2024-10-21 06:11:40.362271+00
(1 row)
xk_test=#
8.当前无故障运行时间
SELECT date_trunc('second', current_timestamp - pg_postmaster_start_time()) as uptime;
<nt_timestamp - pg_postmaster_start_time()) as uptime;
uptime
-------------------------------
+000000000 00:15:24.000000000
(1 row)
xk_test=#
9.查看数据库占用的硬盘空间和表占用的硬盘空间
xk_test=# SELECT pg_size_pretty(pg_database_size(current_database()));
pg_size_pretty
----------------
13 MB
(1 row)
xk_test=#
10.查看表所占用的空间
xk_test=# SELECT pg_size_pretty(pg_relation_size('ld'));
pg_size_pretty
----------------
8192 bytes
(1 row)
xk_test=# SELECT pg_size_pretty(pg_total_relation_size('ld'));
pg_size_pretty
----------------
8192 bytes
(1 row)
xk_test=#
11.查看当前数据所占内存大小
xk_test=# select sys_database_size(current_database())/1024/1024 || 'MB' MB;
MB
----------------------
13.469443321228027MB
(1 row)
12.查看所有数据所占内存的大小
select (sum(sys_database_size(datname))/1024/1024) || 'MB' MB from sys_database;
<ze(datname))/1024/1024) || 'MB' MB from sys_database;
MB
-----------------------
79.8705387115478516MB
(1 row)
xk_test=#
13.查看表的大小
xk_test=# select sys_relation_size('public.ld')/1024 || 'KB' KB;
KB
-----
8KB
(1 row)
xk_test=#
14.查看表和索引的大小
xk_test=# select sys_size_pretty(sys_total_relation_size('public.ld'));
sys_size_pretty
-----------------
8192 bytes
(1 row)
xk_test=#
15.索引的大小
select sys_size_pretty(sys_total_relation_size('public.ld')-sys_relation_size('public.ld'));
<on_size('public.ld')-sys_relation_size('public.ld'));
sys_size_pretty
-----------------
0 bytes
(1 row)
16.查看当前会话的客户端ip和端口（如果服务器是本地登录则会显示为空）
xk_test=# select inet_client_port();
inet_client_port
------------------
(1 row)
xk_test=# select inet_client_addr();
inet_client_addr
------------------
(1 row)
17.查看当前会话的进程ID
test=# select sys_backend_pid;
sys_backend_pid
-----------------
267
(1 row)
test=#
18.查询物理存储
xk_test=# select sys_relation_filepath('ld');
sys_relation_filepath
-----------------------
base/16384/16385
(1 row)
xk_test=#
19.查看数据中链接信息
xk_test=# select datname ,usename,client_addr,client_port from sys_stat_activity;
datname | usename | client_addr | client_port
---------+---------+-------------+-------------
| | |
| system | |
| system | |
xk_test | system | | -1
| | |
| | |
| | |
(7 rows)
xk_test=#
20.查询数据库集簇系统参数
xk_test=# SELECT name,setting from sys_settings;
name |
setting
--------------------------------------------+---------------------------------------
------------------------------------------------------------------------------------
------------------------------------------------------------------------------------
----------------------------------------------------------
allow_system_table_mods | off
amm | 0
application_name | ksql
archive_cleanup_command |
archive_command | /bin/true
archive_dest |
archive_mode | on
archive_timeout | 0
array_nulls | on
authentication_timeout | 60
auto_bmr.auto_bmr_conninfo |
auto_bmr.auto_bmr_max_sess | 5
auto_bmr.auto_bmr_req_timeout | 60
auto_bmr.auto_bmr_sess_threshold | 100
auto_bmr.auto_bmr_sys_threshold | 1024
--More--
21.查看所有会话的sql信息
xk_test=# select datname,usename,client_addr,client_port from sys_stat_activity;
datname | usename | client_addr | client_port
---------+---------+-------------+-------------
| | |
| system | |
| system | |
xk_test | system | | -1
| | |
| | |
| | |
(7 rows)
xk_test=#
22.查看当前存在会话的sql信息
select datname,usename,client_addr,client_port from sys_stat_activity where datname is not null;
<ort from sys_stat_activity where datname is not null;
datname | usename | client_addr | client_port
---------+---------+-------------+-------------
xk_test | system | | -1
(1 row)
xk_test=#
23.查看执行的sql语句
select datname,usename,query,state from sys_stat_activity where datname is not null;
<ate from sys_stat_activity where datname is not null;
datname | usename | query
| state
---------+---------+----------------------------------------------------------------
----------------------+--------
xk_test | system | select datname,usename,query,state from sys_stat_activity where
datname is not null; | active
(1 row)
xk_test=#
24.查看当前正在执行状态的语句
select datname,usename,query,state from sys_stat_activity where state like 'active';
<ate from sys_stat_activity where state like 'active';
datname | usename | query
| state
---------+---------+----------------------------------------------------------------
----------------------+--------
xk_test | system | select datname,usename,query,state from sys_stat_activity where
state like 'active'; | active
(1 row)
xk_test=#
25.查询语句运行时间
select current_timestamp - query_start as runtime,datname,usename,pid,query from sys_stat_activity where state != 'idle';
<,query from sys_stat_activity where state != 'idle';
runtime | datname | usename | pid |
query
-------------------------------+---------+---------+-----+--------------------------
------------------------------------------------------------------------------------
--------------
+000000000 00:00:00.000000000 | xk_test | system | 271 | select current_timestamp
- query_start as runtime,datname,usename,pid,query from sys_stat_activity where sta
te != 'idle';
(1 row)
xk_test=#
26.查看会话的事务状态
select datname,usename,query,wait_event,pid from sys_stat_activity where datname='xk_test';
<vent,pid from sys_stat_activity where datname='xk_test';
datname | usename | query
| wait_event | pid
---------+---------+----------------------------------------------------------------
-----------------------------+------------+-----
xk_test | system | select datname,usename,query,wait_event,pid from sys_stat_activ
ity where datname='xk_test'; | | 271
(1 row)
27.关闭会话的事务清理阻塞 # pid为事务ID
select sys_terminate_backend(pid);
往期文章回顾
MOP社区新闻
金仓专栏
告别繁琐！KingbaseES v9数据库一键安装-青学会&金仓专栏（1）
KingbaseES v9数据库Docker安装-青学会&金仓专栏（2）
DBA实战小技巧
实战：记一次RAC故障排查
DBA实战运维小技巧安装篇（一）Oracle 主流版本不同架构下的静默安装指南
DBA实战运维小技巧存储篇（一）根目录满了如何处理
DBA实战运维小技巧存储篇（二）打包迁移单机数据库至新存储
MOP社区投稿-内核开发
简单解析 IvorySQL 增强 Oracle xml 兼容能力的原理
简单讨论 PostgreSQL C语言拓展函数返回数据表的方式
简单分析 pg_config 程序的作用与原理
Redis 日志机制简介（一）：SlowLog
Redis 日志机制简介（二）：AOF 日志
Redis 日志机制简介（三）：RDB 日志
pg_cron插件使用介绍
Redis 的指令表实现机制简介
pg几款源码工具介绍
Redis 事务功能简介
MOP顾问说
文章转载自青年数据库学习互助会，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
