283
技術社區[雲棲]
數據庫平滑switchover的要素 - 會話資源漂移
標簽
PostgreSQL , 中間件 , 連接池 , 平滑切換 , 會話設置 , 會話狀態 , 綁定變量語句 , prepared statement
背景
數據庫遷移、切換是很普遍的一個話題，但是大多數的方案，對用戶來說都是有感知的，所以用戶的體驗並不好。
例如用戶使用了綁定變量語句，主備角色切換後綁定變量語句沒有了，會導致調用報錯。
我們需要維護主庫的硬件，那麼可以在中間件層麵，將主備數據庫的角色進行平滑調換，維護好硬件，再平滑的調換回來。
數據庫主備切換時，如何做到會話級無感知？首先我們要了解會話中都有哪些內容，哪些內容是需要隨角色切換一起遷移的。從而做到用戶無感知。
（本文HA指中間件層級的HA，並非APP直連數據庫，VIP切換的那種HA。）
簡單的switchover過程舉例：
等待所有會話的事務結束，會話都處於idle狀態，凍結會話，不允許提交任何SQL，然後進行角色切換，並將每個會話的資源狀態平移。
會話資源狀態
會話中有些什麼狀態？通過discard這條SQL就可以了解。
https://www.postgresql.org/docs/10/static/sql-discard.html
DISCARD — discard session state
discard all相當於執行如下
SET SESSION AUTHORIZATION DEFAULT;
RESET ALL;
DEALLOCATE ALL;
CLOSE ALL;
UNLISTEN *;
SELECT pg_advisory_unlock_all();
DISCARD PLANS;
DISCARD SEQUENCES;
DISCARD TEMP;
會話資源中目前可能包含如下（每個PG版本可能有些許差異）：
SESSOIN角色、參數設置、綁定變量語句、遊標、異步消息監聽、AD鎖、序列、臨時表等。
下麵介紹一下每種資源的查詢方法，以及在新的主庫上進行資源複原的方法。
一、SESSION AUTHORIZATION
超級用戶可以將會話用戶設置為其他用戶，普通用戶無權切換用戶。
當前用戶為postgres，設置SESSION AUTHORIZATION為test
postgres=# set SESSION AUTHORIZATION test;
SET
postgres=> show SESSION AUTHORIZATION;
session_authorization
-----------------------
test
(1 row)
postgres=> select usename from pg_stat_activity where pid=pg_backend_pid();
usename
----------
postgres
(1 row)
查詢方法
postgres=> show SESSION AUTHORIZATION;
session_authorization
-----------------------
test
(1 row)
postgres=> select usename from pg_stat_activity where pid=pg_backend_pid();
usename
----------
postgres
(1 row)
複原方法
當pg_stat_activity.usename不等於SESSION AUTHORIZATION時，需要通過如下方法複原它。
postgres=# set SESSION AUTHORIZATION test;
SET
二、參數
PostgreSQL的一些參數是允許用戶在會話、事務中進行設置的。如下context in ('user','superuser')時，用戶可以在會話或事務中設置。
postgres=# select distinct context from pg_settings ;
context
-------------------
superuser-backend
sighup
superuser
postmaster
internal
user
backend
(7 rows)
設置例子
postgres=> set tcp_keepalives_count=1;
SET
source表示參數來自哪裏的設置，如果來自會話或事務級設置，則顯示session
postgres=> select distinct source from pg_settings ;
source
----------------------
session
default
command line
configuration file
client
override
environment variable
(7 rows)
重置方法
postgres=# reset tcp_keepalives_count;
RESET
postgres=# select name,setting,reset_val,source,context from pg_settings where name='tcp_keepalives_count';
name | setting | reset_val | source | context
----------------------+---------+-----------+---------+---------
tcp_keepalives_count | 3 | 0 | default | user
(1 row)
查詢方法
postgres=# select name,setting,reset_val,source,context from pg_settings where source ='session' and setting<>reset_val;
name | setting | reset_val | source | context
----------------------+---------+-----------+---------+---------
tcp_keepalives_count | 1 | 0 | session | user
(1 row)
複原方法
postgres=> set tcp_keepalives_count=1;
SET
三、綁定變量語句
使用綁定變量可以減少數據庫的parser, plan開銷，提高高並發的查詢性能，同時避免SQL注入。
不同的驅動，有不同的使用方法。
https://www.postgresql.org/docs/10/static/libpq-exec.html
使用綁定變量的例子
CREATE OR REPLACE FUNCTION public.getps()
RETURNS void
LANGUAGE plpgsql
STRICT
AS $function$
declare
rec record;
begin
for rec in select t from pg_prepared_statements t loop
raise notice '%', (rec.*)::text;
end loop;
end;
$function$;
create table ps(id int primary key, info text);
insert into ps select generate_series(1,10000), 'test';
vi test.sql
\set id random(1,10000)
select * from ps where id=:id;
select getps();
使用綁定變量的模式，調用SQL
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 1 -j 1
NOTICE: ("(P0_1,""select * from ps where id=$1;"",""2017-06-19 15:22:21.821454+08"",{integer},f)")
NOTICE: ("(P0_2,""select getps();"",""2017-06-19 15:22:21.822045+08"",{},f)")
.....
查詢方法
postgres=# \d pg_prepared_statements
View "pg_catalog.pg_prepared_statements"
Column | Type | Collation | Nullable | Default
-----------------+--------------------------+-----------+----------+---------
name | text | | |
statement | text | | |
prepare_time | timestamp with time zone | | |
parameter_types | regtype[] | | |
from_sql | boolean | | |
postgres=# select * from pg_prepared_statements;
name | statement | prepare_time | parameter_types | from_sql
------+-----------+--------------+-----------------+----------
(0 rows)
postgres=# prepare a(int) as select * from ps where id=$1;
PREPARE
postgres=# execute a(1);
id | info
----+------
1 | test
(1 row)
postgres=# select * from pg_prepared_statements;
name | statement | prepare_time | parameter_types | from_sql
------+-------------------------------------------------+------------------------------+-----------------+----------
a | prepare a(int) as select * from ps where id=$1; | 2017-06-19 15:23:24.68617+08 | {integer} | t
(1 row)
複原方法
不同的驅動，複原方法不一樣。
請根據pg_prepared_statements的內容進行複原。
NOTICE: ("(P0_1,""select * from ps where id=$1;"",""2017-06-19 15:22:21.821454+08"",{integer},f)")
PGresult *PQprepare(PGconn *conn,
const char *stmtName,
const char *query,
int nParams,
const Oid *paramTypes);
四、遊標
如果我們使用了hold選項，那麼遊標不會隨事務結束而關閉，因此在遷移會話時也需要注意是否有這類遊標。
postgres=# \h declare
Command: DECLARE
Description: define a cursor
Syntax:
DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
CURSOR [ { WITH | WITHOUT } HOLD ] FOR query
postgres=# begin;
BEGIN
postgres=# declare cur cursor with hold for select * from ps where id=1;
DECLARE CURSOR
postgres=# end;
COMMIT
postgres=# select * from pg_cursors ;
name | statement | is_holdable | is_binary | is_scrollable | creation_time
------+---------------------------------------------------------------+-------------+-----------+---------------+-------------------------------
cur | declare cur cursor with hold for select * from ps where id=1; | t | f | t | 2017-06-19 15:27:58.604183+08
(1 row)
postgres=# close cur;
CLOSE CURSOR
postgres=# select * from pg_cursors ;
name | statement | is_holdable | is_binary | is_scrollable | creation_time
------+-----------+-------------+-----------+---------------+---------------
(0 rows)
查詢方法
postgres=# select * from pg_cursors ;
name | statement | is_holdable | is_binary | is_scrollable | creation_time
------+---------------------------------------------------------------+-------------+-----------+---------------+-------------------------------
cur | declare cur cursor with hold for select * from ps where id=1; | t | f | t | 2017-06-19 15:27:58.604183+08
(1 row)
複原方法
postgres=# declare cur cursor with hold for select * from ps where id=1;
DECLARE CURSOR
五、異步消息監聽
PostgreSQL的異步消息，可以通過異步消息，推送事件。例子如下：
https://www.postgresql.org/docs/10/static/libpq-notify.html
https://www.postgresql.org/docs/10/static/libpq-example.html
postgres=# listen a;
LISTEN
postgres=# notify a , 'hello i am digoal';
NOTIFY
Asynchronous notification "a" with payload "hello i am digoal" received from server process with PID 21412.
查詢方法
查詢已經開啟了哪些異步監聽
postgres=# select pg_listening_channels();
pg_listening_channels
-----------------------
a
(1 row)
複原方法
postgres=# listen a;
LISTEN
六、advisory lock
advisory lock可以用於秒殺、解決高並發鎖衝突問題、解決無空洞序列值問題等。
《PostgreSQL 使用advisory lock實現行級讀寫堵塞》
《PostgreSQL 無縫自增ID的實現 - by advisory lock》
《PostgreSQL 使用advisory lock或skip locked消除行鎖衝突, 提高幾十倍並發更新效率》
postgres=# \df *.*advis*
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+----------------------------------+------------------+---------------------+--------
pg_catalog | pg_advisory_lock | void | bigint | normal
pg_catalog | pg_advisory_lock | void | integer, integer | normal
pg_catalog | pg_advisory_lock_shared | void | bigint | normal
pg_catalog | pg_advisory_lock_shared | void | integer, integer | normal
pg_catalog | pg_advisory_unlock | boolean | bigint | normal
pg_catalog | pg_advisory_unlock | boolean | integer, integer | normal
pg_catalog | pg_advisory_unlock_all | void | | normal
pg_catalog | pg_advisory_unlock_shared | boolean | bigint | normal
pg_catalog | pg_advisory_unlock_shared | boolean | integer, integer | normal
pg_catalog | pg_advisory_xact_lock | void | bigint | normal
pg_catalog | pg_advisory_xact_lock | void | integer, integer | normal
pg_catalog | pg_advisory_xact_lock_shared | void | bigint | normal
pg_catalog | pg_advisory_xact_lock_shared | void | integer, integer | normal
pg_catalog | pg_try_advisory_lock | boolean | bigint | normal
pg_catalog | pg_try_advisory_lock | boolean | integer, integer | normal
pg_catalog | pg_try_advisory_lock_shared | boolean | bigint | normal
pg_catalog | pg_try_advisory_lock_shared | boolean | integer, integer | normal
pg_catalog | pg_try_advisory_xact_lock | boolean | bigint | normal
pg_catalog | pg_try_advisory_xact_lock | boolean | integer, integer | normal
pg_catalog | pg_try_advisory_xact_lock_shared | boolean | bigint | normal
pg_catalog | pg_try_advisory_xact_lock_shared | boolean | integer, integer | normal
(21 rows)
advisory lock分為事務級鎖和會話級鎖，在會話遷移時，會話處於IDLE狀態， 隻需要關注會話級鎖。
postgres=# select pg_try_advisory_lock(1);
pg_try_advisory_lock
----------------------
t
(1 row)
查詢方法
postgres=# select * from pg_locks where locktype='advisory' and pid=pg_backend_pid();
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------+---------+----------
advisory | 13158 | | | | | | 0 | 1 | 1 | 3/123301864 | 21412 | ExclusiveLock | t | f
(1 row)
複原方法
注意複原時，需要指定是否為shared lock。
postgres=# select pg_try_advisory_lock(1);
pg_try_advisory_lock
----------------------
t
(1 row)
七、序列
序列使用後，會在會話中存儲最後一次使用的序列的VAL，以及每個序列被使用後的最後一次獲取的VAL。
postgres=# create sequence seq1;
CREATE SEQUENCE
沒有被調用的序列，返回錯誤。
postgres=# select currval('seq');
ERROR: currval of sequence "seq" is not yet defined in this session
沒有調用過任何序列，返回錯誤。
postgres=# select lastval();
ERROR: lastval is not yet defined in this session
調用序列
postgres=# select nextval('seq1');
nextval
---------
1
(1 row)
返回會話中指定序列最後一次調用的VAL
postgres=# select currval('seq1');
currval
---------
1
(1 row)
返回整個會話中最後一次序列調用的VAL
postgres=# select lastval();
lastval
---------
1
(1 row)
查詢方法
postgres=# select * from seq1;
last_value | log_cnt | is_called
------------+---------+-----------
1 | 32 | t
(1 row)
postgres=# select nextval('seq1');
nextval
---------
2
(1 row)
postgres=# select * from seq1;
last_value | log_cnt | is_called
------------+---------+-----------
2 | 31 | t
(1 row)
複原方法
序列雖然可以設置當前值，但是會影響全局，強烈建議不要這麼做。
目前沒有好的方法複原序列在會話中的lastval。
八、臨時表
postgres=# create temp table tmp(id int, info text);
CREATE TABLE
postgres=# select oid,relname from pg_class where relpersistence ='t' and relkind='r' and pg_table_is_visible(oid);
oid | relname
-------+---------
44804 | tmp
(1 row)
查詢方法
postgres=# select oid,relname from pg_class where relpersistence ='t' and relkind='r' and pg_table_is_visible(oid);
oid | relname
-------+---------
44804 | tmp
(1 row)
********* QUERY **********
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 ~ '^(tmp)$'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
**************************
********* QUERY **********
SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, c.relhasoids, 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
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
WHERE c.oid = '44810';
**************************
********* QUERY **********
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, a.attnum,
(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,
NULL AS indexdef,
NULL AS attfdwoptions,
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 = '44810' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;
**************************
********* QUERY **********
SELECT inhparent::pg_catalog.regclass, pg_get_expr(c.relpartbound, inhrelid), pg_get_partition_constraintdef(inhrelid) FROM pg_catalog.pg_class c JOIN pg_catalog.pg_inherits ON c.oid = inhrelid WHERE c.oid = '44810' AND c.relispartition;
**************************
********* QUERY **********
SELECT pol.polname, pol.polpermissive,
CASE WHEN pol.polroles = '{0}' THEN NULL ELSE array_to_string(array(select rolname from pg_roles where oid = any (pol.polroles) order by 1),',') END,
pg_catalog.pg_get_expr(pol.polqual, pol.polrelid),
pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid),
CASE pol.polcmd
WHEN 'r' THEN 'SELECT'
WHEN 'a' THEN 'INSERT'
WHEN 'w' THEN 'UPDATE'
WHEN 'd' THEN 'DELETE'
END AS cmd
FROM pg_catalog.pg_policy pol
WHERE pol.polrelid = '44810' ORDER BY 1;
**************************
********* QUERY **********
SELECT oid, stxrelid::pg_catalog.regclass, stxnamespace::pg_catalog.regnamespace AS nsp, stxname,
(SELECT pg_catalog.string_agg(pg_catalog.quote_ident(attname),', ')
FROM pg_catalog.unnest(stxkeys) s(attnum)
JOIN pg_catalog.pg_attribute a ON (stxrelid = a.attrelid AND
a.attnum = s.attnum AND NOT attisdropped)) AS columns,
(stxkind @> '{d}') AS ndist_enabled,
(stxkind @> '{f}') AS deps_enabled
FROM pg_catalog.pg_statistic_ext stat WHERE stxrelid = '44810'
ORDER BY 1;
**************************
********* QUERY **********
SELECT pub.pubname
FROM pg_catalog.pg_publication pub
LEFT JOIN pg_catalog.pg_publication_rel pr
ON (pr.prpubid = pub.oid)
WHERE pr.prrelid = '44810' OR pub.puballtables
ORDER BY 1;
**************************
********* QUERY **********
SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '44810' AND c.relkind != 'p' ORDER BY inhseqno;
**************************
********* QUERY **********
SELECT c.oid::pg_catalog.regclass, pg_get_expr(c.relpartbound, c.oid) FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '44810' AND EXISTS (SELECT 1 FROM pg_class c WHERE c.oid = '44810') ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;
**************************
Table "pg_temp_3.tmp"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
info | text | | | | extended | |
複原方法
postgres=# create temp table tmp(id int, info text);
CREATE TABLE
小結
主備切換時，將會話資源狀態進行平移，可以大幅提升客戶端的體驗，使得數據庫硬件維護、遷移等工作也會變得更加輕鬆。
中間件需要維護客戶端連接和數據庫會話的映射關係，平移後映射關係同樣需要保持一致。
參考
https://www.postgresql.org/docs/10/static/libpq-exec.html
https://www.postgresql.org/docs/10/static/sql-discard.html
最後更新：2017-06-19 19:31:44