雑記

◆ PostgreSQLでリストアに失敗したときの対処法

PostgreSQL 9.2でバージョンアップした訳でもないのにリストアに失敗するという不可解な現象に遭遇しました。今後も無いとは限らないので、備忘のためにメモ。

Drupalを運用しているサイトで

% pg_dump -Fc -b -U #{DB_USER} #{DB} > #{DB}.pg_dump

でダンプしたデータを、（作業で不具合が出て(^^;)

% pg_restore -C -U #{DB_USER} -d postgres #{DB}.pg_dump

でリストアしようとしたところ、

pg_restore: [アーカイバ(db)] TOC処理中にエラーがありました:
pg_restore: [アーカイバ(db)] TOCエントリ4637; 2606 62213193 CONSTRAINT variable_pkey pgsqlのエラーです
pg_restore: [アーカイバ(db)] could not execute query: ERROR:  could not create unique index "variable_pkey"
DETAIL:  Key (name)=(menu_expanded) is duplicated.
   コマンド: ALTER TABLE ONLY variable
    ADD CONSTRAINT variable_pkey PRIMARY KEY (name);

というエラーが。この種のエラーは過去にも数度経験していて、これまではdropdbとpg_restoreを再実行すると何事も無かったかのように成功していたのですが、なぜか今回は再現性があって、かなり焦りました。

エラーの解読

まずは上の{=呪文=}エラーを解読しなければなりません。理解できてから改めて読むと修正に必要な情報がすべて書かれているんですが、1行目の"TOC"の時点で意味不明で戦々恐々です。

で、解読の経緯ははしょりますが、

• pg_dump/pg_restoreではバックアップデータを細かな処理単位で管理している
• この管理されている単位の一覧がTOCとなり、「TOCエントリ」は個別の処理をさす

ということらしく、そこが理解できるとあとは結構スムーズで、上記エラーメッセージは

• "4637; ..."番の処理でエラーが発生した
• そのエラー内容は"variable_pkey"という名前のunique制約付きインデックス生成の失敗
• 詳細："name"フィールドで"menu_expand"という値のエントリが重複している
• 実行しようとしたコマンドは"ALTER TABE ONLY ..."

という内容であることが分かりました。ここまで理解できればなんとか手作業で修正できそうです。

エラーが発生した処理の確認

まずは上記エラーが発生した処理を念のため確認します。 最初にエラーメッセージに表示されているTOCエントリ

4637; 2606 62213193 CONSTRAINT variable_pkey pgsql

という1行だけのテキストファイルを適当な名前(err_toc.txt)で作成します。

pg_restoreで-L(大文字のエル)オプションを使って上記ファイルを指定すると、その処理の内容を確認できます。

% pg_restore  -L err_toc.txt #{DB}.pg_dump
--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

--
-- Name: variable_pkey; Type: CONSTRAINT; Schema: public; Owner: pgsql; Tablespace: 
--

ALTER TABLE ONLY variable
    ADD CONSTRAINT variable_pkey PRIMARY KEY (name);


--
-- PostgreSQL database dump complete
--

前処理がついていますが、エラーメッセージにある"ALTER TABLE ONLY ..."です。

データベースの修復

マニュアルを見る限りではpg_restoreは途中で処理に失敗しても残りのTOCを継続して処理するようなのですが、気分の問題としてひとまずエラーがない状況で最大限DBを復旧させます。

まずpg_resotreでは-l(小文字のエル)オプションでTOCを取得できるので、これをテキストに書き出します。

% pg_restore -l #{DB}.pg_dump > toc.txt

このtoc.txtをエディタで開いてエラーとなった"4637; ..."のTOCエントリの行をコメントアウトします。

% vim toc.txt
  :
{~#adf:;~}4637; 2606 62213193 CONSTRAINT public variable_pkey pgsql
  :

行頭にセミコロンを付けて保存したら、今度は先ほどの-Lオプションを使ってDBに接続してリストアします。

% pg_restore --disable-triggers -C -U pgsql -d postgres　#{DB}.pg_dump -L toc.txt

これで今回はエラーなくリストアできました。なお、処理の内容によってはエラーより後の処理で不具合が出る可能性もあるので、toc.txtをエラー前とエラー後に分割して、

1. エラー前の処理までのリストア
2. エラーへの対策とエラーが発生した処理の実行
3. エラー後の処理のリストア

という順番で修復する必要があるかもしれません。今回はPRIMARY KEY制約の付加だけなので、他の処理はすべて実行してしまっても大丈夫でしょう。

エラーの修復

DBに接続してエラーの状況を確認します。エラーメッセージから"variable"というテーブルのname = 'menu_expanded'というデータのエラーだと分かるので、見てみます。が、その前にvariableテーブルはDrupalのオブジェクトを保存するためのテーブルなので、その内容をdrushコマンドをつかって確認しておきます。

% drush vget menu_expanded
menu_expanded: Array
(
)

どうやら空の配列のようです。次にDBに接続してテーブルの内容を確認します。

% psql -U pgsql #{DB}
DB=# SELECT * FROM variable WHERE name = 'menu_expanded';
     name      | value  
---------------+--------
 menu_expanded | a:0:{}
 menu_expanded | a:0:{}
(2 行)

なぜか全く同じ内容のエントリが2つあるので、削除してしまいます。

DB=# DELETE FROM variable WHERE name = 'menu_expanded';
DELETE 2

削除されている事を確認します。

DB=# SELECT * FROM variable WHERE name = 'menu_expanded';
 name | value 
------+-------
(0 行)

ここで直接INSERT文を発行して上記データを登録しても良いのですが、Drupalでは1文字でもtypoがあるとNULL扱いになってしまうので、こちらもdrushを使います。

=# \q
% drush eval "variable_set('menu_expanded', array());"

データを確認します。

% psql -U pgsql #{DB}
DB=# SELECT * FROM variable WHERE name = 'menu_expanded';
     name      | value  
---------------+--------
 menu_expanded | a:0:{}
(1 行)

これでデータは修正できたはずですので、最初の方で作ったerr_toc.txtを使ってエラーの出ていた処理を再実行します。-vオプションを付けると冗長な出力をしてくれます。-Cオプションを付けたり接続先のDBを間違えたりしないよう気をつけましょう。

% pg_restore --disable-triggers -U pgsql -d #{DB} #{DB}.pg_dump -v -L err_toc.txt
pg_restore: リストアのためにデータベースに接続しています
pg_restore: CONSTRAINT variable_pkeyを作成しています
pg_restore: CONSTRAINT variable_pkey用の所有者と権限を設定しています

念のため、確認します。

% psql -U pgsql DB
DB=# \d variable
                       テーブル "public.variable"
  列   |           型           |                 修飾語                 
-------+------------------------+----------------------------------------
 name  | character varying(128) | not null default ''::character varying
 value | text                   | not null
インデックス:
    "variable_pkey" PRIMARY KEY, btree (name)

DB=# \q

"name"がPRIMARY KEYとしてインデックスに表示されているので、正しく修復できた事が分かります。

基本的にはエラーが出なくなるまでこの作業の繰り返しです。

参考

http://d.hatena.ne.jp/naga_sawa/20120918/1347967306

-----