です。

/pgsql/data/pg_log/配下のログファイルに以下のエラーが出力された場合の対応方法についてまとめます。

could not write to file "pg_xlog/xlogtemp.28545": No space left on device

環境は以下の通りです。

EC2
CentOS
postgresql-9.4.5

そもそもこの事象は何故起こるか

この事象はPostgreSQLによるpg_xlogディレクトリへのWALファイルの書き込み→ストレージの容量不足によって発生します。

# du -sh /usr/local/pgsql/data/* | sort -hr
185G    /usr/local/pgsql/data/pg_xlog
71M /usr/local/pgsql/data/base
14M /usr/local/pgsql/data/pg_log
448K    /usr/local/pgsql/data/global
56K /usr/local/pgsql/data/pg_subtrans
28K /usr/local/pgsql/data/pg_multixact

WALファイルとは何か？

WALファイルとは先行書き込みログ（WAL）のことであり、データベースのデータファイルに行われた全ての変更が記録されています。

主にリストア用の設定となり、postgresql.confのarchive modeをonにしているとこのファイルの作成処理が走ります。

単純にarchive modeをoffにすればそもそもWALファイルの作成処理が走らなくなるのでこのエラーは発生しません。(リカバリできなくなるのでお勧めはしません)

PostgreSQLは常に、クラスタのデータディレクトリ以下のpg_xlog/ディレクトリ内で先行書き込みログ（WAL）を管理しています。このログはデータベースのデータファイルに行われた全ての変更を記録します。このログは主にクラッシュ時の安全性を目的としています。システムがクラッシュしたとしても、最後のチェックポイント以降に作成されたログ項目を"やり直し"することで、データベースを整合性を維持した状態にリストアすることができます。 https://www.postgresql.jp/document/9.4/html/continuous-archiving.html#BACKUP-ARCHIVING-WAL

対応方法

以下の手順で復旧を行います。

pg_xlog配下の不要なWALファイルを削除
リストアし、Postgresqlプロセスの再起動

pg_xlog配下の不要なWALファイルを削除

/pg_xlog、/pg_xlog/archive_status/配下の不要なファイルを削除します。

削除対象の見分け方は以下のページの対応方法がわかりやすく書かれているので引用させて頂きます。

削除してよいファイルを判別するには pg_xlog の下の archive_status ディレクトリを確認します。ここに「000000010000000000000028.ready」というファイルがあれば「000000010000000000000028」という WALファイルは削除して良いということを表しています。また「000000010000000000000028.ready」ファイルも削除してよいです。したがいまして、対応する「〜.ready」ファイルの無い数個の WALファイルを残して、それ以外の WALファイルを全て削除するという対応になります。 https://powergres.sraoss.co.jp/s/ja/product/win-tech/malfunction9_0.php

上記方法で削除対象を抽出したら不要なファイルの削除作業を行います。自分の場合はスクリプトで削除作業を行いました。

pg_xlog配下のWALファイルとarchive_status配下の.ready付きのファイルが一致する場合はWALファイルのタイムスタンプが新しいものを数個残してそれ以外を削除してしまいましょう。

タイムスタンプが最新のWALを保管(複数個残した方が良い)

# mv /usr/local/pgsql/data/pg_xlog/000000010000005C0000007C /tmp/

削除スクリプトの作成

# vi /usr/local/pgsql/data/pg_xlog/delete_archive.sh

以下内容の貼り付け

#!/bin/bash

cnt=0
while read line
do
    rm -f $line
done <<END
000000010000002E00000040
000000010000002E00000041
000000010000002E00000042
000000010000002E00000043
000000010000002E00000044
archive_status/000000010000002E00000051.ready
archive_status/000000010000002E00000052.ready
END

スクリプトの実行

# cd /usr/local/pgsql/data/pg_xlog/
# sh delete_archive.sh

データの確認

$ df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/xvda1      197G  1.8G  185G   1% /
tmpfs           1.8G     0  1.8G   0% /dev/shm

復旧

# su - postgres
$ /usr/local/pgsql/bin/pg_ctl start
server starting

$ ps -ef | grep postgres
root     28769 28756  0 04:43 pts/1    00:00:00 su - postgres
postgres 28770 28769  0 04:43 pts/1    00:00:00 -bash
root     28970 28947  0 06:26 pts/2    00:00:00 su - postgres
postgres 28971 28970  0 06:26 pts/2    00:00:00 -bash
postgres 29056     1  1 06:34 pts/2    00:00:00 /usr/local/pgsql/bin/postgres
postgres 29057 29056  0 06:34 ?        00:00:00 postgres: logger process
postgres 29059 29056  0 06:34 ?        00:00:00 postgres: checkpointer process
postgres 29060 29056  0 06:34 ?        00:00:00 postgres: writer process
postgres 29061 29056  0 06:34 ?        00:00:00 postgres: wal writer process
postgres 29062 29056  0 06:34 ?        00:00:00 postgres: autovacuum launcher process
postgres 29063 29056  0 06:34 ?        00:00:00 postgres: stats collector process

$ netstat -ant | grep 5432
tcp        0      0 0.0.0.0:5432                0.0.0.0:*                   LISTEN
$