pg_dumpコマンド、pg_dumpallコマンド、pg_restoreコマンド

バックアップ形式	バックアップコマンド	リストアコマンド
平文	`pg_dump`, `pg_dumpall`	`psql`
平文以外	`pg_dump`	`pg_restore`

pg_dumpコマンド、pg_dumpallコマンドによるバックアップ

postgres@8040c0c21fcb:/$ pg_dump --help

pg_dump dumps a database as a text file or to other formats.

Usage:
  pg_dump [OPTION]... [DBNAME]

General options:
  -f, --file=FILENAME          output file or directory name
  -F, --format=c|d|t|p         output file format (custom, directory, tar,
                               plain text (default))
...

主要なオプション
-f, --file=FILENAME
- バックアップ先のファイル名
- 省略時stdout
-F, --format=c|d|t|p (-Fp みたいな感じに指定)
- 出力形式
- c: custom
- d: directory
- t: tar
- p: plain text (デフォルト)
テーブルとデータ作っておく

CREATE TABLE tab1 (
  col1 integer,
  col2 integer,
  col3 integer
);

INSERT INTO tab1 VALUES (1, 1, 1);
INSERT INTO tab1 VALUES (2, 2, 2);

平文でstdoutにダンプ出してみる

postgres@8040c0c21fcb:/$ pg_dump -Fp

...

--
-- Name: tab1; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.tab1 (
    col1 integer,
    col2 integer,
    col3 integer
);


ALTER TABLE public.tab1 OWNER TO postgres;

--
-- Data for Name: tab1; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.tab1 (col1, col2, col3) FROM stdin;
1   1   1
2   2   2
\.

--
-- PostgreSQL database dump complete
--

カスタム形式でpostgresデータベースをファイルに書き出してみる
- PostgreSQL独自のバイナリ圧縮形式

postgres@8040c0c21fcb:/home/postgres$ pg_dump -Fc -f sample.dump

これと同義

postgres@8040c0c21fcb:/home/postgres$ pg_dump -Fc > sample.dump

全データベース書き出すにはpg_dumpall

psqlコマンドを使った平文形式のリストア

psql -f <ファイル名>で流し込むだけ

pg_restoreコマンドを使った平文形式以外のリストア

公式/pg_restore

postgres@8040c0c21fcb:/home/postgres$ pg_restore --help

pg_restore restores a PostgreSQL database from an archive created by pg_dump.

Usage:
  pg_restore [OPTION]... [FILE]

General options:
  -d, --dbname=NAME        connect to database name
  -f, --file=FILENAME      output file name (- for stdout)
...

データベース名を指定すると、そのデータベースへリストアする
データベース名を指定しない場合、平文のSQLをstdoutに書き出す
- 【補】PostgreSQL12からは-f -が必要
ファイルからリストア

postgres@8040c0c21fcb:/home/postgres$ pg_restore -p 5433 -d postgres < sample.dump

標準出力に書き出し(customやtarをデコードする感じ)

postgres@8040c0c21fcb:/home/postgres$ pg_restore sample.dump -f -

--
-- PostgreSQL database dump
--
...

バックアップ&リストアの組み合わせの例

(pg_dump/pg_dumpall) + (pg_restore/psql -f)
いずれもサーバ稼働中に行える
- オンラインバックアップ(ホットバックアップ)
- 論理バックアップ
  - テーブル構造やデータを意識する
  - 【補】別のメジャーバージョンへのリストア可能

postgres@8040c0c21fcb:/home/postgres$ pg_restore -p 5433 -d postgres < sample.dump

既にオブジェクトがあると怒られる

pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 8; 2615 16384 SCHEMA exam_schema postgres
pg_restore: error: could not execute query: ERROR:  schema "exam_schema" already exists
Command was: CREATE SCHEMA exam_schema;


pg_restore: from TOC entry 203; 1259 16385 TABLE tab1 postgres
pg_restore: error: could not execute query: ERROR:  relation "tab1" already exists
Command was: CREATE TABLE public.tab1 (
    col1 integer,
    col2 integer,
    col3 integer
);


pg_restore: warning: errors ignored on restore: 2

cleanオプションを指定すると、restore前にデータベースオブジェクトを削除する

  -c, --clean                  clean (drop) database objects before recreating

postgres@8040c0c21fcb:/home/postgres$ pg_restore -p 5433 -c -d postgres < sample.dump

エラー出ない

postgres@8040c0c21fcb:/home/postgres$

ディレクトリコピーによるバックアップ&リストア

サーバを停止してデータベースクラスタをコピーする方法
- オフラインバックアップ(コールドバックアップ)
- 物理バックアップ
  - テーブル構造やデータを意識しない
  - 【補】別のメジャーバージョンへのリストア不可

PITR: Point In Time Recovery

公式
ロールフォワードに使うログ

PITRの概要

base backup
- ある時点でのデータベース全体のバックアップ
WAL: Write Ahead Log
- すべての変更の記録 (=差分)
- 16MBのファイル(ログセグメント)に記録されていく
  - ファイルサイズはinitdbの--wal-segsize=SIZE_IN_MBで設定できる
- 古いものをアーカイブして再利用していく

PITRによるバックアップの前準備/設定

公式
wal_level
- WALに書き込まれる情報の度合い (下のものほど情報が多い)
  - minimal
    - 即時シャットダウン後のクラッシュリカバリに必要な最低限のみ
    - PITR不可
  - replica (デフォルト)
    - WALアーカイブ、レプリケーション対応
  - logical
    - 論理デコード対応
- postmaster
archive_mode
- WALセグメントをarchive_commandでWALアーカイブ入りするかどうか
  - off
    - しない
  - on
    - する
  - always
    - する
    - 普段はonと変わらない
    - アーカイブリカバリ、スタンバイモード中も有効
