postgres dba日常管理sql

以下是 Postgres DBA 日常管理 SQL：

1. 检查数据库大小和使用情况：

SELECT pg_size_pretty(pg_database_size('database_name'));
SELECT pg_size_pretty(pg_total_relation_size('table_name'));

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

SELECT * FROM pg_stat_activity;

3. 检查慢查询：

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

4. 检查表的索引：

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;

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

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;

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

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';

7. 检查表的统计信息：

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

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

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;

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