創(chuàng)建一個(gè)測(cè)試表
|
1
2
3
4
5
6
7
8
|
postgres=# create table t1(a int);CREATE TABLEpostgres=# select pg_relation_filepath('t1'); pg_relation_filepath---------------------- base/75062/75297(1 row) postgres=# |
在操作系統(tǒng)上已經(jīng)可以看到該文件。
|
1
2
|
$ ls -la $PGDATA/base/75062/75297-rw------- 1 postgres postgres 0 Nov 9 11:11 /data/pgdata/11/data/base/75062/75297 |
插入一些數(shù)據(jù)：
|
1
2
3
4
5
6
7
8
|
postgres=# show segment_size; segment_size-------------- 1GB(1 row)postgres=# insert into t1 select * from generate_series(1,100000000);INSERT 0 100000000postgres=# |
因?yàn)閟egment_size的設(shè)置為1GB，磁盤(pán)上已經(jīng)有了多個(gè)文件
|
1
2
3
4
5
6
|
$ ls -la $PGDATA/base/75062/75297*-rw------- 1 postgres postgres 1073741824 Nov 9 11:19 /data/pgdata/11/data/base/75062/75297-rw------- 1 postgres postgres 1073741824 Nov 9 11:17 /data/pgdata/11/data/base/75062/75297.1-rw------- 1 postgres postgres 1073741824 Nov 9 11:18 /data/pgdata/11/data/base/75062/75297.2-rw------- 1 postgres postgres 439803904 Nov 9 11:19 /data/pgdata/11/data/base/75062/75297.3-rw------- 1 postgres postgres 917504 Nov 9 11:18 /data/pgdata/11/data/base/75062/75297_fsm |
現(xiàn)在，開(kāi)啟另一個(gè)會(huì)話(huà)(session 2)。
在session2中，啟動(dòng)一個(gè)事務(wù)并創(chuàng)建一個(gè)空表，但是不提交事務(wù)：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
postgres=# begin;BEGINpostgres=# create table t2(a int);CREATE TABLEpostgres=# select pg_relation_filepath('t2'); pg_relation_filepath---------------------- base/75062/75300(1 row) postgres=# select * from pg_backend_pid(); pg_backend_pid---------------- 17710(1 row) postgres=# |
在操作系統(tǒng)已經(jīng)可以看到對(duì)應(yīng)的文件：
|
1
2
|
$ ls -la $PGDATA/base/75062/75300-rw------- 1 postgres postgres 0 Nov 9 11:23 /data/pgdata/11/data/base/75062/75300 |
如果這個(gè)時(shí)候，posrgresql server發(fā)生了奔潰、或者發(fā)生了oom被kill了或者session被kill了。會(huì)發(fā)生什么呢？
我們來(lái)模擬一下session被kill的場(chǎng)景：
|
1
|
$ kill -9 17710 |
再次在session2中執(zhí)行查詢(xún)：
|
1
2
3
4
5
6
|
postgres=# select 1;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.postgres=# |
這個(gè)session在事務(wù)提交之前被kill了，事務(wù)無(wú)法正常完成，但是事務(wù)已經(jīng)創(chuàng)建了一個(gè)表。應(yīng)該發(fā)生什么呢？事務(wù)被回滾，創(chuàng)建的表應(yīng)該不存在了。
|
1
2
3
4
5
|
postgres=# select * from t2;ERROR: relation "t2" does not existLINE 1: select * from t2; ^postgres=# |
這正是我們所預(yù)期的。但在操作系統(tǒng)上，文件仍然存在：
|
1
2
|
$ ls -la $PGDATA/base/75062/75300-rw------- 1 postgres postgres 0 Nov 9 11:23 /data/pgdata/11/data/base/75062/75300 |
這樣，文件就成了孤兒文件(orphaned file)。
postgresql并不知道這個(gè)文件屬于哪個(gè)relation
|
1
2
3
4
5
|
postgres=# select relname from pg_class where oid = '75300'; relname---------(0 rows) postgres=# |
這樣，你就需要自己手動(dòng)清理孤兒文件了！
假設(shè)你做了大量的數(shù)據(jù)的加載，就在加載完成之前，會(huì)話(huà)被殺死:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
postgres=# begin;BEGINpostgres=# create table t3(a int);CREATE TABLEpostgres=# select pg_relation_filepath('t3'); pg_relation_filepath---------------------- base/75062/99528(1 row) postgres=# select * from pg_backend_pid(); pg_backend_pid---------------- 21988(1 row) postgres=# insert into t3 select * from generate_series(1,1000000000);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: Failed. |
雖然會(huì)話(huà)被kill了。但是磁盤(pán)上的空間并沒(méi)有被釋放。
|
1
2
3
4
|
$ ls -la $PGDATA/base/75062/99528*-rw------- 1 postgres postgres 1073741824 Nov 9 11:51 /data/pgdata/11/data/base/75062/99528-rw------- 1 postgres postgres 413777920 Nov 9 11:51 /data/pgdata/11/data/base/75062/99528.1-rw------- 1 postgres postgres 385024 Nov 9 11:51 /data/pgdata/11/data/base/75062/99528_fsm |
在最糟糕的時(shí)候，可能會(huì)占用大量的磁盤(pán)空間。那是否有什么方法去檢測(cè)這些孤兒文件呢？
你需要比較postgresql中的目錄表中的記錄和文件系統(tǒng)上信息，然后刪除這些孤兒文件。這個(gè)過(guò)程需要小心謹(jǐn)慎。
首先獲得你要檢測(cè)的數(shù)據(jù)庫(kù)的oid：
|
1
2
3
4
5
6
|
postgres=# select oid from pg_database where datname = 'postgres'; oid ------- 75062(1 row) postgres=# |
這樣就可以知道文件在文件系統(tǒng)上的位置。即 $PGDATA/base/[OID_OF_THE_DATABASE]
然后，獲得孤兒文件：
|
1
2
3
4
5
6
7
8
9
10
11
12
|
postgres=# select * from pg_ls_dir ( '/data/pgdata/11/data/base/75062' ) as file where file ~ '^[0-9]*$' and file::text not in (select oid::text from pg_class ); file ------- 75280 75281 75282 75283 75300 83144 99528(7 rows) postgres=# |
補(bǔ)充：理解postgreSQL中的prepared transactions和處理孤兒(orphans)事務(wù)
Prepared transactions是PostgreSQL的一個(gè)關(guān)鍵特性。理解該特性提供的功能和處理任何潛在的陷阱對(duì)于系統(tǒng)的維護(hù)是很關(guān)鍵的。所以，我們來(lái)深入研究一下具體什么是prepared transactions。
關(guān)于事務(wù)
在數(shù)據(jù)庫(kù)系統(tǒng)中，事務(wù)是一種處理通常包含多個(gè)語(yǔ)句的塊中的全部或零個(gè)語(yǔ)句的方法。在提交整個(gè)塊之前，該塊中語(yǔ)句的結(jié)果對(duì)其他事務(wù)不可見(jiàn)。 如果事務(wù)失敗或回滾，則對(duì)數(shù)據(jù)庫(kù)完全沒(méi)有影響。
事務(wù)依附于會(huì)話(huà)。但是，當(dāng)要執(zhí)行與會(huì)話(huà)獨(dú)立的事務(wù)時(shí)（也有其他好處）。這就是“prepared transactions”的來(lái)源。
prepared transactions
prepared transaction是獨(dú)立于會(huì)話(huà)、抗崩潰、狀態(tài)維護(hù)的事務(wù)。事務(wù)的狀態(tài)存儲(chǔ)在磁盤(pán)上，這使得數(shù)據(jù)庫(kù)服務(wù)器即使在從崩潰中重新啟動(dòng)后也可以恢復(fù)事務(wù)。在對(duì)prepared transaction執(zhí)行回滾或提交操作之前，將一直維護(hù)該事務(wù)。
PostgreSQL文檔聲明，在一個(gè)已存在的事務(wù)塊中，可以使用prepare transaction 'transaction_id‘命令創(chuàng)建一個(gè)prepared transaction。它進(jìn)一步聲明該過(guò)程為兩階段提交準(zhǔn)備了一個(gè)事務(wù)。
此外，建議應(yīng)用程序或交互式會(huì)話(huà)不要使用prepared transaction。理想情況下，外部事務(wù)管理器應(yīng)該跨同構(gòu)或異構(gòu)數(shù)據(jù)庫(kù)資源執(zhí)行原子的全局事務(wù)。
在postgreSQL中，缺省的max_prepared_transaction=0；即關(guān)閉了prepared transaction。如果你想使用prepared transaction，建議將max_prepared_transaction設(shè)置成max_connections的值。在同步的流復(fù)制standby庫(kù)上，最好將其設(shè)置的比max_connections大一點(diǎn)，以免standby不能接收查詢(xún)。
在任何給定的時(shí)間，你可以查看活躍狀態(tài)的prepared transactions，通過(guò)查看視圖pg_prepared_xacts。
pg_prepared_xacts視圖含有以下一些列：
|
1
2
3
4
|
#select * from pg_prepared_xacts; transaction | gid | prepared | owner | database-------------+-----+----------+-------+----------(0 rows) |
1.transaction：事務(wù)id
2.gid：用戶(hù)為prepared transaction定義的名稱(chēng)
3.prepared：prepared日期，創(chuàng)建事務(wù)時(shí)帶有時(shí)區(qū)的時(shí)間戳
4.owner：創(chuàng)建該prepared transaction的事務(wù)
5.database：數(shù)據(jù)庫(kù)名
創(chuàng)建prepared transaction
知道什么是prepared transaction之后，現(xiàn)在來(lái)看看如何創(chuàng)建一個(gè)prepared transaction。創(chuàng)建一個(gè)該事務(wù)通常需要四個(gè)步驟：
1.begin(或start transaction)
2.執(zhí)行需要的操作
3.prepare transaction
4.commit(或rollback prepared)
prepare transaction、commit prepared、或rollback prepared后面加上一個(gè)gid，可以唯一標(biāo)識(shí)prepared transaction。
例如下面的代碼塊：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
postgres=# begin;BEGINpostgres=# create table abce(id int);CREATE TABLEpostgres=# insert into abce values(1);INSERT 0 1postgres=# prepare transaction 'abce_insert';PREPARE TRANSACTIONpostgres=# select * from pg_prepared_xacts; transaction | gid | prepared | owner | database-------------+-------------+-------------------------------+----------+---------- 16362 | abce_insert | 2020-12-09 11:41:45.742375+08 | postgres | postgres(1 row) postgres=# commit prepared 'abce_insert';COMMIT PREPAREDpostgres=# select * from pg_prepared_xacts; transaction | gid | prepared | owner | database-------------+-----+----------+-------+----------(0 rows) postgres=# |
當(dāng)一個(gè)含有一個(gè)或多個(gè)活躍的prepared transactions的postgresql停止了或者奔潰了，會(huì)為每個(gè)活躍的prepared transaction創(chuàng)建一個(gè)文件，在目錄pg_twophase中。
比如，我們有個(gè)prepared transaction：
|
1
2
3
4
5
6
7
|
postgres=# select * from pg_prepared_xacts; transaction | gid | prepared | owner | database-------------+--------------+-------------------------------+----------+---------- 16363 | abce_insert2 | 2020-12-09 11:46:01.983483+08 | postgres | postgres(1 row) postgres=# |
所以我沒(méi)有提交事務(wù)就停止了postgresql server。postgresql就會(huì)創(chuàng)建一個(gè)名為00003FEB的文件，對(duì)應(yīng)于prepared transaction的事務(wù)id。
|
1
2
3
|
$ ls -l ../data/pg_twophase/total 4-rw------- 1 postgres postgres 220 Dec 9 11:47 00003FEB |
00003FEB等價(jià)于16363。在postgresql被重啟后，在啟動(dòng)日志會(huì)報(bào)如下信息：
|
1
2
3
|
2020-12-09 11:51:28.112 CST [963] LOG: database system was shut down at 2020-12-09 11:47:39 CST2020-12-09 11:51:28.113 CST [963] LOG: recovering prepared transaction 16363 from shared memory2020-12-09 11:51:28.132 CST [960] LOG: database system is ready to accept connections |
如果你不希望恢復(fù)一個(gè)prepared transaction，可以簡(jiǎn)單地刪除pg_twophase文件夾下的相應(yīng)文件。
這很簡(jiǎn)單，不是嗎?那么我們?yōu)槭裁床唤?jīng)常地使用它呢?畢竟，它提供了更高的提交操作成功的可能性。事情要是這么簡(jiǎn)單就好了!
prepared transaction可能遇到哪些錯(cuò)誤？
如果客戶(hù)端消失了，則prepared transaction可以未完成(既不提交也不回滾)。發(fā)生這種情況的原因多種多樣，包括客戶(hù)機(jī)崩潰，或者服務(wù)器崩潰導(dǎo)致客戶(hù)機(jī)連接被終止而無(wú)法重新連接。你實(shí)際上是依靠事務(wù)管理器來(lái)確保沒(méi)有孤立的prepared transaction。
除了崩潰之外，還有另一種原因可以使prepared transaction未完成。如果一個(gè)用于恢復(fù)的備份包含了事務(wù)的prepared階段，但是沒(méi)有包含關(guān)閉事務(wù)的階段，仍然會(huì)生成孤兒事務(wù)。
或者，DBA創(chuàng)建了一個(gè)prepared transaction，卻忘記了關(guān)閉它。
所以，如果一個(gè)prepared transaction沒(méi)有完成，又會(huì)有什么大不了的呢?
真正的問(wèn)題
真正的問(wèn)題是，孤兒prepared transaction繼續(xù)持有可能包含鎖的關(guān)鍵系統(tǒng)資源，或者使事務(wù)ID保持活動(dòng)狀態(tài)，該事務(wù)ID可能會(huì)阻止vacuum清除只對(duì)該孤兒事務(wù)可見(jiàn)、對(duì)其它事務(wù)不可見(jiàn)的死的元組。
回想一下我在上面創(chuàng)建的prepared 事務(wù)。當(dāng)事務(wù)prepared，并且在提交該事務(wù)之前，如果另一個(gè)事務(wù)試圖更改該表，它將無(wú)法獲取所需的鎖并掛起，直到解決了prepared事務(wù)（提交或回滾）為止。 否則，alter命令會(huì)無(wú)限期掛起，最終，我必須發(fā)出CTRL + C來(lái)停止該命令。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
postgres=# select * from pg_prepared_xacts; transaction | gid | prepared | owner | database-------------+--------------+-------------------------------+----------+---------- 16363 | abce_insert2 | 2020-12-09 11:46:01.983483+08 | postgres | postgres(1 row) postgres=# alter table abce add column b int;^CCancel request sentERROR: canceling statement due to user requestpostgres=# select c.oid,c.relname,l.locktype,l.relation,l.modepostgres-# from pg_class cpostgres-# inner join pg_locks l on c.oid=l.relationpostgres-# where c.relname='abce'; oid | relname | locktype | relation | mode --------+---------+----------+----------+------------------ 370883 | abce | relation | 370883 | RowExclusiveLock(1 row) postgres=# |
對(duì)vacuum的阻塞可能會(huì)更嚴(yán)重，在極端情況下，會(huì)導(dǎo)致數(shù)據(jù)庫(kù)關(guān)閉，因?yàn)楣聝簆repared事務(wù)會(huì)阻止事務(wù)id的wrap around。
發(fā)現(xiàn)和通知
雖然一般的預(yù)期是prepared事務(wù)在幾秒鐘內(nèi)完成，但是情況并不總是這樣。一個(gè)prepared事務(wù)可能持續(xù)幾分鐘、幾小時(shí)甚至幾天。
為這些事務(wù)維護(hù)元數(shù)據(jù)本身可能是一項(xiàng)挑戰(zhàn)。但是，我建議設(shè)置一個(gè)術(shù)語(yǔ)來(lái)定義prepared事務(wù)可以存在的最大時(shí)間。例如，考慮以下的prepared事務(wù):
|
1
2
3
4
5
6
|
postgres=# BEGIN;BEGINpostgres=# INSERT INTO abce VALUES(3);INSERT 0 1postgres=# PREPARE TRANSACTION 'abce_insert 1m';PREPARE TRANSACTION |
或者下面的事務(wù)：
|
1
2
3
4
5
6
|
postgres=# BEGIN;BEGINpostgres=# INSERT INTO abce VALUES(4);INSERT 0 1postgres=# PREPARE TRANSACTION 'abce_insert 1d';PREPARE TRANSACTION |
在這些事務(wù)名稱(chēng)中，最后一部分定義事務(wù)的時(shí)間。任何超出時(shí)間的事務(wù)可以通過(guò)sql查詢(xún)輕易地找出來(lái)：
|
1
2
3
4
5
6
7
8
9
|
postgres=# select gid,prepared,regexp_replace(gid, '.* ', '') AS agefrom pg_prepared_xactsWHERE prepared + CAST(regexp_replace(gid, '.* ', '') AS INTERVAL) < NOW(); gid | prepared | age----------------+-------------------------------+----- abce_insert 1m | 2020-12-09 13:39:01.383091+08 | 1m(1 row) postgres=# |
這里就很清晰地顯示了一個(gè)不應(yīng)該再有效的事務(wù)。因此，使用一個(gè)外部代理或者cron任務(wù)可以輕易找出這些事務(wù)，或者通知管理員、或者回滾事務(wù)。
在我看來(lái)，這是一種簡(jiǎn)單而容易的方式，可以確保即使事務(wù)管理器失敗或DBA意外地留下了一個(gè)事務(wù)，也可以在你的環(huán)境中管理孤兒事務(wù)。
結(jié)論
Prepared transactions顯然是一個(gè)非常重要的功能，但是需要使用回退通知程序或清理程序仔細(xì)設(shè)置環(huán)境，以輕松確保這些事務(wù)不會(huì)不必要地占用關(guān)鍵資源，并且系統(tǒng)保持良好狀態(tài)。
PostgreSQL社區(qū)中仍在討論如何處理孤兒prepared事務(wù)。它是否成為postgresql核心的一部分尚待觀察。同時(shí)，我們需要使用外部工具來(lái)管理這些事務(wù)，或者設(shè)法解決這個(gè)問(wèn)題。
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持服務(wù)器之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。
原文鏈接：https://www.cnblogs.com/abclife/p/13948101.html
