[PG大总结]数据库管理常用命令🔎

---

非常不错的总结，强烈建议保存下来，需要的时候看一看。

以下为本人初学 PostgreSql 时做的笔记，囊括了数据库管理常用命令，有时还翻出来查查。之后也会保持更新

---

PostgreSQL (也称为psql) 是一个开源数据库，始于1996年。从那以后，它已经成为世界上最流行的开源数据库引擎之一，被数百万需要比标准MySQL数据库更多功能的开发人员使用。在这篇文章中，我将分享一系列常见的查询、命令和片段，这些查询、命令和片段在PostgreSQL数据库的维护和日常使用中非常有用，这些年来我收集的，现在和你分享。

显示正在运行的查询 (9.2之前)

SELECT procpid, age(clock_timestamp(), query_start), usename, current_query 
FROM pg_stat_activity 
WHERE current_query != '' AND current_query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;

显示正在运行的查询 (9.2之后)

SELECT pid, age(clock_timestamp(), query_start), usename, query, state
FROM pg_stat_activity
WHERE state != 'idle' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

找到活跃数据库

SELECT *
FROM pg_stat_activity
WHERE datname = '<database_name>';

终止活跃数据库

SELECT	pg_terminate_backend (pid)
FROM	pg_stat_activity
WHERE	pg_stat_activity.datname = '<database_name>';

正在终止运行的查询

SELECT pg_cancel_backend(procpid);

Kill空闲查询

SELECT pg_terminate_backend(procpid);

Vacuum命令

VACUUM (VERBOSE, ANALYZE);

查询所有数据库用户

select * from pg_stat_activity where current_query not like '<%';

查询所有数据库及其大小

select * from pg_user;

查询所有表及其大小，带/不带索引

select datname, pg_size_pretty(pg_database_size(datname))
from pg_database
order by pg_database_size(datname) desc;

查询缓存命中率 (不应低于0.99)

SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit)  as heap_hit, (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
FROM pg_statio_user_tables;

查询表索引使用率 (不应低于0.99)

SELECT relname, 100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, n_live_tup rows_in_table
FROM pg_stat_user_tables 
ORDER BY n_live_tup DESC;

查询缓存中有多少索引

SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit)  as idx_hit, (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
FROM pg_statio_user_indexes;

查询表索引使用率

SELECT relname, 
  CASE WHEN (seq_scan + idx_scan) != 0
    THEN 100.0 * idx_scan / (seq_scan + idx_scan) 
    ELSE 0
  END AS percent_of_times_index_used,
  n_live_tup AS rows_in_table
FROM pg_stat_user_tables 
ORDER BY n_live_tup DESC;

查询检查所有数据库的大小 (如在磁盘空间中)

SELECT d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
  CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
    THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname)) 
    ELSE 'No Access' 
  END AS SIZE 
FROM pg_catalog.pg_database d 
ORDER BY 
  CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') 
    THEN pg_catalog.pg_database_size(d.datname)
    ELSE NULL 
  END;

检查每个表的大小 (如在磁盘空间中)

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;

检查当前持有锁

SELECT t.relname, l.locktype, page, virtualtransaction, pid, mode, granted 
FROM pg_locks l, pg_stat_all_tables t 
WHERE l.relation = t.relid ORDER BY relation asc;

获取所有表大小

SELECT
  schema_name,
  relname,
  pg_size_pretty(table_size) AS size,
  table_size

FROM (
       SELECT
         pg_catalog.pg_namespace.nspname           AS schema_name,
         relname,
         pg_relation_size(pg_catalog.pg_class.oid) AS table_size

       FROM pg_catalog.pg_class
         JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
     ) t
WHERE schema_name NOT LIKE 'pg_%'
ORDER BY table_size DESC;

获取schema大小

FROM (
       SELECT
         pg_catalog.pg_namespace.nspname                AS schema_name,
         sum(pg_relation_size(pg_catalog.pg_class.oid)) AS schema_size
       FROM pg_catalog.pg_class
         JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
       group by 1
     ) t
WHERE schema_name NOT LIKE 'pg_%'
ORDER BY schema_size DESC;

显示未使用的索引

SELECT relname AS table_name, indexrelname AS index_name, idx_scan, idx_tup_read, idx_tup_fetch, pg_size_pretty(pg_relation_size(indexrelname::regclass))
FROM pg_stat_all_indexes
WHERE schemaname = 'public'
AND idx_scan = 0
AND idx_tup_read = 0
AND idx_tup_fetch = 0
ORDER BY pg_relation_size(indexrelname::regclass) DESC;

终止与当前数据库的所有正在运行的连接

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = current_database()  
  AND pid <> pg_backend_pid();

查找索引v1的基数

SELECT relname, relkind, reltuples as cardinality, relpages 
FROM pg_class 
WHERE relname LIKE 'tableprefix%';

查找索引v2的基数

SELECT schema_name,
       object_name,
       object_type,
       cardinality,
       pages
FROM (
       SELECT pg_catalog.pg_namespace.nspname AS schema_name,
              relname                         as object_name,
              relkind                         as object_type,
              reltuples                       as cardinality,
              relpages                        as pages
       FROM pg_catalog.pg_class
              JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
     ) t
WHERE schema_name NOT LIKE 'pg_%'
  and schema_name <> 'information_schema'
  --and schema_name = '$schema_name'
  --and object_name = '$object_name'
ORDER BY pages DESC, schema_name, object_name;

缓存中有多少索引

SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit) as idx_hit, (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio FROM pg_statio_user_indexes;

最后一次手动清理时间和分析时间

select relname,last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables;

每个表需要清空的死元组总数

select n_dead_tup, schemaname, relname from pg_stat_all_tables;

需要在DB中清空的死元组总数

select sum(n_dead_tup) from pg_stat_all_tables;