PostgreSQL 11のpg_dump/pg_restoreはcustom形式が楽

  • PostgreSQL
  • Backup

この記事は、構成と文章の整理にAIを使っています。
あくまで読みやすくするための補助で、内容そのものをAIにすべて任せて書いているわけではありませんが、ところどころ相談しながら整えています。

ダンプ

--format=custom でダンプする。

pg_dump -U example_user -h example-host -d example_db --format=custom --file=db.dump

custom形式は圧縮されるし、pg_restore で一部のテーブルだけ戻すみたいな選択リストアもできる。plain形式（SQLテキストそのまま）よりこっちの方が扱いやすい。

リストア

まずDBを作る。CREATE DATABASEできる権限のユーザーで接続する。

psql -U example_user -d postgres -h example-host
create database example_db_copy;

拡張機能（PostGISなど）を使っているDBなら、リストア前に先に作っておく。

CREATE EXTENSION IF NOT EXISTS postgis;

リストア用ユーザーがsuperuserじゃない場合、dumpの中に含まれる CREATE EXTENSION 文がそのままだとエラーになるらしい（未確認、次回試すときに要検証）。先に作っておけば、dump側の該当行はスキップされて通る。

その後、本体のリストア。

pg_restore -U example_user -h example-host -d example_db_copy db.dump

ハマりそうな点

  • dump元とrestore先でロール（オーナー）が違う場合、--no-owner（-O）と --no-acl（-x）を付けないと OWNER TO や GRANT でエラーになる
  • custom形式は並列リストアに対応してるので、テーブル数が多いなら -j 4 とかで速くなる
  • パスワード入力を省きたいなら .pgpass か PGPASSWORD 環境変数を使う
  • create database する時、dump元と同じ ENCODING / LC_COLLATE を指定しないと文字化けやソート順の違いで問題になることがある。元DBがUTF8以外なら明記しておいた方がいい