postgresql 常用运维语句。

Postgresql 运维常用查询

ALL COLUMNS

显示所有的列的信息

SELECT c.table_schema,
       c.table_name       as table,
       c.ordinal_position as order,
       c.column_name      as column,
--	 c.data_type as type,
       CASE
           WHEN c.data_type IN ('character', 'varchar') THEN c.data_type || '(' || c.character_maximum_length || ')'
           WHEN c.data_type IN ('numeric')
               THEN c.data_type || '(' || c.numeric_precision || ',' || c.numeric_scale || ')'
           ELSE c.data_type
           END,
--	 c.character_maximum_length as max_len,
       c.is_nullable      as null,
       col_description(t.oid, c.ordinal_position)
FROM information_schema.columns c
         JOIN pg_class t ON (t.relname = c.table_name)
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2, 3;

ALL CONSTRAINTS

显示所有的键信息

SELECT table_schema,
       table_name,
       constraint_name as constraint,
       constraint_type as type
FROM information_schema.table_constraints
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2;

ALL TABLES

显示所有的表信息

SELECT table_schema as schema,
       table_name   as table
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
  AND table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2;

BAD INDEX

失效索引查找

SELECT n.nspname,
       i.relname,
       i.indexrelname,
       CASE
           WHEN idx.indisprimary
               THEN 'pkey'
           WHEN idx.indisunique
               THEN 'uidx'
           ELSE 'idx'
           END AS type,
       'INVALID'
FROM pg_stat_all_indexes i
         JOIN pg_class c ON (c.oid = i.relid)
         JOIN pg_namespace n ON (n.oid = c.relnamespace)
         JOIN pg_index idx ON (idx.indexrelid = i.indexrelid)
WHERE idx.indisvalid = FALSE
ORDER BY 1, 2;

TABLE PRIMARY KEY

表主键查找

SELECT n.nspname,
       t.relname as table,
       c.conname as pk_name
FROM pg_class t
         JOIN pg_constraint c ON (c.conrelid = t.OID AND c.contype = 'p')
         JOIN pg_namespace n ON (n.oid = t.relnamespace)
WHERE relkind = 'r'
  AND t.relname NOT LIKE 'pg_%'
  AND t.relname NOT LIKE 'sql_%'
ORDER BY n.nspname, t.relname, c.conname;

BLOCK TRANSACTION

块交换

SELECT w.query                          as waiting_query,
       w.pid                            as w_pid,
       w.usename                        as w_user,
       l.query                          as locking_query,
       l.pid                            as l_pid,
       l.usename                        as l_user,
       t.schemaname || '.' || t.relname as tablename
FROM pg_stat_activity w
         JOIN pg_locks l1 ON (w.pid = l1.pid and not l1.granted)
         JOIN pg_locks l2 on (l1.relation = l2.relation and l2.granted)
         JOIN pg_stat_activity l ON (l2.pid = l.pid)
         JOIN pg_stat_user_tables t ON (l1.relation = t.relid)

CACHE HIT RATIO

每个数据库的缓存命中率

SELECT pg_stat_database.datname,
       pg_stat_database.blks_read,
       pg_stat_database.blks_hit,
       round((pg_stat_database.blks_hit::double precision
                  / (pg_stat_database.blks_read
               + pg_stat_database.blks_hit
               + 1)::double precision * 100::double precision)::numeric, 2) AS cachehitratio
FROM pg_stat_database
WHERE pg_stat_database.datname !~ '^(template(0|1)|postgres)$'::text
ORDER BY round((pg_stat_database.blks_hit::double precision
                    / (pg_stat_database.blks_read
        + pg_stat_database.blks_hit
        + 1)::double precision * 100::double precision)::numeric, 2) DESC;

COLUMN DIFFERENCE

列名相同，类型不同

SELECT sa.nspname as schemaa,
       ca.relname as tablea,
       a.attname as columna, ta.typname,
       sb.nspname as schemab,
       cb.relname as tableb,
       a.attname as columnb, tb.typname
  FROM pg_attribute a
       JOIN pg_type ta ON (ta.oid = a.atttypid)
       JOIN pg_class ca ON (ca.oid = a.attrelid)
       JOIN pg_catalog.pg_namespace sa ON (ca.relnamespace = sa.oid),
       pg_attribute b
       JOIN pg_type tb ON (tb.oid = b.atttypid)
       JOIN pg_class cb ON (cb.oid = b.attrelid)
       JOIN pg_catalog.pg_namespace sb ON (cb.relnamespace = sb.oid)
 WHERE a.attname = b.attname
   AND ta.typname <> tb.typname
   AND ca.relname NOT LIKE 'pg_%'
   AND ca.relname NOT LIKE 'information%'
   AND ca.relname NOT LIKE 'sql_%'
   AND ca.relkind = 'r'
   AND cb.relname NOT LIKE 'pg_%'
   AND cb.relname NOT LIKE 'information%'
   AND cb.relname NOT LIKE 'sql_%'
   AND cb.relkind = 'r'
   AND 'rollback' NOT IN (sa.nspname, sb.nspname)
   AND 'id' NOT IN (a.attname, b.attname);

