【豆知識】バイナリデータをpg_dumpでバックアップしたときのエラーを解決したい
PostgreSQLインサイド

PostgreSQLでは、バイナリデータを「バイナリ列データ型」としてテーブル内で扱う場合、型名は「bytea」を使用します。 バイナリデータを含むテーブルをpg_dumpコマンドで論理バックアップしたところ、以下のようなエラーメッセージが出力されることがあります。

pg_dump: error: Dumping the contents of table "xxxxx" failed: PQgetResult() failed.
pg_dump: error: Error message from server: ERROR:  invalid memory alloc request size xxxxxxxxx

日本語環境では以下のようなメッセージが出力されることがあります。

pg_dump: error: テーブル"xxxxx"の内容のダンプに失敗: PQgetResult()が失敗しました。
pg_dump: error: サーバのエラーメッセージ: ERROR:  invalid memory alloc request size xxxxxxxxx

pg_dumpコマンドでは、バイナリ形式のデータを文字として出力します。そのため、バイナリ列データ型のデータが文字に変換され、データ量が増大し、文字として扱えるデータの最大長（約1ギガバイト）を超えたことが原因です。

このような場合、SQLコマンドのCOPYを使ってバイナリ形式で論理バックアップを実施してください。

ここでは、約537メガバイトのバイナリデータを含むテーブルをバックアップする例で説明します。データベース名はmydbとします。なお、この記事は、PostgreSQL 15.0をベースに作成しています。initdb時の符号化方式（エンコーディング）は「UTF8」を指定しています。

前提

1. テーブル：goods1を作成します。列は3つ作成し、バイナリデータを格納する列：dataのデータ型は「bytea」と定義します。

mydb=# CREATE TABLE goods1 (id serial not null primary key, name text, data bytea not null);
CREATE TABLE

1. 動画ファイルのバイナリデータ：sample.mp4（約537メガバイト）をpg_read_binary_file関数でbytea型に変換し、テーブルに格納します。

mydb=# INSERT INTO goods1(name,data) VALUES ('DEMO1',pg_read_binary_file('/home/data/sample.mp4')); 
INSERT 0 1

1. pg_dumpコマンドでテーブル：goods1をバックアップすると、エラーが発生しました。

$ pg_dump -t goods1 mydb -c > goods1_dump.data 
pg_dump: error: Dumping the contents of table "goods1" failed: PQgetResult() failed.
pg_dump: error: Error message from server: ERROR:  invalid memory alloc request size 1126352709
pg_dump: error: The command was: COPY public.goods1 (id, name, data) TO stdout;

対処例

そこで、SQLコマンドのCOPYを使って、バイナリデータをバイナリ形式のままで論理バックアップを実施します。

1. COPYでテーブル：goods1のデータをバックアップします。データ形式のFORMATパラメーターには「binary」（デフォルトはtext）を設定します。

mydb=# COPY public.goods1  TO  '/home/data/goods1_copy.data' (FORMAT binary);
COPY 1     <-- 1件のコピー（バックアップ）が正常終了

1. バックアップデータ：goods1_copy.dataが生成されたかを確認します。

$ ls -l 
-rw-r--r--. 1 fsepuser fsepuser 563176397 5月 22 11:16 goods1_copy.data

なお、論理バックアップの方法を変更（pg_dumpコマンドからSQLコマンドのCOPYに変更）することにより、取得したバックアップデータを使ったリカバリー方法も変更する必要があります。詳細については「論理バックアップの方法」を参照してください。

バイナリデータを論理バックアップする上で、参考になる情報を示します。

バイナリデータの論理バックアップ

バイナリデータは、文字列に変換せずにそのままの形式で論理バックアップを実施することをお薦めします。1バイトのバイナリデータは文字コードに変換されると、コード系によって1~4バイトに変動しますので、注意が必要です。
参考までに、約300メガバイトの動画ファイルのバイナリデータ：FEP_demo2.mp4をpg_dumpコマンドとCOPYのそれぞれでバックアップしたデータ量を以下に示します。
pg_dumpコマンドでバックアップしたデータがCOPYでバックアップしたデータに比べて、約2倍のデータ量になっていることがわかります。

$ ls -l
-rw-r--r--. 1 fsepuser fsepuser  307359797 5月 22 11:44 goods2_copy.data   <-- COPYでバックアップ
-rw-rw-r--. 1 fsepuser fsepuser  614721497 5月 22 11:45 goods2_dump.data   <-- pg_dumpでバックアップ（注）

• 注)
  お薦めのバックアップ方法ではありませんが、処理中に1ギガバイトを超えなかったため、エラーになりませんでした。

論理バックアップの方法

論理バックアップは、PostgreSQLを起動した状態でデータベースに格納されているデータをファイルに書き出すバックアップ方法です。論理バックアップは、バックアップを実行した時点へのリカバリーのみが可能です。論理バックアップを実施する方法として以下の3つがあります。

pg_dumpallコマンド

データベースクラスタ全体の内容をSQLの形でバックアップします。取得したスクリプト形式(SQLコマンドが書き込まれた平文ファイル)のバックアップデータは、psqlコマンドを使ってバックアップを取得した時点へリカバリーします。

pg_dumpコマンド

指定したデータベースの内容をSQLの形でバックアップします。取得したスクリプト形式のバックアップデータは、psqlコマンドを使ってバックアップを取得した時点へリカバリーします。取得したアーカイブファイル形式のバックアップデータは、pg_restoreコマンドを使ってバックアップを取得した時点へリカバリーします。

SQLコマンドのCOPY

指定したテーブルのデータを独自のテキスト形式、バイナリ形式、またはCSV形式でバックアップします。取得したバックアップデータは、COPYを使ってバックアップを取得した時点へリカバリーします。

論理バックアップの詳細およびリストアについては、以下の記事を参照してください。

• PostgreSQLのバックアップとリカバリー

PostgreSQL 15文書

• Documentation（PostgreSQLオフィシャル）
  • Chapter 8. Data Types
    • 8.4. Binary Data Types
  • SQL Commands
    • COPY
• PostgreSQL日本語ドキュメント（日本PostgreSQLユーザ会）
  • 第8章 データ型
    • 8.4. バイナリ列データ型
  • SQLコマンド
    • COPY

2023年6月9日公開

本コンテンツに関するお問い合わせ