淺談PostgreSQL中的孤兒文件用法(orphaned data files)
創(chuàng)建一個測試表
postgres=# create table t1(a int);
CREATE TABLE
postgres=# select pg_relation_filepath('t1');
pg_relation_filepath
----------------------
base/75062/75297
(1 row)
postgres=#
在操作系統(tǒng)上已經可以看到該文件。
$ ls -la $PGDATA/base/75062/75297 -rw------- 1 postgres postgres 0 Nov 9 11:11 /data/pgdata/11/data/base/75062/75297
插入一些數據：
postgres=# show segment_size; segment_size -------------- 1GB (1 row) postgres=# insert into t1 select * from generate_series(1,100000000); INSERT 0 100000000 postgres=#
因為segment_size的設置為1GB，磁盤上已經有了多個文件
$ 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)在，開啟另一個會話(session 2)。
在session2中，啟動一個事務并創(chuàng)建一個空表，但是不提交事務：
postgres=# begin;
BEGIN
postgres=# create table t2(a int);
CREATE TABLE
postgres=# 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)已經可以看到對應的文件：
$ ls -la $PGDATA/base/75062/75300 -rw------- 1 postgres postgres 0 Nov 9 11:23 /data/pgdata/11/data/base/75062/75300
如果這個時候，posrgresql server發(fā)生了奔潰、或者發(fā)生了oom被kill了或者session被kill了。會發(fā)生什么呢？
我們來模擬一下session被kill的場景：
$ kill -9 17710
再次在session2中執(zhí)行查詢：
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=#
這個session在事務提交之前被kill了，事務無法正常完成，但是事務已經創(chuàng)建了一個表。應該發(fā)生什么呢？事務被回滾，創(chuàng)建的表應該不存在了。
postgres=# select * from t2;
ERROR: relation "t2" does not exist
LINE 1: select * from t2;
^
postgres=#
這正是我們所預期的。但在操作系統(tǒng)上，文件仍然存在：
$ 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并不知道這個文件屬于哪個relation
postgres=# select relname from pg_class where oid = '75300'; relname --------- (0 rows) postgres=#
這樣，你就需要自己手動清理孤兒文件了！
假設你做了大量的數據的加載，就在加載完成之前，會話被殺死:
postgres=# begin;
BEGIN
postgres=# create table t3(a int);
CREATE TABLE
postgres=# 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.
雖然會話被kill了。但是磁盤上的空間并沒有被釋放。
$ 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
在最糟糕的時候，可能會占用大量的磁盤空間。那是否有什么方法去檢測這些孤兒文件呢？
你需要比較postgresql中的目錄表中的記錄和文件系統(tǒng)上信息，然后刪除這些孤兒文件。這個過程需要小心謹慎。
首先獲得你要檢測的數據庫的oid：
postgres=# select oid from pg_database where datname = 'postgres'; oid ------- 75062 (1 row) postgres=#
這樣就可以知道文件在文件系統(tǒng)上的位置。即 $PGDATA/base/[OID_OF_THE_DATABASE]
然后，獲得孤兒文件：
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=#
補充：理解postgreSQL中的prepared transactions和處理孤兒(orphans)事務
Prepared transactions是PostgreSQL的一個關鍵特性。理解該特性提供的功能和處理任何潛在的陷阱對于系統(tǒng)的維護是很關鍵的。所以，我們來深入研究一下具體什么是prepared transactions。
關于事務
在數據庫系統(tǒng)中，事務是一種處理通常包含多個語句的塊中的全部或零個語句的方法。在提交整個塊之前，該塊中語句的結果對其他事務不可見。 如果事務失敗或回滾，則對數據庫完全沒有影響。
事務依附于會話。但是，當要執(zhí)行與會話獨立的事務時（也有其他好處）。這就是“prepared transactions”的來源。
prepared transactions
prepared transaction是獨立于會話、抗崩潰、狀態(tài)維護的事務。事務的狀態(tài)存儲在磁盤上，這使得數據庫服務器即使在從崩潰中重新啟動后也可以恢復事務。在對prepared transaction執(zhí)行回滾或提交操作之前，將一直維護該事務。
PostgreSQL文檔聲明，在一個已存在的事務塊中，可以使用prepare transaction 'transaction_id‘命令創(chuàng)建一個prepared transaction。它進一步聲明該過程為兩階段提交準備了一個事務。
此外，建議應用程序或交互式會話不要使用prepared transaction。理想情況下，外部事務管理器應該跨同構或異構數據庫資源執(zhí)行原子的全局事務。
在postgreSQL中，缺省的max_prepared_transaction=0；即關閉了prepared transaction。如果你想使用prepared transaction，建議將max_prepared_transaction設置成max_connections的值。在同步的流復制standby庫上，最好將其設置的比max_connections大一點，以免standby不能接收查詢。
在任何給定的時間，你可以查看活躍狀態(tài)的prepared transactions，通過查看視圖pg_prepared_xacts。
pg_prepared_xacts視圖含有以下一些列：
#select * from pg_prepared_xacts; transaction | gid | prepared | owner | database -------------+-----+----------+-------+---------- (0 rows)
1.transaction：事務id
2.gid：用戶為prepared transaction定義的名稱
3.prepared：prepared日期，創(chuàng)建事務時帶有時區(qū)的時間戳
4.owner：創(chuàng)建該prepared transaction的事務
5.database：數據庫名
創(chuàng)建prepared transaction
知道什么是prepared transaction之后，現(xiàn)在來看看如何創(chuàng)建一個prepared transaction。創(chuàng)建一個該事務通常需要四個步驟：
1.begin(或start transaction)
2.執(zhí)行需要的操作
3.prepare transaction
4.commit(或rollback prepared)
prepare transaction、commit prepared、或rollback prepared后面加上一個gid，可以唯一標識prepared transaction。
例如下面的代碼塊：
postgres=# begin; BEGIN postgres=# create table abce(id int); CREATE TABLE postgres=# insert into abce values(1); INSERT 0 1 postgres=# prepare transaction 'abce_insert'; PREPARE TRANSACTION postgres=# 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 PREPARED postgres=# select * from pg_prepared_xacts; transaction | gid | prepared | owner | database -------------+-----+----------+-------+---------- (0 rows) postgres=#
當一個含有一個或多個活躍的prepared transactions的postgresql停止了或者奔潰了，會為每個活躍的prepared transaction創(chuàng)建一個文件，在目錄pg_twophase中。
比如，我們有個prepared transaction：
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=#
所以我沒有提交事務就停止了postgresql server。postgresql就會創(chuàng)建一個名為00003FEB的文件，對應于prepared transaction的事務id。
$ ls -l ../data/pg_twophase/ total 4 -rw------- 1 postgres postgres 220 Dec 9 11:47 00003FEB
00003FEB等價于16363。在postgresql被重啟后，在啟動日志會報如下信息：
2020-12-09 11:51:28.112 CST [963] LOG: database system was shut down at 2020-12-09 11:47:39 CST 2020-12-09 11:51:28.113 CST [963] LOG: recovering prepared transaction 16363 from shared memory 2020-12-09 11:51:28.132 CST [960] LOG: database system is ready to accept connections
如果你不希望恢復一個prepared transaction，可以簡單地刪除pg_twophase文件夾下的相應文件。
這很簡單，不是嗎?那么我們?yōu)槭裁床唤洺５厥褂盟?#63;畢竟，它提供了更高的提交操作成功的可能性。事情要是這么簡單就好了!
prepared transaction可能遇到哪些錯誤？
如果客戶端消失了，則prepared transaction可以未完成(既不提交也不回滾)。發(fā)生這種情況的原因多種多樣，包括客戶機崩潰，或者服務器崩潰導致客戶機連接被終止而無法重新連接。你實際上是依靠事務管理器來確保沒有孤立的prepared transaction。
除了崩潰之外，還有另一種原因可以使prepared transaction未完成。如果一個用于恢復的備份包含了事務的prepared階段，但是沒有包含關閉事務的階段，仍然會生成孤兒事務。
或者，DBA創(chuàng)建了一個prepared transaction，卻忘記了關閉它。
所以，如果一個prepared transaction沒有完成，又會有什么大不了的呢?
真正的問題
真正的問題是，孤兒prepared transaction繼續(xù)持有可能包含鎖的關鍵系統(tǒng)資源，或者使事務ID保持活動狀態(tài)，該事務ID可能會阻止vacuum清除只對該孤兒事務可見、對其它事務不可見的死的元組。
回想一下我在上面創(chuàng)建的prepared 事務。當事務prepared，并且在提交該事務之前，如果另一個事務試圖更改該表，它將無法獲取所需的鎖并掛起，直到解決了prepared事務（提交或回滾）為止。 否則，alter命令會無限期掛起，最終，我必須發(fā)出CTRL + C來停止該命令。
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 sent ERROR: canceling statement due to user request postgres=# select c.oid,c.relname,l.locktype,l.relation,l.mode postgres-# from pg_class c postgres-# inner join pg_locks l on c.oid=l.relation postgres-# where c.relname='abce'; oid | relname | locktype | relation | mode --------+---------+----------+----------+------------------ 370883 | abce | relation | 370883 | RowExclusiveLock (1 row) postgres=#
對vacuum的阻塞可能會更嚴重，在極端情況下，會導致數據庫關閉，因為孤兒prepared事務會阻止事務id的wrap around。
發(fā)現(xiàn)和通知
雖然一般的預期是prepared事務在幾秒鐘內完成，但是情況并不總是這樣。一個prepared事務可能持續(xù)幾分鐘、幾小時甚至幾天。
為這些事務維護元數據本身可能是一項挑戰(zhàn)。但是，我建議設置一個術語來定義prepared事務可以存在的最大時間。例如，考慮以下的prepared事務:
postgres=# BEGIN; BEGIN postgres=# INSERT INTO abce VALUES(3); INSERT 0 1 postgres=# PREPARE TRANSACTION 'abce_insert 1m'; PREPARE TRANSACTION
或者下面的事務：
postgres=# BEGIN; BEGIN postgres=# INSERT INTO abce VALUES(4); INSERT 0 1 postgres=# PREPARE TRANSACTION 'abce_insert 1d'; PREPARE TRANSACTION
在這些事務名稱中，最后一部分定義事務的時間。任何超出時間的事務可以通過sql查詢輕易地找出來：
postgres=# select gid,prepared,regexp_replace(gid, '.* ', '') AS age from pg_prepared_xacts WHERE 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=#
這里就很清晰地顯示了一個不應該再有效的事務。因此，使用一個外部代理或者cron任務可以輕易找出這些事務，或者通知管理員、或者回滾事務。
在我看來，這是一種簡單而容易的方式，可以確保即使事務管理器失敗或DBA意外地留下了一個事務，也可以在你的環(huán)境中管理孤兒事務。
結論
Prepared transactions顯然是一個非常重要的功能，但是需要使用回退通知程序或清理程序仔細設置環(huán)境，以輕松確保這些事務不會不必要地占用關鍵資源，并且系統(tǒng)保持良好狀態(tài)。
PostgreSQL社區(qū)中仍在討論如何處理孤兒prepared事務。它是否成為postgresql核心的一部分尚待觀察。同時，我們需要使用外部工具來管理這些事務，或者設法解決這個問題。
以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。
相關文章
這篇文章主要介紹了PostgreSQL表膨脹監(jiān)控案例(精確計算)，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
postgresql 實現(xiàn)replace into功能的代碼
這篇文章主要介紹了postgresql 實現(xiàn)replace into功能的代碼，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要介紹了postgreSQL中的case用法說明，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
在postgresql數據庫中創(chuàng)建只讀用戶的操作
這篇文章主要介紹了在postgresql數據庫中創(chuàng)建只讀用戶的操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
這篇文章主要介紹了pgsql 刪除仍有活動鏈接的數據庫操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
PostgreSQL實現(xiàn)一個通用標簽系統(tǒng)
這篇文章主要給大家介紹了關于利用PostgreSQL實現(xiàn)一個通用標簽系統(tǒng)的相關資料，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2019-01-01
PostgreSQL因大量并發(fā)插入導致的主鍵沖突的解決方案
在數據庫操作中,并發(fā)插入是一個常見的場景,然而,當大量并發(fā)插入操作同時進行時,可能會遇到主鍵沖突的問題,本文將深入探討 PostgreSQL 中解決因大量并發(fā)插入導致的主鍵沖突的方法,并通過具體的示例進行詳細說明,需要的朋友可以參考下2024-07-07
這篇文章主要給大家介紹了關于如何查看PostgreSQL數據庫的版本,查看PostgreSQL 數據庫的版本號,可用方法很多,文中介紹了三種方法,對大家的學習或者工作具有一定的參考借鑒價值,需要的朋友可以參考下2024-06-06
Postgresql 實現(xiàn)查詢一個表/所有表的所有列名
這篇文章主要介紹了Postgresql 實現(xiàn)查詢一個表/所有表的所有列名，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
PostgreSQL 實現(xiàn)distinct關鍵字給單獨的幾列去重
這篇文章主要介紹了PostgreSQL 實現(xiàn)distinct關鍵字給單獨的幾列去重，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01