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文書)](https://www.postgresql.jp/document/11/html/app-pgdump.html)

実行例

```
$ 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文書)](https://www.postgresql.jp/document/11/html/app-pgrestore.html)

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

> 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文書)](https://www.postgresql.jp/document/11/html/app-pgdump.html)
- [pg_restore(PostgreSQL 11.5文書)](https://www.postgresql.jp/document/11/html/app-pgrestore.html)
- [pg_dumpall(PostgreSQL 11.5文書)](https://www.postgresql.jp/document/11/html/app-pg-dumpall.html)