バックアップコマンドを SQL で作成 : 研究に使うポスグレ

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） >>