天天看點
傳回

PostgreSQL AB表切換最佳實踐 - 提高切換成功率，杜絕雪崩

标簽

PostgreSQL , 雪崩 , 鎖等待 , 切換表名 , DDL

https://github.com/digoal/blog/blob/master/201807/20180725_04.md#%E8%83%8C%E6%99%AF 背景

AB表切換經常出現在資料導入的場景中，例如每天或者每個固定周期，需要全量導入一批資料到資料庫，同時被導入的資料要持續的被查詢。

為了盡量避免導入影響查詢，通常會使用AB表切換的方法。使用如下步驟：

1、建新表

2、全量資料導入到新表

3、在新表上面建必要的索引

4、切換新、老表名

但是，注意，由于切換表名是DDL操作，會加排它鎖，與所有其他所會發送沖突。如果資料庫中有長事務持有了老的表的任何鎖，那麼DDL會被堵塞，等待，同時會堵塞後來的任何需要持有老表鎖的請求。

1、長事務，持有老表鎖（共享鎖）

2、切換表名，DDL，請求排他鎖，等待，排它鎖加入鎖等待隊列

3、其他會話，查詢老表（請求共享鎖），與鎖等待隊列中的DDL排他鎖沖突，等待，共享鎖加入鎖等待隊列。

以上情況，很容易造成雪崩。

《PostgreSQL 設定單條SQL的執行逾時 - 防雪崩》

那麼如何避免雪崩？并且在較短的時間内完成AB表切換呢？

1、殺持有新、舊表鎖的會話  
  
2、在事務中切換表名  
  
開啟事務  
  
設事務級鎖逾時  
  
對A,B表加排它鎖  
  
切換A,B表  
  
重試若幹次  
  
結束事務

我們可以把以上步驟函數化，提供調用

https://github.com/digoal/blog/blob/master/201807/20180725_04.md#%E5%88%87%E6%8D%A2%E8%A1%A81%E5%92%8C%E8%A1%A82%E7%9A%84%E5%87%BD%E6%95%B0%E6%8E%A5%E5%8F%A3%E5%A6%82%E4%B8%8B%E6%9A%B4%E5%8A%9B%E7%89%88 切換表1和表2的函數接口如下(暴力版) 

create or replace function exchange_tab(  
  nsp name,       -- schema name  
  from_tab name,  -- 表名1  
  to_tab name,    -- 表名2  
  mid_tab name,   -- 中間表名（使用不存在的表）  
  timeout_s int,  -- 鎖逾時時間（秒），建議設小一點，比如1秒  
  retry int,      -- 重試幾次  
  steps int default 1    -- 重試次數判斷  
) returns boolean as $$  
declare  
begin  
  -- 檢查中間表是否已存在，存在則報錯  
  perform 1 from pg_class where relname=mid_tab and relnamespace=(select oid from pg_namespace where nspname=nsp) limit 1;  
  if found then  
    raise notice 'you should use not exists table for exchange.';  
    return false;  
  end if;  
    
  -- 如果重試次數達到，還沒有切換成功，則傳回切換不成功  
  if steps >= retry then  
    return false;  
  end if;  
    
  -- 設定鎖逾時  
  execute format('set local lock_timeout=%L;', timeout_s||'s');  
    
  -- 殺死持有 表1，表2 鎖的會話  
  -- 如果是普通使用者，隻能殺死同名使用者下的其他會話，是以如果持鎖的是其他使用者，則需要使用超級使用者才能殺死  
  perform pg_terminate_backend(pid) from pg_stat_activity where   
    pid in (select pid from pg_locks where   
              database=(select oid from pg_database where datname=current_database())   
              and relation in ((nsp||'.'||from_tab)::regclass, (nsp||'.'||to_tab)::regclass)  
           )   
    and pid<>pg_backend_pid();  
    
  -- 對表1，表2 加排他鎖  
  execute format('lock table %I.%I in ACCESS EXCLUSIVE mode;', nsp, from_tab);  
  execute format('lock table %I.%I in ACCESS EXCLUSIVE mode;', nsp, to_tab);  
    
  -- 切換表1，表2  
  execute format('alter table %I.%I rename to %I;', nsp, to_tab, mid_tab);  
  execute format('alter table %I.%I rename to %I;', nsp, from_tab, to_tab);  
  execute format('alter table %I.%I rename to %I;', nsp, mid_tab, from_tab);  
    
  -- 傳回切換成功  
  return true;  
  
  -- 任何一步失敗（比如鎖逾時異常），則重試  
  exception when others then  
    -- 重試次數顯示  
    raise notice 'retry: %', steps;  
      
    -- 如果重試次數達到，還沒有切換成功，則傳回切換不成功  
    if steps >= retry then  
      return false;  
    else  
      -- 遞歸調用，重試，傳入參數重試次數+1.    
      return exchange_tab(nsp, from_tab, to_tab, mid_tab, timeout_s, retry, steps+1);  
    end if;  