CURRENT LOCKS

当前锁信息，注意，不是死锁，事务的隔离级别和锁类型可以参考pg官方文档

SELECT pd.datname,
       l.relation,
       n.nspname,
       c.relname,
       l.pid,
       a.usename,
       locktype,
       mode,
       granted,
       tuple
FROM pg_locks l
         JOIN pg_class c ON (c.oid = l.relation)
         JOIN pg_namespace n ON (n.oid = c.relnamespace)
         JOIN pg_stat_activity a ON (a.pid = l.pid)
         left join pg_database pd on a.datname = pd.datname
ORDER BY database,
         relation,
         pid;

CURRENT QUERY

当前查询

SELECT a.datname,
       a.pid as pid,
       CASE WHEN a.client_addr IS NULL
            THEN 'local'
            ELSE a.client_addr::text
       END as client_addr,
       a.usename as user,
       a.wait_event,
       l.pid as blocking_pid,
       l.usename as blicking_user,
       a.query,
       a.query_start,
       current_timestamp - a.query_start as duration
  FROM pg_stat_activity a
LEFT  JOIN pg_locks l1 ON (a.pid = l1.pid )
LEFT  JOIN pg_locks l2 on (l1.relation = l2.relation )
LEFT  JOIN pg_stat_activity l ON (l2.pid = l.pid)
LEFT  JOIN pg_stat_user_tables t ON (l1.relation = t.relid)
 WHERE pg_backend_pid() <> a.pid
ORDER BY a.datname,
         a.query_start;

DB INFO

数据库信息

SELECT db.datname,
       au.rolname                       as datdba,
       pg_encoding_to_char(db.encoding) as encoding,
       db.datallowconn,
       db.datconnlimit,
       db.datfrozenxid,
       tb.spcname                       as tblspc,
--	 db.datconfig,
       db.datacl
FROM pg_database db
         JOIN pg_authid au ON au.oid = db.datdba
         JOIN pg_tablespace tb ON tb.oid = db.dattablespace
ORDER BY 1;

DB SIZE

数据库大小

SELECT datname,
       pg_size_pretty(pg_database_size(datname))                               as size_pretty,
       pg_database_size(datname)                                               as size,
       (SELECT pg_size_pretty(SUM(pg_database_size(datname))::bigint)
        FROM pg_database)                                                      AS total,
       ((pg_database_size(datname) / (SELECT SUM(pg_database_size(datname))
                                      FROM pg_database)) * 100)::numeric(6, 3) AS pct
FROM pg_database
ORDER BY datname;

DISTINCT DATA TYPE

数据库中的所有数据类型

SELECT DISTINCT t.typname
FROM pg_class c
         JOIN pg_attribute a ON (a.attrelid = c.oid)
         JOIN pg_type t ON (t.oid = a.atttypid)
         JOIN pg_catalog.pg_namespace s ON (c.relnamespace = s.oid)
WHERE c.relname NOT LIKE 'pg_%'
  AND c.relname NOT LIKE 'information%'
  AND c.relname NOT LIKE 'sql_%'
  AND c.relkind = 'r'
ORDER BY t.typname;

FILE NAME AND TABLE NAME

找到表名对应的物理存储文件名称

SELECT (SELECT oid
        FROM pg_database
        WHERE datname = current_database()) AS database,
       c.relfilenode                        as filename,
       n.nspname                            as schema,
       c.relname                            as table
FROM pg_class c
         JOIN pg_namespace n ON (n.oid = c.relnamespace)
WHERE relname NOT LIKE 'pg_%'
  AND relname NOT LIKE 'information%'
  AND relname NOT LIKE 'sql_%'
  AND relkind = 'r'
ORDER BY relfilenode;

GEN INDEX CREATE

显示当前库内所有索引创建语句

SELECT pg_get_indexdef(idx.indexrelid) || ';'
FROM pg_stat_all_indexes i
         JOIN pg_class c ON (c.oid = i.relid)
         JOIN pg_namespace n ON (n.oid = c.relnamespace)
         JOIN pg_index idx ON (idx.indexrelid = i.indexrelid)
