PG_DBA_运维手册

[up主专用，视频内嵌代码贴在这]

PG_DBA_运维手册

查看database的owner

1
select datname, usename as owner from pg_database left join pg_user on usesysid = datdba;

查看可见SCHEMA

1
select * from information_schema.schemata;

查看表膨胀（对所有表进行膨胀率排序），取前10个

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
    schemaname||'.'||relname as table_name,
    pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup * 100 / (n_live_tup + n_dead_tup),2) AS dead_tup_ratio
FROM
    pg_stat_all_tables
WHERE
    n_dead_tup >= 1000
and		schemaname in ('base','workspace','permission','process','form','pipeline')    
ORDER BY dead_tup_ratio DESC
LIMIT 10;

表膨胀处理

1
2
3
VACUUM (VERBOSE, ANALYZE)  表明，不锁表，不影响业务使用

VACUUM ( FULL,VERBOSE ) 表名称 ，注意会锁表，连查询都不可以

设置序列catche 1 (无缓存,每个进程取值就连续了)

1
2
select   'alter sequence '|| sequence_name|| ' cache 1 ;'
 from information_schema.sequences where sequence_schema != 'public'

查看性能sql

查看正在执行的sql,长事务（top sql）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
select datname,
       usename,
       client_addr,
       application_name,
       state,
       backend_start,
       xact_start,
       xact_stay,
       query_start,
       query_stay,
	   now() - query_start AS Duration, 
       replace(query, chr(10), ' ') as query
from
  (select pgsa.datname as datname,
          pgsa.usename as usename,
          pgsa.client_addr client_addr,
          pgsa.application_name as application_name,
          pgsa.state as state,
          pgsa.backend_start as backend_start,
          pgsa.xact_start as xact_start,
          extract(epoch
                  from (now() - pgsa.xact_start)) as xact_stay,
          pgsa.query_start as query_start,
          extract(epoch
                  from (now() - pgsa.query_start)) as query_stay,
          pgsa.query as query
   from pg_stat_activity as pgsa
   where pgsa.state != 'idle'
     and pgsa.state != 'idle in transaction'
     and pgsa.state != 'idle in transaction (aborted)'
	 and usename='datalink'
	--and (now() - query_start) > interval '10 seconds'
	 ) idleconnections
order by query_stay desc
limit 5;

查看之前执行的sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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 <> '<IDLE>' 
ORDER BY 
  lap DESC;

procpid：进程id ,
强制结束
SELECT pg_cancel_backend(进程id) –取消后台操作;
SELECT pg_terminate_backend(PID) –中断session;
start：进程开始时间
lap：经过时间
current_query：执行中的sql

慢SQL、TOP SQL优化示例

1
2
3
4
5
begin;  
set local lock_timeout='1s';  
set local statement_timeout=0;  
explain (analyze,verbose,timing,costs,buffers,timing) SQL;  -- SQL代替为要分析的SQL  
rollback;

空闲连接

1
2
3
4
select * from pg_stat_activity
where usename='datalink'
and state='idle'
order by query_start

