postgres常规维护

参考文档： PostgreSQL 9 Administration Cookbook （第二版）中文版 第九章 常规维护

postgresql提供vacuum工具命令。可以和analyze联合使用。不过在合适的时候，会被autovacuum自动执行。autovacuum默认是被启用的。
--确定autovacuum是开启的，需要启用以下参数

autovacuum = on   --控制服务器是否运行自动清理启动器后台进程。默认为开启，不过要自动清理正常工作还需要启用track_counts。
track_counts = on   --启用在数据库活动上的统计收集。这个参数默认为打开，因为自动清理守护进程需要被收集的信息。只有超级用户可以更改这个设置

-- pg的conf文件中，有大量的独立可调整的参数来控制autovacuum

autovacuum
autovacuum_analyze_scale_factor
autovacuum_analyze_threshold
autovacuum_freeze_max_age
autovacuum_max_workers
autovacuum_naptime
autovacuum_vacuum_cost_delay
autovacuum_vacuum_cost_limit
autovacuum_vacuum_scale_factor
autovacuum_vacuum_threshold
log_autovacuum_min_duration

-- 单独的表，可以通过存储参数来控制，可以使用下面的设置

ALTER TABLE mytable SET (storage_parameter = value);

--与维护相关的存储参数有

autovacuum_enabled
autovacuum_vacuum_cost_delay
autovacuum_vacuum_cost_limit
autovacuum_vacuum_scale_factor
autovacuum_vacuum_threshold
autovacuum_freeze_min_age
autovacuum_freeze_max_age
autovacuum_freeze_table_age
autovacuum_analyze_scale_factor
autovacuum_analyze_threshold

-- TOAST表可以使用以下参数来控制

toast.autovacuum_enabled
toast.autovacuum_vacuum_cost_delay
toast.autovacuum_vacuum_cost_limit
toast.autovacuum_vacuum_scale_factor
toast.autovacuum_vacuum_threshold
toast.autovacuum_freeze_min_age
toast.autovacuum_freeze_max_age
toast.autovacuum_freeze_table_age
toast.autovacuum_analyze_scale_factor
toast.autovacuum_analyze_threshold

--举例，单独对表设置autovacuum参数

mydb=# alter table t set (autovacuum_enabled = true);
ALTER TABLE
mydb=# alter table t set (autovacuum_enabled = false);
ALTER TABLE
mydb=# alter table t set (autovacuum_enabled = true);
ALTER TABLE
mydb=# alter table t set (autovacuum_enabled = on);   -- 也可以用on/off 替代true/false 
ALTER TABLE
ALTER TABLE pgbench_accounts SET ( autovacuum_vacuum_cost_delay = 20 ,toast.autovacuum_enabled = off);  -- 对toast表设置的参数
ALTER TABLE pgbench_accounts SET ( toast.autovacuum_enabled = off);  -- 关闭toast表上的autovacuum

--如果设置了autovacuum，则autovacuum会每间隔autovacuum_naptim秒就被唤醒，并决定是否运行vacuum和analyze命令，或同时运行 。
-- 同时运行的维护进程不会超过autovacuum_max_workers个。由于autovacuum子进程会执行IO操作，因此会累计成本点，直到累计成本点
达到auto vacuum_vacuum_cost_limit设置的成本值时，才会休眠autovacuum_vacuum_cost_delay设置的时间。
所以增大autovacuum_vacuum_cost_delay的值会减慢每个vacuum，从而降低对活动用户的影响。当autovacum_analyze_threshold值的发生变化并且
表的增删改操作对表的影响百分比达到了autovacuum_analyze_scale_factore设置的值时，autovacuum将运行analyze命令。

mydb=# show autovacuum_naptime;
 autovacuum_naptime 
--------------------
 1min
(1 row)

mydb=# show autovacuum_max_workers;
 autovacuum_max_workers 
------------------------
 3
(1 row)

mydb=# show autovacuum_vacuum_cost_limit;
 autovacuum_vacuum_cost_limit 
------------------------------
 -1
(1 row)

mydb=# show autovacuum_vacuum_cost_delay;
 autovacuum_vacuum_cost_delay 
