PostgreSQL 入門

PostgreSQL11に一般ユーザ権限でサンプルDB「dvdrental」をpg_restore

投稿日:2019年7月27日

データベースインストール後にサンプルデータを投入することで、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図をリバース生成してみます。

-PostgreSQL, 入門
-,

執筆者:

関連記事

ERROR: Several keys given – pgcrypto does not handle keyring、またはERROR: Corrupt ascii-armor

gpgキーストアに同じUIDで複数の鍵ペアを登録してしまうと、ファイルにexportした際に1ファイルに複数の鍵情報が入ってしまい、1ファイル1鍵を期待しているpgcryptoに怒られます。 目次1 …

「Jailer Database Tool」でDB関連作業を効率化

開発時のデータベースクライアントは何を使っていますか?A5? 「Jailer Database Tool」を使っていないなら、普段使いツールに加えるとちょっとデータベースの見え方が変わるかも知れません …

スタンドアロンモードのPgAdmin4が開くデフォルトブラウザを変更

PostgreSQL公式GUIクライアントツールの「PgAdmin4」。 PostGISのgeometry型をWeb地図上に表示出来たり、サーバモードで起動すればチーム内で共通的に使えるPostgre …

開発用PostgreSQLをインストールした後の外部接続、ユーザ作成、認証周りの設定

ローカル開発用にPostgreSQLを入れた後、開発や分析をし易くする為に外部ホストからパスワード付き接続出来るようにしていきます。 また、postgresスーパーユーザは権限が強すぎて開発アプリから …

Omnibus版のGitLab、Mattermostデータベースに外部ホストからJDBCで接続

Omnibusで入れたPostgreSQLは、 Unix Domainソケットで接続待機Peer認証で認証 していて外部からの接続は出来ないことが前回分かりました。One IT Thing  …

 

shingo.nakanishi
 

東京在勤、1977年生まれ、IT職歴2n年、生涯現役技術者を目指しています。健康第一。