バックアップ

バックアップはハードウェアの故障などにより、ハードウェアなどの障害が発生してデータベースが利用できなくなったり、データの操作を誤ってしまい間違ったデータを登録してそのデータが参照され他のデータもおかしくなり手作業では元に戻せないなど正常な状態に復元するために行う作業です。

バックアップには全てデータベースの全てをバックアップするフルバックアップと前回取得したフルバックアップから更新されたデータのみをバックアップする差分と増分があります。

毎回フルバックアップを実施するのもいいのですが、フルバックアップは全てのデータベースですので時間が掛かる上にデータ容量も大きくなります。

運用の開始時点では良いのですがデータ容量が増てくると定期的なフルバックアップ完了しなかったり、システム性能に影響を与える場合もありますので、差分や増分バックアップを検討します。

Linuxであれば、cronなどを利用して定期的に実行します。 また、上記を決めたらデータベース運用管理マニュアルに記載してその手順通りに動作することも確認します。
正常にバックアップがとれていること(容量確認など)を定期的に確認する必要もあります。

リカバリ

バックアップしたファイルを利用してリカバリを行います。
基本的にリカバリを行えば、同様に動作する意味合いです。
そのため、リカバリしてファイルを復元させるだけではなくIPアドレスの変更など環境に起因する問題もリカバリ手順として含める必要があります。
例えば、本番サーバとバックアップサーバがあり、本番サーバがダウンしたことを想定してバックアップサーバにリカバリを行い本番サーバと同様の動作をしていることを確認します。
データベース運用管理マニュアルに記載する必要があります。

移行

新サービス移行・ハードウェア経年劣化・OSサポート期間終了などの理由でデータベースを新システムにいこうする場合について記載します。

(4) バックアップ

PostgresSQLのバックアップ方法には次の3つがあります。
PostgresSQLのバックアップとリストアは同じバージョンで行う必要があります。

1. コールドバックアップ
2. ホットバックアップ
3. PITRによるバックアップ

[1. コールドバックアップ]
コールドバックアップとは、PostgresSQLを停止させて、データベースクラスタをコピーすることです。
テーブルスペースを利用している場合はそのディレクトリもコピーすることが必要です。
コピー後はそのまま起動すればよく、設定ファイルなども引き継げます。

[2. ホットバックアップ]
pg_dump・pg_dumpall・pg_basebackupのコマンドを利用してPostgresSQLを停止していない状態でバックアップをすることです。

[pg_dump]
pg_dumpコマンドで、psqlコマンドでリストア可能な形式として出力したバックアップファイルには、データベースを作成するSQLコマンドも含まれています。

pg_dump [OPTION]... [DBNAME]

General options:
-f, --file=FILENAME 出力するファイルまたはディレクトリを指定します。
-F, --format=c|d|t|p 出力時のファイルフォーマット(custom, directory, tar,plain text (default))を指定します。
-j, --jobs=NUM 並列ダンプを利用します。
-v, --verbose 詳細な情報を出力いたします。
-V, --version バージョンを表示します。
-Z, --compress=0-9 圧縮レベルを指定します。
--lock-wait-timeout=TIMEOUT ダンプ時に共有テーブルのロックがあった場合のタイムアウトを設定します。
-?, --help ヘルプを表示します。

Options controlling the output content:
-a, --data-only データのみをダンプ(スキーマは含まない。)します。
-b, --blobs ラージオブジェクトをダンプに含めます。
-c, --clean データベースオブジェクトを整理（削除）するコマンドを書き出します。
-C, --create ダンプにデータベース作成コマンドを含めます。
-E, --encoding=ENCODING ダンプ時の文字コードを設定します。
-n, --schema=SCHEMA スキーマのみダンプします。
-N, --exclude-schema=SCHEMA 指定したスキーマのパターンのみダンプしません。
-o, --oids ダンプ時にOIDを含めます。
-O, --no-owner データベースの所有権を出力しません。
-s, --schema-only スキーマのみをダンプ(データは含まない。)します。
-S, --superuser=NAME トリガ向こうのためのスーパーユーザを指定します。
-t, --table=TABLE 指定したテーブルのみダンプします。
-T, --exclude-table=TABLE 指定したテーブルにマッチしたものをダンプ出力しません。
-x, --no-privileges アクセス権限をダンプに含みません。
--binary-upgrade アップグレード用のユーティリティを使用します。
--column-inserts カラム名と一緒にインサートコマンドをダンプします。
--disable-dollar-quoting 関数本体用のドル引用符の使用を無効にします。
--disable-triggers リストア時にトリガーを無効にします。
--enable-row-security 行セキュリティがある場合に指定します。
--exclude-table-data=TABLE 指定したテーブルのパターンのみダンプしません。
--if-exists データベースオブジェクトを初期化するときに条件を使用します。
--inserts コピーではなくインサートコマンドでダンプします。
--no-security-labels セキュリティラベルをダンプしません。
--no-synchronized-snapshots 9.2以前で-jオプションを利用できます。
--no-tablespaces テーブル空間を選択するダンプ時に出力しません。
--no-unlogged-table-data ログを取らないテーブルの内容をダンプしません。
--quote-all-identifiers 強制的にすべての識別子に引用符を付与します。
--section=SECTION 指定した部分のみをダンプします。
--serializable-deferrable 使用されるスナップショットがその後のデータベース状態と一貫性を持つことを保証します。
--snapshot=SNAPSHOT データベースのダンプを作成する時に、指定した同期スナップショットを使用します。
--strict-names 各スキーマ指定(-n/--schema)および各テーブル指定(-t/--table)が、ダンプされるデータベース内の少なくとも1つのスキーマおよびテーブルにマッチ時に指定します。
--use-set-session-authorization オブジェクトの所有者を指定するためにダンプに出力します。