end;  
$$ language plpgsql strict;

https://github.com/digoal/blog/blob/master/201807/20180725_04.md#%E4%BE%8B%E5%AD%90 例子

1、建立兩張表，用來切換

create table abc(id int);  
create table abc_tmp(id int);

2、分别寫入100,1000條記錄

insert into abc select generate_series(1,100);  
insert into abc_tmp select generate_series(1,1000);

3、開啟一個事務，查詢ABC表，持有共享鎖

begin;  
select * from abc limit 1;  
  id    
------  
    1

4、在另一個會話中切換表abc, abc_tmp。瞬間切換成功

select * from exchange_tab(  
  'public',  
  'abc',  
  'abc_tmp',  
  'abc_notexists',  
  1,  
  10  
);  
  
  
 exchange_tab   
--------------  
 t  
(1 row)

5、查詢abc表的會話已被殺

postgres=# select * from abc;  
FATAL:  terminating connection due to administrator command  
server closed the connection unexpectedly  
        This probably means the server terminated abnormally  
        before or while processing the request.  
The connection to the server was lost. Attempting reset: Succeeded.

6、檢查是否切換成功

postgres=# select * from exchange_tab(  
  'public',  
  'abc',  
  'abc_tmp',  
  'abc_notexists',  
  1,  
  10  
);  
 exchange_tab   
--------------  
 t  
(1 row)  
  
postgres=# select count(*) from abc;  
 count   
-------  
  1000  
(1 row)  
  
postgres=# select count(*) from abc_tmp;  
 count   
-------  
   100  
(1 row)  
  
postgres=# select * from exchange_tab(  
  'public',  
  'abc',  
  'abc_tmp',  
  'abc_notexists',  
  1,  
  10  
);  
 exchange_tab   
--------------  
 t  
(1 row)  
  
postgres=# select count(*) from abc;  
 count   
-------  
   100  
(1 row)  
  
postgres=# select count(*) from abc_tmp;  
 count   
-------  
  1000  
(1 row)

https://github.com/digoal/blog/blob/master/201807/20180725_04.md#%E5%88%87%E6%8D%A2%E8%A1%A81%E5%92%8C%E8%A1%A82%E7%9A%84%E5%87%BD%E6%95%B0%E6%8E%A5%E5%8F%A3%E5%A6%82%E4%B8%8B%E5%8F%AF%E6%8E%A7%E7%89%88 切換表1和表2的函數接口如下(可控版)

1、增加一個參數，是否殺持鎖會話（暴力）。

2、增加一個SLEEP參數，在重試前，等待若幹秒。

