One IT Thing

IT業界で飯を食う為の学習系雑記

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, 入門
-,

執筆者:

関連記事

PostgreSQL エポックタイムカラムを今月初~今月末で範囲検索

エポックタイムは1970年1月1日 0時0分0秒から現在までの秒数。 PostgreSQLではこれを人間に読みやすいTIMESTAMP型(’2019-09-04 00:00:00&#821 …

BIツール「metabase」でサンプルDB「dvdrental」をグラフ化する

「売り上げやユーザ増加傾向をDBから抽出して上役に報告したりチームで共有したい」 こういった時、SQL結果をExcelでグラフ化、とかしていませんか? SQLを流して結果をコピーしてExcelに張り付 …

開発用PostgreSQLをCentOSにインストールしてSQLを流すまで

開発や勉強に使う為のPostgreSQLをCentOSにインストールします。 インストールから基本操作を体験して、0からデータベースを構築する経験を積んでおきます。 目次1 環境2 インストール手順を …

SSHトンネルを使ってリモートデータベースをBI

クラウド上のDBを分析する為、ローカルPCにSSHトンネル(ポートフォワード)環境を作ります。 リモートDBポートにクラウド外からセキュアに接続できれば、クラウドにBIツールを入れる手間が省けます。 …

pgcryptoの有効化と共通鍵暗号の動作確認

PostgreSQLで「pgcrypto」拡張機能を有効にすると、PostgreSQLに保存するデータを暗号化出来るようになります。 INSERTする際、共通鍵暗号はpgp_sym_encrypt関数 …

 

shingo.nakanishi
 

東京在勤、職歴20年越え中年ITエンジニアです。まだ開発現場で頑張っています。

19歳(1996年)から書き始めた個人日記が5,000日を超え、残りの人生は発信をして行きたいと思い、令和元日からこのサイトを開始しました。勉強と試行錯誤をしながら、自分が経験したIT関連情報を投稿しています。

私と同じく、今後IT業界で生計を立てて行きたいと考えている方や、技術共有したいけどフリーランスで孤独、といった方と一緒に成長、知識共有して行けたら楽しいな、と思っています。