postgres dba日常管理sql大全，包含权限相关

以下是 Postgres DBA 日常管理 SQL 大全，包含权限相关：

1. 检查当前活动的连接：

SELECT * FROM pg_stat_activity;

2. 检查慢查询：

SELECT query, total_time, calls, rows FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

3. 检查表的索引：

SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_all_indexes WHERE schemaname = 'schema_name' ORDER BY idx_scan DESC LIMIT 10;

4. 检查表的空间使用情况：

SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;

5. 检查表的主键和外键：

SELECT tc.table_schema, tc.constraint_name, tc.table_name, kcu.column_name, ccu.table_schema AS foreign_table_schema, ccu.table_name AS foreign_table_name, ccu.column_name AS foreign_column_name FROM information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name AND ccu.table_schema = tc.table_schema WHERE constraint_type = 'FOREIGN KEY' AND tc.table_schema = 'schema_name';

6. 检查表的统计信息：

SELECT * FROM pg_stats WHERE tablename = 'table_name' ORDER BY attname;

7. 检查表的空间占用情况：

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size(C.oid) DESC
  LIMIT 10;

8. 查看用户和用户组：

SELECT * FROM pg_user;
SELECT * FROM pg_group;

9. 创建用户和用户组：

CREATE USER username WITH PASSWORD 'password';
CREATE GROUP groupname;

10. 授权和取消授权：

GRANT SELECT, INSERT, UPDATE, DELETE ON table_name TO username;
REVOKE SELECT, INSERT, UPDATE, DELETE ON table_name FROM username;
GRANT groupname TO username;
REVOKE groupname FROM username;

11. 创建数据库和模式：

CREATE DATABASE database_name;
CREATE SCHEMA schema_name;

12. 检查数据库和模式：

SELECT * FROM pg_database;
SELECT * FROM information_schema.schemata;

13. 修改数据库和模式：

ALTER DATABASE database_name RENAME TO new_database_name;
ALTER SCHEMA schema_name RENAME TO new_schema_name;

14. 删除数据库和模式：

DROP DATABASE database_name;
DROP SCHEMA schema_name;

这些 SQL 可以帮助 Postgres DBA 进行日常管理和维护工作，包含权限管理。