旧い旧い PostgreSQL データベースを新しい環境に移行する際に、文字コードの問題で pg_restore が以下のエラーを吐く場合の回避方法。
pg_restore: [アーカイバ(db)] COPY failed for table "table_name": ERROR: invalid byte sequence for encoding "EUC_JP": 0x## 0x##PostgreSQLの文字コードの問題
旧い PostgreSQL は「文字コード無指定（SQL_ASCII）」でマルチバイトの生文字コードを保存する事ができた。開発したアプリケーション側で文字コードを把握していれば、それで問題が無かった。
現在の PostgreSQL ではサーバ、クライアントとも使用する文字コードを設定する事が必要で、DBエンジンがサーバ・クライアント間の文字コード変換を行ってくれる。大変便利。
しかし。
これが、旧いバージョンで運用していた PostgreSQL DB を新しいバージョンの環境に pg_dump と pg_restore で環境移行する時に問題になる。
保存されているデータが英数字だけなら問題が無いのだが、UNIX環境で伝統的だった EUC-JP（PosrgreSQL の文字コード表記では EUC_JP）は規格外の文字コード（いわゆる外字）を運用上使う事が出来た。これは、おそらく各種PCアプリケーションがShift_JIS系（CP932など）の外字領域の文字を、単純計算に基づく変換でEUC-JPエンコーディングスキームと相互変換してくれていたせいだと思われる。
所が、PostgreSQL エンジンにはそのような規格外を慮ってくれる配慮が無いので、これらの特殊文字または紛れ込んだ規格外文字コードが例え1レコードでも含まれるテーブルは pg_restore でまるまるインポート（COPY）が失敗する。その時に出るエラーが上記の「invalid byte sequence for encoding …」。
データが少ない場合は元のDBデータの方を変更するなり削除するなりして再度 db_dump からやり直せばいいのだが、データが多いとかなり手間になる。
文字コード変更のベストプラクティス
という事で、色々試行錯誤の上行きついたのが以下の方法。詳細はそれぞれ後述。
- 「–encoding=UTF8」オプションを付けて db_dump する
- 一旦圧縮をほどいて中のデータファイル（#.dat）を手動で utf-8 に変換
- 再度 tar 圧縮
- db_restore でインポート
「–encoding=UTF8」オプションを付けて db_dump する
例えば、こんな感じです。
pg_dump -Ft --encoding=UTF8 -b DBNAME > utf8-dump.tar一旦圧縮をほどいて手動で utf-8 に変換
UTF-8を指定してダンプしても、元のDBの文字コードが SQL_ASCII の場合は、データファイル（#.dat）は元使っていた文字コードで書き出されるのでこれを変換する。
まずは、以下のコマンドでアーカイブ内のファイルの順番を表示させテキストエディタなどに保存しておく。
tar -tf utf8-dump.tar圧縮をほどいて文字コード変換を行う。下記は nkf を使い、EUC-JP から UTF-8 に変換している。
tar -xvf utf8-dump.tar
find . -name "2*.dat" -exec nkf -E -w --overwrite {} \;この時、blog_#.dat というファイルがあればそれはバイナリーデータなので変換対象から除く。上記では、ファイル名が “2*.dat” のファイルを変換している。
再度 tar 圧縮
先ほど保存しておいたリストを使って、tar 圧縮する。これは、toc.dat が先頭に無いとエラーになるため。そのほかの順番が必要かは不明ですが、念のため同じ順番で作成。私の場合はこんな感じのファイル名でした。
tar -cf 4import-utf8.tar \
toc.dat \
2175.dat \
：
blobs.toc \
2206.dat \
restore.sql \db_restore でインポート
cat 4import-utf8.tar | pg_restore --dbname=DBNAMEこれでもエラーが出るものは、元データを粛々と修正する。エラー表示のあったテーブル名を restore.sql の中で探すと、どの #.dat ファイルかが分かるので該当の行数をチェックして元のデータを割り出す（結構根気のいる作業）。