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

今まで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