暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / PG 常用维护性 SQL 记录

PG 常用维护性 SQL 记录

原创 岳麓丹枫 2023-09-22
246

Table of Contents

查看哪些角色对表有增删改查权限

SELECT grantor, grantee, table_schema, table_name,  string_agg(privilege_type,',') as privilege_type
FROM information_schema.role_table_grants
group by grantor, grantee, table_schema, table_name;

查看哪些角色对函数有执行权限

SELECT
    routine_catalog AS fct_db,
    routine_schema  AS fct_sch,
    routine_name    AS fct_nam,
    privilege_type  AS fct_priv,
    array_agg (grantee::text ORDER BY grantee::text) AS fct_rol
FROM
    information_schema.routine_privileges
WHERE
    routine_schema NOT IN ('information_schema','pg_catalog')
GROUP BY
    routine_catalog, routine_schema, routine_name, privilege_type
ORDER BY
    routine_catalog, routine_schema, routine_name, privilege_type
;

根据序列名获取表及列信息

select ts.nspname as object_schema,
        tbl.relname as table_name, 
       col.attname as column_name,
       s.relname   as sequence_name
from pg_class s
  join pg_namespace sn on sn.oid = s.relnamespace 
  join pg_depend d on d.refobjid = s.oid and d.refclassid='pg_class'::regclass 
  join pg_attrdef ad on ad.oid = d.objid and d.classid = 'pg_attrdef'::regclass
  join pg_attribute col on col.attrelid = ad.adrelid and col.attnum = ad.adnum
  join pg_class tbl on tbl.oid = ad.adrelid 
  join pg_namespace ts on ts.oid = tbl.relnamespace 
where s.relkind = 'S'
--  and s.relname = 'sequence_name'
  and d.deptype in ('a', 'n');

参考:https://www.modb.pro/db/181436

查看postgresql数据库用户系统权限、对象权限

参考连接

查看所有主键及其相关字段信息

  • 方法1
select kcu.table_schema,
       kcu.table_name,
       tco.constraint_name,
       string_agg(kcu.column_name,', ') as key_columns
from information_schema.table_constraints tco
join information_schema.key_column_usage kcu 
     on kcu.constraint_name = tco.constraint_name
     and kcu.constraint_schema = tco.constraint_schema
     and kcu.constraint_name = tco.constraint_name
where tco.constraint_type = 'PRIMARY KEY'
group by tco.constraint_name,
       kcu.table_schema,
       kcu.table_name
order by kcu.table_schema,
         kcu.table_name;

参考: https://dataedo.com/kb/query/postgresql/list-all-primary-keys-in-database

  • 方法2
SELECT conrelid::regclass AS table_name,
       conname AS primary_key, 
       pg_get_constraintdef(oid) 
FROM   pg_constraint 
WHERE  contype = 'p' 
AND    connamespace = 'public'::regnamespace   
ORDER  BY conrelid::regclass::text, contype DESC;

参考:https://soft-builder.com/how-to-list-all-primary-keys-in-postgresql-database/#:~:text=The%20following%20script%20can%20get%20all%20primary%20keys%3A,connamespace%20%3D%20%27public%27%3A%3Aregnamespace%20ORDER%20BY%20conrelid%3A%3Aregclass%3A%3Atext%2C%20contype%20DESC%3B

查看 排除主键索引之外的 其他所有唯一性约束与唯一索引

-- 获取 排除主键索引之外的其他的所有唯一性索引
select * from pg_indexes where schemaname='public' and indexname not in 
(
	with tmp as
	(
	select kcu.table_schema, kcu.table_name, tco.constraint_name, string_agg(kcu.column_name,', ') as key_columns from information_schema.table_constraints tco join information_schema.key_column_usage kcu on kcu.constraint_name = tco.constraint_name and kcu.constraint_schema = tco.constraint_schema and kcu.constraint_name = tco.constraint_name where tco.constraint_type = 'PRIMARY KEY' group by tco.constraint_name, kcu.table_schema, kcu.table_name order by kcu.table_schema, kcu.table_name
	)
	select constraint_name from tmp where table_schema='public' group by constraint_name
) and indexdef ilike '%UNIQUE%';

