macOs Sierra  + homebrewな環境でPostgresqlを導入する備忘録

※注:古い情報なので仮想環境上のlinuxなどに構築することをお勧めします。

DB、SQL系の本を買って勉強するも
大抵Windows用の環境構築しか乗っていなかったので
まぁ完全な素人でもないし、と放漫なことに検証環境を用意しなかった。
Indexとか実行計画等を検証するために
自分のmacOs Sierra  + homebrewな環境にpostgresqlを導入する。

- 環境情報
  - Postgresqlのバージョン:9.6.1
  - macOs Sierraのバージョン:10.12.2


## postgresql導入
```
$ brew update
$ brew install postgresql
```

### 2018/1/1追記
新しい環境(High Sierra:10.13.2,PostgreSQL:10.1)で再度試したところ、追加のインストールが催促されました。

```
Warning: The post-install step did not complete successfully
You can try again using `brew postinstall postgresql`

```

再度指定されたコマンドを実行することでうまく行きました

```bash:
$ brew postinstall postgresql

cd /usr/local/Cellar/postgresql/10.1/bin/
$ ./psql --version
psql (PostgreSQL) 10.1

```

あとはパスを設定するコマンドを.bash_profileに追記して完了

```:.bash_profileの末尾に追記
export PATH=/usr/local/Cellar/postgresql/10.1/bin/:$PATH
```
## 起動〜接続
```bash:
$ postgres
postgres does not know where to find the server configuration file.
You must specify the --config-file or -D invocation option or set the PGDATA environment variable.
```


設定ファイルが見つからない的な警告が出る。
環境変数PGDATAに値をセットするか -Dオプションを使え的なメッセージが出る。
<参考>brewのissuで同じ内容の質問が上がっていた
https://github.com/Homebrew/legacy-homebrew/issues/21920
`man postgres`で調べてみる。


```:manから抜粋
　　　　When postgres starts it needs to know the location of the data area.
       The location must be specified by the -D option or the PGDATA
       environment variable; there is no default. Typically, -D or PGDATA
       points directly to the data area directory created by initdb(1).
```

initdbで作成したディレクトリを-D付きのコマンドライン引数か、
環境変数PGDATAで指定する必要がある。

補足:
https://www.postgresql.org/docs/9.2/static/creating-cluster.html
initdbで作成したディレクトリ(データ領域)のことをデータベースクラスタと言うらしい。

調査し回った結果、brew でinstall時にデフォルトで
`/usr/local/var/postgres`
に作成されたものを使うのが一般的なようだが、今回は勉強がてらに
`<user>/Documents/Study/db`
に作成してみる。

```bash:initdbでデータベースクラスターを作成
$ cd Documents/Study/db
$ mkdir postgres
$ initdb ./postgres -E utf8
```
色々ファイルが作成され、データベースクラスタが出来た

```bash:
$ls postgres/
PG_VERSION		pg_logical		pg_subtrans
base			pg_multixact	pg_tblspc
global			pg_notify		pg_twophase
pg_clog			pg_replslot		pg_xlog
pg_commit_ts 	pg_serial		postgresql.auto.conf
pg_dynshmem		pg_snapshots	postgresql.conf
pg_hba.conf		pg_stat
pg_ident.conf	pg_stat_tmp
```
postgresql.confにローカル以外のアクセスを出来ないように
`listen_addresses = 'localhost'`を設定。

では、あらためて-Dオプション付きでデータベースクラスタを指定して起動

```bash:
$ postgres -D ./postgres/
```

別コンソールからアクセスしてみる。
※補足:pg_ctrlコマンドでバックグラウンドで動作させ同じコンソールからアクセスすることも可能

```bash:
$ psql -d postgres
psql (9.6.1)
Type "help" for help.

postgres=# 
```
接続できた。

## DB作成
接続したデータベースクラスタにdbを作成する。

```postgres:
postgres=# CREATE DATABASE sandbox;
CREATE DATABASE
postgres=# \c sandbox
You are now connected to database "sandbox" as user <user>.
```

なんかデフォルトのユーザーで接続されたっぽいので
一通りやってみるためにユーザーも作成してみる
## ユーザー追加
ユーザー追加のオプション等豊富なので参考になった
https://www.postgresql.jp/document/9.2/html/app-createuser.html

```bash:
$ createuser -P -e　<new_user> 
Enter password for new role: 
Enter it again: 
CREATE ROLE <new_user> PASSWORD '<暗号化されたパスワード>' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;
$ 
```
- -P(--pwprompt)でパスワードのプロンプトを表示
- -e(--echo)でcreateuserで実行される問い合わせを表示

createuserはサーバーが起動している状態で行う必要がある。
今回は接続するサーバーを指定していないのでローカルの環境(起動〜接続で立ち上げたところにユーザーを作成する動作になった。)

折角パスワードを設定したユーザーを作成したので
パスワード入力ができるようにpg_hba.confの設定を変更する
デフォルトでローカルホストからのアクセスはtrustとなっていたのでmd5で認証するように設置を変更(trust->md5に変えるだけ)
参考:http://net-newbie.com/postgres/password.html

パスワードが要求されるようになった。

```bash:
$ psql -h localhost -U <new_user> -d sandbox
Password for user <new_user>: 
psql (9.6.1)
Type "help" for help.

sandbox=> 
```


## SQLの動作確認:CREATE TABELE〜INSERT〜SELECT

```postgres:
sandbox=> create table hello(colum varchar(256));
CREATE TABLE
sandbox=> insert into hello(colum) values('world');
INSERT 0 1
sandbox=> select * from hello;
 colum 
-------
 world
(1 row)
```

## コマンド等メモ

最低限のメモ

### ターミナル
|コマンド|意味|
|:-:    |:-:|
|postgres -D \<datadir>|起動|
|pg_ctl start -D \<datadir>|バックグラウンドで起動|
|pg_ctl stop -D \<datadir>|上記の停止|
|psql -d \<dbname>|接続(ローカル)|


### psqlコマンド
|コマンド|意味|
|:-:    |:-:|
|\q     |psqlの終了|
|\?|psqlのコマンド一覧|
|\h|sqlの一覧|
|\dt|table一覧|
|\d \<table\>|tableの項目一覧|


## 参考
https://github.com/Homebrew/legacy-homebrew/issues/21920
[Homebrewを使ったPostgreSQLのインストール(Mac OS El Capitan)](http://tstomoki.com/programming/posgre_inst)
[PostgreSQL: Documentation: 9.2: Creating a Database Cluster](https://www.postgresql.org/docs/9.2/static/creating-cluster.html)
[PostgreSQL でパスワード認証を使う](http://net-newbie.com/postgres/password.html)
[createuser](https://www.postgresql.jp/document/9.2/html/app-createuser.html)