PostgreSQL排查及常见脚本

– 远程登陆PG数据库

-- 参数说明：-h 服务器 -p 端口 -U 用户 -d 数据库
psql -h 127.0.0.1 -U dbuser -p 5832 -d database

– 数据库当前连接

SELECT 
    pg_stat_get_backend_pid(s.backendid)        AS procpid,
    pg_stat_get_backend_activity(s.backendid)   AS current_query 
FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;

– 数据库当前执行SQL

SELECT procpid,
       start,
       now() - start AS lap,
       current_query
FROM (
    SELECT backendid,
           pg_stat_get_backend_pid(S.backendid) AS procpid,
           pg_stat_get_backend_activity_start(S.backendid) AS start,
           pg_stat_get_backend_activity(S.backendid) AS current_query
    FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS S
) AS S
WHERE current_query <> ''
-- 可根据procpid查询
--  AND procpid = '67080'
ORDER BY lap DESC;

– 数据库全部表和对应表信息

-- 全部表
select * from pg_tables;

-- 表字段/注释/类型
select a.attnum                                                                             AS serial_num,
       a.attname                                                                            AS field,
       concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\(.*\)')) AS type,
       d.description                                                                        AS description
from pg_class c,pg_attribute a,pg_type t,pg_description d
where a.attnum>0
    AND a.attrelid=c.oid
    AND a.atttypid=t.oid
    AND d.objoid=a.attrelid
    AND d.objsubid=a.attnum
    -- 对应表名
    AND c.relname='table_name';

-- 或者
SELECT n.nspname                   AS schemaname,
       c.relname                   AS tablename,
       d.description               AS table_comment,
       pg_get_userbyid(c.relowner) AS tableowner,
       t.spcname                   AS tablespace,
       c.relhasindex               AS hasindexes,
       c.relhasrules               AS hasrules,
       c.relhastriggers            AS hastriggers,
       c.relrowsecurity            AS rowsecurity
FROM pg_class c
         LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
         LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
         LEFT JOIN pg_description d ON d.objoid = c.oid
WHERE c.relname = 'table_name';

---

后续更新…