はじめに
PostgreSQLのオンライン論理バックアップでは、pg_dumpかpg_dumpallコマンドを使用します。
pg_dumpは1つのデータベースを対象にバックアップを取得します。一方、pg_dumpallはデータベースクラスタ全体のバックアップを取得することができます。
pg_dumpはバックアップ取得時点までリストアできます。データベース単位でバックアップできますが、データベースクラスタ全体はバックアップできません。また、ユーザ・ロールなどの共通のデータもバックアップできません。このようなデータをバックアップする場合はpg_dumpallを使用します。
以降でバックアップ・リストアの例を記載していますが、環境はPostgreSQL 11を使用しています。他のバージョンでも同じです。
pg_dump
pg_dumpではデータベースのバックアップをファイルに出力することができます。
pg_dumpコマンドの書式は以下のとおりです。
pg_dump データベース名 > ダンプファイル
pg_dump -f ダンプファイル データベース名
実行例
$ pg_dump testdb > test.dmp
ファイル形式
ダンプは、-Fオプション（--format）で以下を指定することができます。オプションを指定しない場合はプレーンテキストになります。
- p: プレーンテキスト
- t: tar
- c: カスタム
- d: ディレクトリ
カスタムの場合はデフォルトで圧縮されます。特に理由がなければカスタムを使うことが多いかと思います。
実行例
$ pg_dump -Fp testdb > test.dmp
$ pg_dump -Ft testdb > test.tar
$ pg_dump -Fc testdb > test.custom
$ pg_dump -Fd -f testdir testdb
※ディレクトリの場合は-fオプションで出力先のディレクトリを指定します。
冗長（詳細）モードでバックアップ
-vオプション(--verbose)でコンソールに進捗状況等の情報を出力します。
$ pg_dump -v testdb > test.dmp
pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
～省略～
pg_dump: dumping contents of table "public.pgbench_history"
pg_dump: processing data for table "public.pgbench_tellers"
pg_dump: dumping contents of table "public.pgbench_tellers"
pg_dump: creating CONSTRAINT "public.pgbench_accounts pgbench_accounts_pkey"
pg_dump: creating CONSTRAINT "public.pgbench_branches pgbench_branches_pkey"
pg_dump: creating CONSTRAINT "public.pgbench_tellers pgbench_tellers_pkey"
テーブルを指定してバックアップ
-tオプション(--table)でバックアップ対象のテーブルを指定できます。
複数テーブルを対象にする場合は-tオプションはテーブルごとに指定します。
$ pg_dump -t pgbench_accounts -t pgbench_branches testdb > test.dmp
データのみをバックアップ
-aオプション(--data-only)でデータのみをダンプします。スキーマ（データ定義）はダンプしません。
$ pg_dump -a testdb > test.dmp
定義のみをバックアップ
-sオプション(--schema-only)でスキーマ（データ定義）のみをダンプします。データはダンプしません。
$ pg_dump -s testdb > test.dmp
特定のスキーマのみをバックアップ
-nオプション(--schema)で特定のスキーマのみをダンプします。
$ pg_dump -n public testdb > test.dmp
並列（パラレル）バックアップ
-jオプション(--jobs)で並列数を指定してバックアップします。
並列バックアップの場合は、出力フォーマットはディレクトリのみを指定できます。
$ pg_dump -j 2 -Fd -f testdir testdb
※-f testdirで出力先のディレクトリを指定
リモートからバックアップ
-hでPostgreSQLサーバ、-Uでユーザ名を指定してリモートからバックアップを取得できます。
$ pg_dump -h XX.XX.XX.XX -U testuser testdb > test.dmp
※もし、ポートを変えている場合は-pオプションを使用します。
pg_restore
リストアの際はpsql、pg_restoreコマンドを使用します。
プレーンテキストの場合はpsql、それ以外（カスタム、tar、ディレクトリ）の場合はpg_restoreコマンドを使用します。
プレーンテキスト形式のダンプファイルをリストア
psql データベース名 < ダンプファイル名
実行例
$ psql testdb < test.dmp
カスタム形式のダンプファイルをリストア
カスタム形式のダンプファイルをリストアします。
tar、ディレクトリの場合も同じです。
$ pg_restore -d testdb test.custom
リストア前にデータを削除する
-cオプション(--clean)でリストア前にデータを削除します。
$ pg_restore -c -d testdb test.custom
指定したテーブルのみをリストア
-tオプション(--table)で指定したテーブルのみをリストアします。
$ pg_restore -d testdb -t pgbench_accounts test.custom
アーカイブの内容を表示する
$ pg_restore -l test.custom
;
; Archive created at 2019-12-04 14:00:25 CET
; dbname: testdb
; TOC Entries: 17
; Compression: -1
; Dump Version: 1.13-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 11.5
; Dumped by pg_dump version: 11.5
;
;
; Selected TOC Entries:
;
2; 3079 35939 EXTENSION - pgstattuple
3709; 0 0 COMMENT - EXTENSION pgstattuple
199; 1259 16392 TABLE public pgbench_accounts postgres
200; 1259 16395 TABLE public pgbench_branches postgres
197; 1259 16386 TABLE public pgbench_history postgres
198; 1259 16389 TABLE public pgbench_tellers postgres
3701; 0 16392 TABLE DATA public pgbench_accounts postgres
3702; 0 16395 TABLE DATA public pgbench_branches postgres
3699; 0 16386 TABLE DATA public pgbench_history postgres
3700; 0 16389 TABLE DATA public pgbench_tellers postgres
3575; 2606 16407 CONSTRAINT public pgbench_accounts pgbench_accounts_pkey postgres
3577; 2606 16403 CONSTRAINT public pgbench_branches pgbench_branches_pkey postgres
3573; 2606 16405 CONSTRAINT public pgbench_tellers pgbench_tellers_pkey postgres
その他
指定したテーブルの全データのinsert文を生成する
$ pg_dump --table pgbench_accounts --data-only --column-inserts testdb > test.sql
実行すると以下のようにINSERT文が生成されます。
INSERT INTO public.pgbench_accounts (aid, bid, abalance, filler) VALUES (12014, 1, 0, ' ');
INSERT INTO public.pgbench_accounts (aid, bid, abalance, filler) VALUES (12015, 1, 0, ' ');
INSERT INTO public.pgbench_accounts (aid, bid, abalance, filler) VALUES (12016, 1, 0, '