create or replace function exchange_tab( 
  nsp name,       -- schema name  
  from_tab name,  -- 表名1  
  to_tab name,    -- 表名2  
  mid_tab name,   -- 中間表名（使用不存在的表）  
  timeout_s int,  -- 鎖逾時時間（秒），建議設小一點，比如1秒  
  retry int,      -- 重試幾次  
  kill boolean default false,  -- 是否執行terminate backend
  sleepts int default 1,  -- 重試前睡眠多少秒
  steps int default 1    -- 重試次數判斷  
) returns boolean as $$  
declare  
begin  
  -- 檢查中間表是否已存在，存在則報錯  
  perform 1 from pg_class where relname=mid_tab and relnamespace=(select oid from pg_namespace where nspname=nsp) limit 1;  
  if found then  
    raise notice 'you should use not exists table for exchange.';  
    return false;  
  end if;  
    
  -- 如果重試次數達到，還沒有切換成功，則傳回切換不成功  
  if steps >= retry then  
    return false;  
  end if;  
    
  -- 設定鎖逾時  
  execute format('set local lock_timeout=%L;', timeout_s||'s');  
  
  if kill then
    -- 殺死持有 表1，表2 鎖的會話  
    -- 如果是普通使用者，隻能殺死同名使用者下的其他會話，是以如果持鎖的是其他使用者，則需要使用超級使用者才能殺死  
    perform pg_terminate_backend(pid) from pg_stat_activity where   
      pid in (select pid from pg_locks where   
                database=(select oid from pg_database where datname=current_database())   
                and relation in ((nsp||'.'||from_tab)::regclass, (nsp||'.'||to_tab)::regclass)  
             )   
      and pid<>pg_backend_pid();  
  end if;
  
  -- 對表1，表2 加排他鎖  
  execute format('lock table %I.%I in ACCESS EXCLUSIVE mode;', nsp, from_tab);  
  execute format('lock table %I.%I in ACCESS EXCLUSIVE mode;', nsp, to_tab);  
    
  -- 切換表1，表2  
  execute format('alter table %I.%I rename to %I;', nsp, to_tab, mid_tab);  
  execute format('alter table %I.%I rename to %I;', nsp, from_tab, to_tab);  
  execute format('alter table %I.%I rename to %I;', nsp, mid_tab, from_tab);  
    
  -- 傳回切換成功  
  return true;  
  
  -- 任何一步失敗（比如鎖逾時異常），則重試  
  exception when others then  
    -- 重試次數顯示  
    raise notice 'retry: %', steps;  
      
    -- 睡眠
    perform pg_sleep(sleepts);
    
    -- 如果重試次數達到，還沒有切換成功，則傳回切換不成功  
    if steps >= retry then  
      return false;  
    else  
      -- 遞歸調用，重試，傳入參數重試次數+1.    
      return exchange_tab(nsp, from_tab, to_tab, mid_tab, timeout_s, retry, kill, sleepts, steps+1);  
    end if;  
end;  
$$ language plpgsql strict;

https://github.com/digoal/blog/blob/master/201807/20180725_04.md#ab%E8%A1%A8%E5%88%87%E6%8D%A2%E6%97%B6%E5%A6%82%E6%9E%9C%E5%85%B6%E4%BB%96sql%E8%A2%AB%E9%94%81%E7%AD%89%E5%BE%85%E5%A0%B5%E5%A1%9E%E5%A0%B5%E5%A1%9E%E9%87%8A%E6%94%BE%E5%90%8E%E5%88%B0%E5%BA%95%E6%93%8D%E4%BD%9C%E7%9A%84%E6%98%AFa%E8%A1%A8%E8%BF%98%E6%98%AFb%E8%A1%A8 ab表切換時，如果其他SQL被鎖等待堵塞，堵塞釋放後到底操作的是a表還是b表？

https://github.com/digoal/blog/blob/master/201807/20180725_04.md#%E6%8F%92%E5%85%A5%E6%93%8D%E4%BD%9C 插入操作

1、建表

create table abc(id int);  
  
create table abc_tmp(id int);

2、插入測試，傳回目前插入的是哪個表

postgres=# insert into abc values(1) returning *,tableoid,tableoid::regclass;  
 id | tableoid | tableoid   
----+----------+----------  
  1 |    26746 | abc  
(1 row)  
  
INSERT 0 1

3、開啟切換流程，先鎖住2個要被切換的表

postgres=# begin;  
BEGIN  
postgres=# lock table abc in access exclusive mode ;  
LOCK TABLE  
postgres=# lock table abc_tmp in access exclusive mode ;  
LOCK TABLE

4、開啟另一個會話，插入，并傳回目前插入的是哪個表？

postgres=# insert into abc values(1) returning *,tableoid,tableoid::regclass;

堵塞，等待

5、切換流程繼續，切換AB表

postgres=# alter table abc rename to abc_mid;  
ALTER TABLE  
postgres=# alter table abc_tmp rename to abc;  
ALTER TABLE  
postgres=# alter table abc_mid rename to abc_tmp;  
ALTER TABLE  
postgres=# end;  
COMMIT

