PostgreSQLで論理バックアップと物理バックアップの説明が公式のドキュメントに無いよね...

www.publickey1.jp

GitHubは、リリース後の成果物を変更できなくする新機能「Immutable Release」（変更不可リリース）機能を正式版として提供すると発表しました。

GitHub、リリース後のバイナリなどアセットを変更不可にする「Immutable Release」（変更不可リリース）機能を正式リリース－ Publickey

⇧ 誤ってリリースしてしまった場合の対応については、記載が無いのよな...

公式のドキュメントを確認しても、

docs.github.com

⇧ 誤ってリリースしてしまった場合の対応については、どうすべきなのかの記載が無いのよ...

何と言うか、ユースケースについて、考慮漏れが多いのよな...

PostgreSQLで論理バックアップと物理バックアップの説明が公式のドキュメントに無いよね...

公式のドキュメントで、

www.postgresql.jp

PostgreSQLクライアントアプリケーション

ここには、PostgreSQLクライアントアプリケーションとユーティリティについてのリファレンス情報があります。これらのコマンドがすべて汎用的なユーティリティであるという訳ではありません。一部は特定の権限を必要とします。これらアプリケーションの共通機能は、データベースサーバが稼働しているかどうかに依存しない、どのホストでも実行できるという点です。

コマンドラインから指定された場合、ユーザ名とデータベース名の大文字小文字は保持されます。空白文字や特殊文字がある場合は引用符付けが必要かもしれません。テーブル名やその他の識別子では文書化されていない限り大文字小文字は保持されませんので、引用符付けが必要かもしれません。

https://www.postgresql.jp/document/17/html/reference-client.html

⇧ とあるのだが、

pg_basebackup — PostgreSQL クラスタのベースバックアップを取得する
pg_dump — PostgreSQLデータベースをスクリプトファイルまたは他のアーカイブファイルへ抽出する
pg_dumpall — PostgreSQLのデータベースクラスタをスクリプトファイルへ抽出する

について、

物理バックアップ
論理バックアップ

のどちらになるのかについての説明が無いのよね...

で、stackoverflowによると、

stackoverflow.com

https://stackoverflow.com/questions/62653659/what-is-recommended-way-to-do-a-postgresql-database-backup-pg-dump-or-pg-baseba

pg_dump creates a logical backup, that is a series of SQL statements that, when executed, create a new database that is logically like the original one.

pg_basebackup creates a physical backup, that is a copy of the files that constitute the database cluster. You have to use recovery to make such a backup consistent.

https://stackoverflow.com/questions/62653659/what-is-recommended-way-to-do-a-postgresql-database-backup-pg-dump-or-pg-baseba

⇧ と説明してくれている。

なのだが、根拠としている一次情報の出典が無いのよね...

一応、

gihyo.jp

第4章 PostgreSQL の運用に便利な機能―バックアップ、レプリケーション、パーティション、バージョンアップ | gihyo.jp

⇧ 上記サイト様によりますと、「PostgreSQL」のバックアップの種類としては、3つあるらしいのだが、

物理バックアップ
論理バックアップ

の違いは、ディスク使用量も変わって来るはずなので、大きい気がする...

「ChatGPT」氏に質問してみたところ、以下のような回答が返ってきた。

🔹① 物理バックアップ（例：pg_basebackup やファイルコピー）

概要

PostgreSQL の データディレクトリ全体をそのままコピー する方式。
つまり、データファイル（.dat）、WALログ、メタデータをバイナリ形式のまま複製します。

特徴

フォーマット：バイナリ（データブロック単位）
圧縮：通常はなし（オプションで圧縮可）
冗長データ：VACUUM未実行領域、空き領域、削除済み行の残骸なども含む
容量：実際のテーブルサイズ + 空き領域 + WALログ分
→ つまり、実際のデータより大きくなることが多い

🔹② 論理バックアップ（例：pg_dump）

概要

PostgreSQL が各テーブルの内容を SQL文（またはカスタムフォーマット）としてエクスポート。
バイナリデータはテキストまたは圧縮形式で書き出されます。

特徴

フォーマット：SQLテキストまたは圧縮バイナリ
圧縮：自動または手動で可能（例：pg_dump -Fc）
冗長データ：含まれない（論理的に有効な行のみ）
容量：実際のデータ量に比例
→ 物理バックアップよりかなり小さくなる傾向

🔹容量の違いまとめ

実データ	物理バックアップサイズ	論理バックアップサイズ（`pg_dump -Fc`）
100 GB DB	約 100〜120 GB	約 30〜70 GB（データ内容により変動）
1 GB DB（テキスト中心）	約 1 GB	約 300〜500 MB
1 GB DB（バイナリ中心）	約 1 GB	約 800〜1000 MB

🔹実際の例（目安）

