OSS-DB　Silver

OSS-DBは色々なものがありますが、商用データベースとの連携に優れ、エンタープライズシステムでも多くの導入実績があるのが「PostgreSQL」です。
OSS-DB Silverは「PostgreSQL　11」に関する能力を問う資格です。

試験で問われる範囲は以下の通りです。

これらから50題出題され、65%程度の正答率で合格となります。

今回は運用管理の中からバックアップ方法について説明します。

Sec 6. バックアップとリストア

pg_dump/pg_dumpall/pg_restore

バックアップ

pg_dump [接続オプション] [-Fp | -Fc | -Ft] [-f ファイル名] [DB名]
pg_dumpall [接続オプション] [-f ファイル名]

• データベース名を指定する場合はpg_dump、全ての取得をする場合はpg_dumpall
• バックアップの出力形式
  • -Fp : 平文(plain)
  • -Fc : カスタム形式（PostgreSQL独自のバイナリ形式）
  • -Ft : tar形式
• ファイル名を指定しない場合は標準出力に出力される

リストア

• 平文のリストア

  psql [接続オプション] [-f ファイル名]

• 平文形式以外のリストア

  pg_restore [接続オプション] [オプション] [-d DB名] [ファイル名]

  • DB名を指定しなかった場合は標準出力に出力される

その他

• pg_dump、pg_dumpall、pg_restoreはPostgreSQL実行中に実施する

• ディレクトリごとコピーしておく方法もある（物理バックアップ）

PITR(Point In Time Recovery)

データベース全チアのバックアップに加えて、PostgreSQLの運用中に、アーカイブとして記録された更新データを基にデータベースをリカバリする方法。

概要

• ベースバックアップ：ある時点での全体のバックアップ
• WAL; Write Ahead Logging; 先行書き込みログ：データベースに対しする変更のログ
• WALアーカイブ：WALファイルは16MBなので、ディスクがいっぱいになった時に待避する

上記３点を用いてロールフォーワード形式でリカバリしていく

前準備/設定

postgresql.conf

• wal_level : デフォルトはreplica、PITR使用時はreplicaかlogicalを指定する。
• archive_mode : WALアーカイブを有効にするかどうか
• archive_command : WALファイルをWALアーカイブにとしてコピーするためのシェルコマンドを指定する

ベースバックアップ

1. pg_start_backup('ラベル名')を呼び出す

   psql -c "SELECT pg_start_backup('label', true);"

2. データベースクラスタのディレクトリをコピー

   tar czvf /mnt/backup.tar.gz data

3. pg_stop_backup()を呼び出す。

   psql -c "SELECT pg_stop_backup()"

※スーパユーザでの実行が必要

リカバリの手順

1. ベースバックアップのリストア

   tar xzvf /mnt/backup.tar.gz

2. 未アーカイブのWALファイルをコピー

   rm -rf $PGDATA/pg_wal #ベースバックアップに残存するWALファイルは削除
   cp -r /mnt/data/pg_wal $PGDATA #未アーカイブのWALファイルのコピー

3. recovery.confファイルを設定してPostgreSQLを起動
   $PGDATAディレクトリの下にrecovery.confファイルを用意して起動すると、リカバリが開始する。

   cat $PGDATA/recovery.conf
   restore_command = 'cp /mnt/archivedir/%f %p'

COPY文と\copyコマンド

テーブルのデータをファイルにコピー／ファイルのデータをテーブルへローディングする方法

• COPY : サーバ側で動作

  COPY テーブル名 TO {'絶対パスのファイル名' | STDOUT} [[WITH] (オプション [, ...])]
  COPY テーブル名 FROM {'絶対パスのファイル名' | STDIN} [[WITH] (オプション [, ...])]

  オプション

  • DELIMITER '区切り文字' : デフォルトはタブ
  • FORMAT csv : CSV形式で入出力する場合
  • HEADER : ヘッダ行の指定。出力では各列の名前が出力され、入力では先頭行が無視。CSV形式を指定した場合に有効

• \copy : クライアント側で動作

  \copy テーブル名 to {ファイル名 | stdout} [with] [delimiter [as] '区切り文字'] [csv [header]]
  \copy テーブル名 from {ファイル名 | stdin} [with] [delimiter [as] '区切り文字'] [csv [header]]

  • 通信が発生するので負荷が大きい
    →サーバ上のファイルにアクセス可能であればCOPYを使用する方がよい
  • COPYはスーパユーザ権限が必要かつ、絶対パスで記載
    <->\copyはスーパユーザ権限は不要かつ、相対パスでもよい