■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
port = 5432
unix_socket_directories = '/var/run/postgresql'
$ 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 行)