archive_command
- WALセグメントをWALアーカイブとしてコピーするためのシェルコマンド
- cpとかcopyとか
- プレースホルダ
  - %p
    - ファイルパス
  - %f
    - ファイル名
- 例
  - cp %p /mnt/server/archivedir/%f'
- $PGDATA/pg_wal/のWALがコピーされていく

PITRによるベースバックアップ

【補】公式/CHECKPOINT
- 全てのデータファイルをディスクに書き出す
- PostgreSQL拡張
いつ行われる
- checkpoint_timeoutに設定した時間が前回checkpointから経過した
  - デフォルト5min
- WALファイルの総サイズがmax_wal_sizeを超えたら
  - デフォルト1GB
  - WALセグメント1つのサイズはデフォルト16MBなので64 + 1ファイル (バージョン12時点)
- pg_start_backup()関数による強制実行
- などなど
バックアップにはPostgreSQLサーバーの停止の必要なし
- cf. リストアには必要
流れ
1. pg_start_backup()関数呼び出し
  - チェックポイントの強制実行
2. データベースクラスタのディレクトリを丸ごとバックアップ
  - tgzとかで
3. pg_stop_backup()関数呼び出し
  - バックアップモード終了
コンフィグ設定
- archive_modeをonに
- archive_commandを設定
  - とりあえずダミーのexit 0にした(雑)

  # - Archiving -
   
  #archive_mode = off       # enables archiving; off, on, or always
+ archive_mode = on     # enables archiving; off, on, or always
                # (change requires restart)
  #archive_command = ''     # command to use to archive a logfile segment
+ archive_command = 'exit 0'        # command to use to archive a logfile segment
                # placeholders: %p = path of file to archive
                #               %f = file name only
                # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
  #archive_timeout = 0      # force a logfile segment switch after this
                # number of seconds; 0 disables

pg_start_backup()関数実行

postgres@8040c0c21fcb:/home/postgres$ psql -p 5433 -c "SELECT pg_start_backup('label', true);"

 pg_start_backup 
-----------------
 0/4000028
(1 row)

(この間にデータベースクラスタディレクトリを丸ごとバックアップする)

tarとかmvとか

pg_stop_backup()関数実行

postgres@8040c0c21fcb:/home/postgres$ psql -p 5433 -c "SELECT pg_stop_backup();"

NOTICE:  all required WAL segments have been archived
 pg_stop_backup 
----------------
 0/4000138
(1 row)

PITRによるリカバリの手順

ながれ
1. ベースバックアップのリストア
2. ロールフォワードリカバリの準備
  1. ベースバックアップに残存している古いWALファイル($PGDATA/pg_wal/)を削除
  2. 未アーカイブのWALファイルを$PGDATA/pg_wal/へコピー
3. recovery.confを設定してPostgreSQLを起動
  - PostgreSQL11のドキュメント
    - recovery.confはrecovery.doneになる
  - 【補】PostgreSQL12からはpostgresql.confへ統合された

postgresql.conf

...
# - Archive Recovery -

# These are only used in recovery mode.

#restore_command = ''       # command to use to restore an archived logfile segment
                # placeholders: %p = path of file to restore
                #               %f = file name only
                # e.g. 'cp /mnt/server/archivedir/%f %p'
                # (change requires restart)
#archive_cleanup_command = ''   # command to execute at every restartpoint
#recovery_end_command = ''  # command to execute at completion of recovery
...

未アーカイブのWALファイルのコピーを行わなかった場合、WALアーカイブの最新の部分までがリカバリされる
- つまり、ベースバックアップ時点にリカバリされる
- 障害発生時点まではロールフォワードしない

COPY文と\copyコマンド

	COPY文	\copyコマンド
処理場所	サーバサイド	クライアントサイド
filename	シングルクォートで囲む、絶対パス	シングルクォートで囲まない、相対パス

\copyコマンドはN/Wを介した通信が発生する
- 大容量ファイルの場合はCOPY文を使おう

COPY文

postgres@8040c0c21fcb:/home/postgres$ psql -c "\h COPY"

Command:     COPY
Description: copy data between a file and a table
Syntax:
COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]
    [ WHERE condition ]

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' | PROGRAM 'command' | STDOUT }
    [ [ WITH ] ( option [, ...] ) ]

主要なオプション
- DELIMITER 'delimiter_character'
  - 各行の列の区切り文字
  - デフォルト水平タブ
- FORMAT format_name
  - csvとか
- HEADER [ boolean ]
  - csvのヘッダ行の入出力をするか否か
STDINの場合\.を、ファイルの場合EOFを読むまで入出力する
pg_dumpで平文バックアップしたSQLの中身

COPY public.tab1 (col1, col2, col3) FROM stdin;
1   1   1
2   2   2
\.

\copyコマンド

\copy { table [ ( column_list ) ] | ( query ) } { from | to } { 'filename' | program 'command' | stdin | stdout | pstdin | pstdout } [ [ with ] ( option [, ...] ) ]

勉強日記

チラ裏

OSS-DB試験対策緑本 ch6 バックアップとリストア

pg_dumpコマンド、pg_dumpallコマンド、pg_restoreコマンド

pg_dumpコマンド、pg_dumpallコマンドによるバックアップ

psqlコマンドを使った平文形式のリストア

pg_restoreコマンドを使った平文形式以外のリストア

バックアップ&リストアの組み合わせの例

ディレクトリコピーによるバックアップ&リストア

PITR: Point In Time Recovery

PITRの概要

PITRによるバックアップの前準備/設定

PITRによるベースバックアップ

PITRによるリカバリの手順

COPY文と\copyコマンド

COPY文

\copyコマンド