Connection options:
-d, --dbname=DBNAME 出力するファイルまたはデータベースを指定します。
-h, --host=HOSTNAME 接続するホスト名を指定します。
-p, --port=PORT 接続するポート番号を指定します。
-U, --username=NAME 接続するユーザ名を指定します。
-w, --no-password 接続時にパスワード無しを指定します。
-W, --password 接続時にパスワードを指定します。
--role=ROLENAME 接続時にロール名を指定します。

「pg_dumpコマンド（例)」
バックアップコマンド
# pg_dump -Fc testdb -f testdb.dump

pg_restore restores a PostgreSQL database from an archive created by pg_dump.
pg_restoreコマンドにencodingオプションはありません。

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

General options:
-d, --dbname=NAME 接続するデータベース名を指定します。
-f, --file=FILENAME リストア時のファイルを指定します。
-F, --format=c|d|t|p リストア時のファイルフォーマット(custom, directory, tar,plain text (default))を指定します。
-l, --list アーカイブの内容を一覧として出力します。
-v, --verbose 詳細な情報を出力いたします。
-V, --version バージョンを表示します。
-?, --help ヘルプを表示します。

Options controlling the restore:
-a, --data-only データのみをリストア(スキーマは含まない。)します。
-c, --clean データベースオブジェクトを整理（削除）するコマンドをリストアします。
-C, --create ダンプにデータベース作成コマンドをリストアします。
-e, --exit-on-error エラーが発生した場合終了します。
-I, --index=NAME 指定したインデックスをリストアします。
-j, --jobs=NUM 並列ダンプを利用してリストアします。
-L, --use-list=FILENAME FILENAME出力の選択/順序付けにこのファイルの目次を使用します。
-n, --schema=NAME 指定したスキーマをリストアします。
-O, --no-owner オブジェクト所有権を飛ばします。
-P, --function=NAME(args) 指定した関数をリストアします。
-s, --schema-only スキーマのみをリストア(データは含まない。)します。
-S, --superuser=NAME トリガーを無効にするために使用するスーパーユーザーのユーザー名を指定します。
-t, --table=TABLE 指定したテーブルのみリストアします。
-T, --trigger=NAME 指定したトリガーのみリストアします。
-x, --no-privileges 権限情報をスキップします。
-1, --single-transaction シングルトランザクションのみリストアします。
--disable-triggers リストア時にトリガーを無効にします。
--enable-row-security 行セキュリティを有効にします。
--if-exists オブジェクトの削除時にIF EXISTSを使用します--no-data-for-failed-tablesはテーブルのデータを復元しません - データのみの復元中にトリガーを無効にします。
--no-data-for-failed-tables 関連するテーブルの作成に失敗してもテーブルデータオブジェクトはリストアします。
--no-security-labels セキュリティラベルはリストアしません。
--no-tablespaces テーブル空間コマンドを利用してリストアしません。
--section=SECTION 指定した部分のみをリストアします。
--strict-names スキーマ指定(-n/--schema)およびテーブル指定(-t/--table)がバックアップファイル内の少なくとも1つのスキーマあるいはテーブルにマッチするか指定します。
--use-set-session-authorization ALTER OWNERコマンドの代わりに、標準SQLのSET SESSION AUTHORIZATIONコマンドを出力して、オブジェクトの所有権を決定します。

Connection options:
-d, --dbname=DBNAME データベースを指定します。
-h, --host=HOSTNAME 接続するホスト名を指定します。
-p, --port=PORT 接続するポート番号を指定します。
-U, --username=NAME 接続するユーザ名を指定します。
-w, --no-password 接続時にパスワード無しを指定します。
-W, --password 接続時にパスワードを指定します。
--role=ROLENAME 接続時にロール名を指定します。