6、堵塞結束，到底插入了哪個表？

id | tableoid | tableoid   
----+----------+----------  
  1 |    26743 | abc  
(1 row)  
  
INSERT 0 1

結論：插入了切換後的ABC表

https://github.com/digoal/blog/blob/master/201807/20180725_04.md#%E6%9B%B4%E6%96%B0%E6%93%8D%E4%BD%9C 更新操作

接上面的表

truncate abc;  
truncate abc_tmp;  
  
insert into abc values (1);  
insert into abc_tmp values (1);

1、更新測試，傳回目前插入的是哪個表

update abc set id=2 returning *,ctid,tableoid,tableoid::regclass;  
  
 id | ctid  | tableoid | tableoid   
----+-------+----------+----------  
  2 | (0,2) |    26743 | abc  
(1 row)  
  
UPDATE 1

2、開啟切換流程，先鎖住2個要被切換的表

postgres=# begin;  
BEGIN  
postgres=# lock table abc in access exclusive mode ;  
LOCK TABLE  
postgres=# lock table abc_tmp in access exclusive mode ;  
LOCK TABLE

3、開啟另一個會話，插入，并傳回目前插入的是哪個表？

postgres=# update abc set id=2 returning *,ctid,tableoid,tableoid::regclass;

4、切換流程繼續，切換AB表

postgres=# alter table abc rename to abc_mid;  
ALTER TABLE  
postgres=# alter table abc_tmp rename to abc;  
ALTER TABLE  
postgres=# alter table abc_mid rename to abc_tmp;  
ALTER TABLE  
postgres=# end;  
COMMIT

5、堵塞結束，到底更新了哪個表？

id | ctid  | tableoid | tableoid   
----+-------+----------+----------  
  2 | (0,2) |    26746 | abc  
(1 row)  
  
UPDATE 1

結論：更新了切換後的ABC表

https://github.com/digoal/blog/blob/master/201807/20180725_04.md#%E7%BB%B4%E7%B3%BB%E5%90%8C%E6%A0%B7%E7%B4%A2%E5%BC%95%E7%BA%A6%E6%9D%9F%E9%BB%98%E8%AE%A4%E5%80%BC%E7%9A%84ab%E8%A1%A8%E5%88%87%E6%8D%A2 維系同樣索引、限制、預設值的AB表切換

業務上通常會使用中間表，加載全量資料後，再與業務表切換。除了資料，還需要考慮索引、限制、預設值等與業務表保持一緻。

https://github.com/digoal/blog/blob/master/201807/20180725_04.md#%E4%BE%8B%E5%AD%90-1

1、交換表UDF(包括索引)

create or replace function exchange_table(v_nsp name, v_old_tbl name, v_new_tbl name) returns void as $$  
declare  
  idx_def text[];  
  sql text;  
begin  
  set lock_timeout ='5s';  
  select array_agg(regexp_replace(indexdef,'INDEX (.+) ON (.+) ','INDEX i'||to_char(clock_timestamp(),'yyyymmddhh24miss')||'_'||rn||' ON '||quote_ident(v_nsp)||'.'||quote_ident(v_new_tbl))) 
    into idx_def 
    from (select *,row_number() over() rn from pg_indexes where schemaname=v_nsp and tablename=v_old_tbl) t;  

  foreach sql in array idx_def   
  loop  
    execute sql;     
  end loop;  
  -- 如果索引非常多，可以異步并行建立，所有索引建立完成後再切換表  
  -- PG 11版本，不需要異步建立，因為單個索引的建立已經支援并行  
  execute format('drop table %I.%I', v_nsp, v_old_tbl);  
  execute format('alter table %I.%I rename to %I', v_nsp, v_new_tbl, v_old_tbl);  
end;  
$$ language plpgsql strict;

PG11 并行建立索引例子

《PostgreSQL 快速給指定表每個字段建立索引》

異步并行調用參考

《阿裡雲RDS PostgreSQL OSS 外部表實踐 - (dblink異步調用封裝并行) 從OSS并行導入資料》 《在PostgreSQL中跑背景長任務的方法 - 使用dblink異步接口》

