pgdumpコマンド、pgdumpallコマンド、pg_restoreコマンド
バックアップ形式 | バックアップコマンド | リストアコマンド |
---|---|---|
平文 | pg_dump , pg_dumpall |
psql |
平文以外 | pg_dump |
pg_restore |
pgdumpコマンド、pgdumpallコマンドによるバックアップ
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
: customd
: directoryt
: tarp
: 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コマンドを使った平文形式以外のリストア
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 -
が必要
- 【補】PostgreSQL12からは
- ファイルからリストア
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によるベースバックアップ
-
- 全てのデータファイルをディスクに書き出す
- PostgreSQL拡張
-
いつ行われる
-
checkpoint_timeout
に設定した時間が前回checkpointから経過した- デフォルト5min
-
WALファイルの総サイズが
max_wal_size
を超えたら- デフォルト1GB
- WALセグメント1つのサイズはデフォルト16MBなので64 + 1ファイル (バージョン12時点)
pg_start_backup()
関数による強制実行- などなど
-
-
バックアップにはPostgreSQLサーバーの停止の必要なし
- cf. リストアには必要
-
流れ
-
pg_start_backup()
関数呼び出し- チェックポイントの強制実行
-
データベースクラスタのディレクトリを丸ごとバックアップ
- tgzとかで
-
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によるリカバリの手順
-
ながれ
- ベースバックアップのリストア
-
ロールフォワードリカバリの準備
- ベースバックアップに残存している古いWALファイル(
$PGDATA/pg_wal/
)を削除 - 未アーカイブのWALファイルを
$PGDATA/pg_wal/
へコピー
- ベースバックアップに残存している古いWALファイル(
-
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 [, ...] ) ]