運用管理（52%）：：：バックアップ方法【重要度：7】dokoQL不可

 

バックアップとリストア

 

• バックアップ：DBを安全などこかに保存すること
• リストア：バックアップの逆：保存していたDBを元に戻して動くようにすること

 

バックアップ（リストア）の種類

• バックアップ（リストア）には、オンラインとオフラインがある（Fig.1 参照）

 

※Fig.1 において、オンラインは server is running 、オフラインは no server running と表記

 

• オンラインバックアップ（リストア）・・・PostgreSQL 稼働中のバックアップ（リストア）
• オフラインバックアップ（リストア）・・・PostgreSQL 停止中のバックアップ（リストア）

 

 

 

バックアップ（リストア）の対象

• バックアップ（リストア）の対象は、DBC、DB、Table となる（Fig.1 参照）

 

• DBC・・・DBC を丸ごとバックアップ（リストア）
• DB：DB1, DB2, DB3・・・DBC 中の個々のDB をバックアップ（リストア）
• Table：Table1, Table2・・・DB 中の個々のTable をバックアップ（リストア）

 

 

 

オンラインバックアップ（リストア）

 

  暗記  

• 使用プログラムは、pg_dumpall, pg_dump, pg_restore, psql, copy, \copy

 

• 使用プログラム
  • アプリケーション、SQL、メタコマンド

 

• • PostgreSQLクライアントアプリケーション
    • pg_dumpall, pg_dump, pg_restore, psql
  • SQL文
    • copy
  • psql のメタコマンド
    • \copy

 

• 対象
  • DBC, DB, Table

 

• • DBC・・・pg_dumpall / psql
  • DB  ・・・pg_dump / pg_restore
  • Table  ・・copy文 / \copy

 

 

  暗記  

• 入出力形式は、平文、カスタム、tar がある
• DB のときは、全形式使用できる
  • DBC, Table のときは、平文のみ使用

 

• 入出力形式
  • 平文、カスタム、tar

 

• • DBC・・・平文（.sql）
  • DB  ・・・平文（.sql）、カスタム（.dump）、tar（.tar）
  • Table  ・・平文（.txt）

 

 

DBC

• 書式：pg_dumpall  [-f ファイル名]
• 書式：psql  [-f ファイル名]

例：DBC のバックアップ・リストア

[postgres]$ pg_dumpall > db.sql
[postgres]$ psql -f db.sql
[postgres]$ ▯

※拡張子sql は平文形式（SQL文）。テキストエディタで読める

DB

• １つのDBC には、複数のDB が存在
• 書式：pg_dump  [-Fp | -Fc | -Ft ]  [-f ファイル名]  [DB名]
  • -F：Format（出力形式）
    • -Fp：plane（平文）：デフォルト
    • -Fc：custom（カスタム）
    • -Ft：tar（tar）
• 書式：pg_restore  [-d DB名]  [ファイル名]

例：1つのDBC に複数DB

[postgres]$ psql -l
-----------+----------+-----------+---------+-------+-------------------
   Name    |  Owner   | Encoding  | Collate | Ctype | Access privileges
-----------+----------+-----------+---------+-------+-------------------
 examdb    | postgres | SQL_ASCII | C       | C     |
 postgres  | postgres | SQL_ASCII | C       | C     |
 template0 | postgres | SQL_ASCII | C       | C     |
 template1 | postgres | SQL_ASCII | C       | C     |
-----------+----------+-----------+---------+-------+-------------------

[postgres]$ ▯

例：平文形式でバックアップした場合

[postgres]$ pg_dump examdb -f examdb.sql
[postgres]$ psql -f examdb.sql
[postgres]$ ▯

※拡張子sql は平文形式（SQL文）

 

例：カスタム形式でバックアップした場合

[postgres]$ pg_dump -Fc examdb -f examdb.dump
[postgres]$ pg_restore -d examdb examdb.dump
[postgres]$ ▯