表大小前一百个（包含索引）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
WITH bloat AS (
	WITH t1 AS (
		SELECT
			schemaname,
			tablename,
			( 23 + CEIL ( COUNT ( * ) >> 3 ) ) :: BIGINT nullheader,
			MAX ( null_frac ) nullfrac,
			CEIL ( SUM ( ( 1 - null_frac ) * avg_width ) ) :: BIGINT datawidth 
		FROM
			pg_stats 
		GROUP BY
			schemaname,
			tablename 
		),
		t2 AS (
		SELECT
			schemaname,
			tablename,
			( datawidth + 8 - ( CASE WHEN datawidth % 8 = 0 THEN 8 ELSE datawidth % 8 END ) ) -- avg data len
			+ ( 1 - nullfrac ) * 24 + nullfrac * ( nullheader + 8 - ( CASE WHEN nullheader % 8 = 0 THEN 8 ELSE nullheader % 8 END ) ) avgtuplelen 
FROM
	t1 
	) SELECT C
	.oid,
	avgtuplelen
FROM
	t2 T,
	pg_class C,
	pg_namespace n 
WHERE
	T.schemaname = n.nspname 
	AND C.relname = T.tablename 
	AND C.relnamespace = n.oid 
	AND relpages > 100 
	),
	parti AS (
	SELECT
		parti.inhparent,
		parti.inhrelid,
		stat.n_dead_tup,
		c.relpages,
		c.reltuples
	FROM
		(
		SELECT
			inhparent,
			inhrelid
		FROM
			pg_inherits inh UNION ALL
			( SELECT b.inhparent AS grandpa, A.inhrelid me FROM pg_inherits A INNER JOIN pg_inherits b ON A.inhparent = b.inhrelid ) 
		) AS parti
		inner join pg_class c on parti.inhrelid = c.oid
		LEFT JOIN pg_stat_user_tables stat ON parti.inhrelid = stat.relid 
	) SELECT
	 n.nspname "tableSchema",
	C.relname "tableName",
	SUM ( pg_total_relation_size ( COALESCE ( parti1.inhrelid, C.oid ) ) ) AS "tableSize", --表及其子分区、索引大小，包含索引
	round(
		SUM ( pg_total_relation_size ( COALESCE ( parti1.inhrelid, C.oid ) ) ) / ( SELECT SUM ( pg_database_size ( datname ) ) AS dbsize FROM pg_database ),
		4 
	) AS tblpercent,-- 表占数据文件比例	 
	COALESCE ( MAX ( c.reltuples ), 0 ) + COALESCE ( SUM ( COALESCE ( parti1.reltuples, 0 ) ), 0 ) AS "tableRows",
CASE	
		WHEN (coalesce(max( bloat.avgtuplelen), 0) = 0  and coalesce(avg( parti1.avgtuplelen), 0) = 0) or  MAX ( COALESCE (c.relpages , 0 ) ) + SUM ( COALESCE ( parti1.relpages, 0 ))  = 0 THEN
		0 ELSE  
		round((1 - CEIL ( (MAX ( COALESCE ( c.reltuples, 0 ))  + SUM ( COALESCE ( parti1.reltuples, 0 ))) * 
		(case when max( parti1.avgtuplelen) is not null then AVG( parti1.avgtuplelen)  else max ( COALESCE ( bloat.avgtuplelen, 0 ) ) end  ) / 8168) / 
		(  MAX ( COALESCE (c.relpages , 0 ) ) + SUM ( COALESCE ( parti1.relpages, 0 )) ))::NUMERIC, 2)
	END AS bloat_pct,
	COALESCE ( MAX ( stat.n_dead_tup ), 0 ) + COALESCE ( SUM ( COALESCE ( parti1.n_dead_tup, 0 ) ), 0 ) AS "deadTup" --表内死数据行
FROM
	pg_class
	C INNER JOIN pg_namespace n ON C.relnamespace = n.oid
	LEFT JOIN pg_stat_user_tables stat ON C.oid = stat.relid
	LEFT JOIN (select p.*, b.* from parti p left join bloat as b  ON p.inhrelid = b.oid) as parti1 on c.oid = parti1.inhparent
	LEFT JOIN bloat ON C.oid = bloat.oid 
WHERE
	n.nspname NOT IN ( 'pg_catalog', 'information_schema' ) 
	AND n.nspname !~ '^pg_toast' 
	AND C.relkind IN ( 'r', 'p' ) 
	AND C.oid NOT IN ( SELECT inhrelid FROM pg_inherits ) 
GROUP BY
	C.relname,
	n.nspname 
ORDER BY
	"tableSize" DESC limit 100

–查看表记录数

1
2
3
4
select relname as 表名, reltuples as 记录数 
from pg_class 
where relkind = 'r' 
and relnamespace = (select oid from pg_namespace where nspname='base') order by 记录数 desc;

