【OSS-DB Silver】PostgreSQLのバックアップとリストア

OSS-DB Silver

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

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

分類 説明
一般知識(16%) OSS-DBの一般的特徴、リレーショナルデータベースに関する一般知識
運用管理(52%) インストール、ツールの使い方、設定ファイル、バックアップ方法、運用管理作業
開発/SQL (32%) SQLコマンド、組み込み関数、トランザクションの概念

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

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

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

pg_dump/pg_dumpall/pg_restore

バックアップ形式 バックアップコマンド リストアコマンド
平文形式 pg_dump/pg_dumpall psql
平文形式以外 pg_dump 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_dumppg_dumpallpg_restoreはPostgreSQL実行中に実施する

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

PITR(Point In Time Recovery)

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

概要

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

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

前準備/設定

postgresql.conf

  • wal_level : デフォルトはreplica、PITR使用時はreplicalogicalを指定する。
  • 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はスーパユーザ権限は不要かつ、相対パスでもよい

コメント