DBリストア失敗の悲哀
データベースに格納されたデータは大事だ。これは宇宙の真理である。なのでバックアップを取る。こまめにとる。しかしリストアする機会はバックアップを取る機会よりも少ない。こまめにとられたバックアップをいざリストアしようとしてエラーが出てハマって焦るというのはデータベースあるあるの筆頭と言ってよい。リストアできないバックアップほど人の心をむしばむものは無い。リストアできれば助かるのに、そこにデータがあるのに、手が届かない。そんな悲しい思いをしなくて済むように、リストアのエラー解決TIPSは広く共有されるべきだ。今回はPostgreSQLのバックアップリストアでハマって１日つぶしてしまって泣きそうになったのでちょっと備忘録として残しておきたいと思う。きっと書いておかないと半年後にまた同じ問題で半日つぶすことになってしまうだろう。ブログに備忘録を書くのはとても役に立つのだ。是非皆様にもお勧めしたい。
PostgreSQLのバックアップ
PostgreSQLのバックアップはpg_dumpコマンドで取得するが、オプションによって大きく二つに分かれる。平文のSQLで取得してpsqlコマンドでリストアする方法と、アーカイブ形式で取得してpg_restoreコマンドでリストアする方法だ。アーカイブ形式でのバックアップリストアは、いろいろ高機能だがリソースをたくさん食い要求されるサーバースペックも高くなるので、今回はSQLで取得するスクリプト方式で取得し、psqlコマンドでリストアする方法をとっていた。具体的にはこんなコマンドだ。
# su - postgres -c "pg_dump -cC -f $backup_file -F plain fabulousDB"
ローカルのソケットからの接続はピア認証になるようにpg_hba.confで設定しているので、ユーザーpostgresになってpg_dumpコマンドを発行している。オプションは、-cC でいったんリストア時にデータベース”fabulousDB”をDROPし、”CREATE”しなおしてからデータを流し込むSQLを吐き出すようにしている。そして-f で指定したファイルに、-F で指定した通り平文のSQLでバックアップを取得する。
リストアの失敗
しかしこれをリストアしようとすると失敗する。リストア時に発行したコマンドは以下の通りだ。
# su - postgres -c "psql fabulousDB" < $backup_file
先ほどと同じく、ユーザー”postgres”でpsqlコマンドを発行している。単純に、データーベース”fabulousDB”に対して、バックアップSQLをリダイレクトで流し込んでいる。リストアされる環境には、バックアップを取得した環境と同じ名前のDBが存在しなければならない。普通ならこれですんなりリストアされるはずだが、今回は本番環境のfabulousDBを、検証環境でリストアしようとして失敗し、以下の例に挙げるようなエラーに遭遇した。
\Nは無効なコマンドです
ERROR: "&#"またはその近辺で構文エラー
行 1: 中
こんなエラーがずらーーーっと出てしまったが、リストア時に”psql -v ON_ERROR_STOP=1″を指定しておくと一つ目のエラーで止まってくれるので、エラーの羅列を眺めなくても済む。なんだか無効なコマンドが発行されてるっぽいので、文字コードの問題かと思ったが、クライアントもDBもちゃんとUTF8で統一されている。
-bash-4.2$
-bash-4.2$
-bash-4.2$ psql
psql (9.6.3)
"help" でヘルプを表示します.
postgres=#
postgres=#
postgres=# \l
データベース一覧
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権
------------+-------------+------------------+-------------+-------------------+-----------------------
fabulousDB | fabulousUser| UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
postgres | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
template0 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(5 行)
postgres=#
postgres=#
postgres=# \q
-bash-4.2$
-bash-4.2$ less dbdump.sql
--
-- PostgreSQL database dump
--
-- Dumped from database version 9.6.3
-- Dumped by pg_dump version 9.6.3
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;
以下略
出力されたバックアップのSQLを覗いてみると、どうやらCOPYコマンドのところで失敗している。\Nというのは、カラムに空白を入れるときの指定だ。これがどうやら単独のコマンドとして認識されてしまいそんなコマンドはないよと言われているようだ。
--
-- Data for Name: fabulousTB; Type: TABLE DATA; Schema: fabulousSC ; Owner: fabulousUser
--
COPY categorytb (id, name, del_flg) FROM stdin;
1 突撃！ \N
2 隣の！ \N
3 ばんごはん！ \N
\.
検索の上の方に固まる誤った回答の数々
検索エンジンに思いついた単語や出たエラーをぶち込むと、同じお悩みの人がたくさんいて、そのことごとくに”COPY”コマンドじゃなくて”INSERT”コマンドを使うようにpg_dumpのオプションに”–inserts”を使え！という解決策ばかり出てきた。でもそれはしたいことと違う。なぜCOPYコマンドが使えないのかというのが問題なのだ。INSERTできたとしても、COPY出来ないなら検証環境と本番環境に差異が出てしまう。それはまずい。そんな中、一筋の光明が見えた、以下のリンクのスレッドだ。
A lot of “invalid command \N” when I try to restore PostgreSQL dump
上記のスレッドでは”uuid-ossp”が足りないので”postgresql-contrib”をインストールして追加すれば治るとのことだった。私の環境では”uuid-ossp”は使ってないので、本番環境と検証環境でEXTENSIONの差異を見比べてみる。psqlコマンドでpostgresqlのコマンドラインに入って”\dx”でEXTENSIOnの一覧を確認する。
まずは、本番環境。
-bash-4.2$ psql fabulousDB
psql (9.6.3)
"help" でヘルプを表示します.
fabulousDB=#
fabulousDB=# \dx
インストール済みの拡張の一覧
名前 | バージョン | スキーマ | 説明
-----------+------------+------------+--------------------------------------------------------------
pg_bigm | 1.2 | public | text index searching based on bigrams
pg_repack | 1.4.0 | public | Reorganize tables in PostgreSQL databases with minimal locks
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(3 行)
fabulousDB=#
続いて、検証環境。
-bash-4.2$ psql fabulousDB
psql (9.6.3)
"help" でヘルプを表示します.
fabulousDB=#
fabulousDB=# \dx
インストール済みの拡張の一覧
名前 | バージョン | スキーマ | 説明
-----------+------------+------------+--------------------------------------------------------------
pg_bigm | 1.2 | public | text index searching based on bigrams
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 行)
fabulousDB=#
検証環境には、EXTENSIONの”pg_repack“が足りない！pg_repackはとても便利だ。削除や追加の激しいDBではデータがスカスカになり、使われていないのに解放されていないストレージ領域がたくさんできてしまう。それを回収するのに”VACUUM”コマンドを使うのだが、”VACUUM FULL”などは排他的に使わねばならず、ロックがかかってしまい使い勝手が悪い。そこでpg_reorgの出番だが、それをさらにフォークして開発されているのが、pg_repackだ。最小限のロックでDBを再編成して未使用領域を解放してくれるのでとても重宝する。幸い、CentOSではコマンド一発で追加できる。
# yum install pg_repack96.x86_64
中略
# su - postgres
最終ログイン: 2017/08/01 (火) 16:45:03 JST日時 pts/0
-bash-4.2$
-bash-4.2$
-bash-4.2$ psql
-bash-4.2$ psql
psql (9.6.3)
"help" でヘルプを表示します.
postgres=#
postgres=# \c fabulousDB
データベース "fabulousDB" にユーザ"postgres"として接続しました。
fabulousDB=#
fabulousDB=# CREATE EXTENSION pg_repack;
これだけだ。とても簡単。ちなみに、”SELECT * FROM pg_available_extensions;”で現在使用可能なEXTENSIONの一覧が見れるでそこにあれば既にPostgreSQLにはインストールされているので”CREATE EXTENSION”して、DBにインストールすればいい。”CREATE EXTENSION”の後は、”\dn”でちゃんとDBに追加されていることを確認する。”CREATE EXTENSION”はDB毎に行う必要があるので、”\c DB名”で目的のDBに接続した状態で行う。これでリストアしたところ、あっさりとエラーが消え、リストアが完了した。長々と書いたが、要するにバックアップ元とリストア先でインストールされたEXTENSIONに差があるとエラーになるよっていうお話だ。チャンとリストアできて、本番検証間で環境もそろってrepackできるようになった。よしよし。
