コマンドプロンプトとpg_dumpを使ってデータベース単位でのバックアップを取得する
前のエントリの続きです。
コマンドプロンプトでデータベースの一覧を取ってきたのは、データベース単位でダンプしたいからでした。PostgreSQLにはpg_dumpallという全バックアップの方法があるんですが、個別のデータベースを復旧させたい(それ以外のデータベースに影響を与えたくない)という状況には向いていません 1 。
先のエントリで取ってきたデータベースのリストを使い、個々のデータベースごとにバックアップファイルを作ります。
コマンドプロンプト(DOS)でFOR文を使う
処理ですが
①psqlとクエリからデータベース名を取得し
② FORの変数としてひとつずつ格納
③ pg_dumpに引き渡してバックアップファイルを作る
という流れです。DOSのFOR文で①の結果を食いたい場合は /f オプションでトークン拾う指定を噛ませ、usebackqを使いバッククオートの処理内容(①の文字)を呼び出していきます。
FOR /f "usebackq" %i in (`psql -U postgres -t -q -d postgres -c "SELECT datname IN pg_database;"`) DO [バックアップ処理]
%iに変数が入っていくのでこのままでも良いんですが、このクエリだとpostgresやtemplateといったデータベースもバックアップ対象になってしまうので、WHEREを使っていらないものを削ります。
FOR /f "usebackq" %i in (`psql -U postgres -t -q -d postgres -c "SELECT datname IN pg_database WHERE datname NOT IN ('postgres', 'template0', 'template1');"`) DO [バックアップ処理]
pg_dumpでバックアップ処理
DOの後にpg_dumpを使ってバックアップ処理を記述します。上の[バックアップ処理]の部分を埋めていく作業です。pg_dumpのバックアップは以下のような書き方です。
pg_dump -U postgres [データベース名] > [バックアップファイル名]
FOR文の%iにデータベース名が入ってくるので、これをDOの後で受けて
FOR /f "usebackq" %i in (`psql -U postgres -t -q -d postgres -c "SELECT datname IN pg_database WHERE datname NOT IN ('postgres', 'template0', 'template1');"`) DO pg_dump -U postgres %i > %i.backup
PostgreSQLのバックアップファイルは.backup拡張子なので、%i.backupと記します。これで各データベース単位のバックアップファイルを生成できるはず。フォルダを指定したい場合は
FOR /f "usebackq" %i in (`psql -U postgres -t -q -d postgres -c "SELECT datname IN pg_database WHERE datname NOT IN ('postgres', 'template0', 'template1');"`) DO pg_dump -U postgres %i > C:\User\Desktop\%i.backup
みたいな書き方をします。
バッチファイル化
ここまでの内容はコマンドプロンプト上では上手く行きますが、バッチファイル化すると動きません。コマンドプロンプト上で.batを実行すると「i の使い方が間違っています。」というメッセージが表示されます。先人の知見を頼りに解決していきましょう。
上のTipsには明確な答えはないんですが、サンプル読んでると見えてくるものがあります。バッチファイルにする場合変数は%%iでないと駄目っぽい 2 。従って以下のように書き換えてやると.batでも動きます。
FOR /f "usebackq" %%i in (`psql -U postgres -t -q -d postgres -c "SELECT datname IN pg_database WHERE datname NOT IN ('postgres', 'template0', 'template1');"`) DO pg_dump -U postgres %%i > C:\User\Desktop\%%i%.backup
これでタスクスケジューラに渡すことができるので、定期的なバックアップが取りやすくなりますね。 追記です。PostgreSQLの9.2以前ではうまく動きません。オプションの扱いが変わってるようなので、エラーを見て該当するオプションを外してください。
bashだとどうするか
ンゴゴDOSですか・・・bashならこれでよかとですが・・・
— ぱぴろんちゃん👓 (@papiron) 2017年7月26日
$ echo 'SELECT datname FROM pg_database;' | psql -t -q | awk NF | sed 's/.*/pg_dump & > &.dump/' | sh