リストアコマンド
# pg_restore -d testdb testdb.dump

pg_dumpall extracts a PostgreSQL database cluster into an SQL script file.

Usage:
pg_dumpall [OPTION]...

General options:
-f, --file=FILENAME 出力するファイルを指定します。
-v, --verbose 詳細な情報を出力いたします。
-V, --version バージョンを表示します。
-Z, --compress=0-9 圧縮レベルを指定します。
--lock-wait-timeout=TIMEOUT ダンプ時に共有テーブルのロックがあった場合のタイムアウトを設定します。
-?, --help ヘルプを表示します。

Options controlling the output content:
-a, --data-only データのみをダンプ(スキーマは含まない。)します。
-c, --clean データベースオブジェクトを整理（削除）するコマンドを書き出します。
-g, --globals-only グローバルオブジェクトのみをダンプします。
-o, --oids ダンプ時にOIDを含めます。
-O, --no-owner データベースの所有権を出力しません。
-r, --roles-only dump only roles, no databases or tablespaces
-s, --schema-only スキーマのみをダンプ(データは含まない。)します。
-S, --superuser=NAME トリガ向こうのためのスーパーユーザを指定します。
-t, --tablespaces-only テーブルスペースのみダンプします。
-x, --no-privileges アクセス権限をダンプに含みません。
--binary-upgrade アップグレード用のユーティリティを使用します。
--column-inserts カラム名と一緒にインサートコマンドをダンプします。
--disable-dollar-quoting 関数本体用のドル引用符の使用を無効にします。
--disable-triggers リストア時にトリガーを無効にします。
--if-exists データベースオブジェクトを初期化するときに条件を使用します。
--inserts コピーではなくインサートコマンドでダンプします。
--no-security-labels セキュリティラベルをダンプしません。
--no-tablespaces テーブル空間を選択するダンプ時に出力しません。
--no-unlogged-table-data ログを取らないテーブルの内容をダンプしません。
--quote-all-identifiers 強制的にすべての識別子に引用符を付与します。
--use-set-session-authorization オブジェクトの所有者を指定するためにダンプに出力します。

Connection options:
-d, --dbname=DBNAME 出力するファイルまたはデータベースを指定します。
-h, --host=HOSTNAME 接続するホスト名を指定します。
-l, --database=DBNAME 初期のデータベースを選択します。
-p, --port=PORT 接続するポート番号を指定します。
-U, --username=NAME 接続するユーザ名を指定します。
-w, --no-password 接続時にパスワード無しを指定します。
-W, --password 接続時にパスワードを指定します。
--role=ROLENAME 接続時にロール名を指定します。

「pg_dumpallコマンド（例)」
バックアップコマンド
# pg_dumpall -f all.sql
リストアコマンド
# psql -f testdb.sql

[pg_basebackup]
バックアップにはレプリケーションプロトコルが用いられ、リモートで動作している別サーバからもベースバックアップを取得することができます。
max_wal_sendersを、バックアップ用に少なくとも１つのセッションを残すように十分高く設定する必要があります。
pg_basebackupでは常にデータベースクラスタ全体のバックアップを取得します。
pg_basebackupではテーブル空間のバックアップも含まれます。
※同じサーバの場合、テーブル空間も同じPathになるので注意が必要です。

Usage:
pg_basebackup [OPTION]...

Options controlling the output:
-D, --pgdata=DIRECTORY 書き出すディレクトリを指定します。
-F, --format=p|t 出力時のファイルフォーマット(plainまたはtar)を指定します。
-r, --max-rate=RATE 最大転送速度を指定します。
-R, --write-recovery-conf 最低限のrecovery.confを書き出します。
-S, --slot=SLOTNAME レプリケーションで利用するスロット名を指定します。
-T, --tablespace-mapping=OLDDIR=NEWDIR テーブル空間をバックアップ中とマッピングします。
-x, --xlog バックアップにWALファイルを含めます。(fetch mode)
-X, --xlog-method=fetch|stream 必要なWALファイルを含めます。
--xlogdir=XLOGDIR トランザクションログをディレクトリを指定します。
-z, --gzip gzip圧縮で出力します。
-Z, --compress=0-9 指定したレベル圧縮で出力します。

General options:
-c, --checkpoint=fast|spread チェックポイントでfastまたはspreadを設定します。
-l, --label=LABEL バックアップラベルを設定します。
-P, --progress 進行状況を表示します。
-v, --verbose 詳細な情報を出力いたします。
-V, --version バージョンを表示します。
-?, --help ヘルプを表示します。

