Postgresql 10

(2019/11/20 バックアップのための追記)

OS: Ubuntu 18.04 LTS
Version: psql (PostgreSQL) 10.10 (Ubuntu 10.10-1.pgdg18.04+1)

# Ubuntu 18.04 LTS service
```
$ sudo service postgresql start
$ sudo service postgresql status
$ sudo systemctl enable postgresql
```

# Login for first time

```/etc/postgresql/10/main/pg_hba.conf 
~ ~ ~ 略 ~ ~ ~
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5  <<< IDENT-based authenticationから変更
# IPv6 local connections:
host    all             all             ::1/128                 md5  <<< IDENT-based authenticationから変更
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5  <<< IDENT-based authenticationから変更
host    replication     all             ::1/128                 md5  <<< IDENT-based authenticationから変更
```

```
$ sudo -u postgres psql
```

# Change password
```sql
# ALTER USER postgres password 'postgres';  # パスワード例
```

# Login
```
$ psql -d testdb -h localhost -U postgres
```

## postgresql.conf
ログインで下記のエラーが出た場合

>psql: could not connect to server: Connection refused
        Is the server running on host "localhost" (127.0.0.1) and accepting
        TCP/IP connections on port 5432?

postgresql.confで使用したいポートがあっているか（既定値：port = 5432）確認し、なっていなかったらconf fileを変更しサービスをリスタートする。
 
```
sudo vi /etc/postgresql/10/main/postgresql.conf
```

# Create database
```sql
# CREATE DATABASE testdb TEMPLATE template0 ENCODING 'UNICODE';
```

# Database
```sql
# \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
 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
 testdb    | postgres | UTF8     | C       | C     | 
(4 rows)
```

## Schema (2020/03/18 追記)

```
// Schema list
inventory=# \dn
      List of schemas
      Name       |  Owner   
-----------------+----------
 public          | postgres
 systeminventory | postgres

// select schema
inventory=# select schema_name from information_schema.schemata where schema_name='systeminventory';
   schema_name   
-----------------
 systeminventory
(1 row)

// Drop schema
inventory=# DROP SCHEMA IF EXISTS systeminventory;
DROP SCHEMA

// Create table
inventory=# CREATE TABLE IF NOT EXISTS SystemInventory.bios (ip VARCHAR, ....);

// Schema配下に作られたテーブルを確認
inventory=# \dt systeminventory.bios;
                   List of relations
     Schema      |        Name         | Type  |  Owner   
-----------------+---------------------+-------+----------
 systeminventory | bios                | table | postgres

~ ~ ~ ~ ~ ~ ~ ~ ~ 略 ~ ~ ~ ~ ~ ~ ~ ~ ~

// テーブルのカラムを確認
inventory=# \d systeminventory.bios;
                     Table "systeminventory.bios"
     Column      |       Type        | Collation | Nullable | Default 
-----------------+-------------------+-----------+----------+---------
 ip              | character varying |           |          | 
 biosreleasedate | character varying |           |          | 

~ ~ ~ ~ ~ ~ ~ ~ ~ 略 ~ ~ ~ ~ ~ ~ ~ ~ ~

```

## DBの切り替え
```sql
# \c [DB名]
```
# Table操作関連（例）
```sql
// Drop table
# DROP TABLE signal_events;

// Create table
# CREATE TABLE IF NOT EXISTS signal_events (
# time timestamp PRIMARY KEY NOT NULL,
# product_code varchar(10),
# side varchar(10),
# price double precision,
# size double precision);

// Insert
# INSERT INTO signal_events 
# (time, product_code, side, price, size) 
# VALUES 
# ('2019-11-17 08:00:00', 'BTC_JPY', 'BUY', '927654', '0.0315');
```

# Client_encoding Error
## 経緯
Go の github.com/lib/pq はUTF8をサポートするため、PostgreSQLをUTF8で使いたい。
Windows 10 日本語版にインストールしたPostgreSQLではclient_encodingが既定でsjisになってしまう。
-> `Err: client_encoding must be absent or 'UTF8'`のエラーでGo のパッケージからテーブルへのへアクセスができない。

## 結果
規定でUTF8のためWSLにインストールしたPostgreSQLで問題なく動いた。

どれもNGだったがやってみたことのメモ：

* PostgreSQLの再インストール
* client_encoding 変更

```sql
SHOW client_encoding;
SET client_encoding='utf8';
```

* Windowsの環境変数を変更

```
PGCLIENTENCODING  : UTF8
```

* initdb

```sql
initdb --encoding UNICODE;
```

# バックアップのための追記
## モジュールインストール
```
$ sudo apt install postgresql-contrib
$ pg_dump --version
pg_dump (PostgreSQL) 10.10 (Ubuntu 10.10-1.pgdg18.04+1)
```

## OS ユーザをpostgreに登録とGrant
```sql
postgres=# CREATE USER ubuntu SYSID 1000 PASSWORD 'ubuntu'; 
NOTICE:  SYSID can no longer be specified  // 不要とのこと
CREATE ROLE
postgres=# GRANT USAGE ON SCHEMA public TO ubuntu;
GRANT
postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO ubuntu;
GRANT
postgres=# GRANT ubuntu TO postgres;
GRANT ROLE

// 逆だったので外す
postgres=# REVOKE ubuntu FROM postgres;
postgres=# GRANT postgres TO ubuntu;
postgres=# \du
                                    List of roles
 Role name |                         Attributes                         | Member of  
-----------+------------------------------------------------------------+------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 ubuntu    |                                                            | {postgres}
```

## バックアップ実行
```bash
$ pg_dump -Fc testdb -f testdb.dump
# 問題なければ何も返らない。
# 下記はバックアップ実行ユーザ(ubuntu)にGRANTをしておかないとでる。
pg_dump: [archiver (db)] query failed: ERROR:  permission denied for relation btc_jpy_1s
```
## テストのためにDROPとCREATE
```sql
postgres=# DROP DATABASE testdb;
DROP DATABASE
postgres=# CREATE DATABASE testdb TEMPLATE template0 ENCODING 'UNICODE';
CREATE DATABASE
```

## リストア
```bash
$ pg_restore -d testdb testdb.dump
# 問題なければ何も返らない。
```
### リストアエラー

* 非特権ユーザーでのバックアップだったためコメントを書かれた。

```
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2922; 0 0 COMMENT EXTENSION plpgsql 
pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of extension plpgsql
    Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
```
[参考先](http://tkrd.hatenablog.com/entry/2017/04/27/012832)によると、
> pg_dump コマンドは plgpsql エクステンションに関するコメントをダンプファイルに書き込む。このエクステンションの所有者は特権ユーザーであるため、リストア時にエラーを引き起こす。

* 下記はバックアップ実行ユーザ(ubuntu)にGRANTをしておかないとでる。

```
pg_restore: [archiver (db)] Error from TOC entry 201; 1259 24576 TABLE signal_events postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  must be member of role "postgres"
    Command was: ALTER TABLE public.signal_events OWNER TO postgres;
```

## 今まで通り使えることの確認
Ticker情報をtableに追記するGOのモジュールを実行し、追記されていることを確認。
その情報をWebserverからブラウザへ配信更新され続けていることを確認。