Kattsu Sandbox

PostgreSQLでbaseディレクトリ内の孤立したテーブルデータを削除する方法

投稿日:
PostgreSQL

はじめに

以前、それほど巨大なテーブルはないにも関わらず PostgreSQL 内のデータファイルでディスク容量が逼迫してしまうという事態に陥ったことがありました。 調査してみると PostgreSQL 内の base ディレクトリにたしかに巨大なテーブルファイル（すべてあわせて 500GB を超える）が存在していましたが、このテーブルファイルは論理的なテーブルとしては存在しない孤立ファイルでした。 PostgreSQL では論理的なテーブルが削除されれば、CHECKPOINT などのタイミングで物理的にもファイルは削除されるはずです。 論理的なテーブルは存在しないのに、物理的なファイルのみ存在するとそれを論理側から削除する方法もなくゴミデータとしてディスクを圧迫してしまいます。 なぜ、孤立ファイルができてしまったか、その確認方法、削除方法などについて書いていきます。

ちなみに、PostgreSQL のテーブルデータの物理的な保存については下記記事にも書きました。 【PostgreSQL】テーブルデータ・インデックスの物理的な保存とログ先行書き込み

孤立ファイルを作る方法

結論から言うと、孤立ファイルはトランザクション内でテーブル作成とデータ挿入を行い、コミット前にサーバクラッシュや OOM によりトランザクションのプロセスが KILL されることにより残ってしまいます。 実際に実験してみみます。

-- トランザクションの開始
begin;

-- テーブルの作成
create table t1 (a int);

-- テーブルファイルパスの取得
select pg_relation_filepath('t1');
 pg_relation_filepath
----------------------
 base/13091/24576
(1 row)

-- プロセスIDの取得
select * from pg_backend_pid();
 pg_backend_pid
----------------
           1195
(1 row)

テーブルファイルパスを調べてみますがこの時点ではまだファイルサイズは 0 です。

$ cd /var/lib/postgresql/11/main
$ ls -la base/13091/24576
-rw------- 1 postgres postgres 0 May 26 23:09 base/13091/24576

試しに少し大きめのデータを INSERT してみます。

-- 1000万行を挿入
insert into t1 select * from generate_series(1,10000000);
INSERT 0 10000000
# テーブルサイズが増加したことを確認
$ ls -lah base/13091/24576
-rw------- 1 postgres postgres 346M May 26 23:12 base/13091/24576

このタイミングで先程のプロセスを SIGKILL で KILL し、OOM によるプロセスキルを擬似的に再現します。 ※このとき PostgreSQL がリカバリモードに入り、メインプロセスまで KILL して再起動しなければならない場合があるので注意します。

$ kill -9 1195

psql で select をするとトランザクション中にコネクションが切断されたことがわかります。

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.

トランザクション中にエラーが起きたのでテーブル作成は当然ロールバックされており、テーブルが存在しないことが確認できます。

select * from t1;
ERROR:  relation "t1" does not exist
LINE 1: select * from t1;

しかし、先程確認したテーブルファイルはサイズもそのままに残ってしまっています。

$ ls -lah base/13091/24576
-rw------- 1 postgres postgres 346M May 26 23:19 base/13091/24576

念の為、pg_class テーブルでテーブルファイルの oid で探してみますがレコードは存在しません。 先程のテーブルファイルは PostgreSQL の内部システムテーブルからも関知していない孤立したファイルであることがわかります。

select relname from pg_class where oid = '24576';
 relname
---------
(0 rows)

孤立ファイルの削除方法

このように作成されてしまった孤立ファイルは、PostgreSQL が内部システムで関知していないため勝手に掃除されることがなくユーザーが手動で削除する必要があります。 先程のテーブルファイルは数百 MB ほどでしたが、場合によっては冒頭のように数百 GB のファイル郡を作ることもあります。

削除方法についてですが、残念ながらあまりスマートな方法はありません。 pg_class テーブルとつけあわせて、pg_class に存在しないのにテーブルファイルに存在するものは孤立ファイルであると判断して削除していくしかありません。 誤って存在するテーブルファイルを削除してしまうと当然論理側のテーブルにも影響があるのでこの作業は慎重に行う必要があります。

まとめ

まとめると下記のような手順で孤立ファイルは作られてしまいます。

  • トランザクション中、コミット前に下記操作が行われる
  • テーブル作成・レコード挿入
  • OOM などによるトランザクションプロセスの KILL

この問題はデータインポート中にディスクフルや OOM による PostgreSQL のダウンなどで意外と起きえます。 小さいデータサイズであれば問題になることは少ないが、巨大なデータとなると PostgreSQL の復旧だけでは解決しない問題になります。 根本的な解決にはディスクフルや OOM の手前で安全に PostgreSQL をダウンさせるようリソースチェックの仕組みを整える必要などがあります。

参考

Can there be orphaned data files in PostgreSQL? - Blog dbi services

書いている人

大阪でソフトウェアエンジニアとして働いています。
BUKUMANGA - はてなブックマーク数を元にwebマンガをまとめたサイトを運営しています。

© 2020 Kattsu Sandbox