LoginSignup
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@brighton0725

備忘録 PostgreSQLのSample DBのDVDRENTALのオーナーやスキーマを変更する

Last updated at Posted at 2017-04-20

PostgreSQLの検証を行うのに、Sample DBを使用すると、スキーマがpublicだったりするので、それを自分の好きなスキーマに変更する内容です。

環境

項目 内容
PostgreSQLスーパーユーザー postgres
新規ユーザー名 zz01adm01
ルール Create DB,Create role,login
DB名 dvdrental
PGDATA /psql/data
項目 内容
OS CentOS 7.3
DB PostgreSQL9.6.2

Sample DBのダウンロードし、サーバーにアップロードする。

POSTGRESQL TUTORIALサイトから、Sample DBをダウンロードする。

201704201.JPG

ダウンロードファイルを展開する。

今回は、/psql/dataにファイルを配置し、展開する。

[postgres@test01 data]$ unzip dvdrental.zip
Archive:  dvdrental.zip
  inflating: dvdrental.tar
[postgres@test01 data]$ ls
base           pg_clog        pg_log        pg_serial     pg_tblspc             postgresql.conf
dvdrental.tar  pg_commit_ts   pg_logical    pg_snapshots  pg_twophase           postgresql.conf.org
dvdrental.zip  pg_dynshmem    pg_multixact  pg_stat       PG_VERSION            postmaster.opts
global         pg_hba.conf    pg_notify     pg_stat_tmp   pg_xlog               postmaster.pid
lost+found     pg_ident.conf  pg_replslot   pg_subtrans   postgresql.auto.conf'

環境を構築する

  • 新ユーザーを作成する。
[postgres@test01 data]$ createuser -r -l -d -U postgres -P zz01adm01
Enter password for new role:
Enter it again:
  • zz01adm01ユーザーが作成されていることを確認
[postgres@test01 ~]$ psql
psql (9.6.2)
Type "help" for help.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 zz01adm01 | Create role, Create DB                                     | {}

postgres=# select usename from pg_user;
  usename
-----------
 postgres
 zz01adm01
(2 rows)

postgres=# select rolname from pg_roles;
      rolname
-------------------
 postgres
 pg_signal_backend
 zz01adm01
(3 rows)

postgres=#
  • スーパーユーザーでDBを作成する。
[postgres@test01 ~]$ createdb dvdrental -O postgres
[postgres@test01 ~]$ psql -l
                             List of databases
   Name    |   Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+-----------+----------+---------+-------+-----------------------
 dvdrental | postgres  | UTF8     | C       | C     |
 postgres  | postgres  | UTF8     | C       | C     |
 template0 | postgres  | UTF8     | C       | C     | =c/postgres          +
           |           |          |         |       | postgres=CTc/postgres
 template1 | postgres  | UTF8     | C       | C     | =c/postgres          +
           |           |          |         |       | postgres=CTc/postgres

(4 rows)
  • Sample DBをリストアする
[postgres@test01 data]$ pg_restore -U postgres -d dvdrental ./dvdrental.tar
[postgres@test01 data]$
  • Sample DBを確認する
[postgres@sistest01 data]$ psql dvdrental -U postgres
psql (9.6.2)
Type "help" for help.

dvdrental=# \dt
             List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+----------
 public | actor         | table | postgres
 public | address       | table | postgres
 public | category      | table | postgres
 public | city          | table | postgres
 public | country       | table | postgres
 public | customer      | table | postgres
 public | film          | table | postgres
 public | film_actor    | table | postgres
 public | film_category | table | postgres
 public | inventory     | table | postgres
 public | language      | table | postgres
 public | payment       | table | postgres
 public | rental        | table | postgres
 public | staff         | table | postgres
 public | store         | table | postgres
(15 rows)
dvdrental=#

DBオーナーを変更する

  • DBのオーナーを、postgresからzz01adm01に変更する
[postgres@test01 data]$ psql -U postgres -d dvdrental
psql (9.6.2)
Type "help" for help.

dvdrental=# alter database dvdrental owner to zz01adm01;
ALTER DATABASE
dvdrental=# \q
  • オーナーが変更されたことを確認する
[postgres@test01 data]$ psql -l
                             List of databases
   Name    |   Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+-----------+----------+---------+-------+-----------------------
 dvdrental | zz01adm01 | UTF8     | C       | C     |
 postgres  | postgres  | UTF8     | C       | C     |
 template0 | postgres  | UTF8     | C       | C     | =c/postgres          +
           |           |          |         |       | postgres=CTc/postgres
 template1 | postgres  | UTF8     | C       | C     | =c/postgres          +
           |           |          |         |       | postgres=CTc/postgres
 zipcode   | zz01adm01 | UTF8     | C       | C     |
(5 rows)

[postgres@test01 data]$

テーブルのオーナー、スキーマを変更する

  • オーナー変更のSQLファイルを作成
    テーブル数が多いため、オーナー変更のSQLファイルを作成して実行する