以上UDF，結合

《PostgreSQL dblink異步調用實踐,跑并行多任務 - 例如開N個并行背景任務建立索引, 開N個背景任務跑若幹SQL》

 改成并行建立

create or replace function exchange_table(v_nsp name, v_old_tbl name, v_new_tbl name) returns void as $$  
declare  
  idx_def text[];  
  sql text;  
  sqls text[];      
begin  
  
  set lock_timeout ='5s';  
  select array_agg(regexp_replace(indexdef,'INDEX (.+) ON (.+) ','INDEX i'||to_char(clock_timestamp(),'yyyymmddhh24miss')||'_'||rn||' ON '||quote_ident(v_nsp)||'.'||quote_ident(v_new_tbl))) 
    into idx_def 
    from (select *,row_number() over() rn from pg_indexes where schemaname=v_nsp and tablename=v_old_tbl) t;  
  foreach sql in array idx_def   
  loop  
    sqls := array_append(sqls, sql);  
  end loop;  
  -- 如果索引非常多，可以異步并行建立，所有索引建立完成後再切換表  
  -- PG 11版本，不需要異步建立，因為單個索引的建立已經支援并行  

  -- 并行
  perform run_sqls_parallel(  
    16,   -- 并行度  
    sqls  -- 執行index SQL數組  
  );  

  -- 切換表名
  execute format('drop table %I.%I', v_nsp, v_old_tbl);  
  execute format('alter table %I.%I rename to %I', v_nsp, v_new_tbl, v_old_tbl);  
end;  
$$ language plpgsql strict;

2、DEMO

業務表\索引

create table tbl(id int primary key, info text not null, crt_time timestamp default now());  
create index idx_tbl_1 on tbl (crt_time);  
insert into tbl select generate_series(1,100),md5(random()::text);

臨時表（無索引，隻包含與業務表一樣的限制）

create table tmp (like tbl including CONSTRAINTS including DEFAULTS);

加載資料到臨時表(demo)

insert into tmp select generate_series(1,200),md5(random()::text);

臨時表切換為業務表，并加與業務表相同的索引

select exchange_table('public', 'tbl', 'tmp');  
  
postgres=# select count(*) from tbl;  
 count   
-------  
   200  
(1 row)  
  
postgres=# select * from tmp;  
ERROR:  relation "tmp" does not exist  
LINE 1: select * from tmp;  
                      ^  
           
postgres=# \d tbl  
                           Table "public.tbl"  
  Column  |            Type             | Collation | Nullable | Default   
----------+-----------------------------+-----------+----------+---------  
 id       | integer                     |           | not null |   
 info     | text                        |           | not null |   
 crt_time | timestamp without time zone |           |          | now()  
Indexes:  
    "i_20180827222503_1" UNIQUE, btree (id)  
    "i_20180827222503_2" btree (crt_time)

https://github.com/digoal/blog/blob/master/201807/20180725_04.md#%E5%B0%8F%E7%BB%93 小結

使用本文提到的方法對AB表進行切換，可以完全杜絕雪崩。同時，如果有正在使用AB表的會話，會被回退，由于切換AB表僅涉及中繼資料的更新，是以切換很快，影響很小。

注意，使用切換表的方法，實際上有一個問題是依賴關系的問題，如果依賴關系複雜,RENMAE的方式，需要注意依賴關系的切換，包括主外鍵，繼承，分區等。

是以，對于依賴關系複雜的情況，更好的方法是類似pg_repack的方法，切換表的filenode(即資料檔案映射關系，當然還需要注意TOAST也要切換)，而不是RENAME TABLE。

https://github.com/digoal/blog/blob/master/201807/20180725_04.md#%E5%8F%82%E8%80%83 參考

《PostgreSQL 鎖等待監控 珍藏級SQL - 誰堵塞了誰》
sql 移動開發 關系型資料庫 postgresql 索引 command失效 時間點restore command 執行mkfs command mssql最佳實踐使用非對稱 vsftpd連接配接command
上一篇: PostgreSQL 基于PG内置流複制的，靠譜的PostgreSQL高可用方案
下一篇: PostgreSQL SRF (set-returning functions) 函數where, index實作

繼續閱讀