当前位置：首页 > news >正文

vastbase-基本操作

news 2026/3/29 17:27:21

数据库版本2.2.15

1、查询数据库创建日期

vastbase=# SELECT oid, datname FROM pg_database WHERE datname = 'vastbase';
oid | datname
-------+---------
18500| vastbase
(1 row)

vastbase=# SHOW data_directory;
data_directory
----------------
/data/vastdata
(1 row)

[vastbase@vdb1 ~]$ ll /data/vastdata/base/18500/PG_VERSION
-rw------- 1 vastbase vastbase 4 Jan 12 18:11 /data/vastdata/base/18500/PG_VERSION

2、实例启动时间-使用 pg_postmaster_start_time() 函数
vastbase=# SELECT pg_postmaster_start_time() AS start_time;
start_time
------------------------------
2026-01-23 15:06:33.06293+08
(1 row)

3、当前数据库的总大小

vastbase=# SELECT pg_size_pretty(pg_database_size(current_database())) AS db_size;
db_size
---------
51 GB
(1 row)

4、快速查询：当前连接数 vs 最大上限--查询的是整个环境的
SELECT
current_setting('max_connections')::int AS max_connections_limit, -- 最大连接数上限
count(*) AS current_active_connections, -- 当前活跃连接数
current_setting('max_connections')::int - count(*) AS available_slots -- 剩余可用连接数
FROM pg_stat_activity;

详细分析：按用户或应用统计连接数
SELECT usename,
count(*) AS connection_count
FROM pg_stat_activity
GROUP BY usename
ORDER BY connection_count DESC;

按客户端 IP 统计
SELECT client_addr,
count(*) AS connection_count
FROM pg_stat_activity
WHERE client_addr IS NOT NULL
GROUP BY client_addr
ORDER BY connection_count DESC;

调整最大连接数
vastbase=# ALTER SYSTEM SET max_connections = 500;

5、查询数据库用户
查询所有非系统内置用户（推荐）
SELECT usename AS username,
usesysid AS user_oid,
usecreatedb AS can_create_db,
usesuper AS is_superuser,
passwd IS NOT NULL AS has_password,
valuntil AS password_expiry
FROM pg_user
WHERE usename NOT IN ('postgres','vastbase','omm','root','dbadmin','pg_signal_backend','pg_monitor','pg_read_all_settings','pg_read_all_stats', 'pg_stat_scan_tables') ORDER BY usename;

仅查询普通业务用户（非超级用户）
SELECT
usename,
usecreatedb,
valuntil
FROM pg_user
WHERE usesuper = false
AND usename NOT LIKE 'pg_%' -- 排除以 pg_ 开头的系统角色
AND usename NOT IN ('postgres', 'vastbase', 'omm');

查询当前正在活跃连接的业务用户
SELECT usename,client_addr,application_name,state,backend_start,query
FROM pg_stat_activity
WHERE usename NOT IN ('postgres', 'vastbase', 'omm')
AND pid <> pg_backend_pid() -- 排除当前查询会话自己
ORDER BY backend_start DESC;

6、创建用户实例：vastbase 用户 test_cdsp 密码：9a%XCjsYDj7ZKCbL
create tablespace TEST_CDSP_D LOCATION '/vdata/vastbase/tablespace/TEST_CDSP_D' MAXSIZE '32G' ;
create user test_cdsp identified by "9a%XCjsYDj7ZKCbL" default tablespace TEST_CDSP_D ;
grant all on tablespace test_cdsp_D to test_cdsp ;
grant connect on database vastbase to test_cdsp ;
grant EXECUTE ANY PACKAGE,EXECUTE ANY FUNCTION to test_cdsp ;