15.查看活动连接
sELECT * FROM pg_stat_activity;
14.查看事件触发器的具体内容
SELECT pg_get_functiondef(evtfoid);
13.查看事件触发器
SELECT * FROM pg_event_trigger;
\dy
12.查看普通触发器
select * from information_schema.triggers;
select * from pg_trigger;
11.创建用户
CREATE USER zss WITH PASSWORD 'pass123';
11.登录数据库
psql -Uzss -W -h 10.163.1.5 -p18801 postgres
10.查看当前的schema
SELECT current_schema();
查看当前的用户
SELECT current_user;
9.创建数据库
create database zss;
创建表
create table zss(age int,name varchar(30));
insert into zss(age,name) values(1,'lisi');
select * from zss;
给表增加一个序列
ALTER TABLE zss ADD COLUMN id SERIAL PRIMARY KEY;
查看指定表的序列，索引
\d zss ;
查看表大小，不能查看表的序列，索引
\dt zss ; \dt+ zss ;
8.用户连接到数据库的权限
GRANT CONNECT ON DATABASE jfcshjk TO zss;
授予某个用户对schema的使用权限
GRANT USAGE ON SCHEMA bill_query_cost TO zss;
授予某个模式下的所有表的权限
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO zss;
授予某个模式下的所有序列的权限（如果表有序列，只有表的所有权限，而没有序列的增删改权限，就无法对表正常增删改操作）
GRANT ALL PRIVILEGES ON ALL sequences IN SCHEMA zssschema TO zss;
授予某个用户对某个schema下新创建表的权限
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT SELECT ON TABLES TO zss;
授予某个用户对某个schema下新创建序列的权限
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT ALL PRIVILEGES ON SEQUENCES TO zss;
7.查看所有用户
\du \du+ SELECT * FROM pg_roles;
1.查看所有用户对指定表的权限
SELECT grantee, table_schema, table_name,privilege_type FROM information_schema.role_table_grants WHERE table_name = 'idcsync_ddl_commands';
2.查看指定用户对指定表的权限
SELECT grantee, table_schema, table_name,privilege_type FROM information_schema.role_table_grants WHERE table_name = 'xxx' AND grantee = 'xxx' ;
3.查看指定函数
SELECT * FROM pg_catalog.pg_proc WHERE proname = 'xxx';
4.修改指定表的owner
alter table idcsync_ddl_indexs Owner to acct_audit;
5.查看触发器
SELECT evtname, evtevent, evtfoid::regproc, evtenabled FROM pg_event_trigger WHERE evtfoid::regproc::text = 'idcsync_capture_ddl';
禁用触发器
ALTER EVENT TRIGGER idcsync_capture_ddl_trigger DISABLE;
6.查看指定表的创建时间
SELECT
relname as table_name,
to_char(
(pg_stat_file('base/' ||
(SELECT oid FROM pg_database WHERE datname = current_database()) ||
'/' || relfilenode
)).modification,
'YYYY-MM-DD HH24:MI:SS'
) as created_time
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind = 'r'
AND n.nspname = 'public' and relname='idcsync_ddl_commands'
ORDER BY created_time DESC;
6.查大库
SELECT
datname as database_name,
pg_size_pretty(pg_database_size(datname)) as size,
pg_database_size(datname) as size_bytes
FROM pg_database
WHERE datistemplate = false
ORDER BY pg_database_size(datname) DESC;
查大表
ctg_dmp_web=# SELECT
schemaname,
relname as tablename, -- 使用 relname 而不是 tablename
pg_size_pretty(pg_total_relation_size(schemaname || '.' || relname)) as total_size,
pg_size_pretty(pg_table_size(schemaname || '.' || relname)) as table_size,
pg_size_pretty(pg_indexes_size(schemaname || '.' || relname)) as index_size,
n_live_tup as row_count,
n_dead_tup as dead_rows,
-- 额外信息
seq_scan as sequential_scans,
idx_scan as index_scans,
-- 计算索引扫描率
CASE
WHEN (seq_scan + idx_scan) > 0
THEN round((idx_scan::numeric / (seq_scan + idx_scan) * 100), 2)
ELSE 0
END as index_scan_rate_percent
FROM pg_stat_user_tables
WHERE pg_total_relation_size(schemaname || '.' || relname) > 100 * 1024 * 1024 -- 大于100MB
ORDER BY pg_total_relation_size(schemaname || '.' || relname) DESC;
7.查看无主键的表
SELECT
schemaname AS 模式名,
tablename AS 表名
FROM
pg_tables -- 系统视图，包含所有表的基本信息
WHERE
schemaname NOT IN ('pg_catalog', 'information_schema') -- 排除系统自带的模式
AND (schemaname, tablename) NOT IN (
-- 子查询：获取所有有主键的表（模式名+表名）
SELECT
n.nspname AS schemaname, -- 表所在的模式名
c.relname AS tablename -- 表名
FROM
pg_constraint con
JOIN pg_class c ON con.conrelid = c.oid -- 关联表对象
JOIN pg_namespace n ON c.relnamespace = n.oid -- 关联模式
WHERE
con.contype = 'p' -- 约束类型为 'p'（primary key，主键）
)
ORDER BY
schemaname, tablename;
查看指定schema无主键的表：
SELECT schemaname AS 模式名, tablename AS 表名FROM pg_tables WHERE schemaname = '指定schema'
AND (schemaname, tablename) NOT IN ( SELECT
n.nspname AS schemaname,
c.relname AS tablename
FROM
pg_constraint con
JOIN pg_class c ON con.conrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE
con.contype = 'p' -- 主键约束
AND n.nspname = '指定schema' -- 限制在目标 schema 内
)
ORDER BY
tablename;