查看schema大小

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
SELECT "tableSchema" as "column",   --schemanamen 
 sum("tableSize") / 1024 / 1024 "schemaSize" --schema大小
 FROM ( WITH bloat AS (
	WITH t1 AS (
		SELECT
			schemaname,
			tablename,
			( 23 + CEIL ( COUNT ( * ) >> 3 ) ) :: BIGINT nullheader,
			MAX ( null_frac ) nullfrac,
			CEIL ( SUM ( ( 1 - null_frac ) * avg_width ) ) :: BIGINT datawidth 
		FROM
			pg_stats 
		GROUP BY
			schemaname,
			tablename 
		),
		t2 AS (
		SELECT
			schemaname,
			tablename,
			( datawidth + 8 - ( CASE WHEN datawidth % 8 = 0 THEN 8 ELSE datawidth % 8 END ) ) -- avg data len
			+ ( 1 - nullfrac ) * 24 + nullfrac * ( nullheader + 8 - ( CASE WHEN nullheader % 8 = 0 THEN 8 ELSE nullheader % 8 END ) ) avgtuplelen 
FROM
	t1 
	) SELECT C
	.oid,
	avgtuplelen
FROM
	t2 T,
	pg_class C,
	pg_namespace n 
WHERE
	T.schemaname = n.nspname 
	AND C.relname = T.tablename 
	AND C.relnamespace = n.oid 
	AND relpages > 100 
	),
	parti AS (
	SELECT
		parti.inhparent,
		parti.inhrelid,
		stat.n_dead_tup,
		c.relpages,
		c.reltuples
	FROM
		(
		SELECT
			inhparent,
			inhrelid
		FROM
			pg_inherits inh UNION ALL
			( SELECT b.inhparent AS grandpa, A.inhrelid me FROM pg_inherits A INNER JOIN pg_inherits b ON A.inhparent = b.inhrelid ) 
		) AS parti
		inner join pg_class c on parti.inhrelid = c.oid
		LEFT JOIN pg_stat_user_tables stat ON parti.inhrelid = stat.relid 
	) SELECT
	 n.nspname "tableSchema",
	C.relname "tableName",
	SUM ( pg_total_relation_size ( COALESCE ( parti1.inhrelid, C.oid ) ) ) AS "tableSize", --表及其子分区、索引大小，包含索引
	round(
		SUM ( pg_total_relation_size ( COALESCE ( parti1.inhrelid, C.oid ) ) ) / ( SELECT SUM ( pg_database_size ( datname ) ) AS dbsize FROM pg_database ),
		4 
	) AS tblpercent,-- 表占数据文件比例	 
	COALESCE ( MAX ( c.reltuples ), 0 ) + COALESCE ( SUM ( COALESCE ( parti1.reltuples, 0 ) ), 0 ) AS "tableRows",
CASE	
		WHEN (coalesce(max( bloat.avgtuplelen), 0) = 0  and coalesce(avg( parti1.avgtuplelen), 0) = 0) or  MAX ( COALESCE (c.relpages , 0 ) ) + SUM ( COALESCE ( parti1.relpages, 0 ))  = 0 THEN
		0 ELSE  
		round((1 - CEIL ( (MAX ( COALESCE ( c.reltuples, 0 ))  + SUM ( COALESCE ( parti1.reltuples, 0 ))) * 
		(case when max( parti1.avgtuplelen) is not null then AVG( parti1.avgtuplelen)  else max ( COALESCE ( bloat.avgtuplelen, 0 ) ) end  ) / 8168) / 
		(  MAX ( COALESCE (c.relpages , 0 ) ) + SUM ( COALESCE ( parti1.relpages, 0 )) ))::NUMERIC, 2)
	END AS bloat_pct,
	COALESCE ( MAX ( stat.n_dead_tup ), 0 ) + COALESCE ( SUM ( COALESCE ( parti1.n_dead_tup, 0 ) ), 0 ) AS "deadTup" --表内死数据行
FROM
	pg_class
	C INNER JOIN pg_namespace n ON C.relnamespace = n.oid
	LEFT JOIN pg_stat_user_tables stat ON C.oid = stat.relid
	LEFT JOIN (select p.*, b.* from parti p left join bloat as b  ON p.inhrelid = b.oid) as parti1 on c.oid = parti1.inhparent
	LEFT JOIN bloat ON C.oid = bloat.oid 
WHERE
	n.nspname NOT IN ( 'pg_catalog', 'information_schema' ) 
	AND n.nspname !~ '^pg_toast' 
	AND C.relkind IN ( 'r', 'p' ) 
	AND C.oid NOT IN ( SELECT inhrelid FROM pg_inherits ) 
GROUP BY
	C.relname,
	n.nspname 
ORDER BY
	"tableSize" DESC
 ) as foo group by "tableSchema" order by "schemaSize" desc