Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationEventAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
20
Help us understand the problem. What are the problem?

More than 1 year has passed since last update.

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

参考

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
20
Help us understand the problem. What are the problem?