最近はブログに書けるようなまとまった勉強をしていなくて非常に不安なtom__boです。
今回はPostgreSQLつかっていろいろ実験してみたいので、環境を構築することにしました。（ただの作業メモになりそう。。。）
バージョンのアップグレードも試してみたいので、ver9.3を使ってみる。(9.5出てた(´・ω・｀))
基本的に以前買った↓を参考にしている *1
www.amazon.co.jp
サンプルデータベースはドキュメントのものを利用して準備していきます。
PostgreSQL Tutorial
＜目次＞
- vagrantでcentos65を立てる
- CentOSでの準備
- PostgreSQLインストール
- DB初期化
- ユーザ・ロール作成、権限管理
- サンプルデータ投入
- ダンプとリストア
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の環境構築でしたーーー
終わり⊂ﾟＵ┬───┬~
