PostgreSQLのオンライン論理バックアップ（pg_dump）

はじめに

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(PostgreSQL 11.5文書)

実行例

$ 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コマンドを使用します。

• pg_restore(PostgreSQL 11.5文書)

プレーンテキスト形式のダンプファイルをリストア

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, '                              

参考

• pg_dump(PostgreSQL 11.5文書)
• pg_restore(PostgreSQL 11.5文書)
• pg_dumpall(PostgreSQL 11.5文書)