------------------------------
 20ms
(1 row)

mydb=# show log_autovacuum_min_duration;
 log_autovacuum_min_duration 
-----------------------------
 0
(1 row)

mydb=# show autovacuum_analyze_threshold;
 autovacuum_analyze_threshold 
------------------------------
 50
(1 row)

mydb=# show autovacuum_analyze_scale_factor;
 autovacuum_analyze_scale_factor 
---------------------------------
 0.1
(1 row)

mydb=# 

--如果设置了log_autovacuum_min_duration，则autovacuum的操作记录会被记录到服务器日志中

--查询显示表及其toast表的reoptions

SELECT n.nspname, c.relname,
pg_catalog.array_to_string(c.reloptions || array(
select 'toast.' ||
x from pg_catalog.unnest(tc.reloptions) x),', ')
as relopts
FROM pg_catalog.pg_class c
LEFT JOIN
pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid) JOIN
pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind = 'r'
AND nspname NOT IN ('pg_catalog', 'information_schema');

vacuum运行时允许增删改操作，但是会阻止如alert table、create index之类的操作。autovacuum运行时可以检测用户是否在表上请求了一个冲突锁，如果
会妨碍用户使用，则其会自己取消运行。
需要注意的是：
vacuum运行时不会收缩表。
vacuum full，会完全收缩一张表，但是会锁住整张表很长的时间，所以尽量避免这样的操作。
vacuum full会逐个地重写表的每一行，并完全重建所有索引，虽然这个过程比以前版本会快很多，但是对于大表还是需要很长时间。

-- 避免自动冻结和页损坏
PG会定期执行清理还清除旧事物标识符，这就是“冻结”。目的是为了延迟事务重叠。
在PG中，行的发展路线有两种：
行的版本死亡，并且需要vacuum去清除；
行的版本变得足够老，需要被冻结，由vacuum进程处理 。

假设表加载了10亿行数据，当这些行在哪里已足够久并且开始被冻结时，此表上的下一次vacuum会重写所有这些行来冻结他们的事务标识符。
也就是说，autovacuum会被唤醒，并开始使用大量的IO来执行冻结。

当行的事务标识符变得比当前事务的下一个值老vacuum_freeze_min_age时，就会发生冻结。
vacuum命令通常被优化为只查找表中需要清理的块。当表的年龄达到vacuum_freeze_table_age时，会忽略这个优化并扫描整张表。当这样做
的时候，会发现需要冻结并重写的行。所以会导致大幅提高IO。

-- 避免事务重叠
在单用户模式下进行vacuum

postgres --single -D $PG_DATA postgres       -- single参数，以单用户模式运行，类似linux的运行级别为1的模式，也称为单用户模式 
vacuum 

-- 移除过期的准备事务 （分布式事务的两阶段提交的准备事务）
检查max_prepared_transactions，如果为0，说明没有任何预先存在的准备事务
SHOW max_prepared_transactions;
如果设置大于0，则需要查看是否有准备事务。
视图pg_prepared_xacts显示关于两阶段提交（详见PREPARE TRANSACTION）的当前准备好事务的信息。
pg_prepared_xacts为每一个预备事务包含一行。当事务被提交或回滚时，相应的项将被移除。
SELECT * FROM pg_prepared_xacts;   --查看是否有准备事务

--移除一个准备事务也称为“解决未决事务”。事务卡在提交和中止之间。数据库或者事务管理器崩溃后，使得事务被遗留在两阶段提交的两步之间。

commit prepared 'prep1';   -- 提交更改
rollback prepared 'prep1';   -- 回滚更改 

-- 得到一个被锁对象的完整报告

SELECT l.locktype, x.database, l.relation, l.page,
l.tuple,l.classid, l.objid, l.objsubid,
l.mode, x.transaction, x.gid, x.prepared,
x.owner
FROM pg_locks l JOIN pg_prepared_xacts x
ON l.virtualtransaction = '-1/' ||
x.transaction::text;

-- 或使用更简单的查询