WHERE NOT idx.indisprimary
  AND NOT idx.indisunique
  AND i.relname NOT LIKE 'pg_%'
  AND i.idx_scan = 0
ORDER BY n.nspname,
         i.relname;

INDEX BLOAT

索引信息

SELECT N.nspname,
       T.relname                                                                    AS "table",
       C.relname                                                                    AS idx_name,
       round(100 * pg_relation_size(indexrelid) / pg_relation_size(indrelid)) / 100 AS "index_ratio %",
       pg_size_pretty(pg_relation_size(indexrelid))                                 AS index_size,
       pg_size_pretty(pg_relation_size(indrelid))                                   AS table_size_size
FROM pg_index I
         LEFT JOIN pg_class C ON (c.oid = I.indexrelid)
         LEFT JOIN pg_class T ON (T.oid = I.indrelid)
         LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
  AND c.relkind = 'i'
  AND pg_relation_size(indrelid) > 0
ORDER BY 4 desc, 1;

SELECT n.nspname                                                                                 as schema,
       i.relname                                                                                 as table,
       i.indexrelname                                                                            as index,
       i.idx_scan,
       i.idx_tup_read,
       i.idx_tup_fetch,
       CASE
           WHEN idx.indisprimary
               THEN 'pkey'
           WHEN idx.indisunique
               THEN 'uidx'
           ELSE 'idx'
           END                                                                                   AS type,
/*
       pg_get_indexdef(idx.indexrelid, 1, FALSE) as idxcol1,
       pg_get_indexdef(idx.indexrelid, 2, FALSE) as idxcol2,
       pg_get_indexdef(idx.indexrelid, 3, FALSE) as idxcol3,
       pg_get_indexdef(idx.indexrelid, 4, FALSE) as idxcol4,
*/
       CASE
           WHEN idx.indisvalid
               THEN 'valid'
           ELSE 'INVALID'
           END                                                                                   as statusi,
       pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(i.relname))                 as size_in_bytes,
       pg_size_pretty(pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(i.relname))) as size
FROM pg_stat_all_indexes i
         JOIN pg_class c ON (c.oid = i.relid)
         JOIN pg_namespace n ON (n.oid = c.relnamespace)
         JOIN pg_index idx ON (idx.indexrelid = i.indexrelid)
WHERE i.relname LIKE '%%'
  AND n.nspname NOT LIKE 'pg_%'
--   AND idx.indisunique = TRUE
--   AND NOT idx.indisprimary
--   AND i.indexrelname LIKE 'tmp%'
--   AND idx.indisvalid IS false
/*   AND NOT idx.indisprimary
   AND NOT idx.indisunique
   AND idx_scan = 0
*/
ORDER BY 1, 2, 3;

INDEX SIZE

SELECT n.nspname as schema,
       c.relname as index,
       a.rolname as owner,
       c.relfilenode as filename,
       CASE WHEN indisprimary
	    THEN 'pkey'
	    WHEN indisunique
	    THEN 'uidx'
	    ELSE'idx'
	END as type,
       pg_size_pretty(pg_total_relation_size(n.nspname|| '.' || c.relname)) as total_size,
       pg_total_relation_size(n.nspname|| '.' || c.relname) as total_size_bytes
  FROM pg_index i
  JOIN pg_class c     ON (c.oid = i.indexrelid)
  JOIN pg_namespace n ON (n.oid = c.relnamespace)
  JOIN pg_authid a ON ( a.oid = c.relowner )
  WHERE relname NOT LIKE 'pg_%' AND
relname NOT LIKE 'information%' AND
relname NOT LIKE 'sql_%' AND
relkind IN ('i')
ORDER BY 7 DESC, 1, 2;

ALTER INDEX SPACE

将索引迁移至其他表空间的生成语句

SELECT 'ALTER INDEX ' || i.indexrelname
           || ' SET TABLESPACE <new_table_space>;'
FROM pg_stat_all_indexes i
         JOIN pg_class c ON (c.oid = i.relid)
         JOIN pg_namespace n ON (n.oid = c.relnamespace)
         JOIN pg_index idx ON (idx.indexrelid = i.indexrelid)
--  WHERE n.nspname = 'public'
ORDER BY 1;

ALTER TABLE SPACE

将表移至其他表空间的生成语句

SELECT 'ALTER TABLE ' || c.relname || ' SET TABLESPACE <new_table_space>;'
FROM pg_class c
         JOIN pg_namespace n ON (n.oid = c.relnamespace)