Connection options:
-d, --dbname=DBNAME 出力するファイルまたはデータベースを指定します。
-h, --host=HOSTNAME 接続するホスト名を指定します。
-p, --port=PORT 接続するポート番号を指定します。
-s, --status-interval=INTERVAL サーバに返送する状態パケットの間隔を秒単位で指定します。
-U, --username=NAME 接続するユーザ名を指定します。
-w, --no-password 接続時にパスワード無しを指定します。
-W, --password 接続時にパスワードを指定します。

[3. PITRによるバックアップ]
PITRはPoint In Time Recoveryで、バックアップをもとに任意の時点へ復元させれます。
pg_xlog ディレクトリが消失した場合、リカバリ時に一部の更新情報が失われ、最新の状態にまで復旧できない場合があります。
ベースバックアップを再取得すれば、古いアーカイブログは削除しても問題ありません。
WALログ位置を特定するLSN(Log Sequence Number)がユニークで付与されます。
ストリーミングレプリケーションと合わせて利用し、復元には、任意のリストアポイント・任意のトランザクションID・最後のWALログ位置・任意の時刻)を指定します。
recovery_target_timelineをデフォルト値の場合、ベースバックアップ取得時の最新のタイムラインに回復します。
PITRの設定の流れを次に記載します。

1. postgresql.conf設定
wal_level = archive or host_standby
#archiveはログをを追加します。hot_standbyは更にスタンバイ上の読取専用の問い合わせが可能です。
archive_mode = on
#archive_commandとセットで設定します。
archive_command = 'cp %p /tmp/postgres/%f'
#WALファイルを退避させるアーカイブコマンドを設定します。

2. postgresql再起動
# service postgresql-9.6 restart

3．ベースバックアップの開始
postgres=# select pg_start_backup('base.tar.gz')

4. ファイルコピー
# mkdir /tmp/postgres/
# mkdir /tmp/postgres/base
# chown -R postgres:postgres /tmp/postgres
# tar czf /tmp/postgres/base/base.tar.gz /var/lib/pgsql/9.6/data

5. ベースバックアップの終了
postgres=# select pg_stop_backup();

6. アーカイブログの確認
# /usr/pgsql-9.6/bin/pgbench -i -s 3
# /usr/pgsql-9.6/bin/pgbench
# ls -l /tmp/postgres/

PITRを利用したリストアの流れを次に記載します。

1. postgresqlサービス停止
# service postgresql-9.6 stop

2. ベースファイルの解凍
# cd /
# tar zxf /tmp/postgres/base/base.tar.gz

3. リカバリ設定
# rm -fr /var/lib/pgsql/9.6/data/pg_xlog/*
# vi /var/lib/pgsql/9.6/data/recovery.conf

4. recovery.confの設定
restore_command = 'cp /tmp/postgres/%f %p'
#archive_commandの逆を設定します。
#recovery_target_name =
#今回は設定しませんが、リストアポイントを設定します。
#recovery_target_time =
#今回は設定しませんが、リカバリしたい時刻を設定します。
#recovery_target_xid =
#今回は設定しませんが、リカバリしたいxidを設定します。
#リカバリ設定をしない場合、最後のWAL位置までリカバリします。
#設定を２つ以上指定したた場合、最後に指定されたものが使われます。
#初期状態では最後までリストアします。

次の関数を用いることで、事前にリストアポイントを定義することができます。

postgres=# select pg_create_restore_point('rpoint1');
pg_create_restore_point
-------------------------
0/902ACF8
(1 row)

5. postgresqlサービス起動
# chown postgres:postgres /var/lib/pgsql/9.6/data/recovery.conf
# service postgresql-9.6 start
※/var/lib/pgsql/9.6/data/recovery.doneが存在すればリカバリ完了です。

[リカバリ制御関数]
pg_is_xlog_replay_paused()
→リカバリが停止中であれば真を返す
pg_xlog_replay_pause()
→即座にリカバリを停止する(スーパーユーザに限定)
pg_xlog_replay_resume()
→もしリカバリ停止中であれば再開する(スーパーユーザに限定)

[4. CLUSTER]
CLUSTERとは、インデックスに従ってテーブルをクラスタ化します。
PRIMARY KEYが存在しなくても問題がなく、テーブルおよびインデックスサイズ少なくすることと性能が改善する可能性があります。
maintenance_work_memの値を大きくすると性能向上が期待できます。
コマンド実行時にはテーブルロックが発生します。
単一テーブルで複数のインデックスがある場合、同時に最適化することはできません。
インデックスtest_indに従って、テーブルtestをクラスタ化する場合は次のコマンドです。

postgres=# CLUSTER test USING test_ind;

← 再編成
セキュリティ →