環境
PostgreSQL12.4
Ubuntu20.4
はじめに
PostgreSQLでバックアップを取るコマンドには２種類あります。
データベース単位でバックアップを取る場合は、pg_dumpを使います。複数のデータベースが混在していて、その中から特定の１つのデータベースに対してバックアップを取りたい場合はpg_dumpを使います。また、複数のデータベースに対して、ごそっと丸ごとバックアップを取りたい場合は、pg_dumpallを使います。
pg_dumpallはデータベース全体に対してバックアップを取るため、当然、ロールのバックアップも取ってくれます。しかし、pg_dumpだとロールのバックアップまでは取ってくれません。
リストアするときにデータベース単位でリストアをしたい場合は、pg_dumpを使ってバックアップを取得することになります。開発でテスト的に更新したデータを元に戻すような場合はpg_dumpを使います。
データベースそのものが起動しなくなったり、データが全て破損してしまったというような障害に対して、可用性を高めるためにバックアップを取得するのであれば、pg_dumpallを使ってバックアップを取ることになります。
pg_dump、pg_dumpallは、ともにPostgreSQLから提供されているクライアントツールです。よって、コマンドはOSのプロンプトから実行します。PostgreSQLにログインしてから実行するSQLコマンドではありません。
pg_dumpallで取得したバックアップをリストアするときは、psqlコマンドを使いますが、pg_dumpで取得したバックアップをリストアするときは、作成したダンプのファイル形式によって、リストアするコマンドが変わってきます。
圧縮がかかっていない、平文（プレーン/スクリプト形式）でダンプを作成した場合は、psqlを使ってリストアします。そして、圧縮したファイル形式（カスタム/アーカイブ形式）でダンプを作成した場合は、pg_restoreを使ってリストアします。pg_restoreにはtオプションがあり、tオプションを使うと、テーブル単位でリストアすることができます。
pg_dumpには排他ロックがかからない仕様になっています。よって、ユーザーからテーブルに対して、読み書きが発生している最中にpg_dumpを実行してもユーザーのレスポンスには影響が出ないようになっています。
pg_dumpを使って、スクリプト形式でバックアップを取得した場合、出力されるダンプはテキスト形式となるため、エディタでデータ中身を確認することができます。アーカイブ形式で出力したダンプをエディタで見ても、当然、文字化けしたデータしか見ることができません。
pg_dump
pg_dumpのコマンド例です。
#pg_dump -Fp -c -h localhost -d hoge_development -p 5432 -U postgres -t m_user -f /vagrant/hoge/dump_01.sql
オプションの説明
-Fp
出力形式を指定します。pはplainを指定しています。plainは平文形式で出力します。plainで作成すれば、後から、データの中身を確認することができますが、アーカイブファイル形式で保存しておいた方がセキュリティ的にはいいということにはなります。
| オプション -F | Table単位のバックアップ | Table単位のリストア |
|---|---|---|
| プレーン | 〇 | × |
| アーカイブ | 〇 | 〇 |
プレーン形式で全テーブルのダンプを作成した場合、ダンプの中からテーブル単位でリストアをすることはできません（自分でSQL文を加工すれば可能にはなります）。テーブル単位でリストアしたい場合は、テーブル単位でダンプを作成してくださいということになります。
アーカイブ形式で全テーブルのダンプを作成した場合は、ダンプの中からテーブル単位でリストアをすることができます。
-c
出力形式をplainにした場合のみ有効になるオプションです。出力形式でアーカイブ形式を指定した場合は有効になりません。
-cをつけると、バックアップダンプの中に、Drop tableと、Creata tableのSQL文が付加されます。-cをつけないと、Drop tableが付かずに、Creata tableだけが付加されます。
リストアする前に各テーブルに対し手動でDrop tableしてからリストアする場合は、-cは付けないことになります。テーブルだけでなくインデックスも削除されてから再作成されることになりす。
| オプション -c | Drop Table | Create Table |
|---|---|---|
| あり | 〇 | 〇 |
| なし | × | 〇 |
-cを付けて作成したダンプに対して、手動でDrop Tableをした後にリストアをすると、存在しないテーブルに対して、Drop Tableが流れるため、「テーブルが存在しません」といったエラーがでることになります。
-cを付けずに作成したダンプに対して、手動でDrop Tableをしないでリストアをすると、存在するテーブルに対して、Create Tableが流れるため、「既にテーブルが存在します」といったエラーがでることになります。
エラーが出た後、データのリストア処理はちゃんと流れますが、なんとなく不安だなと思う場合は、手動でしっかりとDrop Tableをしてからエラーを出すことなくリストアさせた方が無難です。
-d
ダンプを作成するデータベース名を指定します。
-p
ポート番号を指定します。
デフォルトのポート番号のままで変更していない場合は省略しても問題ありません。
-h
サーバーのホスト名を指定します。サーバーにログインした状態でバックアップを取る場合は、localhostになります。
-U
ログインするユーザー名を指定します。スーパーユーザーであるpostgresでいいかと思います。
-t
テーブル単位でダンプを取得したい場合は、テーブル名を指定します。省略するとデータベースに含まれる全テーブルがダンプの対象になります。
-f
出力先のパスとファイルを指定します。拡張子は付けなくても問題ありませんが、プレーン形式でダンプを作成する場合、拡張子はsqlにしておくと分かり易いかと思います。
参考（公式マニュアル）
pg_dump
https://www.postgresql.jp/document/12/html/app-pgdump.html
pg_restore
https://www.postgresql.jp/document/12/html/app-pgrestore.html
psql
https://www.postgresql.jp/document/12/html/app-psql.html