読者です 読者をやめる 読者になる 読者になる

labunix's blog

labunixのラボUnix

PostgreSQLにサンプルデータ(dvdrental)を取り込んでみる。

■PostgreSQLにサンプルデータ(dvdrental)を取り込んでみる。

 DVD Rental ER Model
 http://www.postgresqltutorial.com/postgresql-sample-database/

■Jessieでも基本的に同じ。

 Debian Wheezyにpostgresql9.1を導入してみる。
 http://labunix.hateblo.jp/entry/20140713/1405261849

■postgreSQLのサーバ、クライアントのインストールとバージョン確認

$ lsb_release -d
Description:	Debian GNU/Linux 8.2 (jessie)

$ sudo apt-get install -y postgresql postgresql-client
$ sudo -u postgres psql -V
psql (PostgreSQL) 9.4.5
$ sudo -u postgres psql -q -c "select now();"
             now              
 ------------------------------
 2016-01-12 00:05:19.86738+09
(1)

■ログインユーザとユーザの接続元制限の設定

$ sudo -u postgres createuser `whoami`
$ sudo -u postgres psql -c "alter user `whoami` password '"`whoami`"'"
ALTER ROLE
$ psql -U `whoami` -d postgres -c "select current_user;"
 current_user 
 --------------
 labunix
(1)

$ sudo -u postgres psql -c "alter role `whoami` superuser"
ALTER ROLE
$ sudo -u postgres psql -c "\du"
                                         ロール一覧
 ロール名 |                                 属性                                 | メンバー 
 ----------+----------------------------------------------------------------------+----------
 labunix  | スーパーユーザ                                                       | {}
 postgres | スーパーユーザ, ロールを作成できる, DBを作成できる, レプリケーション | {}

$ grep "5432\|localhost\|^unix" /etc/postgresql/9.4/main/postgresql.conf 
#listen_addresses = 'localhost'		# what IP address(es) to listen on;
					# defaults to 'localhost'; use '*' for all
port = 5432				# (change requires restart)
unix_socket_directories = '/var/run/postgresql'	# comma-separated list of directories

$ netstat -an | grep 5432
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN     
tcp6       0      0 ::1:5432                :::*                    LISTEN     
unix  2      [ ACC ]     STREAM     LISTENING     190966   /var/run/postgresql/.s.PGSQL.5432

$ sudo sed -i -e 's/^#\(listen_addresses\)/\1/' -e 's/localhost/127.0.0.1/' \
       /etc/postgresql/9.4/main/postgresql.conf; \
  sudo /etc/init.d/postgresql restart

$ netstat -an | grep 5432
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN     
unix  2      [ ACC ]     STREAM     LISTENING     195270   /var/run/postgresql/.s.PGSQL.5432

$ sudo -u postgres grep -v "^#\|^\$" /etc/postgresql/9.4/main/pg_hba.conf 
local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

$ sudo -u postgres sed -i -e 's/\(all\)\( *\)\(peer\)/\1\2md5/' /etc/postgresql/9.4/main/pg_hba.conf

$ sudo -u postgres grep -v "^#\|^\$" /etc/postgresql/9.4/main/pg_hba.conf 
local   all             postgres                                peer
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

$ createdb -U `whoami` `whoami`
$ psql -U labunix -c "select datname,datctype from pg_database where datname='labunix';" -A -F,
datname,datctype
labunix,ja_JP.UTF-8
(1)

$ sudo passwd postgres 
新しい UNIX パスワードを入力してください:
新しい UNIX パスワードを再入力してください:
passwd: パスワードは正しく更新されました
$ sudo /etc/init.d/postgresql restart

■サンプルデータ「dvdrental」のダウンロード

 DVD Rental ER Model
 http://www.postgresqltutorial.com/postgresql-sample-database/

$ md5sum dvdrental.zip 
4b8044eec4950e17df853f7ea948a137  dvdrental.zip
$ zipinfo dvdrental.zip 
Archive:  dvdrental.zip
Zip file size: 551437 bytes, number of entries: 1
 -rw-a--     6.3 fat  2838016 bx defN 13-May-26 20:17 dvdrental.tar
1 file, 2838016 bytes uncompressed, 551277 bytes compressed:  80.6%
$ unzip dvdrental.zip 
Archive:  dvdrental.zip
  inflating: dvdrental.tar
