【PostgreSQL】pg_dump/pg_restore によるバックアップ/リストア
以前本ブログで「pgAdmin 4」によるバックアップ/リストア1を取り上げましたが、その他にpg_dumpコマンドによる論理バックアップや、pg_basebackupコマンドによる物理バックアップがあります。
本ブログでは一般的に使用されている「pg_dump/pg_restore」を紹介します。
【PostgreSQL】pg_dump による論理バックアップとは？
論理バックアップとは、PostgreSQLのデータベースの構造（スキーマ）とデータをSQL形式、または特定のフォーマット（カスタムやtar）でエクスポートするバックアップ方法です。
pg_dump や pg_dumpall を使用し、テキスト形式のSQLスクリプトやバイナリ形式のバックアップを取得します。
論理バックアップの特徴
- 利点
- 異なるバージョンのPostgreSQLでも復元可能
- データベースの稼働中でも取得可能
- 部分的なリストアが可能
- 欠点
- バックアップ・リストアに時間がかかる
- データベースのサイズが大きいと処理が遅くなる
pg_dumpにおけるデータの整合性
pg_dumpはデータベースの稼働中でも一貫したバックアップを取得できるように、MVCC（Multi-Version Concurrency Control） を利用しています。
これによりバックアップ開始時点のデータの状態をスナップショットとして維持し、一貫性を保つことができます。
MVCC（Multi-Version Concurrency Control）の仕組み
- バックアップ開始時点のデータベース全体のスナップショットを取得する。
- バックアップ中に、データの更新や削除が発生してもデータを直接上書きせず、新しいバージョンを作成して反映させる。古いバージョンはそのまま保持する（Vacuumで削除）。
- バックアップ中に変更が加わっても、その時点のスナップショットを使用してデータをエクスポートするため、データの整合性が確保される。
pg_dumpによるバックアップの例
カスタム形式でのバックアップ例を以下に示します。
C:\>pg_dump -U postgres -d sales_db -F c -f H:\sales_db.backup■主なオプション
| № | オプション | 意 味 |
|---|---|---|
| 1 | -U <username> | バックアップを取得するユーザー名 |
| 2 | -d <databasename> | バックアップ対象のデータベース名 |
| 3 | -F c | カスタムフォーマットでバックアップ |
| 4 | -F t | tar形式でバックアップ |
| 5 | -F p | プレーンテキスト形式でバックアップ |
| ６ | -f | バックアップを保存するファイルの名 |
■カスタム形式のバックアップファイル
ファイルの内容を確認するには、pg_restore -l コマンドを使用します。
C:\>pg_restore -l H:\sales_db.backup
;
; Archive created at 2025-02-02 13:45:30
; dbname: sales_db
; TOC Entries: 55
; Compression: gzip
; Dump Version: 1.15-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 16.4
; Dumped by pg_dump version: 16.4
;
; Selected TOC Entries:
;
227; 1255 16690 PROCEDURE public update_stock(integer, integer) postgres
220; 1259 16692 TABLE public books postgres
4849; 0 0 ACL public TABLE books postgres
・
・
4662; 2604 16672 DEFAULT public sales sale_id postgres
4836; 0 16692 TABLE DATA public books postgres
4842; 0 16731 TABLE DATA public bookstock postgres
・
・
4866; 0 0 SEQUENCE SET public sales_sale_id_seq postgres
4679; 2606 16699 CONSTRAINT public books books_pkey postgres
4685; 2606 16738 CONSTRAINT public bookstock bookstock_pkey postgres
・
・
C:\>；で始まる行はコメントで、それ以外の各行はそれぞれダンプファイルに格納されているオブジェクトの種類を表しています。
- 「TABLE」 ：テーブルの定義
- 「TABLE DATA」 ：テーブル内の実データ
- 「CONSTRAINT」 ：主キー制約等の制約
例えばこの中から「books」テーブルの定義だけを取り出したい場合は、220; 1259 16692 TABLE public books postgres 以外を全てコメントにします。
pg_dumpall
pg_dumpall はPostgreSQL のデータベース全体を一度にバックアップするためのコマンドです。
バックアップ例を以下に示します。
c:\>pg_dumpall -U postgres -f H:\all_databases.sql■all_databases.sqlの内容
ファイルの内容を確認するにはtxt等でファイルを開きます。
--
-- PostgreSQL database cluster dump
--
SET default_transaction_read_only = off;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
--
-- Roles
--
CREATE ROLE "Cool_Kabachan";
ALTER ROLE "Cool_Kabachan" WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:ZywoKavkssKiW+W2z/dWuw==$LeqzMdccg9u3ZHV5F2S+Z4ZcN9z6yGhkmIwcNN0gLzE=:H/CM8tGyuByU+9EdH2/jWsZljtAU2P2oznSXhg8dL0M=';
CREATE ROLE sales_db_user;
ALTER ROLE sales_db_user WITH NOSUPERUSER INHERIT CREATEROLE CREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
--
-- User Configurations
--■主なオプション
| № | オプション | 意 味 |
|---|---|---|
| 1 | -U <username> | バックアップを取得するユーザー名 |
| 2 | -f<filename> | バックアップファイルの保存先 |
| 3 | -g | グローバルオブジェクト（Roll/Table Space等）をバックアップする |
| 4 | -v | 詳細な出力を表示 |
| 4 | –clean | リストア時、既存のオブジェクトを削除してから復元 |
| 5 | –no-coments | コメントをバックアップしない |
| 6 | –no-owner | オブジェクトの所有者情報をバックアップしない |
| ７ | -f | バックアップを保存するファイル名 |
【PostgreSQL】バックアップファイルのリストア
リストアする場合「pg_restore」と「psql」コマンドを使用しますが、バックアップファイルの形式によって下表の様に使い分けします。
| № | リストアコマンド | ファイル形式 |
|---|---|---|
| 1 | pg_restore | カスタムフォーマット（-F c） |
| 2 | pg_restore | Tar形式（-F t） |
| 4 | psql | プレーンテキスト（-F p） |
pg_restoreでのリストアの種類
pg_restore には、以下のリストア方法があります
既存のデータベースにリストアする
この方法では、バックアップファイルの内容が test_db データベースに適用されます。
pg_restore -U postgres -d test_db test_db.backup新しいデータベースを作成してリストアする
新しいデータベースを作成し、そこにリストアする場合は -C オプションを使います。
コマンドの処理の流れは、
test_dbをCREATE DATABASE文を使って作成する。test_dbにデータをリストアする。
※指定できるオプションは後述しますが、-C を使用すると、リストア先のデータベースを新規作成するため、既存の test_db がある場合はエラーになります。
pg_restore -U postgres -C -d postgres test_db.backupSQL スクリプトを出力して手動で適用する
バックアップファイルから SQL スクリプトを抽出し、psql を使ってリストアする方法もあります。この方法では、SQLスクリプトの内容を確認した上で適用できます。
pg_restore -U postgres -f output.sql backup_file
psql -U postgres -d target_db -f output.sqlpg_restoreの主なオプション
| № | オプション | 意 味 |
|---|---|---|
| １ | -U <username> | リストアを実行するユーザー |
| ２ | -d <dbname> | リストアするデータベース |
| ３ | -C | 新規にDBを作成してリストア |
| ４ | -f <file> | SQL スクリプトを出力 |
| ５ | -l | バックアップファイルの内容をリスト表示 |
| ６ | -v | 詳細な出力 |
| ７ | –clean | 既存のオブジェクトを削除してからリストア |
| ８ | –if-exists | --clean 使用時、オブジェクトが存在する場合のみ削除 |
| 10 | –no-owner | 所有者情報を削除してリストア |
| 11 | -F <format> | バックアップ形式を指定（通常は不要） |
pg_restore の実用例
既存のデータベースを空にしてからリストア
- –clean オプション
既存のデータを削除してからリストアでします。この場合、データベース内のすべてのオブジェクト（テーブル、ビュー、関数など）が削除されます。
pg_restore -U postgres --clean -d test_db test_db.backup特定のテーブルのみをリストア
- -ｔ オプション
特定のテーブルのみをリストアします。
pg_restore -U postgres -d test_db -t my_table test_db.backuppsql でのリストア方法の種類
psql を使ったリストア方法には、以下の3種類があります。
既存のデータベースにリストアする
既存のデータベースに直接SQLスクリプトを適用する方法です。このコマンドはtest_db.sql の内容を test_db に適用し、バックアップの内容を復元します。
psql -U postgres -d test_db -f test_db.sqlpg_dumpall のバックアップをリストアする
pg_dumpall で作成したSQLファイルには、データベースの作成 (CREATE DATABASE) やロールの作成 (CREATE ROLE) などの情報も含まれています。そのため、リストア時には postgres データベースに対して実行し、新しいデータベースを作成します。
psql -U postgres -f all_databases.sqlインタラクティブモードでリストアする
SQLスクリプトの内容を手動で適用する場合は、psql の対話モードを使用します。
psql -U postgres -d test_dbプロンプトに入った後、以下のようにSQLファイルを読み込むことができます。
\i backup.sqlpsql の主なオプション
| № | オプション | 意 味 |
|---|---|---|
| 1 | -U <username> | リストアを実行するユーザーを指定（例: postgres） |
| 2 | -d <dbname> | リストアするデータベースを指定 |
| 3 | -C | CREATE DATABASE を含めてリストア（新規DB作成） |
| 4 | -f <file> | SQL スクリプトを出力 |
| 5 | -l | バックアップファイルの内容をリスト表示 |
| 6 | -v | 詳細な出力（verbose） |
| 7 | –clean | 既存のオブジェクトを削除してからリストア |
| 8 | –if-exists | --clean 使用時、オブジェクトが存在する場合のみ削除 |
| 9 | –no-owner | 所有者情報を削除してリストア |
| 10 | -F | バックアップ形式を指定（通常は不要） |
psql を使ったリストアの具体例
pg_dump で作成したSQLバックアップをリストア
pg_dumpで取得したプレーンSQL形式のバックアップ (-F p) を、既存のデータベースにリストアします。
psql -U postgres -d test_db -f test_db.sqlpg_dumpall のバックアップをリストア
pg_dumpallで作成したバックアップファイルall_databases.sqlをリストアします。
psql -U postgres -f all_databases.sql特定のSQLコマンドを直接実行
- データベースを作成してからSQLバックアップをリストアします。
psql -U postgres -c "CREATE DATABASE new_db;"
psql -U postgres -d new_db -f test_db.sqlON_ERROR_STOP=1 でエラー発生時に停止
- 通常、
psqlはエラーが発生しても続行しますが、エラー時に処理を停止するには-v ON_ERROR_STOP=1を指定します。
psql -U postgres -d test_db -v ON_ERROR_STOP=1 -f test_db.sqlまとめ
PostgreSQLのバックアップ・リストアは、データの保全や移行に不可欠な操作です。以下のツールを用途に応じて正しく使い分けましょう。
- バックアップツール
| № | コマンド | 用途 | 形式 | 特徴・注意点 |
|---|---|---|---|---|
| １ | pg_dump | 単一データベースのバックアップ | -F p（プレーンSQL）, -F c（カスタム） | カスタム形式は pg_restore でリストア |
| ２ | pg_dumpall | 全データベース・ロール・設定をバックアップ | -F p（プレーンSQL のみ） | psql でのみリストア可能 |
- リストアツール
| № | コマンド | 用途 | 特徴・注意点 |
|---|---|---|---|
| １ | pg_restore | pg_dump のカスタム・タール・ディレクトリ形式 をリストア | -C で新規DB作成、-j で並列リストアが可能 |
| ２ | psql | プレーンSQL形式 (pg_dump -F p / pg_dumpall) をリストア | -f ファイル名 で適用、ON_ERROR_STOP=1 でエラー時停止 |
脚 注
- 「pgAdmin 4」によるバックアップ/リストアは下記ブログを参照下さい。
ブログ：「pgAdmin 4」によるバックアップ/リストア ↩︎
