postgresql_バックアップ

JITAKU_SVR_Wiki

postgresqlに接続する場合は、postgresqlのSuperUser(postgres)にスイッチする必要がある。

# su - postgres

データベース全体のバックアップ [#ce607ac8]

pg_dumpall †

$ pg_dumpall > pg_dumpall.sql
=>含まれるsqlにはdrop文は含まれていない。
つまり、真っ白なDBに対してリストアすることが前提。

$ pg_dumpall -c > pg_dumpall.sql
=>含まれるsqlにはdrop文は含まれていいる。
つまり、すでに存在しているDBに対してリストアすることが前提。

TIPS †

真っ白なDB、すでに存在しているDBを注意する必要がある。
なぜなら、何もないDBにDROPを走らせてもエラーになる。
逆にすでに存在しているDBにDROPを走らせないとエラーになる(warning?未確認)ことを考慮する必要がある。

データベース全体リストア †

上記の結果で分かるように、リストアする場合もdumpを取った形式によってリストアの方法も変わってくる。

psqlコマンドでリストア †

$ psql < pg_dumpall.sql
以下のエラーが出る場合

psql:pg_dumpall:11: ERROR:  role "postgres" already exists
etc...

これがすでに存在しているパターン。
この場合はpg_dumpallをオプションなしで出力していることになるので、一度DBを真っ白にする必要がある。
(データベースをDROPする必要がある)

データベース個別バックアップ †

pg_dump †

pg_dumpはバックアップを出力するときの形式によってリストア方法が変わる。
出力できるバックアップの形式はテキスト、バイナリと指定することが可能になる。

以下は、出力形式がバイナリでsql文に-c、-Cをそれぞれ使用した場合
・pg_dumpパターン1
$ pg_dump -Fc postgres > pg_dump.sql

・pg_dumpパターン2
$ pg_dump -Fc -c postgres > pg_dump-c.sql

・pg_dumpパターン3
$ pg_dump -Fc -C postgres > pg_dump-C.sql

・pg_dumpパターン4
$ pg_dump -Fc -c -C postgres > pg_dump-Cc.sql

オプションについて

-F format

p：プレインテキストのSQLを出力する(デフォルト)
t：pg_restoreへの入力に適したtarアーカイブを出力する。
   このアーカイブ書式の使用は、データベースがリストアされる時のスキーマ要素の削除もしくは並び替えを可能にする。
   どのデータがリストア時にリロードされるかを制限することも可能。
c：pg_restoreへの入力に適したカスタムアーカイブを出力する。
   これは、もっとも柔軟な書式であり、スキーマ要素だけでなくデータロードの並び替えも可能。
   この書式もデフォルトで圧縮される。

その他オプションについて
オプションの指定で内容が大きく変わるので必ず確認すること。

-C：--createの略でdumpから戻すときには''データベースを作成する''意味になる。
    当然リストア先にデータベースが存在する場合にはエラーが出て処理できない。
-c：--cleanの略でdumpから戻すときには''データベースを掃除する''意味になる。
    つまり、すでに存在していても一度削除してから再構築することになる。

postgresqlのグローバルデータの取得 †

$ pg_dumpall -g > dump.global.`date +%Y%m%d`

グローバルデータ(グローバルオブジェクト)のみ取得
※グローバルデータとは『ロールとテーブル空間』を指す

つまり、ユーザや権限など、DBの中身ではなく外身といった感じ。
①globalデータをリストアする
②個別でデータベースをリストアする
という形にすれば、pg_dumpallを使用しなくても良い。
※pg_dumpallで取得するとdumpファイルが大きすぎる場合や、データベースが複数ある場合は、pg_dumpallは避けた方が良いという意見が目立つ。真意は未検証のため不明。

pg_dumpからのリストア †

バイナリ形式のバックアップからのリストアについて †

pg_dumpで取得したバックアップがバイナリ形式の場合はpg_restoreコマンドを使用する必要がある。
バイナリ形式と、テキスト形式の違いは、データ内容や、処理にかかる時間などによるため、自分の環境にあったほうを指定する。

1.バックアップを取得する
$ pg_dump database_name -Fc -f pg_dump.sql
オプションについて

-Fcは上記で既出のため省略
-f:出力するファイルの指定

2.リストア先のDBにリストアするためのDBがあるかを確認する。
存在していない場合は作成する。
$ psql

3.リストア
$ pg_restore < pg_dump.sql

テキスト形式のバックアップからのリストアについて †

pg_dumpで取得したバックアップがテキスト形式の場合はpsqlコマンドを使用する必要がある。

1.バックアップを取得する
$ pg_dump database_name -Fp -f pg_dump.sql
※dbが不膣用で邪魔な場合は削除、かリネームする

postgres=# drop database database_name;

2.リストア先のDBにリストアするためのDBがあるかを確認する。
$ psql

postgres=# \l
        List of databases
   Name    |  Owner   | Encoding
-----------+----------+----------
  test     | postgres | UTF8
ここでもし、リストア先のDBがなかった場合は作成する必要がある。
postgres=# create database test;

3.リストアする
$ psql database_name < pg_dump.sql
※database_nameを指定しないと、postgresに入ってしまうので注意。

テーブル単位のバックアップ、リストア †

バックアップ †

$ pg_dump -d [データベース名] -t [テーブル名] > (出力)ファイル名

リストア †

$ psql [データベース名] < (上記で出力した)ファイル名

※すでにテーブルが存在している場合はスエラーとなるのでテーブルを削除(drop)する必要がある。

# psql
postgres=# drop table hoge;
削除した上で再度、リストアをする。