※拡張子dump はバイナリ圧縮形式

例：tar形式でバックアップした場合

[postgres]$ pg_dump -Ft examdb -f examdb.tar
[postgres]$ pg_restore -d examdb examdb.tar
[postgres]$ ▯

※拡張子tar はtar形式

Table

• 1つのDB には、複数のテーブルが存在
• テーブルに格納されているデータのみファイルにバックアップ
• COPY文（SQL）
  • 書式：copy テーブル名  to       { ‘ファイル名’  |  stdout }
  • 書式：copy テーブル名  from  { ‘ファイル名’  |  stdin }

例：1つのDB に複数Table

[postgres]$ psql -U user1 examdb
examdb=> \dt
------------------------------ 
       List of relations
--------+------+-------+-------
 Schema | Name | Type  | Owner
--------+------+-------+-------
 public | tab1 | table | user1
 public | tab2 | table | user1
 public | tab3 | table | user2
--------+------+-------+-------

examdb=> ▯

 

例：Table のバックアップ・リストア

examdb=> copy tab1 to   ‘/tmp/table1.txt’;      // table1.txt：サーバーマシン上のファイル
examdb=> copy tab1 from ‘/tmp/table1.txt’;

 

• \copy（psql のメタコマンド）
  • 書式：\copy  テーブル名  to       { ファイル名  |  stdout }
  • 書式：\copy  テーブル名  from  { ファイル名  |  stdin }

 

examdb=> \copy tab1 to    table1.txt      // table1.txt：クライアントマシン上のファイル
examdb=> \copy tab1 from  table1.txt

 

 

オフラインバックアップ（リストア）

 

  暗記  

• オフラインでは、Linux コマンドを使用する

 

• DBC をコピーする

backup

$ pg_ctl stop
$ cd /home/postgres/data/..
$ tar cvf backup.tar data

 

restore

$ cd /home/postgres
$ tar xvf backup.tar
$ pg_ctl start

 

 

PITR（Point In Time Recovery）

 

postgresql.conf

• wal_level（enum：replica）：WALの情報度合い
  • minimal, replica, logical
  • 設定反映：PostgreSQL起動時
• archive_mode（boolean：off）：WALの有効/無効
  • 設定反映：PostgreSQL起動時
• archive_command（string）：シェルコマンド
  • WALファイル → WALアーカイブ

 

オンラインバックアップ

• 書式：pg_start_backup( ‘ラベル名’ )
• 書式：pg_stop_backup( )

 

$ cd  $PGDATA/..
$ psql  -c  “select pg_start_backup(‘label1’);”
$ tar czvf /mnt/backup.tar.gz data
$ psql  -c  “select pg_stop_backup();”

 

 

オフラインリストア

 

• PostgreSQL停止

 

$ psql stop				// PostgreSQL停止
$ mv $PGDATA /mnt			// DBC移動
$ tar xzvf /mnt/backup.tar.gz		// base backup を展開
$ rm -rf $PGDATA/pg_wal			// 古いWALを削除
$ cp -r /mnt/data/pg_wal $PGDATA	// 未アーカイブWALをコピー

 

 

• recovery.conf 作成
• PostgreSQL起動

 

$ cat $PGDATA/recovery.conf			// シェルコマンド
restore_command = ‘cp /mnt/archivedir/%f %p’	// OS の cpコマンド
$ pg_ctl start					// PostgreSQL起動

 

参考

• IT資格といえばLPI-Japan | LinuC/OSS-DB/HTML5/ACCEL/OPCEL
• PostgreSQL 11.5文書
• OSS教科書 OSS-DB Silver Ver2.0対応
• 徹底攻略OSS-DB Silver問題集［Ver.2.0］対応
• dokoQL 学習用オンラインSQL実行環境
• スッキリわかるSQL入門 第2版 ドリル222問付き! (スッキリシリーズ)