$ tar tvf dvdrental.tar 
 -rw------- 2048/1024     53933 2013-05-26 22:17 toc.dat
 -rw------- 2048/1024      8004 2013-05-26 22:17 2163.dat
 -rw------- 2048/1024     46786 2013-05-26 22:17 2171.dat
 -rw------- 2048/1024       483 2013-05-26 22:17 2165.dat
 -rw------- 2048/1024     21762 2013-05-26 22:17 2173.dat
 -rw------- 2048/1024      3596 2013-05-26 22:17 2175.dat
 -rw------- 2048/1024     57147 2013-05-26 22:17 2177.dat
 -rw------- 2048/1024    333094 2013-05-26 22:17 2167.dat
 -rw------- 2048/1024    149469 2013-05-26 22:17 2168.dat
 -rw------- 2048/1024     26321 2013-05-26 22:17 2169.dat
 -rw------- 2048/1024    140422 2013-05-26 22:17 2179.dat
 -rw------- 2048/1024       263 2013-05-26 22:17 2181.dat
 -rw------- 2048/1024    718644 2013-05-26 22:17 2183.dat
 -rw------- 2048/1024   1214420 2013-05-26 22:17 2185.dat
 -rw------- 2048/1024       271 2013-05-26 22:17 2187.dat
 -rw------- 2048/1024        57 2013-05-26 22:17 2189.dat
 -rw------- 2048/1024     49601 2013-05-26 22:17 restore.sql

■リストア方式で取り込み
 ただし、チュートリアルと異なり、ログインユーザ用に所有者を変更する。
 リストア前とリストア後に行う方式が考えられる。
 自身の権限を取り消して再度与えている箇所がちょっと謎だったので、
 前者は今回はスルーする。

$ tar xvf dvdrental.tar restore.sql
$ sed -e 's/\(Owner: \)postgres/\1labunix/g' \
      -e 's/\(OWNER TO \)postgres/\1labunix/g' \
    restore.sql | grep postgres
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;

■今回はリストア後に所有者変更を行う。

$ psql -c "create database dvdrental;"
パスワード: 
CREATE DATABASE
$ pg_restore -d dvdrental dvdrental.tar 
パスワード:

$ psql -d dvdrental -c "\d" -q | cat
パスワード: 
                      リレーションの一覧
 スキーマ |            名前            |     型     |  所有者  
 ----------+----------------------------+------------+----------
 public   | actor                      | テーブル   | postgres
 public   | actor_actor_id_seq         | シーケンス | postgres
 public   | actor_info                 | ビュー     | postgres
 public   | address                    | テーブル   | postgres
 public   | address_address_id_seq     | シーケンス | postgres
 public   | category                   | テーブル   | postgres
 public   | category_category_id_seq   | シーケンス | postgres
 public   | city                       | テーブル   | postgres
 public   | city_city_id_seq           | シーケンス | postgres
 public   | country                    | テーブル   | postgres
 public   | country_country_id_seq     | シーケンス | postgres
 public   | customer                   | テーブル   | postgres
 public   | customer_customer_id_seq   | シーケンス | postgres
 public   | customer_list              | ビュー     | postgres
 public   | film                       | テーブル   | postgres
 public   | film_actor                 | テーブル   | postgres
 public   | film_category              | テーブル   | postgres
 public   | film_film_id_seq           | シーケンス | postgres
 public   | film_list                  | ビュー     | postgres
 public   | inventory                  | テーブル   | postgres
 public   | inventory_inventory_id_seq | シーケンス | postgres
 public   | language                   | テーブル   | postgres
 public   | language_language_id_seq   | シーケンス | postgres
 public   | nicer_but_slower_film_list | ビュー     | postgres
 public   | payment                    | テーブル   | postgres
 public   | payment_payment_id_seq     | シーケンス | postgres
 public   | rental                     | テーブル   | postgres
 public   | rental_rental_id_seq       | シーケンス | postgres
 public   | sales_by_film_category     | ビュー     | postgres
 public   | sales_by_store             | ビュー     | postgres
 public   | staff                      | テーブル   | postgres
 public   | staff_list                 | ビュー     | postgres
 public   | staff_staff_id_seq         | シーケンス | postgres
 public   | store                      | テーブル   | postgres
 public   | store_store_id_seq         | シーケンス | postgres
(35)

■データベースの所有者変更

$ sudo -u postgres psql -U postgres -d dvdrental -c "alter database dvdrental owner to labunix;"
ALTER DATABASE

■データベース配下のテーブルには効果が無かったので、
 各テーブルの所有者を変更

$ psql -d dvdrental -c "\dt"
パスワード: 
               リレーションの一覧
 スキーマ |     名前      |    型    |  所有者  
 ----------+---------------+----------+----------
 public   | actor         | テーブル | postgres
 public   | address       | テーブル | postgres
 public   | category      | テーブル | postgres
 public   | city          | テーブル | postgres
 public   | country       | テーブル | postgres
 public   | customer      | テーブル | postgres
 public   | film          | テーブル | postgres
 public   | film_actor    | テーブル | postgres
 public   | film_category | テーブル | postgres
 public   | inventory     | テーブル | postgres
 public   | language      | テーブル | postgres
 public   | payment       | テーブル | postgres
 public   | rental        | テーブル | postgres
 public   | staff         | テーブル | postgres
 public   | store         | テーブル | postgres
