HOME PostgreSQL  
Prev INDEX Next

データベースのダンプとリストア

ダンプ & リストアには様々な利点がある。万が一のサーバクラッシュへの備えや、マシン間でのデータベース移植にはもちろん。しかしそれだけでない。ファイルというポータブルな形にしてしまえばバックアップは簡単。さらに、PostgreSQL のバージョンアップの際には、リストア時に新しいサーバプログラムが構造を適宜変換して取り込むので、バージョンによるデータ互換性問題も、たいてい解決してくれる。日常的には当然だが、 PostgreSQL をバージョンアップする前には必ずダンプを取っておこう。

データベースのダンプ

データそのものやテーブル構造をまるごとファイルに書き出すことができる。ファイルに書き出されるのは、リストアに必要なSQLコマンド。一切合切をいっぺんにダンプできる pg_dumpall を使う方法もある。

コマンド書式：
pg_dump options database > ダンプ先file
主なオプション：
-a データのみ。プレーンテキスト出力以外では使用不可
-c 最初に、オブジェクトを削除するコマンドを書き出す (dropしてからcreate)。-C とは排他。プレーンテキスト時のみ
-C 最初に、データベースそのものを CREATE するコマンドを書き出す。-c とは排他。プレーンテキスト時のみ
-b ラージオブジェクトもダンプする
-F[ptc] 出力ファイルのフォーマット。p=プレーンテキスト、t=tar、c=PostgreSQL式圧縮フォーマット。非プレーンの場合は pg_resotore コマンドでのみリストアできる。逆にプレーンの場合は、pg_restore は使用できない。テーブルのサイズや好みによるが、自分は今のところ巨大なデータベースは持っていないし、ダンプテキストを手編集してリストア時にテーブル構造の変更も可能なことから、プレーンしか使っていない。ただし、プレーンテキスト形式ではラージオブジェクトはダンプできないことに注意。省略時はプレーン
-t table テーブル table のデータのみ
-U user ユーザ user で接続してダンプを行う。7.1.x ではuser は指定できず、代わりに"-u" (小文字) を指定して、ユーザとパスワードのプロンプトが出るよう促すのみ
-v Verboseモード。進捗が stderr に出力される。出力ファイルには影響なし
通常のコマンド例：
user$ pg_dump -U foo -c sample > sample.dump

リストア時、リストア先データベースにまだ DROP 対象のオブジェクトがない場合でも、"DROP オブジェクト" SQLコマンドは、エラーメッセージは吐いても処理は続行されるので、"-c" を付けてダンプしておいたほうがベター。ダンプの定期的な実行を自動化する方法については Tips [その他] - データベースバックアップの自動化 を参照のこと。

データベースのリストア

tar や c の場合は pg_restore を使用するが、ここではプレーンテキストダンプの場合のみ扱う。 pg_dumpall でダンプした場合はこちら。

事前準備

目的のデータベースにトリガー操作や手続き言語の組み込みコマンドが含まれる場合、部分的に psotgres 権限が必要となる。 pg_hba.conf を例の通りに設定してある場合は、一時的に、ユーザ postgres に関する行を "#" でコメントアウトして、必要箇所で postgres のパスワードプロンプトが出るようにする。あるいはここから全ての作業を postgres に su してから行ってもいい。
また、ダンプ時に -C オプションを指定していない場合は、先に目的のデータベースを作っておく必要あり。

psqlコンソール内で行う場合：

user$ psql -e -U foo sample
foo=> \i sample.dump

パスワードプロンプトで、いったい誰のパスワードを入れろと言われているのか分からないことがあるので、psql のコール時に -e オプションも指定して、実行中のクエリを画面に echo させたほうがいい。
psql に入ってからクエリエコーをonにするには、メタコマンド: 

\set ECHO queries

を発行すればよい。環境変数は大文字小文字を区別する。

psqlコマンドで直接行う場合：

user$ psql -e -U foo -f sample.dump

-f オプションは、指定ファイルに書かれた内容をSQLコマンドとして実行する。

リストア後の作業 - アナライズ

