PostgreSQL - Ubuntu14.04にPostgreSQL9.4をインストールしてとりあえず使えるようにするまでの手順


今までDBといったらOracle/MySQLしか扱ったことがなかった  
今回はじめてPostgreSQL環境構築で結構戸惑ってしまったので纏めておきました  

#### 1.PostgreSQL9.4インストール
Ubuntu14.04(2015/11時点)だとデフォルトで9.3がインストールされていしまうので、リポジトリを追加

```
$ sudo add-apt-repository "deb https://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main"
$ wget --quiet -O - https://postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - 
$ sudo apt-get update
$ sudo apt-get install postgresql-9.4
```

#### 2.とりあえず接続してみる
まずはプロセスを確認してみる

```
$ ps aux | grep postgres
postgres  3195  0.0  1.6 249348 16788 ?        S    12:14   0:00 /usr/lib/postgresql/9.4/bin/postgres -D /var/lib/postgresql/9.4/main -c config_file=/etc/postgresql/9.4/main/postgresql.conf
postgres  3197  0.0  0.1 249348  1900 ?        Ss   12:14   0:00 postgres: checkpointer process
postgres  3198  0.0  0.2 249348  2164 ?        Ss   12:14   0:00 postgres: writer process
postgres  3199  0.0  0.1 249348  1688 ?        Ss   12:14   0:00 postgres: wal writer process
postgres  3200  0.0  0.2 249752  2648 ?        Ss   12:14   0:00 postgres: autovacuum launcher process
postgres  3201  0.0  0.1 104492  1740 ?        Ss   12:14   0:00 postgres: stats collector process
root      3299  0.0  0.0  10464   932 pts/0    S+   12:15   0:00 grep --color=auto postgres
```

起動問題なさそうなので、接続しようと思った時、接続方法を理解するのに苦しんだ  
PostgreSQLのデフォルト管理者ユーザはOSの「postgres」ユーザになってからソケット経由で接続する  

```
$ su - postgres
$ psql -l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
```

#### 3.設定ファイル変更
デフォルトではリモートから接続できない設定になっているので修正

```
$ vi /etc/postgresql/9.4/main/postgresql.conf
----------------------------------------
listen_addresses = '*'
----------------------------------------
```

ユーザ認証についてもリモートから接続できない設定になっているので修正  
以下のパラメータが有効になっている状態に  

```
$ vi /etc/postgresql/9.4/main/pg_hda.conf
----------------------------------------
local all all peer
host all all all md5
hostssl all all all md5
----------------------------------------
```

設定を反映するには再起動が必要

```
$ sudo service postgres restart
 * Restarting PostgreSQL 9.4 database server                                                                                                           [ OK ]
```

#### 4.ユーザ作成
OSのユーザを作成する方法やpostgresが提供する「createuser」とかいろいろ作成方法がありそうだけど、SQLで作成する方法が一番しっくりくる

```
$ su - postgres
$ psql
postgres-# CREATE ROLE testuser WITH LOGIN PASSWORD 'testuser';
postgres-# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication | {}
 testuser  |                                                | {}
```

#### 5.リモートから接続
リモートから接続できるか確認

```
psql -U testuser -W -h <HOST> -p <PORT> -d <DB>
Password for user testuser:
psql (9.4.4, server 9.4.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=> 
```

接続確認ができたので完了

#### ※「pg_hba.conf」のフォーマットについて
接続を制限するために設定するファイル
フォーマットは以下のとおり

```
TYPE DATABASE USER CIDR-ADDRESS METHOD
```

* TYPE  
    * local/host/hostssl/hostnosslが指定可能、localはソケット経由の接続、hostはTCP/IP経由の接続、hostsslはSSL経由の接続、hostnosslはSSLを使用しない接続
* DATABASE  
    * 接続を許可したいデータベースを指定、「all」とすると全データベース、複数のデータベースを指定したい場合はカンマ区切り
* USER  
    * 接続を許可するユーザを指定、「all」とすると全ユーザ、複数のユーザを指定したい場合はカンマ区切り
* CIDR-ADDRESS  
    * TYPEがlocal以外の時に指定、接続許可するIPアドレスやネットワークアドレスを指定
* METHOD
    * ユーザの認証方式を指定
        * trust... 認証なし、無条件に接続を許可
        * reject... 無条件に接続拒否
        * md5... md5を利用したパスワード認証
        * password... BASIC認証

#### ※参考

* http://makaaso.hatenablog.com/entry/2015/11/20/173641