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

tom__bo’s Blog

情報系学生が筋トレしたり、筋トレしたり筋トレしたことを書くブログ。もはやダイアリー

PostgreSQL サンプルデータベースで実験環境構築

最近はブログに書けるようなまとまった勉強をしていなくて非常に不安なtom__boです。

今回はPostgreSQLつかっていろいろ実験してみたいので、環境を構築することにしました。(ただの作業メモになりそう。。。)
バージョンのアップグレードも試してみたいので、ver9.3を使ってみる。(9.5出てた(´・ω・`))


基本的に以前買った↓を参考にしている *1
www.amazon.co.jp


サンプルデータベースはドキュメントのものを利用して準備していきます。
PostgreSQL Tutorial


<目次>

  1. vagrantでcentos65を立てる
  2. CentOSでの準備
  3. PostgreSQLインストール
  4. DB初期化
  5. ユーザ・ロール作成、権限管理
  6. サンプルデータ投入
  7. ダンプとリストア

1. Vagrantでcentos6.5を立てる

  • ipaddressだけ指定した192.168.33.1

2. Centosでの準備

  • postgresユーザグループ・postgresユーザを作成
postgres > sudo su
root> groupadd postgres
root> useradd -g postgres postgres 
root> passwd postgres
root> usermod -G wheel postgres
root> yum install readline-devel
root> yum install zlib-devel

3. PostgreSQLインストール

本家からアーカイブをダウンロード。最新版でよければ => ftp://ftp.postgresql.org/pub/latest

postgres> wget ftp://ftp.postgresql.org/pub/source/v9.3.9/postgresql-9.3.9.tar.gz 

postgresユーザに切り替えて、アーカイブの展開、インストール
(参考書*1に合わせて /usr/local/pgsqlに作成することにする)

root > su postgres
postgres> tar xvfz postgresql-9.3.9.tar.gz
postgres> cd postgresql-9.3.9
postgres> ./configure --prefix=/usr/local/pgsql
postgres> gmake
postgres> cd contrib
postgres> gmake
postgres> cd ..
postgres> sudo mkdir -p /usr/local/pgsql
postgres> sudo chown postgres:postgres /usr/local/pgsql
postgres> gmake install
postgres> cd contrib
postgres> gmake install

4. DB初期化

パスを通しておく。

# .bashrc等
export PGHOME=/usr/local/pgsql
export PGDATA=/usr/local/pgsql/data
export PGHOST=localhost
PATH=$PGHOME/bin:$PATH

インストール後にPostgreSQLを初期化する必要がある。ここで、データベースクラスタを作成するbasedir, locale, encodingを指定する。日本で使うことを想定しているならlocaleを指定する必要はない(その場合--no-locale)らしいが、念のため指定しておく。

postgres> initdb --locale=japanese -D /usr/local/pgsql/data -E=UTF8 --no-locale

起動&接続できるはず

postgres> pg_ctl [start | stop | restart | status]
postgres> psql -U postgres

5. ロール, 権限管理

ロールにはデータベースオブジェクトごとへの権限の設定をするべきところだが、実験環境なので、SUPERUSER権限をもったrootロールを作成してしまう。
"SUPERUSER"オプションを付けることでスーパーユーザ権限を持つが、厳密にはスーパユーザ権限は存在せず、"権限検査しない"ロールのことらしい。また、このロールをスーパユーザと呼ぶ。


ロールを確認したらpostgresとrootロールは用意されていて、postgresの方が良さそうなのでこのまま。

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication | {}
 root      | Superuser, Cannot login                        | {}

6. サンプルデータ投入

http://www.postgresqltutorial.com/postgresql-sample-database/
上記のチュートリアルに従って、DL先(Download注意)からzipファイルをダウンロードして投入。
先にDB作成

postgres内で> CREATE DATABASE dvdrental;

リストアすることで突っ込む

postgres> unzip dvdrental.zip
postgres> pg_restore -U postgres -d dvdrental ./dvdrental.tar


確認

postgres=# \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)

postgres=# \c dvdrental;
You are now connected to database "dvdrental" as user "postgres".

dvdrental=# \dt
             List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+----------
 public | actor         | table | postgres
 public | address       | table | postgres
 public | category      | table | postgres
... (省略)

dvdrental=# SELECT * from country limit 3;
 country_id |    country     |     last_update
------------+----------------+---------------------
          1 | Afghanistan    | 2006-02-15 09:44:00
          2 | Algeria        | 2006-02-15 09:44:00
          3 | American Samoa | 2006-02-15 09:44:00
(3 rows)


このサンプルデータはDVDレンタルショップのDBを模したものになっていSakila Sample DatabaseのものをPostgres用に修正したものらしい。以前MySQLで実験しようとした時サンプルデータベースの存在を調べずに自前で作ろうとしたりしたけど、これ使えば他のDB製品との比較とかし易いかも。。。今度見てみる。
↓ ER図も公開されている



7. ダンプとリストア

バックアップの確認のため、データに変更を加えてダンプ->リストアしてみる。
PostgreSQLでは論理バックアップ、物理バックアップのどちらもテーブルのロックをせずにバックアップができる。
嬉しい感じもするが、それはそれで整合性がなくなるので、WALファイルを適用して~~と言った話があった気がする。今後確認。

dvdrental=# SELECT * from actor where first_name like 'Johnny' limit 3;
 actor_id | first_name |  last_name   |      last_update
----------+------------+--------------+------------------------
        5 | Johnny     | Lollobrigida | 2013-05-26 14:47:57.62
       40 | Johnny     | Cage         | 2013-05-26 14:47:57.62
(2 rows)

ジョニーデップがいない 工エエェェ(´д`)ェェエエ工


dvdrental=# SELECT * from actor where first_name like 'Johnny' limit 3;
 actor_id | first_name |  last_name   |      last_update
----------+------------+--------------+------------------------
        5 | Johnny     | Lollobrigida | 2013-05-26 14:47:57.62
       40 | Johnny     | Cage         | 2013-05-26 14:47:57.62
(2 rows)

dvdrental=# INSERT INTO actor (first_name, last_name) VALUES ('Johnny', 'Depp');
INSERT 0 1
dvdrental=# SELECT * from actor where first_name like 'Johnny' limit 3;
 actor_id | first_name |  last_name   |        last_update
----------+------------+--------------+----------------------------
        5 | Johnny     | Lollobrigida | 2013-05-26 14:47:57.62
       40 | Johnny     | Cage         | 2013-05-26 14:47:57.62
      201 | Johnny     | Depp         | 2015-10-25 07:35:00.696709
(3 rows)


ふむ。( ´ー`)


というわけで、ダンプとリストアを試してみるが普通にリストアをするのはデータ投入時にやったので、-cオプションを付けて、作りなおしてみる。

postgres> pg_dump -U postgres -F p -f dvdrental.dump dvdrental -c

ファイルタイプにplaintextを指定したので、dvdrental.dumpで内容が見られる。長いので省略。

ダンプが出来たのでジョニーデップを削除してみる。

dvdrental=# DELETE from actor where first_name like 'Johnny' and last_name like 'Depp';
DELETE 1

dvdrental=# select * from actor where first_name like 'Johnny' limit 4;
 actor_id | first_name |  last_name   |      last_update
----------+------------+--------------+------------------------
        5 | Johnny     | Lollobrigida | 2013-05-26 14:47:57.62
       40 | Johnny     | Cage         | 2013-05-26 14:47:57.62
(2 rows)


リストア。

postgres> pg_restore -U postgres -d dvdrental -F p dvdrental.dump


失敗(´・ω・`)

pg_restore: unrecognized archive format "p"; please specify "c", "d", or "t"


pg_dump時にはファイルフォーマットをpにしたのだが、そもそもpがない。

  • F c にしてdumpをやり直す。

さらにpg_dump時に-cオプションが有効なのはファイルタイプがp: プレインテキストの場合のみで、それ以外のタイプの時はpg_restore時に-cオプションをつけるようだ。https://www.postgresql.jp/document/9.3/html/app-pgdump.html: pg_dumpドキュメントページ

pg_restore -U postgres -d dvdrental -F c dvdrental.dump -c


確認

dvdrental=# select * from actor where first_name like 'Johnny';
 actor_id | first_name |  last_name   |        last_update
----------+------------+--------------+----------------------------
        5 | Johnny     | Lollobrigida | 2013-05-26 14:47:57.62
       40 | Johnny     | Cage         | 2013-05-26 14:47:57.62
      202 | Johnny     | Depp         | 2015-10-25 10:36:08.574025
(3 rows)

よし。




今回はここまで。

<次にやりたいこと>

  • 0xDBE使ってみる
  • マスター/スレーブ構成作る
  • バージョンアップ
  • 他いろいろ

<確認したいこと>

  • sakila sample databaseって何か
  • pg_dump時にロックしないの便利な気がするけど整合性は?


次にやりたいことの前にdvdrentalの内容見て集計クエリのexplainとって見るほうが先かなーとか思っている。
ひとまず今回はPostgreSQLの環境構築でしたーーー





終わり⊂゚U┬───┬~