同じようなクエリを与えたとしても、PostgreSQL は実は裏側で、テーブルのレコード数やカラム数、データの型、結合するテーブル同士の性質などを総合的に判断して、結果を出すのに最も効率の良い計算手順を動的に組み立てている。この働きを「オプティマイザ」「プランナ」と呼ぶが、リストアを行ったばかりの状態では、各データベースの素性がまだ充分に判らないので、オプティマイザが効果的に機能できない。そこでぜひ行うべきなのが、アナライズという作業だ。やり方は 2 通りある。

psql でデータベース毎に行う
user$ su - postgres
postgres$ psql postgres
postgres=# vacuum analyze;
postgres=# \connect another_db
another=# vacuum analyze;
...
クライアントコマンドでいっぺんに
user$ su - postgres
postgres$ vacuumdb -a -z

-a オプションは「すべてのデータベースを」、 -z が「アナライズを行う」の意。

pg_dumpall を使った一斉ダンプ & リストア

もうひとつの付属保守プログラムである pg_dumpall を使うと、全てのデータベースをいっぺんにダンプしたり、ユーザやグループ情報をバックアップしたりできる。当然、 postgres に su してから行わなければならない。

pg_dumpall によるダンプ

システムテーブルも含め全てのデータをダンプするなら、単に；

postgres$ pg_dumpall -v > dump.file

上記で使っている冗長オプション -v 以外にもたくさんのオプションがあるが、使う機会のあるオプションは多くはない；

-a データだけをダンプする
-g グローバルオブジェクト (ユーザとグループ定義) だけをダンプ
-s スキーマ (データベースやテーブルの定義、関数やトリガー) だけダンプし、データは含めない。ユーザとグループの定義はダンプに含まれる

一斉リストア

pg_dumpall で取ったダンプも、普通のダンプと同じような手順でリストアできる。ただし、この場合は固有のデータベースではなく、雛形データベースである template1 に接続する形で行う。

psql コマンドで直接行うやり方
postgres$ psql -e -f dump.file

または、

postgres$ psql -e -f dump.file template1
psql コンソール内で行うやり方
postgres$ psql -e template1
postgres=# \i dump.file

運用のTips [オブジェクトの作成]

テーブル作成例

CREATE TABLE "appo" (
 "app_id" serial UNIQUE,
 "date" date NOT NULL,
 "app" text NOT NULL,
 "m_date" timestamp(0) with time zone DEFAULT ('now'::text)::timestamp(0)
 with time zone,
 "d_id" integer NOT NULL,
 "s_time" time(0) without time zone,
 "dtl" varchar,
 CONSTRAINT "d_id_appo_foreignkey"
 FOREIGN  KEY ("d_id") REFERENCES tdiv ("d_id")  MATCH FULL
 ON UPDATE CASCADE ON DELETE CASCADE,
 PRIMARY KEY ("app_id")
);
CREATE INDEX "appo_date_index" ON appo (date);
CREATE INDEX "appo_d_id_index" ON appo (d_id);

7つのカラムから成り、ひとつのテーブル制約 (CONSTRAINT)、２つのインデックスを持つテーブルを作っている。
テーブル制約 "d_id_appo_foreinkey" では、外部キーを参照する制約を設定している。別のテーブル "tdiv" のカラム "d_id" に無い値は、このテーブルの "d_id" に受け入れられない。また、"tdiv" テーブルからある "d_id" の値 (例えば"3") が削除/変更されると、このテーブルで "d_id" カラムに値 "3" を持つレコードもすべて削除/変更される (ON UPDATE ～ CASCADE)。

serial データ型 (オートナンバリング) のカラムは、PostgreSQL 7.2 までは自動的に UNIQUE となったが、7.3では明示的に列制約 UNIQUE を宣言しなければならなくなった。また、自動的に作成されるシーケンス生成オブジェクト (このテーブルの場合 "appo_app_id_seq" という名前が与えられる) は、DROP SEQUENCE するかデータベースが削除されない限り、データベース上に残る仕様だったが、7.3 で、対象のカラム (この場合 "app_id" カラム) が削除されればシーケンスも削除されるようになった。