tb_chown.sql
alter table public.actor owner to zz01adm01;
alter table public.address owner to zz01adm01;
alter table public.category owner to zz01adm01;
alter table public.city owner to zz01adm01;
alter table public.country owner to zz01adm01;
alter table public.customer owner to zz01adm01;
alter table public.film owner to zz01adm01;
alter table public.film_actor owner to zz01adm01;
alter table public.film_category owner to zz01adm01;
alter table public.inventory owner to zz01adm01;
alter table public.language owner to zz01adm01;
alter table public.payment owner to zz01adm01;
alter table public.rental owner to zz01adm01;
alter table public.staff owner to zz01adm01;
alter table public.store owner to zz01adm01;
  • SQLファイルを実行

この時点では、テーブルのオーナーはpostgresユーザーのため、DBへの接続はpostgresユーザーで行う

[postgres@test01 data]$ psql -U postgres -d dvdrental -f /home/postgres/sql/tb_chown.sql
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
  • オーナーが変更されたことを確認する
[postgres@test01 data]$ psql -U postgres -d dvdrental -c "\dt"
             List of relations
 Schema |     Name      | Type  |   Owner
--------+---------------+-------+-----------
 public | actor         | table | zz01adm01
 public | address       | table | zz01adm01
 public | category      | table | zz01adm01
 public | city          | table | zz01adm01
 public | country       | table | zz01adm01
 public | customer      | table | zz01adm01
 public | film          | table | zz01adm01
 public | film_actor    | table | zz01adm01
 public | film_category | table | zz01adm01
 public | inventory     | table | zz01adm01
 public | language      | table | zz01adm01
 public | payment       | table | zz01adm01
 public | rental        | table | zz01adm01
 public | staff         | table | zz01adm01
 public | store         | table | zz01adm01
(15 rows)
  • スキーマを作成

DBへ接続するときは、zz01adm01ユーザーでdvdrental DBに接続

psql -U zz01adm01 -d dvdrental
psql (9.6.2)
Type "help" for help.

dvdrental=> create schema zz01adm01;
CREATE SCHEMA
  • スキーマの確認
dvdrental-> \dn
    List of schemas
   Name    |   Owner
-----------+-----------
 public    | postgres
 zz01adm01 | zz01adm01
(2 rows)
  • スキーマ変更のSQLファイルを作成
tb_chschema.sql
alter table public.actor set schema zz01adm01;
alter table public.address set schema zz01adm01;
alter table public.category set schema zz01adm01;
alter table public.city set schema zz01adm01;
alter table public.country set schema zz01adm01;
alter table public.customer set schema zz01adm01;
alter table public.film set schema zz01adm01;
alter table public.film_actor set schema zz01adm01;
alter table public.film_category set schema zz01adm01;
alter table public.inventory set schema zz01adm01;
alter table public.language set schema zz01adm01;
alter table public.payment set schema zz01adm01;
alter table public.rental set schema zz01adm01;
alter table public.staff set schema zz01adm01;
alter table public.store set schema zz01adm01;
  • SQLファイルを実行
[postgres@test01 sql]$ psql -U zz01adm01 -d dvdrental -f /home/postgres/sql/tb_chschema.sql
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
  • スキーマが変更されたことを確認
[postgres@sistest01 sql]$ psql -U zz01adm01 -d dvdrental -c "\dt"
               List of relations
  Schema   |     Name      | Type  |   Owner
-----------+---------------+-------+-----------
 zz01adm01 | actor         | table | zz01adm01
 zz01adm01 | address       | table | zz01adm01
 zz01adm01 | category      | table | zz01adm01
 zz01adm01 | city          | table | zz01adm01
 zz01adm01 | country       | table | zz01adm01
 zz01adm01 | customer      | table | zz01adm01
 zz01adm01 | film          | table | zz01adm01
 zz01adm01 | film_actor    | table | zz01adm01
 zz01adm01 | film_category | table | zz01adm01
 zz01adm01 | inventory     | table | zz01adm01
 zz01adm01 | language      | table | zz01adm01
 zz01adm01 | payment       | table | zz01adm01
 zz01adm01 | rental        | table | zz01adm01
 zz01adm01 | staff         | table | zz01adm01
 zz01adm01 | store         | table | zz01adm01
(15 rows)

VIEWのオーナー、スキーマを変更する。

  • VIEWを確認する

VIEWの内容を確認する

[postgres@test01 sql]$ psql -U zz01adm01 -d dvdrental -c "\dv"
                   List of relations
 Schema |            Name            | Type |  Owner
--------+----------------------------+------+----------
 public | actor_info                 | view | postgres
 public | customer_list              | view | postgres
 public | film_list                  | view | postgres
 public | nicer_but_slower_film_list | view | postgres
 public | sales_by_film_category     | view | postgres
 public | sales_by_store             | view | postgres
 public | staff_list                 | view | postgres
(7 rows)
  • オーナー変更のSQLファイルを作成

VIEW数が多いため、オーナー変更のSQLファイルを作成して実行する

