PostgreSQL: 不正な文字列が入ってしまったデータベースの復旧
 2007.05.17

とある,Webアプリケーションで使っていたPostgreSQLのデータベースに対して管理作業を行っていたところ,バックアップからリストアを試みたら
  invalid byte sequence for encoding "EUC_JP": 0x8bdf
なるエラー. 調査すると,PostgreSQLのバージョンが上がって,文字コードのチェックが厳しくなったという話を見つけた. データ移行時の文字コードエラーについてから始まるスレッド. どうやら私たちの問題もこれに該当するようだ. さて,どうやって復旧したか?基本的にはお手軽な方法は思いつかず,泥臭くやった. 1. 日本語が含まれるテーブルをリストアップ 2. 1.で列挙したテーブルのデータだけをdumpする % pg_dump -Ox -a -t foo > foo.dump 3. foo.dump のSQLのコメントやCOPY命令,データ終了を示す'.'を削除.タブ区切りのデータのファイルにする. 4. スクリプト言語などで,1行毎に漢字コード変換を試みてエラーが出た行を記録. 5. 4.で記録した行のデータを手作業で修正 今回は4.のスクリプトにPythonを用いた.
x = 2
enc = 'euc-jp'
for ln, line in enumerate(sys.stdin) :
    cols = line.rstrip().split('t')
    try :
       u = unicode(cols[x], enc)
    except UnicodeError :
       print (ln+1), "error"
xを日本語を含むカラムの番号,encに期待するエンコーディングを設定する.期待するエンコーディングとして不正な文字を含む行の行数がわかる. 今回の場合は,対象テーブル数が2,エラーの行数が約1000,しかもエラーのほとんどは同じ文字によるエラーで3パターン程度.このような小規模であったのでどうにか復旧できた. 最後に,この問題に関してはPostgreSQLに全く責任は無く,不正な文字列の挿入チェックを怠ったアプリケーションが悪い.
カテゴリー:技術情報メモ