データベースインストール後にサンプルデータを投入することで、DBをバックエンドにして動作する他ミドルの検証が楽になったりすることがあります。
それぞれのデータベースで代表的なサンプルデータセットの存在を知っておくと、自分でデータを作らなくて済む分、検証作業が捗ります。
PostgreSQLでは代表的なデータセットとして公式サンプルデータベースの「dvdrental」が設計も実戦的になっていて使いやすいです。
ただこのサンプルデータ、pg_dumpしたユーザがpostgresユーザなのでpg_restoreするとpostgresユーザのテーブルとしてレストアされてしまいます。
個人PCに入れるなら全然構わないですが、共有DBだと特権を持つpostgresユーザ以外に作業用ユーザを作って作業、検証を行うのがマナーな所があるので少々使い勝手が悪く、レストア後に全テーブル所有者を変えるのもかなり手間です。
幸いpg_restoreコマンドにはレストア時に所有者を変える「–no-owner」と「–role」オプションが有るので、これを使って楽に一般ユーザ所有テーブルとしてサンプルDBを入れていきます。
目次
PostgreSQL11インストールと一般ユーザ作成
環境
- CentOS 7.6
- PostgreSQL 11.4
この記事では以下の記事でPostgreSQL11インストール、一般ユーザ作成をしてある状態です。
一般ユーザを作成してあるPostgreSQL環境が無い場合は実施しておきます。
dvdrentalサンプルDBをpg_restore
本題。dvdrentalデータベースにサンプルデータを入れていきます。
（dvdrentalデータベースは上述記事で作ってあります）
postgres=# CREATE DATABASE dvdrental OWNER devel;
CREATE DATABASEサンプルDBのダンプファイルをダウンロード
ポスグレ公式のサンプルDB「dvdrental」をwgetコマンドで落としてきます。
http://www.postgresqltutorial.com/postgresql-sample-database/
// 前述記事で作成した一般ユーザ「devel」で実行
$ cd /tmp
$ wget http://www.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zip
--2019-07-25 00:12:54-- http://www.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zip
www.postgresqltutorial.com (www.postgresqltutorial.com) をDNSに問いあわせています... 108.179.201.93
www.postgresqltutorial.com (www.postgresqltutorial.com)|108.179.201.93|:80 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 550906 (538K) [application/zip]
`dvdrental.zip' に保存中
100%[==================================================================================================>] 550,906 698KB/s 時間 0.8s
2019-07-25 00:12:55 (698 KB/s) - `dvdrental.zip' へ保存完了 [550906/550906]ダウンロードしたら解凍。
$ unzip dvdrental.zip
Archive: dvdrental.zip
inflating: dvdrental.tar
$ ls
dvdrental.tar dvdrental.zip（留意点）
dvdrentalデータベース所有者はdevelユーザになっていても、このダンプtarファイルをpg_restoreでdvdrentalデータベースへレストアすると以下のようにテーブル所有者がスーパーユーザーのpostgresになってしまい、アクセスが出来ません。
$ /usr/pgsql-11/bin/pg_restore -d dvdrental ./dvdrental.tar
$ /usr/pgsql-11/bin/psql -U devel dvdrental
ユーザ devel のパスワード:
psql (11.4)
"help" でヘルプを表示します。
dvdrental=> \d
public | actor | テーブル | postgres
public | actor_actor_id_seq | シーケンス | postgres
public | actor_info | ビュー | postgres
public | address | テーブル | postgres
(snip)
dvdrental=> select * from actor;
ERROR: テーブル actor へのアクセスが拒否されました作成したdevelユーザで操作できるようにテーブル所有者もdevelにしておきたいところです。
develユーザ所有テーブルとしてpg_restore
pg_restoreコマンドにはダンプファイルSQLで指定されている所有者（この場合はpostgres）を無視する「–no-ower」、新しい所有者を指定出来る「–role」オプションが有るのでこれを使います。
pg_restoreコマンドオプションは以下を参照。
https://www.postgresql.jp/document/11/html/app-pgrestore.html
-bash-4.2$ /usr/pgsql-11/bin/pg_restore -U devel --no-owner --role=devel -d dvdrental ./dvdrental.tarテーブル所有者もdevelになりました。
当然「select * from actor;」等すればデータ参照出来ます。
-bash-4.2$ /usr/pgsql-11/bin/psql -U devel dvdrental
ユーザ devel のパスワード:
psql (11.4)
"help" でヘルプを表示します。
dvdrental=> \d
リレーション一覧
スキーマ | 名前 | 型 | 所有者
----------+----------------------------+------------+--------
public | actor | テーブル | devel
public | actor_actor_id_seq | シーケンス | devel
public | actor_info | ビュー | devel
public | address | テーブル | devel
(snip)これで一般ユーザテーブルとして気軽に検証に使っていけます。
dvdrentalは汎用的に使えるテストデータ
PostgreSQLをバックエンドにするツールの検証、SQL勉強、DB設計サンプル、データを増やして負荷実験、etc・・・色んな用途に使えます。
PostgreSQL、そもそもリレーショナルデータベースに慣れてない内はスキルアップの相棒になってくれるので、入れておいて損はありません。
とはいえ慣れない内はこんなにテーブルがあると関連が分かり辛いですね・・・
実戦を想定してテーブル関連を読み解く力を着ける為、次はデータベースからER図をリバース生成してみます。