SELECT DISTINCT x.database, l.relation
FROM pg_locks l JOIN pg_prepared_xacts x
ON l.virtualtransaction = '-1/' ||
x.transaction::text
WHERE l.locktype != 'transactionid';

--检查哪些行被事务改变了，可以使用每张表中都存在的隐藏列xmin。

select * from t where xmin = 12345 ;

-- 当创建一张临时表时，会插入条目到系统表pg_class、pg_type、和pg_attribute中。这些系统表和他们的索引开始变大并膨胀。为了控制膨胀，要么
手动vacuum表，要么在postgresql.conf中设置autovacuum=on .但是不能对系统表运行alter table 指令，也不可以为这些表设置特定的autovacuum参数。

-- 使用查询得到需要vacuum的表以及他们的索引的列表

SELECT relname, pg_relation_size(oid)
FROM pg_class
WHERE relkind in ('i','r') and relnamespace = 11
ORDER BY 2 DESC;

-- 识别和修复膨胀的表和索引
mvcc是pg的核心部分之一，mvcc内部有一些需要dba理解的地方，每行都有一个行版本，而且还有两个系统列xmin和xmax。分别标识出这个版本的行被创建和删除的事务。如果某个版本还没有被删除过，则xmax的值为null 。

大致思路：
通过修改xmin和xmax的值来控制行的可见情况，而不是删除行的版本。
当被插入的时候，xmin的值被设置为插入他的事务号，而xmax则为空。
当行被删除的时候，xman的值被设置为删除操作的事务号，而不实际上删除行。
update操作，则认为实际上是一个紧跟着insert操作的delete操作。被删除的行保留旧的版本，插入的行为新版本。
当回滚一个事务的时候，通过标记事务id为中止来让所有的变化变得不可见 。

通过这种方法，delete、update和rollback语句变得都比较快。代价是sql update命令会导致表和索引的膨胀。

CREATE OR REPLACE VIEW av_needed AS
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,
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,
pg_stat_get_tuples_hot_updated(C.oid)::real /
pg_stat_get_tuples_updated(C.oid) 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;

select * from av_needed where relname ='public.t';

-- 检测索引是否随着时间而变化

SELECT
nspname,relname,
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
FROM pg_index I
LEFT JOIN pg_class C ON (C.oid = I.indexrelid)
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;

-- 或者使用pgstatupple()扫描整个表 ，pg提供的模块pgstattuple,需要创建这个扩展，否则无法查询。

mydb=# create extension pgstattuple;
CREATE EXTENSION
mydb=# 
SELECT * FROM pgstattuple('pg_catalog.pg_proc');
SELECT * FROM pgstatindex('pg_cast_oid_index');

mydb=# \x
Expanded display is on.
mydb=# SELECT * FROM pgstattuple('pg_catalog.pg_proc');
-[ RECORD 1 ]------+-------
table_len          | 655360
tuple_count        | 3025
tuple_len          | 603256
tuple_percent      | 92.05
dead_tuple_count   | 1
dead_tuple_len     | 489
dead_tuple_percent | 0.07
free_space         | 26916
free_percent       | 4.11

mydb=# SELECT * FROM pgstatindex('pg_cast_oid_index');
-[ RECORD 1 ]------+------
version            | 2
tree_level         | 0
index_size         | 16384
root_block_no      | 1
internal_pages     | 0
leaf_pages         | 1
empty_pages        | 0
deleted_pages      | 0
avg_leaf_density   | 54.51
leaf_fragmentation | 0

mydb=# 

-- 维护索引
可以使用reindexdb来操作，具体可以看帮助。根据经验，大多数的索引都不需要重建。重建时，会持有一个全表锁，可能会锁住数据库。
postgres支持create index concurrently命令，创建索引的时候，不会持有一个全表锁。

-- 添加约束而不检查已有行 （类似oracle的no valid）
--建立两个表进行测试

create table ft(fk int primary key,fs text);
create table pt(pk int,ps text);
insert into ft(fk,fs) values(1,'one'),(2,'two');
insert into pt(pk,ps) values(1,'I'),(2,'II'),(3,'III');