実データ	物理バックアップサイズ	論理バックアップサイズ（`pg_dump -Fc`）
100 GB DB	約 100〜120 GB	約 30〜70 GB（データ内容により変動）
1 GB DB（テキスト中心）	約 1 GB	約 300〜500 MB
1 GB DB（バイナリ中心）	約 1 GB	約 800〜1000 MB

🔹結論

✅ ディスク使用量は異なります。

物理バックアップ：実際のデータファイルをコピーするため 大きくなりやすい。
論理バックアップ：有効データのみをエクスポートし 圧縮もされるため小さい。

処理時間についても、「ChatGPT」氏に質問してみたところ、以下のような回答が返ってきた。

🔹前提：処理の仕組みの違い

項目	物理バックアップ (`pg_basebackup` 等)	論理バックアップ (`pg_dump`)
対象	データファイル（バイナリ）をそのままコピー	SQL形式でデータを「書き出す」
単位	クラスタ全体（すべてのDB）	指定DB単位（またはテーブル単位）
主な処理	ファイルコピー	データのSELECT＋整形＋書き込み
並列化	高速（I/Oバウンド）	並列対応（`-j`）だがCPU負荷高め
復元方法	ディレクトリを置き換えるだけ	SQL文を再実行（インサート・インデックス再構築）

🔹① バックアップ時間の比較

処理	物理バックアップ	論理バックアップ
方式	`pg_basebackup`（バイナリコピー）	`pg_dump`（SQL生成）
所要時間（目安）	ほぼファイルコピー速度に比例	テーブルサイズ×SELECT速度（＋圧縮時間）
実際の傾向	非常に速い（数百GBでも数十分〜1時間）	遅い（数百GBで数時間〜半日）
CPU負荷	低め（I/O中心）	高め（データ変換・圧縮でCPU使用）
並列化	`pg_basebackup --max-rate` や OSレベルで対応可	`pg_dump -j N`（スキーマ単位並列）で対応可

🔸 例（実測イメージ）

DBサイズ	`pg_basebackup`	`pg_dump -Fc -j 4`
10 GB	約 1〜2 分	約 5〜10 分
100 GB	約 10〜20 分	約 1〜2 時間
1 TB	約 1〜2 時間	数時間〜半日

※ ストレージ速度やネットワーク帯域で変動します。

🔹② リストア時間の比較

処理	物理リストア	論理リストア
方式	ファイルを上書きして起動	SQL文を再実行して再構築
所要時間	非常に短い（コピー時間のみ）	長い（全テーブルINSERT＋インデックス再作成）
実際の傾向	数分〜数十分	数十分〜数時間〜半日
並列化	OSレベルのファイルコピーで対応	`pg_restore -j N` で高速化可

🔸 例（実測イメージ）

DBサイズ	物理リストア時間	論理リストア時間（`pg_restore -j 4`）
10 GB	数分	約10分
100 GB	約15分	約1〜2時間
1 TB	約1時間	数時間〜半日以上

🔹③ まとめ：速度・負荷・用途比較

観点	物理バックアップ	論理バックアップ
バックアップ速度	★★★★★（速い）	★★☆☆☆（遅い）
リストア速度	★★★★★（速い）	★★☆☆☆（遅い）
CPU負荷	低い	高い
柔軟性	低い（全体のみ）	高い（DB単位・テーブル単位可能）
異バージョン対応	不可	可（メジャーバージョン差もOK）
向いている用途	災害復旧・PITR	テスト環境構築・個別データ復旧

🔹④ 実運用でのおすすめ戦略

タイプ	内容	頻度
物理バックアップ	`pg_basebackup --compress=zstd` + WAL保存	毎晩 or 毎週
論理バックアップ	`pg_dump -Fc -j 4`（主要DBのみ）	週1〜月1
スナップショット	LVM/ZFS/クラウドスナップショット	必要に応じて即時取得

🔹⑤ 結論（時間差まとめ）

処理	物理バックアップ	論理バックアップ
バックアップ	速い（コピーするだけ）	遅い（SQL化する）
リストア	速い（上書きだけ）	遅い（再構築）
バックアップサイズ	やや大きい	小さい（圧縮可）

👉 速度重視なら物理バックアップ、

👉 柔軟性・互換性重視なら論理バックアップ、

という使い分けが基本です。

ちなみに、「物理バックアップ」を実現する「pg_basebackup」コマンドについては、「リストア」用の「PostgreSQLクライアント」のコマンドが用意されていないっぽい...

片や「論理バックアップ」を実現する「pg_dump」コマンドについては、「リストア」用の「PostgreSQLクライアント」のコマンドが用意されている模様。

「PostgreSQL」の「サービス」を停止できる状況なら、処理時間が短くて済む「物理バックアップ」を選択することになるんかな。

世の中の大容量データベースでバックアップをどうしているのかは気になりますな...