WHERE relname NOT LIKE 'pg_%'
  AND relname NOT LIKE 'information%'
  AND relname NOT LIKE 'sql_%'
  AND n.nspname = 'public'
  AND relkind = 'r'
ORDER BY relname;

MOST ACTIVE TABLES

最活跃的前10位表，主要是读这方面的

SELECT schemaname,
       relname,
       idx_tup_fetch + seq_tup_read as TotalReads
  FROM pg_stat_all_tables
WHERE idx_tup_fetch + seq_tup_read != 0
  AND schemaname NOT IN ( 'pg_catalog', 'pg_toast' )
order by TotalReads desc
LIMIT 10;

PG RUNTIME

postgresql 数据库运行时长（主进程）

SELECT pg_postmaster_start_time()                     as pg_start,
       current_timestamp - pg_postmaster_start_time() as runtime;

PG STAT ALL TABLE

表信息统计，包括最近一次vacuum

SELECT n.nspname,
       s.relname,
       c.reltuples::bigint,
--	 n_live_tup,
       n_tup_ins,
       n_tup_upd,
       n_tup_del,
       date_trunc('second', last_vacuum) as last_vacuum,
       date_trunc('second', last_autovacuum) as last_autovacuum,
       date_trunc('second', last_analyze) as last_analyze,
       date_trunc('second', last_autoanalyze) as last_autoanalyze
       ,
       round( current_setting('autovacuum_vacuum_threshold')::integer + current_setting('autovacuum_vacuum_scale_factor')::numeric * C.reltuples) AS av_threshold
/*	 ,CASE WHEN reltuples > 0
	      THEN round(100.0 * n_dead_tup / (reltuples))
	    ELSE 0
       END AS pct_dead,
       CASE WHEN n_dead_tup > round( current_setting('autovacuum_vacuum_threshold')::integer + current_setting('autovacuum_vacuum_scale_factor')::numeric * C.reltuples)
	      THEN 'VACUUM'
	    ELSE 'ok'
	END AS "av_needed"
*/
  FROM pg_stat_all_tables s
  JOIN pg_class c ON c.oid = s.relid
  JOIN pg_namespace n ON (n.oid = c.relnamespace)
 WHERE s.relname NOT LIKE 'pg_%'
   AND s.relname NOT LIKE 'sql_%'
--   AND s.relname LIKE '%TBL%'
 ORDER by 1, 2;

DEAD TUPLE

按关系对象统计死元组，需要vacuum的对象

SELECT *,
       n_dead_tup > av_threshold AS "av_needed",
       CASE
           WHEN reltuples > 0
               THEN round(100.0 * n_dead_tup / (reltuples))
           ELSE 0
           END                   AS pct_dead
FROM (SELECT N.nspname || '.' || C.relname                                                                      AS "relation",
             pg_stat_get_tuples_inserted(C.oid)                                                                 AS n_tup_ins,
             pg_stat_get_tuples_updated(C.oid)                                                                  AS n_tup_upd,
             pg_stat_get_tuples_deleted(C.oid)                                                                  AS n_tup_del,
             CASE
                 WHEN pg_stat_get_tuples_updated(C.oid) > 0
                     THEN pg_stat_get_tuples_hot_updated(C.oid)::real / pg_stat_get_tuples_updated(C.oid)
                 ELSE 0
                 END                                                                                            AS HOT_update_ratio,
             pg_stat_get_live_tuples(C.oid)                                                                     AS n_live_tup,
             pg_stat_get_dead_tuples(C.oid)                                                                     AS n_dead_tup,
             C.reltuples                                                                                        AS reltuples,
             round(current_setting('autovacuum_vacuum_threshold')::integer +
                   current_setting('autovacuum_vacuum_scale_factor')::numeric *
                   C.reltuples)                                                                                 AS av_threshold,
             date_trunc('minute', greatest(pg_stat_get_last_vacuum_time(C.oid),
                                           pg_stat_get_last_autovacuum_time(C.oid)))                            AS last_vacuum,
             date_trunc('minute', greatest(pg_stat_get_last_analyze_time(C.oid),
                                           pg_stat_get_last_analyze_time(C.oid)))                               AS last_analyze
      FROM pg_class C
               LEFT JOIN pg_index I ON C.oid = I.indrelid
               LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
      WHERE C.relkind IN ('r', 't')
        AND N.nspname NOT IN ('pg_catalog', 'information_schema')
        AND N.nspname !~ '^pg_toast'
     ) AS av
ORDER BY av_needed DESC,
         n_dead_tup DESC;

TABLE INDEX

表上所有索引

SELECT cc.relname as table,
       ci.relname as index