给 data 用户授予 create publication 权限

grant create on DATABASE ttp to ttpdata;

统计当前库中每张表数据条数

\o table_count.sql
select $$select '$$ || tablename ||  $$', count(*) from $$ || tablename from pg_tables where schemaname='public' order by tablename \gexec
\o

查询所有外键对应的表与列

SELECT conname "外键约束名", conrelid::regclass AS "表名", a1.attname AS "列名"  FROM pg_constraint c JOIN pg_stat_user_tables t ON t.relid = c.conrelid JOIN pg_attribute a1 ON a1.attnum = ANY(c.conkey) AND a1.attrelid = c.conrelid WHERE confrelid <> 0;
  • 授权序列访问权限
--授予当前 public 中所有序列访问权限
grant usage ,select , update on all sequences in schema public to test_user;

--授予未来 public 中所有序列访问权限
alter default privileges for user test_user in schema public grant select ,update,usage on SEQUENCES to test_user;
  • 授予 public 模式中所有表的 read 权限
--1. 授权已有表的只读权限给 用户
grant usage on schema public to test_user;
grant select on all tables in schema public to test_user;
 
--2. 授予未来新建的表的只读权限 给用户
alter default privileges 
[ for role xxdata ]-- 注意, 这里在多用户情况下, 是必须的, 否则会被当做这些 public 模式下的表是 postgres 创建的, 单用户模式下是可选的
in schema public grant select on tables to test_user;
 
--3. 回收 public 模式中所有表的 read 权限
revoke usage on schema public from test_user;
revoke select on all tables in schema public from test_user;
alter default privileges 
[ for role xxdata ] -- 注意, 这里在多用户情况下, 是必须的, 否则会被当做这些 public 模式下的表是 postgres 创建的, 单用户模式下是可选的
in schema public revoke select on tables from test_user;

查看表所属 schmea 及其oid

-- 假设查询的是 test 表
SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname OPERATOR(pg_catalog.~) '^(test)$'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;

查询表是否有索引, 触发器等信息


SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, false AS relhasoids, c.relispartition, pg_catalog.array_to_string(c.reloptions || array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')
, c.reltablespace, CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence, c.relreplident, am.amname
FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
LEFT JOIN pg_catalog.pg_am am ON (c.relam = am.oid)
WHERE c.oid = (select oid from pg_class where relname OPERATOR(pg_catalog.~) '^(test)$');

通过 SQL 查询表结构及其字段注释信息

SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull,
  (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
   WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation,
  a.attidentity,
  a.attstorage,
  CASE WHEN a.attstattarget=-1 THEN NULL ELSE a.attstattarget END AS attstattarget,
  pg_catalog.col_description(a.attrelid, a.attnum)
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = (select oid from pg_class where relname OPERATOR(pg_catalog.~) '^(test)$')
AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;



--精简版
SELECT a.attname 字段名,
  pg_catalog.format_type(a.atttypid, a.atttypmod) 字段类型,
  a.attnotnull 字段是否非空,
  pg_catalog.col_description(a.attrelid, a.attnum) 字段注释
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = (select oid from pg_class where relname OPERATOR(pg_catalog.~) '^(test)$')
AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

查看表的注释

SELECT relname AS tabname,cast( obj_description ( relfilenode, 'pg_class' ) AS VARCHAR ) AS COMMENT  FROM pg_class c  WHERE	 relkind = 'r' AND relname ='test';

查看低效索引

SELECT
    idstat.relname AS table_name,
    indexrelname AS index_name,
    idstat.idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(idstat.relid)) AS table_size,
    pg_size_pretty(pg_relation_size(indexrelid)) 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'
	and  idstat.relname='dwd_ent_company_info'
ORDER BY
    idstat.relname,
    indexrelname;

找出没有使用过的索引

SELECT
    pi.schemaname,
    pi.relname,
    pi.indexrelname,
    pg_size_pretty(pg_table_size(pi.indexrelid))
FROM
    pg_indexes pis
    JOIN pg_stat_user_indexes pi ON pis.schemaname = pi.schemaname
        AND pis.tablename = pi.relname
        AND pis.indexname = pi.indexrelname
    LEFT JOIN pg_constraint pco ON pco.conname = pi.indexrelname
        AND pco.conrelid = pi.relid
WHERE
    pi.schemaname = 'public'
    AND pco.contype IS DISTINCT FROM 'p'
    AND pco.contype IS DISTINCT FROM 'u'
    AND (idx_scan,
        idx_tup_read,
        idx_tup_fetch) = (0,
        0,
        0)
    AND pis.indexdef !~ 'UNIQUE INDEX'
	and pi.relname='dwd_ent_company_info'
ORDER BY
    relname,
	indexrelname,
    pg_table_size(indexrelid) DESC;

查看直接依赖于表的视图

-- 查看依赖于表的视图: 方法 1
SELECT
    distinct t.relname, v.oid::regclass AS view_name
FROM
    pg_class t
    JOIN pg_depend d ON (t.oid = d.refobjid)
    JOIN pg_rewrite r ON (r.oid = d.objid)
    JOIN pg_class v ON (r.ev_class = v.oid)
WHERE
    t.relkind = 'r'            -- 'r' 表示普通表
    AND v.relkind = 'v'        -- 'v' 表示视图
    AND t.relname = 'test'    -- 替换为你的表名
    AND t.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public');    

参考: https://mp.weixin.qq.com/s/vdWWogWrSRLI5zpD0855ww

-- 查看依赖于表的视图: 方法 2
SELECT * FROM information_schema.view_table_usage WHERE table_name = 'test';

查看无主键的表及其大小

-- 查看没有主键的表
select row_number() over(partition by c.constraint_type) id, current_catalog dbname, t.schemaname ||'.' || t.tablename tname,
case when c.constraint_type is null then false else true end as has_primary_key,
round(pg_total_relation_size(t.tablename::regclass)/2^20) table_mb
from pg_tables t  left join information_schema.table_constraints c
on (t.schemaname = c.table_schema and t.tablename = c.table_name and c.constraint_type='PRIMARY KEY')
where t.schemaname='public' 
and c.constraint_type is null
and t.tablename not in  ( select c.relname from pg_class c  where  c.relkind='p' and c.relnamespace = 'public'::regnamespace);

排查长事务

select pid,usename,xact_start,state_change,wait_event,state,query from  pg_stat_activity where state<>'idle' order by xact_start ;

数据库中 大小占用 TOP 50 的表

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

获取所有表膨胀

with view_table_bloat as (
  SELECT
    current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/
    ROUND((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::float/otta END)::numeric,1) AS tbloat,
    CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::BIGINT END AS wastedbytes,
    iname, /*ituples::bigint, ipages::bigint, iotta,*/
    ROUND((CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages::float/iotta END)::numeric,1) AS ibloat,
    CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes
  FROM (
    SELECT
      schemaname, tablename, cc.reltuples, cc.relpages, bs,
      CEIL((cc.reltuples*((datahdr+ma-
        (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta,
      COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
      COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
    FROM (
      SELECT
        ma,bs,schemaname,tablename,
        (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
        (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
      FROM (
        SELECT
          schemaname, tablename, hdr, ma, bs,
          SUM((1-null_frac)*avg_width) AS datawidth,
          MAX(null_frac) AS maxfracsum,
          hdr+(
            SELECT 1+count(*)/8
            FROM pg_stats s2
            WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
          ) AS nullhdr
        FROM pg_stats s, (
          SELECT
            (SELECT current_setting('block_size')::numeric) AS bs,
            CASE WHEN substring(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
            CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
          FROM (SELECT version() AS v) AS foo
        ) AS constants
        GROUP BY 1,2,3,4,5
      ) AS foo
    ) AS rs
    JOIN pg_class cc ON cc.relname = rs.tablename
    JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
    LEFT JOIN pg_index i ON indrelid = cc.oid
    LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
  ) AS sml
  ORDER BY wastedbytes DESC
) 
select * from view_table_bloat ;

另一个SQL: https://github.com/ioguix/pgsql-bloat-estimation/blob/master/table/table_bloat.sql

所冲突关系图

https://postgres-locks.husseinnasser.com/

获取最老的事务id

with a as (  
(select 'pg_stat_activity' as src, xact_start, usename,datname, query, backend_xid, backend_xmin   
from pg_stat_activity   
  where backend_xid = xid(pg_snapshot_xmin(pg_current_snapshot()))   
  or backend_xmin = xid(pg_snapshot_xmin(pg_current_snapshot()))   
  order by xact_start limit 1 )  
union all   
(select '2pc' as src, prepared as xact_start, owner as usename, database as datname, gid as query, transaction as backend_xid, transaction as backend_xmin  
from pg_prepared_xacts   
  where transaction = xid(pg_snapshot_xmin(pg_current_snapshot()))   
  order by prepared limit 1 )  
)  
select * from a order by xact_start limit 1;

查看wal生产情况

with tmp_file as (
  select t1.file,
      t1.file_ls,
      (pg_stat_file(t1.file)).size as size,
      (pg_stat_file(t1.file)).access as access,
      (pg_stat_file(t1.file)).modification as last_update_time,
      (pg_stat_file(t1.file)).change as change,
      (pg_stat_file(t1.file)).creation as creation,
      (pg_stat_file(t1.file)).isdir as isdir
   from (select dir||'/'||pg_ls_dir(t0.dir) as file,
          pg_ls_dir(t0.dir) as file_ls
       from ( select '/home/postgres/15data/pg_wal'::text as dir
           --需要修改这个物理路径
          ) t0
      ) t1 
   where 1=1
   order by (pg_stat_file(file)).modification desc
) 
select to_char(date_trunc('day',tf0.last_update_time),'yyyymmdd') as day_id,
    sum(case when date_part('hour',tf0.last_update_time) >=0 and date_part('hour',tf0.last_update_time) <24 then 1 else 0 end) as wal_num_all,
    sum(case when date_part('hour',tf0.last_update_time) >=0 and date_part('hour',tf0.last_update_time) <1 then 1 else 0 end) as wal_num_00_01,
    sum(case when date_part('hour',tf0.last_update_time) >=1 and date_part('hour',tf0.last_update_time) <2 then 1 else 0 end) as wal_num_01_02,
    sum(case when date_part('hour',tf0.last_update_time) >=2 and date_part('hour',tf0.last_update_time) <3 then 1 else 0 end) as wal_num_02_03,
    sum(case when date_part('hour',tf0.last_update_time) >=3 and date_part('hour',tf0.last_update_time) <4 then 1 else 0 end) as wal_num_03_04,
    sum(case when date_part('hour',tf0.last_update_time) >=4 and date_part('hour',tf0.last_update_time) <5 then 1 else 0 end) as wal_num_04_05,
    sum(case when date_part('hour',tf0.last_update_time) >=5 and date_part('hour',tf0.last_update_time) <6 then 1 else 0 end) as wal_num_05_06,
    sum(case when date_part('hour',tf0.last_update_time) >=6 and date_part('hour',tf0.last_update_time) <7 then 1 else 0 end) as wal_num_06_07,
    sum(case when date_part('hour',tf0.last_update_time) >=7 and date_part('hour',tf0.last_update_time) <8 then 1 else 0 end) as wal_num_07_08,
    sum(case when date_part('hour',tf0.last_update_time) >=8 and date_part('hour',tf0.last_update_time) <9 then 1 else 0 end) as wal_num_08_09,
    sum(case when date_part('hour',tf0.last_update_time) >=9 and date_part('hour',tf0.last_update_time) <10 then 1 else 0 end) as wal_num_09_10,
    sum(case when date_part('hour',tf0.last_update_time) >=10 and date_part('hour',tf0.last_update_time) <11 then 1 else 0 end) as wal_num_10_11,
    sum(case when date_part('hour',tf0.last_update_time) >=11 and date_part('hour',tf0.last_update_time) <12 then 1 else 0 end) as wal_num_11_12,
    sum(case when date_part('hour',tf0.last_update_time) >=12 and date_part('hour',tf0.last_update_time) <13 then 1 else 0 end) as wal_num_12_13,
    sum(case when date_part('hour',tf0.last_update_time) >=13 and date_part('hour',tf0.last_update_time) <14 then 1 else 0 end) as wal_num_13_14,
    sum(case when date_part('hour',tf0.last_update_time) >=14 and date_part('hour',tf0.last_update_time) <15 then 1 else 0 end) as wal_num_14_15,
    sum(case when date_part('hour',tf0.last_update_time) >=15 and date_part('hour',tf0.last_update_time) <16 then 1 else 0 end) as wal_num_15_16,
    sum(case when date_part('hour',tf0.last_update_time) >=16 and date_part('hour',tf0.last_update_time) <17 then 1 else 0 end) as wal_num_16_17,
    sum(case when date_part('hour',tf0.last_update_time) >=17 and date_part('hour',tf0.last_update_time) <18 then 1 else 0 end) as wal_num_17_18,
    sum(case when date_part('hour',tf0.last_update_time) >=18 and date_part('hour',tf0.last_update_time) <19 then 1 else 0 end) as wal_num_18_19,
    sum(case when date_part('hour',tf0.last_update_time) >=19 and date_part('hour',tf0.last_update_time) <20 then 1 else 0 end) as wal_num_19_20,
    sum(case when date_part('hour',tf0.last_update_time) >=20 and date_part('hour',tf0.last_update_time) <21 then 1 else 0 end) as wal_num_20_21,
    sum(case when date_part('hour',tf0.last_update_time) >=21 and date_part('hour',tf0.last_update_time) <22 then 1 else 0 end) as wal_num_21_22,
    sum(case when date_part('hour',tf0.last_update_time) >=22 and date_part('hour',tf0.last_update_time) <23 then 1 else 0 end) as wal_num_22_23, 
    sum(case when date_part('hour',tf0.last_update_time) >=23 and date_part('hour',tf0.last_update_time) <24 then 1 else 0 end) as wal_num_23_24
from tmp_file tf0
where 1=1
 and tf0.file_ls not in ('archive_status')
group by to_char(date_trunc('day',tf0.last_update_time),'yyyymmdd')
order by to_char(date_trunc('day',tf0.last_update_time),'yyyymmdd') desc;
postgresql
最后修改时间：2024-05-08 16:13:26
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论

目录
  • 查看哪些角色对表有增删改查权限
  • 查看哪些角色对函数有执行权限
  • 根据序列名获取表及列信息
  • 查看postgresql数据库用户系统权限、对象权限
  • 查看所有主键及其相关字段信息
  • 查看 排除主键索引之外的 其他所有唯一性约束与唯一索引
  • 给 data 用户授予 create publication 权限
  • 统计当前库中每张表数据条数
  • 查询所有外键对应的表与列
  • 查看表所属 schmea 及其oid
  • 查询表是否有索引, 触发器等信息
  • 通过 SQL 查询表结构及其字段注释信息
  • 查看表的注释
  • 查看低效索引
  • 找出没有使用过的索引
  • 查看直接依赖于表的视图
  • 查看无主键的表及其大小
  • 排查长事务
  • 数据库中 大小占用 TOP 50 的表
  • 获取所有表膨胀
  • 所冲突关系图
  • 获取最老的事务id
  • 查看wal生产情况