ちなみに、「1PiB」とかの場合について、「ChatGPT」氏に質問してみたところ、以下のような回答が返ってきた。

1PiB（ペビバイト = 約1,125,899GB ≈ 1,125TB） クラスの PostgreSQL データベースになると、バックアップ方式による処理時間・実現可能性・戦略がまったく異次元になります。

以下では、現実的な運用観点から「1PiBのPostgreSQL」における
物理バックアップと論理バックアップの処理時間・実現性・推奨戦略を詳しく説明します👇

🔹前提

1 PiB = 1,024 TiB ≈ 1,125 TB

仮に：

ストレージ：SSD/NVMe RAID、または高速SAN
ネットワーク：40Gbps（5GB/s）
PostgreSQL 16 以上
並列処理・圧縮有効

として、理論値と現実的な処理時間を考えます。

🧩① 物理バックアップの場合（pg_basebackup やストレージスナップショット）

🔧 処理の概要

PostgreSQL データディレクトリ全体をコピーするだけ（バイナリ転送）
実際には「I/O速度＋圧縮処理速度」で決まる

💨 処理時間（目安）

帯域幅 / 転送速度	所要時間（理論値）	備考
1 Gbps（0.125 GB/s）	約 104日	現実的ではない
10 Gbps（1.25 GB/s）	約 10日	WAN越しは厳しい
40 Gbps（5 GB/s）	約 2.5日	ローカルDCでギリギリ現実的
100 Gbps（12.5 GB/s）	約 1日	高速ストレージと併用すれば可能

⚙️ 実際には：

書き込みI/O、WAL出力、CPU圧縮のオーバーヘッドがあるため
→ 理論値の1.5〜2倍の時間を見積もるのが現実的です。

つまり

✅ 高速ストレージ＋高速ネットワークでも「1〜3日規模」のバックアップ時間

🧱 現実的な運用方法

1PiB級になると、単純に pg_basebackup は非現実的です。

代替として以下のような方法が使われます：

手法	説明
ストレージレベルスナップショット	LVM, ZFS, Ceph, AWS EBS, NetApp などの機能で瞬時にスナップショットを取得
ストリーミングレプリカのバックアップ	スタンバイを止めて `pg_basebackup` 実施（本番に負荷をかけない）
増分バックアップ	WALログを継続保存して差分復旧可能に（PITR構成）

✅ リストア時間（1PiB）

バックアップ時と同様に、ファイルコピー速度がボトルネック。

→ 40Gbps環境で 2〜3日 規模。

ストレージスナップショットを使えば「数分でロールバック」も可能。

🧩② 論理バックアップの場合（pg_dump, pg_restore）

⚙️ 処理の概要

各テーブルをSELECTし、SQLまたはバイナリに書き出す
INSERT文でリストア
CPU・I/O・ネットワークすべてがボトルネック

💨 処理時間（目安）

論理バックアップは「データ量に比例して」非常に時間がかかる。

仮に1TBのDBをpg_dump -Fc -j 8でバックアップするのに約1時間かかるとすると：

1PiB = 1,024倍 → 約1,000時間（40日）以上

さらにリストアでは INSERT & インデックス再構築を行うため、

リストアはバックアップの2〜3倍遅い（＝80〜120日規模）。

❌ 結論

各テーブルをSELECTし、SQLまたはバイナリに書き出す
INSERT文でリストア
CPU・I/O・ネットワークすべてがボトルネック

🧩③ まとめ：1PiB級の処理時間比較

項目	物理バックアップ	論理バックアップ
バックアップ方式	バイナリコピー（pg_basebackup, スナップショット）	SELECT＋書き出し（pg_dump）
処理時間	約 1〜3日（40Gbps）	約 40日以上
リストア時間	約 1〜3日	約 80〜120日
実現性	✅ 現実的（ストレージ依存）	❌ 非現実的（時間・負荷が膨大）
運用推奨	スナップショット＋WALアーカイブ	テーブル単位ダンプ（必要部分のみ）

🧩④ 実運用でのおすすめ戦略（1PiB級システム）

用途	推奨手法
フルバックアップ	ストレージスナップショット（ZFS, LVM, EBS など）
差分／増分バックアップ	WAL アーカイブ＋ポイントインタイムリカバリ（PITR）
部分バックアップ	`pg_dump`（特定スキーマ・テーブル単位）
検証環境作成	スタンバイからスナップショット複製
災害復旧	オフサイトスナップショット複製＋WAL適用

🔹結論

比較軸	物理バックアップ	論理バックアップ
処理時間（1PiB）	1〜3日（高速環境で）	数十〜百日
容量効率	実データと同等	圧縮してもほぼ同じ（巨大すぎる）
現実性	✅ 現実的（スナップショット前提）	❌ 実質不可能
運用戦略	スナップショット＋PITR構成が必須	部分ダンプのみ利用可