UbuntuでPostgreSQLの操作メモ

PostgreSQLを使うにあたって調べたことのメモです（自分用）。
適宜、追記・訂正すると思います。

環境： Ubuntu 18.04.1 LTS

## インストール

```
$ apt-get install -y postgresql-10
```

## 起動と停止

### 起動

```
$ /etc/init.d/postgresql start
```

### 停止
```
$ /etc/init.d/postgresql stop
```

## 最初の設定など

テーブルを作成してデータをインサート出来るようになるまでにいくつかやることがある。

### ロールの作成

PostgreSQLはユーザ的なものとしてロールの設定が必要。
今回はデフォルトで用意されている`postgres`でログインして作業を進める。
なお`postgres`は初期状態ではパスワード未設定なので`sudo passwd postgres`で設定する。

```
$ su - postgres
$ psql
Type "help" for help.
postgres=# 
```

以下は作成例。

```
postgres=# create role test01 LOGIN CREATEDB PASSWORD 'test01';
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test01    | Create DB         

```

- ロールは`create role`で作成する。
- ロールの属性を後から変更するときは`alter role test01 LOGIN`などを指定する。
- 属性を外したいときは、対になる`NOLOGIN`が用意されているのでそれを指定する。
- ロールの削除は`drop role`を使う。

詳しい属性については以下を参照。
https://www.postgresql.jp/document/9.4/html/sql-createrole.html

### クライアント認証

このまま作成したロール権限でログインしようとしても以下のエラーが出る。
これはpeer認証がオンになっているためで、ユーザ名がOSのユーザ名と一致していないと認証できない。
http://www.postgresql.jp/document/9.1/html/auth-methods.html#AUTH-PEER

```
$ psql --username test01 --password
Password for user test01: 
psql: FATAL:  Peer authentication failed for user "test01"
```

そのため以下のように書き換える。

```
$ vim /etc/postgresql/10/main/pg_hba.conf 
# "local" is for Unix domain socket connections only
# local   all             all                                     peer
local   all             all                                     trust
```

なお`pg_hba.conf`の場所がわからない場合、スーパーユーザである`postgres`などで以下のクエリを実行すれば取れる。

```
# SHOW hba_file;
/etc/postgresql/10/main/pg_hba.conf 
```

### DB作成

DBを作成してロールを設定する。

```
postgres=# create database test01 owner test01;
CREATE DATABASE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test01    | Create DB                                                  | {}

```

### 接続

これで作成したロールを使って接続できる。
※`psql`はオプションを省略するとOSのユーザ名が設定される。

```
$ psql --username test01 --password
Password for user test01: 
psql
Type "help" for help.
test01=> 
```

## テーブル操作

最低限の環境は整ったのでテーブルを作成してみる。

```
create table test (
  id serial,
  text varchar(500)
);
```
idで指定しているserial型はPostgreSQLの独自型で`test_id_seq`というシーケンスも自動生成してくれる模様。

insert->selectも試してみたが問題なし。
これで一先ずテーブルの操作が出来ることを確認できた。

```
test01=> insert into test values(nextval('test_id_seq'), 'test2');
INSERT 0 1
test01=> select * from test;
 id |  text   
----+---------
  1 | test1
  2 | test2
(2 rows)
```

## DB操作（psql）

`\d`でリレーション一覧
`\du`でロール一覧
`\l`でDB一覧
`\dt`でテーブル一覧

## 参考文献

- https://eng-entrance.com/postgresql-role
- https://qiita.com/aoi_erimiya/items/a37890fb4719357f7f87
- https://www.postgresql.jp/document/9.4/html/sql-createrole.html
- https://qiita.com/ibara1454/items/40ce2d82926f48cf02bc