2014/08/04
PostgreSQL
 >  バックアップコマンドを SQL で作成
特定のテーブルやスキーマだけバックアップを取る場合、pg_dump のコマンドオプションで設定するのが普通。除外指定や正規表現など複雑なこともできる。しかし実際に対象になったテーブルやスキーマは pg_dump を実行してみないと分からず、指定を間違えると痛い。そこで、SQL でシステムカタログから対象を絞り込みつつ pg_dump 文字列を生成し、COPY コマンドで一つのバッチファイルに出力してみる。実行環境は 2014/07/28 を、pg_dump の詳細は下記を参照。

■ PostgreSQL 9.3.2文書 : pg_dump
https://www.postgresql.jp/document/9.3/html/app-pgdump.html

今回使うシステムカタログはテーブル・スキーマに関わる下の二つだけ。pg_tables からスキーマ名だけ取得することもできるが、DISTINCT か GROUP BY でクエリが少し複雑になるので pg_namespace を併用する。

■ PostgreSQL 9.3.2文書 : システムカタログ : pg_tables
https://www.postgresql.jp/document/9.3/html/view-pg-tables.html

■ PostgreSQL 9.3.2文書 : システムカタログ : pg_namespace
https://www.postgresql.jp/document/9.3/html/catalog-pg-namespace.html

↓ テーブル一つずつの pg_dump コマンドを生成する例。オプション -t の後にスキーマ・テーブル名が続くようにする。データベース接続や出力ファイル等のオプションを一回書けば済むが、その分、一テーブルのバックアップのたびに pg_dump が起動するという処理の冗長さがある。

■ クエリ例 1
SELECT format('pg_dump -p 5434 -U postgres -w -d %I -t %I.%I -E SJIS >> %s'
, 'xxxxxx' -- データベース名
, schemaname, tablename
, 'R:/bu_test.sql' -- 出力ファイル名
) FROM pg_tables
WHERE schemaname NOT IN ('information_schema', 'pg_catalog', 'public')
-- バックアップ対象を、適宜絞り込む
ORDER BY 1 ;


コマンド文字列が長いので、右側の続きを ↓ に示す。


↓ もう一例、スキーマごとのバックアップコマンド生成クエリ。オプション -n の後にスキーマ名を続ける。普通のクエリと同様に日本語を安心して使えるのが利点。コマンド実行では、環境ごとにエスケープや文字コードに注意がいる。

■ クエリ例 2
SELECT format('pg_dump -p 5434 -U postgres -w -d %I -n %I -E SJIS >> %s'
, 'daily'
, nspname
, 'R:/bu_test2.sql'
) FROM pg_namespace
WHERE nspname NOT IN ('information_schema', 'pg_catalog', 'public')
AND nspname LIKE '未使用%'
ORDER BY 1 ;


このようなクエリを COPY で丸ごとテキストファイルに出力する。その際、バッチファイルで必要な行(例えば pg_dump のパスに移る)をクエリの前後に追加し、文字コードを実行環境に合わせて設定する。↓ がテンプレートと、実際に上つのクエリを組み込んだ例。

■ クエリを COPY でくるんで出力するテンプレート例
COPY (
SELECT '@PROMPT $'
UNION ALL SELECT '@C:'
UNION ALL SELECT '@CD "Program Files/PostgreSQL/9.3/bin"'
UNION ALL SELECT 'REM Start...' -- バックアップの先頭の合図
-- ここに UNION ALL で先のクエリを連結する
UNION ALL SELECT 'PAUSE'
) TO 'R:/test.bat' (ENCODING 'SJIS') ;

■ 実際の COPY 実行例
COPY (
SELECT '@PROMPT $'
UNION ALL SELECT '@C:'
UNION ALL SELECT '@CD "Program Files/PostgreSQL/9.3/bin"'
UNION ALL SELECT 'REM All Tables...'
UNION ALL
(
SELECT format('pg_dump -p 5434 -U postgres -w -d %I -t %I.%I -E SJIS >> %s'
, 'daily'
, schemaname, tablename
, 'R:/bu_test1.sql'
) FROM pg_tables
WHERE schemaname NOT IN ('information_schema', 'pg_catalog', 'public')
ORDER BY 1
LIMIT 2 -- テスト
)
UNION ALL SELECT 'REM All Schemas...'
UNION ALL
(
SELECT format('pg_dump -p 5434 -U postgres -w -d %I -n %I -E SJIS >> %s'
, 'daily'
, nspname
, 'R:/bu_test2.sql'
) FROM pg_namespace
WHERE nspname NOT IN ('information_schema', 'pg_catalog', 'public')
AND nspname LIKE '未使用%'
ORDER BY 1
)
UNION ALL SELECT 'PAUSE'
) TO 'R:/test.bat' (ENCODING 'SJIS') ;


上のように実行完了すれば、↓ 指定されたパスにバッチファイルができているはず。


↓ 出力されたバッチファイルの中身。画像だけ開けば原寸大になる。OS や使うシェルが違う場合、先ほどの COPY 文を修正する。


Windows なら、バッチファイルをダブルクリックだけで起動する。↓ がその様子。ファイルに追記書き込みの >> 前後で表示が乱れるというか、正常終了の合図の 1 という文字が >> の直前に入り込んでいるが、実行そのものは問題ない。pg_dump の -f オプションでは追記書き込みできないので >> を使った。


↓ 出力されたバックアップファイルの先頭。


pg_dump 実行前にまずクエリでバックアップ対象を確認、さらにバッチファイルの中身を点検できる。新しいテーブルやスキーマができた場合のバッチ更新も簡単。
<< 市町村合併のデータモデル(1)
Word 2007 でラベル付き矢印(2) >>
×

この広告は1年以上新しい記事の投稿がないブログに表示されております。