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データベースにサンプルデータを入れていきます。

サンプルDBのダンプファイルをダウンロード

ポスグレ公式のサンプルDB「dvdrental」をwgetコマンドで落としてきます。

http://www.postgresqltutorial.com/postgresql-sample-database/

-bash-4.2$ 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]

ダウンロードしたら解凍。

-bash-4.2$ unzip dvdrental.zip
Archive:  dvdrental.zip
  inflating: dvdrental.tar
-bash-4.2$ ls
11  dvdrental.tar  dvdrental.zip

(留意点)

dvdrentalデータベース所有者はdevelユーザになっていても、このダンプtarファイルをpg_restoreでdvdrentalデータベースへレストアすると以下のようにテーブル所有者がスーパーユーザーのpostgresになってしまい、アクセスが出来ません。

-bash-4.2$ /usr/pgsql-11/bin/pg_restore -d dvdrental ./dvdrental.tar

-bash-4.2$ /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, 入門
-,

執筆者:

関連記事

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

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

pgadmin4をCentOS7にインストールしてサーバモードで起動する手順

目次1 はじめに1.1 pgadmin3時代1.2 pgadmin4時代2 前提3 環境4 手順4.1 1.yumリポジトリ追加。pgdg(ポスグレ本家)、epel(python関連用)4.2 2.p …

GitLab Omunibus版のPostgreSQLに普通のpsqlコマンドで接続

GitLab Omunibus版でインストールされるビルトインPostgreSQLは、 TCP:5432ではなくホスト内でしか接続出来ないUnix Domain Socketで待機ローカルホストからO …

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

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

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

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


shingo nakanishi。東京で消耗中の職歴20年越え中年ITエンジニアです。「生涯現役プログラマを楽しむ」ことができる働き方探しをライフワークにしています。

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