インデックスは、たくさん作りすぎると、かえって性能が悪化する。100,000レコードのテーブルから1,000レコードを選択するようなクエリではインデックススキャンが行われることもあるが、レコードが100や200では、インデックスはまったく使われないことを考慮すべき。インデックスが使われているかどうかは以下の要領で検証することができる。 (EXPLAIN の前に必ず ANALYZE を実行しないと EXPLAIN の意味がない)。

VACUUM ANALYZE;
EXPLAIN SELECT * FROM table WHERE culumn < value;

ビュー作成例

何度も使う複雑なクエリは、ビューとして登録しておくと便利。

CREATE VIEW "app_index_view" AS
 SELECT a.d_id, date, app, to_char(a.m_date, 'YY/MM/DD HH24:MI') AS "m_date",
 app_id,  d_owner, s_time, e_time
 FROM appo a LEFT JOIN tdiv d USING (d_id)
 GROUP BY a.d_id, a.app_id, a.date, a.app, a.m_date, a.s_time, a.e_time,
 d.d_id, d.d_owner;

8つのカラムを持つテーブル "appo" と、3つのカラムを持つ"tdiv"から、JOIN を使って2つを結合し、必要な8カラムだけを取り出すビューを定義している。一部のカラムでは PostgreSQL 組み込み関数の "to_char()" をかけて値を加工してから出力している。GROUP と HAVING についてはこちら。
このビューを実際に呼ぶには、通常の SELECT によるクエリを行えばよい。 

SELECT * FROM app_index_view WHERE d_id = 2;

使用時にはこれだけのクエリで済むわけだ。

SQL関数の作成例

よく使うクエリやデータの加工ルーティンなどは、関数として登録。クエリ系ならビューでもできるが、ビューとの大きな違いは、実行時に引数を与えられるところ。

CREATE FUNCTION "pick_did" (character  varying) RETURNS integer
 AS  'SELECT d_id FROM tdiv WHERE d_name = $1;' LANGUAGE  'sql';

カッコで引数の型、RETURNSで戻り値の型を指定する。
PostgreSQL では、ユーザ定義であろうと組み込みであろうと、関数を単独で呼ぶには "SELECT func " を使用。

SELECT pick_did('honyara');

引数 'honyara' (文字列の場合はシングルクォートで囲む必要がある) が関数定義中の "$1" に代入される。引数を2つ以上取る関数では、ふたつ目の引数を $2、３つ目を $3....で参照できる。呼ぶ時には func(var, another) のようにカンマ区切りで引数を与えればよい。

PL/pgSQL関数の作成例

クエリから受け取った値を変換／計算／整形したり値を自動的に生成してから、SQLサーバに渡したいとき、有効な手段。PL/pgSQL は条件分岐やループなどの制御構造も装備している。

CREATE FUNCTION m_date_update () RETURNS TRIGGER AS '
 BEGIN
 NEW.m_date := ''now'';
 RETURN NEW;
 END;'
LANGUAGE 'plpgsql';

カラム "m_date" の値として常に現在時刻を生成する関数。 INSERT/UPDATE クエリによって受け取った一連の値は、PL/pgSQL に渡されると、１行のレコードとしてNEWという予約済み変数に代入される。その中のカラム "m_date" の値だけを加工してから、レコード行全体をSQLサーバに渡しているわけだ。

トリガ関数の場合、戻り値の型として "TRIGGER" を指定する。TRIGGER型の関数は、引数を設定することはできない。PostgreSQL 7.2.x までは、同じ意味として "OPAQUE" が使われていた。ただし、7.1 や 7.2 からダンプした、トリガを含むデータベースは、7.3 上でリストアしてもちゃんと解釈され、問題なく使えた。

上記のようなトリガ関数を実際に使用するには、データベース上にトリガも作成する。

CREATE TRIGGER appo_m_date_update BEFORE UPDATE ON appo
 FOR EACH ROW EXECUTE PROCEDURE  m_date_update();

これで、テーブル "appo" でレコードが INSERT または UPDATE される際、自動的にトリガ関数 "m_date_update()" が呼ばれ、更新／追加されたレコードの m_date カラムには、必ず更新日付が入るようになる。

※詳細は、PostgreSQL 7.3.x プログラマガイド

Prev INDEX Next
インストール HOME 運用のTips [クエリ]