PostgreSQLの移行でバイナリデータに苦しめられた話

こんにちは、イノベーションオフィスの吉田です。

先日 PostGIS の移行のお話をしましたが、 PostgreSQL でも苦しんだことが有ったのでご紹介させていただきます。

今回のお話は PostgreSQL 8 系から PostgreSQL 9 系に移行した際のお話です。

大体はよくある pg_dump 、 pg_restore で解決したのですが、一部のデータが移行後に読み込めませんでした。

それはバイナリデータで bytea 型のフィールドでした。

アプリ側のバグ等の可能性も考えて色々やっていた結果以下のドキュメントにたどり着きました。

https://www.postgresql.jp/document/9.0/html/datatype-binary.html

特に以下の部分に注目です。

bytea型は入出力用に2つの外部書式をサポートします。 PostgreSQLの歴史的な"エスケープ"書式と"hex"です。 入力ではこれらの両方とも常に受け入れられます。 出力書式はbytea_output設定パラメータに依存し、デフォルトではhexです。 （hex書式はPostgreSQL 9.0から導入されたものであることに注意してください。 以前のバージョンや一部のツールではこれを理解しません。）

「hex書式はPostgreSQL 9.0から導入された」ですと！？

どうやら bytea 型のフィールドから値を取得する時のデフォルトの形式が変わったようです。

というわけで以下のコマンド一発で直りました・・・

ALTER DATABASE my_db SET bytea_output TO 'escape';

皆様もお気をつけ下さい。

それでは！

書いた人：イノベーションオフィス　室長　吉田