PostgreSQLのバックアップとリストアについて、主にリストアにpsqlを使う方法とpg_restoreを使う方法で混乱したので、調べて理解したことを以下にまとめ、カスタム形式のダンプデータをpg_restoreを使ってリストアを行う為の方法をケース別に解説します。
カスタム形式と平文形式の違い
バックアップ
| 保存形式 | バックアップコマンド | 圧縮 | データのみ | スキーマのみ | テーブル指定 |
|---|---|---|---|---|---|
| カスタム形式 | pg_dump -Fc | 〇 | 〇 | 〇 | 〇 |
| 平文形式 | pg_dump -Fp(省略可) | × | 〇 | 〇 | 〇 |
リストア
| 保存形式 | リストアツール | データのみ | スキーマのみ | テーブル指定 | SQLに変換 | TOCの確認 ※2 | 他DBへの移行 |
|---|---|---|---|---|---|---|---|
| カスタム形式 | pg_restore | 〇 | 〇 | 〇 | 〇 | 〇 | 〇※3 |
| 平文形式 | psql等SQL実行環境 | △※1 | △※1 | △※1 | － | × | 〇 |
※1…バックアップ時に選択した対象を全てリストアする
※2…TOC=Table Of Contents。そのバックアップファイルにどのようなデータが格納されているかを一覧形式で確認したり、一覧から選んだデータだけリストアできる。
※3…SQLに変換してから行う。変換時に標準SQLオプションを指定する事ができる為、平文形式より柔軟性が高い。
目的別コマンドサンプル一覧
この記事で紹介されている目的別のコマンドラインサンプルを一覧でまとめます。
コマンドライン中の -h YOUR_HOST -p YOUR_PORT -U YOUR_USERのDB接続情報オプションは省略しています。-dオプションが付いている場合、これらも必要に応じてオプションに追加して下さい。
カスタム形式のダンプデータを扱う場合（お勧め）
| 目的 | コマンドラインサンプル |
|---|---|
| DBのバックアップ | pg_dump -Fc -d YOUR_DBNAME > backup.dump |
| DBのリストア | pg_restore --clean -d YOUR_DBNAME backup.dump |
| 新規DBにリストア | createdb NEW_DATABASE_NAME pg_restore -d NEW_DATABASE_NAME backup.dump |
| DBの削除 | dropdb DB_NAME |
| 指定テーブルのリストア | pg_restore --clean -t TABLE_NAME -d NEW_DATABASE_NAME backup.dump |
| データのみのリストア | pg_restore --data-only -d YOUR_DBNAME backup.dump |
| ダンプからデータを抽出 | pg_restore --data-only -t TABLE_NAME -f backup_dataonly.sql backup.dump |
| ダンプのTOCを出力 | pg_restore backup.dump -l > backup.list |
| listファイルを用いたリストア | pg_restore -L backup.list backup.dump |
平文形式のダンプデータを扱う場合
| 目的 | コマンドラインサンプル |
|---|---|
| DBのバックアップ | pg_dump -Fp -d YOUR_DBNAME > backup.sql |
| データのみバックアップ | pg_dump -Fp --data-only -d YOUR_DBNAME > backup_data-only.sql |
| スキーマのみバックアップ | pg_dump -Fp --schema-only -d YOUR_DBNAME > backup_schema-only.sql |
| 指定テーブルのバックアップ | pg_dump -t TABLE_NAME -Fp -d YOUR_DBNAME > backup.sql} |
| INSERT文でバックアップ | pg_dump --inserts -Fp -d YOUR_DBNAME > backup.sql |
| INSERT文(列名込み)でバックアップ | pg_dump --column-inserts -Fp -d YOUR_DBNAME > backup.sql |
| リストア | psql -d YOUR_DBNAME < backup.sql |
pg_dump と psql, pg_restoreの関係
pg_dump と pg_restore は対をなすプログラムで、名前の通り pg_dump を使ってバックアップを取り、pg_restore を使ってリストアします。
pg_dump は「カスタム形式（バイナリ）」のダンプデータと、「平文SQLスクリプト形式」のダンプデータを出力することができます。（他にもありますがこの記事では割愛）
カスタム形式のダンプデータは、pg_restore コマンドでリストアします。
平文SQLスクリプト形式のダンプデータの中身はただのSQLなので、psqlコマンドの入力ファイルにすると、リストアが可能です。
ここで注意が必要なのは、pg_dump の規定の出力形式は「平文のSQLスクリプトファイル」 という点です。
そしてこの平文のSQLスクリプトファイルを、pg_restoreは処理することができません。
pg_dumpが規定で出力する平文SQLを処理できるのがpsqlなので、pg_dump と pg_restore ではなく、pg_dump と psql を組み合わせたバックアップ方法しか紹介していないサイトもあるようです。
しかし、分かってくると便利なのは、psqlではなくpg_restore を使ったリストア方法です。その為には、pg_dump -Fc オプションでカスタム形式でのバックアップを行う必要があります。日常的なバックアップ業務において、平文SQLスクリプトファイルとしてバックアップを取らなくてはならない理由は、殆どのケースでないでしょう。（異なるバージョン間、又は異なるデータベースシステム間での移行の際には、平文SQLでのバックアップが必要となります）
この記事では、カスタム形式でバックアップを行い、pg_restoreを使ってリストアを行う場合のケース別のコマンドオプション指定方法をまとめます。
pg_dump によるカスタム形式でのバックアップ
pg_restoreが処理できるのは、カスタム形式でバックアップされたダンプファイルです。
カスタム形式でバックアップを取るには、pg_dump に -Fc オプションを付けます。
pg_dump -Fc -h YOUR_HOST -p YOUR_PORT -d YOUR_DBNAME > backup.dump
-h には、PostgreSQLがインストールされているマシン名、又はIPアドレスを指定します。ローカル端末の場合は localhost と書くか、-h オプションを省略します。
-p には、PostgreSQLの待ち受けポート番号を指定します。規定値の5432の場合、-p オプションを省略できます。
-d には、バックアップを取りたいデータベース名を指定します。mydb1 等になります。
例として、localhostの5432番ポートで実行されているPostgreSQLのmydb1データベースのバックアップを取りたい場合、次のようになります。
pg_dump -Fc -h localhost -p 5432 -d mydb1 > backup.dump
コマンド実行後、postgres ユーザーのパスワードを聞かれます。
もし、ユーザー名を指定して実行したい場合は、次のように -U オプションを指定します。
pg_dump -Fc -h YOUR_HOST -p YOUR_PORT -U YOUR_USER -d YOUR_DBNAME > backup.dump
カスタム形式のダンプファイルは、平文SQLファイルに比べて次の利点があります。
- ファイルサイズが小さい
- リストア時に一部のみをリストアできる
- 後で平文SQLにすることもできる
pg_restore によるリストア
現在のDBのスキーマとデータをバックアップ時点に戻す
mydb1 から取ったバックアップ backup.dump を、mydb1 に対してリストアします。
スキーマもデータもリストアします。
DBのスキーマやデータをいじっていたら動作がおかしくなったので前日の夜時点のバックアップに戻したい、というような場合に用います。
pg_restore --clean -h YOUR_HOST -p YOUR_PORT -U YOUR_USER -d YOUR_DBNAME backup.dump
--clean オプションは、リストアの前にスキーマを削除＆再生成する指定です。省略形は「-c」ですが、大文字の「-C」と紛らわしい（-Cは--createの省略形であり、別のオプション）ので私は--cleanを使っています。
既にデータが存在するデータベースに対してリストアを行うので、このオプションが必ず必要です。指定しないと「既に存在するものに対してCREATEしようとした」というエラーになります。
バックアップを元に新しいデータベースを作成する
バックアップからテスト用の環境を作ったり、移行先の環境を構築したりする際に利用できる方法です。
この方法が一番利用頻度が高いかもしれません。
任意の名前のDBを新規に作成し、そこへバックアップしたスキーマとデータをリストアします。
pg_restoreには指定した名前のDBを新規作成する機能がない為、createdbコマンドで先にDBを作成し、その後にそのDBに対してリストアをします。
createdb -h YOUR_HOST -p YOUR_PORT -U YOUR_USER NEW_DATABASE_NAME
pg_restore -h YOUR_HOST -p YOUR_PORT -U YOUR_USER -d NEW_DATABASE_NAME backup.dump
一度 createdb してしまえば、またバックアップ時点の状態に戻したい時は、--clean を付けて以下のようにすればOKです。
pg_restore --clean -h YOUR_HOST -p YOUR_PORT -U YOUR_USER -d NEW_DATABASE_NAME backup.dump
これで、テスト後には上記の方法で毎回リセットすることができるようになります。
ちなみに pg_restoreには --create（又は-C）というオプションがあり、このオプションの説明には以下のように書かれています。
リストア前にデータベースを作成します。 --cleanも同時に指定されている場合、接続する前に対象データベースを削除し再作成します。
これを読むと、いちいちcreatedbを実行せずとも--createを付ければいいのではと思いますが、この説明には続きがあります。
このオプションがある場合、-dで指定したデータベースは最初のDROP DATABASEとCREATE DATABASEコマンドの発行時にのみ使用されます。 そして、すべてのデータはアーカイブ内に記述された名前のデータベースにリストアされます。
つまり、このオプションを指定するとリストア先が NEW_DATABASE_NAME ではなくダンプファイルを作成した際の元のデータベースになってしまう、ということです。--createオプションはバックアップ元にリストアする際にしか使えないということでしょう。
補足として、作成してテスト用のDBが不要になったら、次のコマンドで削除できます。
dropdb -h YOUR_HOST -p YOUR_PORT -U YOUR_USER DB_NAME
指定したテーブルのみをバックアップ時点に戻す
既存のDBに対して、指定したテーブルのみをバックアップ時点に戻す方法です。
スキーマとデータをリストアします。
例えば何度もやり直す必要があるテストの際に、特定のテーブルを毎回リセットするようなケースです。
こちらも利用頻度が高い方法かもしれません。
pg_restore --clean -t TABLE_NAME -h YOUR_HOST -p YOUR_PORT -U YOUR_USER -d NEW_DATABASE_NAME backup.dump
-t オプションには、リストアしたいテーブル名を指定します。
また、複数のテーブルを指定したい時は -t オプションを複数指定することもできます。
テーブル名には"*"や"?"などのワイルドカードを指定することもできます。その場合、-t "mst*" のように、テーブル名を二重引用符で囲い、ワイルドカードがコマンドライン・シェルに解釈されないようにする必要があります。
現在のDBのデータのみをバックアップ時点に戻す
データとスキーマは一対一ですから、通常は「データのみをリストアする」ということはあまりありませんが、時には必要になるかもしれません。
pg_restoreにはその為の --data-only オプションがあります。
pg_restore --data-only -h YOUR_HOST -p YOUR_PORT -U YOUR_USER -d YOUR_DBNAME backup.dump
--data-only オプションは、データのみをリストアするオプションです。省略形は「-a」ですが、覚えにくいしバッチファイルに記述したりする際にも可読性が低いので、私は--data-onlyを使っています。
注意事項として、データを復元する前に元のデータと同じキーを持つレコードがテーブルに存在していてはなりません。そうしないと、同じキーを持つレコードを復元しようとして「キーの重複」エラーになります。
そこで、復元前にテーブルのデータを削除しておく必要があります。
「データの復元前にdelete from table_name をしてくれるオプション」があると良いのですが、そのようなオプションは pg_restore には無いようです。--clean オプションが使えそうに思えますが、--cleanと--data-onlyを同時に指定することはできません。
よって、この方法を行うのは主に、「データが空っぽだがスキーマだけはある」ようなデータベースに初期データを入れるようなケースとなりそうです。
ダンプファイルから指定のデータのみを抽出してテキストファイルに取り出す
応用として、ダンプファイルからデータのみを抽出してテキストファイルに取り出したい時に、以下の方法を使えます。
pg_restore --data-only -t TABLE_NAME -f backup_dataonly.sql backup.dump
-t オプションは、出力したいテーブル名を指定します。全テーブルのデータを取り出したい場合は -t オプションを省略します。
-f オプションは、出力先のファイルを指定します。
出力されるのは、PostgreSQLのCOPYコマンドを利用した平文のダンプです。
いちいちリストアせずとも、この方法でダンプファイルの中身を見ることが可能となります。
高度なリストア（listファイルを用いたリストア対象の選択）
ダンプファイルにどんなデータが含まれているのか、そのTOC（コンテンツ一覧）を見ることができます。
pg_restore backup.dump -l
この結果をファイルに保存します。
pg_restore backup.dump -l > backup.list
このファイルをテキストエディタで開くと、以下のようになっています。
;
; Archive created at Mon Sep 14 13:55:39 2009
; dbname: DBDEMOS
; TOC Entries: 81
; Compression: 9
; Dump Version: 1.10-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 8.3.5
; Dumped by pg_dump version: 8.3.8
;
;
; Selected TOC Entries:
;
3; 2615 2200 SCHEMA - public pasha
1861; 0 0 COMMENT - SCHEMA public pasha
1862; 0 0 ACL - public pasha
317; 1247 17715 TYPE public composite pasha
319; 1247 25899 DOMAIN public domain0 pasha
セミコロンで始まる行はコメントで、行の先頭の番号は、各行の内部アーカイブIDを示しています。
このファイルの各行に対して、コメントアウト、削除、並び替えを行うことができます。
10; 145433 TABLE map_resolutions postgres
;2; 145344 TABLE species postgres
;4; 145359 TABLE nt_header postgres
6; 145402 TABLE species_records postgres
;8; 145416 TABLE ss_old postgres
その後、このファイルを -L オプションに指定して pg_restore を実行すると、編集した結果でリストアをすることができます。
pg_restore -L backup.list backup.dump
なかなか便利そうですが、私が現在メンテナンス中のシステムのデータベースは複雑すぎて、この方法で取捨選択するのは現実的ではない感じでした。
トラブルシューティング・メモ
pg_restore -t オプションでテーブル単位でリストアするとインデックスが再現されない
以下のように、server1のmydbのカスタム形式のダンプファイルから、server2のtestdbへtable1, table2のみをリストアしたいとします。
pg_dump -Fc -h server1 -d mydb -U myuser > db.dump
pg_restore --clean --if-exists -t table1 -t table2 -h server2 -d testdb -U myuser db.dump
--cleanは同名の既存テーブルを削除するオプションで、--if-existsは削除の際に「IF EXISTS」を付けてくれるオプションです。
一見問題なくできそうに見えるし、実際上記の処理は問題なく実行されますが、結果を確認すると、testdbのtable1, table2 にはインデックスが貼られていません。
pg_restoreコマンドについて調べ得ると、-tオプションでテーブルを復元した際にインデックスが失われるのは仕様らしいです（なぜ？）。
pg_restoreには-Iというオプションがあり、これは指定したインデックスをリストアするオプションらしいので、それぞれのインデックスを指定して次のように実行してみても、特にエラーなく終了する割にはインデックスはやはり貼られていませんでした。（PostgreSQL 15で確認）
pg_dump -Fc -h server1 -d mydb -U myuser > db.dump
pg_restore --clean --if-exists -t table1 -t table2 -I table1_pkey -I table2_pkey -h server2 -d testdb -U myuser db.dump
そこで、やり方を変えて、pg_dumpの方で-tオプションを指定して必要なテーブルのみをdumpし、それを-tオプションなしでpg_restoreしたところ、希望通りインデックス付きでテーブルがリストアされました。
pg_dump -Fc -t table1 -t table2 -h server1 -d mydb -U myuser > db.dump
pg_restore --clean --if-exists -h server2 -d testdb -U myuser db.dump
ただこれだと、-tオプションをつけない定期バックアップからの任意のテーブル復元がやりにくいので、正しい方法があるならば知りたいところです。
おまけ：平文SQLスクリプトでのダンプとpsqlの利用
この記事ではカスタム形式でのダンプとpg_restoreの利用についてまとめていますが、時には平文SQLスクリプトで出力した結果をpsqlを使ってリストアしなければならないケースもあるかもしれないので、簡単にまとめておきます。
データとスキーマをバックアップする
pg_dump -Fp -h YOUR_HOST -p YOUR_PORT -U YOUR_USER -d YOUR_DBNAME > backup.sql
-Fp は規定値なので省略しても構いません。
カスタム形式とは異なり、平文SQLスクリプトでとったダンプはただのSQLコマンドのリストですから、後から「データだけをリストアしたい」とか「このテーブルだけをリストアしたい」のような取捨選択はできません。（自分でテキストエディタを開いて中身を編集すれば可能ですが、複雑なデータベースの場合、現実的ではないでしょう）
データだけ、スキーマだけをリストアしたい場合には、pg_dumpの時点で選択する必要があります。
データのみをバックアップする
pg_dump -Fp --data-only -h YOUR_HOST -p YOUR_PORT -U YOUR_USER -d YOUR_DBNAME > backup_data-only.sql
スキーマのみをバックアップする
pg_dump -Fp --schema-only -h YOUR_HOST -p YOUR_PORT -U YOUR_USER -d YOUR_DBNAME > backup_schema-only.sql
テーブルを指定してバックアップする
pg_dump -t TABLE_NAME -Fp -h YOUR_HOST -p YOUR_PORT -U YOUR_USER -d YOUR_DBNAME > backup.sql
バックアップファイルをCOPY文ではなくINSERT文で出力する
平文SQLスクリプトファイルとして出力する場合、SQLがCOPY文ではなくINSERT文の方が良いという人もいるでしょう。
その場合には、次のように --inserts オプションを指定します。
pg_dump --inserts -Fp -h YOUR_HOST -p YOUR_PORT -U YOUR_USER -d YOUR_DBNAME > backup.sql
このオプションを付けると、リストアに非常に時間がかかることにご注意ください。PostgreSQL以外のデータベースにデータを移行したい場合に有用です。
--inserts だと、列名なしのINSERT文が生成される為、スキーマの変更に弱くなります。
列名付きのINSERT文を出力したい場合には、--inserts オプションではなく --column-inserts オプションを指定します。
pg_dump --column-inserts -Fp -h YOUR_HOST -p YOUR_PORT -U YOUR_USER -d YOUR_DBNAME > backup.sql
リストア
psql -h YOUR_HOST -p YOUR_PORT -U YOUR_USER -d YOUR_DBNAME < backup.sql
データのみ、スキーマのみをリストアしたい場合には、db_dump時にデータのみを指定したバックアップやスキーマのみを指定したバックアップを入力ファイルに指定します。
新しいDBを作成してバックアップデータをリストアする
createdbコマンドで新しいDBを作成しておき、そのDBに対してリストアします。
createdb -h YOUR_HOST -p YOUR_PORT -U YOUR_USER NEW_DATABASE_NAME
psql -h YOUR_HOST -p YOUR_PORT -U YOUR_USER -d NEW_DATABASE_NAME < backup.sql