view_chown.sql
alter view public.actor_info owner to zz01adm01;
alter view public.customer_list owner to zz01adm01;
alter view public.film_list owner to zz01adm01;
alter view public.nicer_but_slower_film_list owner to zz01adm01;
alter view public.sales_by_film_category owner to zz01adm01;
alter view public.sales_by_store owner to zz01adm01;
alter view public.staff_list owner to zz01adm01;
  • SQLファイルを実行する

この時点では、VIEWのオーナーは、postgresユーザーのため、DBへの接続は、postgresユーザーで実施

[postgres@test01 data]$ psql -U postgres -d dvdrental -f /home/postgres/sql/view_chown.sql
ALTER VIEW
ALTER VIEW
ALTER VIEW
ALTER VIEW
ALTER VIEW
ALTER VIEW
ALTER VIEW
  • オーナーが変更されたことを確認
[postgres@sistest01 data]$ psql -U zz01adm01 -d dvdrental -c "\dv"
                   List of relations
 Schema |            Name            | Type |   Owner
--------+----------------------------+------+-----------
 public | actor_info                 | view | zz01adm01
 public | customer_list              | view | zz01adm01
 public | film_list                  | view | zz01adm01
 public | nicer_but_slower_film_list | view | zz01adm01
 public | sales_by_film_category     | view | zz01adm01
 public | sales_by_store             | view | zz01adm01
 public | staff_list                 | view | zz01adm01
(7 rows)
  • スキーマを変更するSQLファイルを作成
view_chschema.sql
alter view public.actor_info set schema zz01adm01;
alter view public.customer_list set schema zz01adm01;
alter view public.film_list set schema zz01adm01;
alter view public.nicer_but_slower_film_list set schema zz01adm01;
alter view public.sales_by_film_category set schema zz01adm01;
alter view public.sales_by_store set schema zz01adm01;
alter view public.staff_list set schema zz01adm01;
  • SQLファイルを実行する

DBへの接続はzz01adm01ユーザーで実施する

[postgres@test01 data]$ psql -U zz01adm01 -d dvdrental -f /home/postgres/sql/view_chschema.sql
ALTER VIEW
ALTER VIEW
ALTER VIEW
ALTER VIEW
ALTER VIEW
ALTER VIEW
ALTER VIEW
  • 変更されたことを確認
[postgres@test01 data]$ psql -U zz01adm01 -d dvdrental -c "\dv"
                     List of relations
  Schema   |            Name            | Type |   Owner
-----------+----------------------------+------+-----------
 zz01adm01 | actor_info                 | view | zz01adm01
 zz01adm01 | customer_list              | view | zz01adm01
 zz01adm01 | film_list                  | view | zz01adm01
 zz01adm01 | nicer_but_slower_film_list | view | zz01adm01
 zz01adm01 | sales_by_film_category     | view | zz01adm01
 zz01adm01 | sales_by_store             | view | zz01adm01
 zz01adm01 | staff_list                 | view | zz01adm01
(7 rows)

テーブルへアクセスして確認

[postgres@test01 data]$ psql -U zz01adm01 -d dvdrental -c "select * from zz01adm01.actor;"
 actor_id | first_name  |  last_name   |      last_update
----------+-------------+--------------+------------------------
        1 | Penelope    | Guiness      | 2013-05-26 14:47:57.62
        2 | Nick        | Wahlberg     | 2013-05-26 14:47:57.62
        3 | Ed          | Chase        | 2013-05-26 14:47:57.62
        4 | Jennifer    | Davis        | 2013-05-26 14:47:57.62
        5 | Johnny      | Lollobrigida | 2013-05-26 14:47:57.62
        6 | Bette       | Nicholson    | 2013-05-26 14:47:57.62
        7 | Grace       | Mostel       | 2013-05-26 14:47:57.62
        8 | Matthew     | Johansson    | 2013-05-26 14:47:57.62
        9 | Joe         | Swank        | 2013-05-26 14:47:57.62
       10 | Christian   | Gable        | 2013-05-26 14:47:57.62
       11 | Zero        | Cage         | 2013-05-26 14:47:57.62
       12 | Karl        | Berry        | 2013-05-26 14:47:57.62
       13 | Uma         | Wood         | 2013-05-26 14:47:57.62
       14 | Vivien      | Bergen       | 2013-05-26 14:47:57.62
       15 | Cuba        | Olivier      | 2013-05-26 14:47:57.62
       16 | Fred        | Costner      | 2013-05-26 14:47:57.62
       17 | Helen       | Voight       | 2013-05-26 14:47:57.62
       18 | Dan         | Torn         | 2013-05-26 14:47:57.62
       19 | Bob         | Fawcett      | 2013-05-26 14:47:57.62
       20 | Lucille     | Tracy        | 2013-05-26 14:47:57.62
       21 | Kirsten     | Paltrow      | 2013-05-26 14:47:57.62

--More--

問題なさそうだ！
よかったよかった。

参考

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

0
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?