FROM pg_index i
         JOIN pg_class cc ON (cc.oid = i.indrelid)
         JOIN pg_class ci ON (ci.oid = i.indexrelid)
-- WHERE ci.relname = 'idx_normal_distro_name'
ORDER BY 1;

TABLE NO PRIMARY KEY

没有主键的表

SELECT n.nspname as schema,
       c.relname as table
FROM pg_class c
         JOIN pg_namespace n ON (n.oid = c.relnamespace)
WHERE relkind = 'r'
  AND relname NOT LIKE 'pg_%'
  AND relname NOT LIKE 'sql_%'
  AND relhaspkey = FALSE
ORDER BY n.nspname, c.relname;

USELESS INDEX

使用次数较少的索引

SELECT idstat.schemaname                                                                                      AS schema,
       idstat.relname                                                                                         AS table_name,
       indexrelname                                                                                           AS index_name,
       idstat.idx_scan                                                                                        AS times_used,
       pg_size_pretty(pg_relation_size(
               quote_ident(idstat.schemaname) || '.' || quote_ident(idstat.relname)))                         AS table_size,
       pg_size_pretty(pg_relation_size(
               quote_ident(idstat.schemaname) || '.' || quote_ident(indexrelname)))                           AS index_size,
       n_tup_upd + n_tup_ins + n_tup_del                                                                      as num_writes,
       indexdef                                                                                               AS definition
FROM pg_stat_user_indexes AS idstat
         JOIN pg_indexes ON indexrelname = indexname
         JOIN pg_stat_user_tables AS tabstat ON idstat.relname = tabstat.relname
WHERE idstat.idx_scan < 200
  AND indexdef !~* 'unique'
ORDER BY idstat.schemaname,
         idstat.relname,
         indexrelname;

SELECT n.nspname as schema,
       i.relname as table,
       i.indexrelname as index,
       i.idx_scan,
       i.idx_tup_read,
       i.idx_tup_fetch,
       pg_size_pretty(pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(i.relname))) AS table_size,
       pg_size_pretty(pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(i.indexrelname))) AS index_size,
       pg_get_indexdef(idx.indexrelid) as idx_definition
  FROM pg_stat_all_indexes i
  JOIN pg_class c ON (c.oid = i.relid)
  JOIN pg_namespace n ON (n.oid = c.relnamespace)
  JOIN pg_index idx ON (idx.indexrelid =  i.indexrelid )
 WHERE i.idx_scan < 200
   AND NOT idx.indisprimary
   AND NOT idx.indisunique
 ORDER BY 1, 2, 3;

USES

用户及权限

SELECT rolname as user,
       CASE
           WHEN rolcanlogin THEN 'user'
           ELSE 'group'
           END,
       CASE
           WHEN rolsuper THEN 'SUPERUSER'
           ELSE 'normal'
           END AS super
FROM pg_authid
ORDER BY rolcanlogin,
         rolname;

WASTE TABLESPACE

WITH s AS (
    SELECT SUM(pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname))::bigint)                  AS table_size,
           pg_size_pretty(SUM(pg_relation_size(
                   quote_ident(n.nspname) || '.' || quote_ident(c.relname))::bigint)::bigint)                      AS table_size_pretty
    FROM pg_class c
             JOIN pg_namespace n ON (n.oid = c.relnamespace)
    WHERE c.relkind = 'r'
      AND c.relname NOT LIKE 'pg_%'
      AND c.relname NOT LIKE 'sql%'
)
SELECT s.table_size,
       s.table_size_pretty,
       SUM(pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(i.indexrelname))::bigint)                  AS unused_idx_size,
       pg_size_pretty(SUM(pg_relation_size(
               quote_ident(n.nspname) || '.' || quote_ident(i.indexrelname))::bigint)::bigint)                      AS unused_idx_size_pretty,
       pg_database_size(current_database())                                                                         as db_size,
       pg_size_pretty(pg_database_size(current_database()))                                                         as db_size_pretty,
       pg_size_pretty(pg_database_size(current_database()) - SUM(pg_relation_size(
               quote_ident(n.nspname) || '.' || quote_ident(i.indexrelname))::bigint)::bigint)                      as db_minus_wasted_space

FROM s,
     pg_stat_all_indexes i
         JOIN pg_class c ON (c.oid = i.relid)
         JOIN pg_namespace n ON (n.oid = c.relnamespace)
         JOIN pg_index idx ON (idx.indexrelid = i.indexrelid)
WHERE i.idx_scan = 0
  AND NOT idx.indisprimary
  AND NOT idx.indisunique
GROUP BY table_size, table_size_pretty;