postgresでcould not extend fileとなりdisk spaceがなくtruncateもできなくなっちゃったら

概要

postgresのdataファイルを配置しているsliceのdisk spaceがfullになってしまい
truncateしようにも could not extend file になってしまう場合の対応

こうなると vacuum full もできない状態になってるはず

通常はsliceに割り当てるdisk spaceを拡張するのだけれど
拡張しないで不要データを削除して対処する方法

対応方法

おおまかな流れ

1. 別sliceなどでdiskがある場所にtable spaceを作る
2. alter table でいくつかのtableを1.で作成したtable spaceに移す
3. trucateできるようになるので、不要データをtruncateする
4. 2.でtable spaceを変更したtableをもとのtable spaceに戻す

※postgresはtruncateがcommitされたタイミングでdisk spaceを開放する

実施例

別sliceなどでdiskがある場所にtable spaceを作る

create tablespace backup location '/hoge/fuga';

いくつかのtableを作ったtable spaceに移す

alter table xxxxtable set tablespace backup;

この時点でdisk容量に少し空きができる。
truncateできるようになったら、不要なテーブルをtruncateする

truncate table 不要なtable;

disk容量が確保できたら table spaceをもとに戻す

alter table xxxxtable set tablespace 元のtablespace;

追記

postgresでwal（アーカイブログ）を有効にしている場合、アーカイブログが累積して
diskを使用している可能性がある。

postgresでは自動でアーカイブログをローテーションや削除する機能がないので
ほっとくとどんどん溜まってしまう。

アーカイブログの出力場所は postgresql.conf の archive_command で出力先を指定している。

この出力先にアーカイブログが溜まっている場合、不要なものを削除することでdisk spaceを確保できる。

アーカイブログ削除対象

ベースバックアップ( pg_basebackup )取得時にできる backup_label の中を見ると削除できるアーカイブログがわかる。

START WAL LOCATION: 0/36000028 (file 0000000100000013000000EC)

のようになっている場合、　末尾ECより過去のログなら削除してもOK

ベースバックアップ取ってない場合は、アーカイブログ出力場所のファイルを見て、作成日が古いものを狙う。

アーカイブログ削除方法

OSコマンドで消してもいいが、一応postgresのツールがあるので使う。

以下のように pg_archivecleanup -n をすると削除対象となるアーカイブログ一覧を取得できる。
指定したアーカイブログ名より過去のものすべてが削除対象となる。
-n をつけた場合は一覧出力だけで実際の削除はしない。

sudo -u postgres pg_archivecleanup -n [アーカイブログ出力ディレクトリ] [アーカイブログファイル名]

出力された結果がOKであれば -n を取って実行するとアーカイブログが削除される

sudo -u postgres pg_archivecleanup [アーカイブログ出力ディレクトリ] [アーカイブログファイル名]