-- 尝试建立一个外键，会遇到错误，因为ft表中没有数字3 ，可以以not valid的方式来建立约束

alter table pt add constraint pc foreign key(pk) references ft(fk);
alter table pt add constraint pc foreign key(pk) references ft(fk) not valid;

mydb=# alter table pt add constraint pc foreign key(pk) references ft(fk);
ERROR:  insert or update on table "pt" violates foreign key constraint "pc"
DETAIL:  Key (pk)=(3) is not present in table "ft".
mydb=# alter table pt add constraint pc foreign key(pk) references ft(fk) not valid;
ALTER TABLE
mydb=# 

-- 查看约束

\d pt 
mydb=# \d pt
                 Table "public.pt"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 pk     | integer |           |          | 
 ps     | text    |           |          | 
Foreign-key constraints:
    "pc" FOREIGN KEY (pk) REFERENCES ft(fk) NOT VALID

mydb=# 

--将not valid 约束转换成有效状态的时候，才会进行违例检查

alter table pt validate constraint pc;

-- 删除不一致后，验证变得可用，外键升级成正常的外键

delete from pt where pk = 3;
alter table pt validate constraint pc ;
\d pt 

mydb=# delete from pt where pk = 3;
DELETE 1
mydb=# alter table pt validate constraint pc ;
ALTER TABLE
mydb=# \d pt
                 Table "public.pt"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 pk     | integer |           |          | 
 ps     | text    |           |          | 
Foreign-key constraints:
    "pc" FOREIGN KEY (pk) REFERENCES ft(fk)

mydb=# 

-- 寻找未使用的索引 ，为0表示索引未被使用过。idx_scan标识索引的使用次数。需要注意的事项：
设计的索引有可能是为了防止重复插入而存在的唯一约束索引。一个insert不会表现为一个index_scan。
但是update或者delete可以表现为一个index_scan.

SELECT schemaname, relname, indexrelname, idx_scan FROM pg_stat_user_indexes ORDER BY idx_scan;
mydb=# SELECT schemaname, relname, indexrelname, idx_scan FROM pg_stat_user_indexes ORDER BY idx_scan;
 schemaname |       relname        |           indexrelname           | idx_scan 
------------+----------------------+----------------------------------+----------
 public     | sales_summary        | sales_summary_seller             |        0
 public     | wrd                  | wrd_word                         |        0
 public     | wrd                  | wrd_trgm                         |        0
 public     | orders               | orders_pkey                      |        0
 public     | orderlines           | orderlines_pkey                  |        0
 public     | boxes                | boxes_position_excl              |        0
 public     | foo                  | foo_fooid_excl                   |        0
 myschema   | company              | company_pkey                     |        0
 public     | measurement_y2006m02 | measurement_y2006m02_logdate_idx |        0
 public     | measurement_y2006m03 | measurement_y2006m03_logdate_idx |        0
 public     | measurement_y2007m11 | measurement_y2007m11_logdate_idx |        0
 public     | users                | users_pkey                       |        0
 public     | invoice              | invoice_pkey                     |        0
 public     | test1                | test1_id_index                   |        0
 public     | test2                | test2_mm_idx                     |        0
 public     | ft                   | ft_pkey                          |        0
 public     | people               | people_pkey                      |        0
 public     | hats                 | hats_pkey                        |        0
 public     | dish                 | dish_pkey                        |        0
(19 rows)

mydb=# 

-- 谨慎删除不必要的索引

--创建函数
 

CREATE OR REPLACE FUNCTION trial_drop_index(iname TEXT)
RETURNS VOID
LANGUAGE SQL AS $$
UPDATE pg_index
SET indisvalid = false
WHERE indexrelid = $1::regclass;
$$;

-- 运行函数，并做一个删除索引的测试
-- 删除索引后，如果遇到性能问题，可以通过使用下面的函数来“恢复”索引

CREATE OR REPLACE FUNCTION trial_undrop_index(iname TEXT)
RETURNS VOID
LANGUAGE SQL AS $$
UPDATE pg_index
SET indisvalid = true
WHERE indexrelid = $1::regclass;
$$;

END