PostgreSQLのExtension付きのDBをbackup→restoreするときに気を付けること

経緯

別サーバからdumpして持ってきたDB（Extension付き）をリストアしたら、Extensionが使えなくなり、試行錯誤していたところ、後輩に教えてもらった内容を自分用にメモで残します。

環境

以下のような環境で作ったDBを別サーバへ復元ようとしました。

• ユーザはpostgresじゃないユーザを作成して利用。一応、パスワード付き（例：hoge_user/hoge_pass）
• スキーマも別途作成（例：hoge_schema）
• Extensionも利用している。（例：postgis）

バックアップ手順

本題じゃないので簡単に。

$ pg_dump hoge_db -U postgres > hoge_dump.sql

リストア前の手順

復元するDBを作成する際の手順に「alter database DB名 owner to ユーザ名」が抜けていて、復元後、\dxでExtensionが見えるも、動作せずに、無駄な時間を使ってしまっていました。

$ psql -U postgres

postgres=# create role hoge_user with login password 'hoge_pass';
postgres=# create database hoge_db;
postgres=# alter database hoge_db owner to hoge_user;
postgres=# \c hoge_db;
hoge_db=# create schema hoge_schema;
hoge_db=# drop schema public; // ここは必要に応じて
hoge_db=# alter schema hoge_schema owner to hoge_user;
hoge_db=# create extension postgis schema hoge_schema;
hoge_db=# alter user hoge_user set search_path to hoge_schema; // ここも必要に応じて

リストアする

以下の手順でいつも通りリストアします。

$ psql -f hoge_dump.sql -d hoge_db -U hoge_user

最後に

「alter database」を忘れて午後丸々ハマっていました。
優秀な後輩に感謝を。