UbuntuにPostgreSQLをインストール



環境: Ubuntu 16.04.2 LTS

PostgresSQL 9.5.7をインストールする。

# インストール

    % sudo apt-get update
    % sudo apt-get install -y postgresql

# セットアップ
## 初期設定

    % sudo -i -u postgres
    postgres% psql
    postgres=# \du
    List of roles
    Role name |                         Attributes                         | Member of
    ----------+------------------------------------------------------------+-----------
    postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

    postgres=# \password postgres
    Enter password for new role:
    Enter it again:

## ユーザ作成
    postgres% createuser -P hoge
    postgres% psql
    postgres=# \du
    List of roles
    Role name |                         Attributes                         | Member of
    ----------+------------------------------------------------------------+-----------
    hoge      |                                                            | {}
    postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

    postgres=# alter role hoge with password 'password';

## DB作成
    postgres=# create database geho;
    CREATE DATABASE
    postgres=# \l
                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
    -----------+----------+----------+-------------+-------------+-----------------------
    postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
    geho      | 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          +
    　        |          |          |             |             | postgres=CTc/postgres


## DBに権限付与
    postgres-# GRANT ALL ON DATABASE geho TO hoge;
    postgres-# \l
                                List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
    -----------+----------+----------+-------------+-------------+-----------------------
    postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
    geho    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
    |          |          |             |             | postgres=CTc/postgres+
    |          |          |             |             | ii=CTc/postgres
    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          +
    |          |          |             |             | postgres=CTc/postgres
    (4 rows)

## 一般ユーザによるDBアクセス
    hoge% psql postgres hoge
    postgres=> \connect geho
    geho=> CREATE TABLE fuga
    geho-> (id int, name character varying(5));
    CREATE TABLE
    geho=> \dt
          List of relations
    Schema | Name | Type  | Owner
    --------+------+-------+-------
    public | fuga | table | hoge
    (1 row)
    geho=> SELECT * FROM fuga;
    id | name
    ----+------
    (0 rows)


#参考文献
https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-16-04