(15)

$ sudo -u postgres psql -U postgres dvdrental -t -c \
    "select 'alter table ' || schemaname || '.' || tablename || ' owner to labunix;'
       from pg_tables where tableowner='postgres' and schemaname='public';" | cat
 alter table public.rental owner to labunix;
 alter table public.film_category owner to labunix;
 alter table public.country owner to labunix;
 alter table public.inventory owner to labunix;
 alter table public.film_actor owner to labunix;
 alter table public.language owner to labunix;
 alter table public.customer owner to labunix;
 alter table public.actor owner to labunix;
 alter table public.address owner to labunix;
 alter table public.category owner to labunix;
 alter table public.film owner to labunix;
 alter table public.city owner to labunix;
 alter table public.payment owner to labunix;
 alter table public.staff owner to labunix;
 alter table public.store owner to labunix;

$ sudo -u postgres psql -U postgres dvdrental -t -c \
    "select 'alter table ' || schemaname || '.' || tablename || ' owner to labunix;'
      from pg_tables where tableowner='postgres' and schemaname='public';" | cat | \
  sudo -u postgres psql -U postgres dvdrental -t -c "`xargs`"
ALTER TABLE

$ psql -d dvdrental -c "\dt"
パスワード: 
              リレーションの一覧
 スキーマ |     名前      |    型    | 所有者  
 ----------+---------------+----------+---------
 public   | actor         | テーブル | labunix
 public   | address       | テーブル | labunix
 public   | category      | テーブル | labunix
 public   | city          | テーブル | labunix
 public   | country       | テーブル | labunix
 public   | customer      | テーブル | labunix
 public   | film          | テーブル | labunix
 public   | film_actor    | テーブル | labunix
 public   | film_category | テーブル | labunix
 public   | inventory     | テーブル | labunix
 public   | language      | テーブル | labunix
 public   | payment       | テーブル | labunix
 public   | rental        | テーブル | labunix
 public   | staff         | テーブル | labunix
 public   | store         | テーブル | labunix
(15)

■データベース配下のビューに対しても
 所有変更を行う。

$ psql -d dvdrental -c "\dv"
パスワード: 
                    リレーションの一覧
 スキーマ |            名前            |   型   |  所有者  
 ----------+----------------------------+--------+----------
 public   | actor_info                 | ビュー | postgres
 public   | customer_list              | ビュー | postgres
 public   | film_list                  | ビュー | postgres
 public   | nicer_but_slower_film_list | ビュー | postgres
 public   | sales_by_film_category     | ビュー | postgres
 public   | sales_by_store             | ビュー | postgres
 public   | staff_list                 | ビュー | postgres
(7)

$ sudo -u postgres psql -U postgres dvdrental -t -c \
    "select 'alter view ' || schemaname || '.' || viewname || ' owner to labunix;'
      from pg_views where viewowner='postgres' and schemaname='public';" | cat
 alter view public.actor_info owner to labunix;
 alter view public.customer_list owner to labunix;
 alter view public.film_list owner to labunix;
 alter view public.nicer_but_slower_film_list owner to labunix;
 alter view public.sales_by_film_category owner to labunix;
 alter view public.sales_by_store owner to labunix;
 alter view public.staff_list owner to labunix;

$ sudo -u postgres psql -U postgres dvdrental -t -c \
    "select 'alter view ' || schemaname || '.' || viewname || ' owner to labunix;'
      from pg_views where viewowner='postgres' and schemaname='public';" | cat | \
  sudo -u postgres psql -U postgres dvdrental -t -c "`xargs`"
ALTER VIEW

$ psql -d dvdrental -c "\dv"
パスワード: 
                    リレーションの一覧
 スキーマ |            名前            |   型   | 所有者  
 ----------+----------------------------+--------+---------
 public   | actor_info                 | ビュー | labunix
 public   | customer_list              | ビュー | labunix
 public   | film_list                  | ビュー | labunix
 public   | nicer_but_slower_film_list | ビュー | labunix
 public   | sales_by_film_category     | ビュー | labunix
 public   | sales_by_store             | ビュー | labunix
 public   | staff_list                 | ビュー | labunix
(7)

■従業員が2人しかいない寂しいレンタルショップです。

$ psql -d dvdrental -c "select first_name,last_name from staff;"
パスワード: 
 first_name | last_name 
 ------------+-----------
 Mike       | Hillyer
 Jon        | Stephens
(2)

$ psql -d dvdrental -c "select name from staff_list;"
パスワード: 
     name     
 --------------
 Mike Hillyer
 Jon Stephens
(2)