RHEL8環境へのPostgreSQL12のインストールと簡易動作確認

RHEL8環境へのPostgreSQL12をRPMインストールし、簡易動作確認を実施した際の作業メモ

# 記載内容

1. 事前準備（PostgreSQL用RPMのダウンロード）
2. PostgreSQL12のインストール（RPMの展開）
3. PostgreSQL初期設定
4. 新規ロールの作成とDB接続設定
5. スキーマ/テーブルの作成
6. テーブルのデータ操作の実施（INSERT/SELECT/UPDATE/DELETE）

# 前提環境
ホストOS：macOS Catalina 10.15.1
仮想化ソフト：VirtualBox Version 6.1.0 r135406
ゲストOS：Red Hat Enterprise Linux release 8.0 (Ootpa)
ゲストOSカーネル：4.18.0-80.el8.x86_64
PostgreSQLバージョン：12.1

# 1. 事前準備

- RPMのダウンロード&RHEL8環境への転送

PostgreSQLコミュニティサイトのリポジトリからRPMのダウンロード
https://yum.postgresql.org/12/redhat/rhel-8-x86_64/
以下最低限のRPMのみダウンロード
`postgresql12-server-12.1-2PGDG.rhel8.x86_64.rpm`
`postgresql12-12.1-2PGDG.rhel8.x86_64.rpm`
`postgresql12-libs-12.1-2PGDG.rhel8.x86_64.rpm`


- PostgreSQL構築先のRHEL8環境への転送

```console
[root@rhel8 postgresql]# cd /work/postgresql/
[root@rhel8 postgresql]# 
[root@rhel8 postgresql]# ls -ltr 
合計 19948
-rw-r--r--. 1 login00 login00 16308316  1月 31 23:33 postgresql12-server-12.1-2PGDG.rhel8.x86_64.rpm
-rw-r--r--. 1 login00 login00  3238556  1月 31 23:34 postgresql12-12.1-2PGDG.rhel8.x86_64.rpm
-rw-r--r--. 1 login00 login00   874940  1月 31 23:35 postgresql12-libs-12.1-2PGDG.rhel8.x86_64.rpm
[root@rhel8 postgresql]# 
```

# 2. PostgreSQL12のインストール（RPMの展開）

- 非インターネット接続環境を想定しRPMインストール

```console
[root@rhel8 postgresql]# rpm -ivh /work/postgresql/*
警告: ./postgresql12-12.1-2PGDG.rhel8.x86_64.rpm: ヘッダー V4 DSA/SHA1 Signature、鍵 ID 442df0f8: NOKEY
Verifying...                          ################################# [100%]
準備しています...              ################################# [100%]
更新中 / インストール中...
   1:postgresql12-libs-12.1-2PGDG.rhel################################# [ 33%]
   2:postgresql12-12.1-2PGDG.rhel8    ################################# [ 67%]
   3:postgresql12-server-12.1-2PGDG.rh################################# [100%]
[/usr/lib/tmpfiles.d/libstoragemgmt.conf:1] Line references path below legacy directory /var/run/, updating /var/run/lsm → /run/lsm; please update the tmpfiles.d/ drop-in file accordingly.
[/usr/lib/tmpfiles.d/libstoragemgmt.conf:2] Line references path below legacy directory /var/run/, updating /var/run/lsm/ipc → /run/lsm/ipc; please update the tmpfiles.d/ drop-in file accordingly.
[/usr/lib/tmpfiles.d/mdadm.conf:1] Line references path below legacy directory /var/run/, updating /var/run/mdadm → /run/mdadm; please update the tmpfiles.d/ drop-in file accordingly.
[/usr/lib/tmpfiles.d/radvd.conf:1] Line references path below legacy directory /var/run/, updating /var/run/radvd → /run/radvd; please update the tmpfiles.d/ drop-in file accordingly.
[/usr/lib/tmpfiles.d/spice-vdagentd.conf:2] Line references path below legacy directory /var/run/, updating /var/run/spice-vdagentd → /run/spice-vdagentd; please update the tmpfiles.d/ drop-in file accordingly.
[/usr/lib/tmpfiles.d/subscription-manager.conf:1] Line references path below legacy directory /var/run/, updating /var/run/rhsm → /run/rhsm; please update the tmpfiles.d/ drop-in file accordingly.
[root@rhel8 postgresql]# 
[root@rhel8 postgresql]# echo $?
0
[root@rhel8 postgresql]# 
```

# 3. PostgreSQL初期設定

- データベースクラスタの作成

```console
[root@rhel8 postgresql]# /usr/pgsql-12/bin/postgresql-12-setup initdb
Initializing database ... OK

[root@rhel8 postgresql]# 
```

- PostgreSQLの起動

```console
[root@rhel8 data]# systemctl start postgresql-12.service 
[root@rhel8 data]# 
[root@rhel8 data]# systemctl status postgresql-12.service 
● postgresql-12.service - PostgreSQL 12 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service; disabled; vendor preset: disabled)
   Active: active (running) since Sat 2020-02-01 00:17:32 EST; 51s ago
     Docs: https://www.postgresql.org/docs/12/static/
  Process: 3513 ExecStartPre=/usr/pgsql-12/bin/postgresql-12-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 3518 (postmaster)
    Tasks: 8 (limit: 5064)
   Memory: 17.3M
   CGroup: /system.slice/postgresql-12.service
           ├─3518 /usr/pgsql-12/bin/postmaster -D /var/lib/pgsql/12/data/
           ├─3520 postgres: logger   
           ├─3522 postgres: checkpointer   
           ├─3523 postgres: background writer   
           ├─3524 postgres: walwriter   
           ├─3525 postgres: autovacuum launcher   
           ├─3526 postgres: stats collector   
           └─3527 postgres: logical replication launcher   

 2月 01 00:17:32 rhel8 systemd[1]: Starting PostgreSQL 12 database server...
 2月 01 00:17:32 rhel8 postmaster[3518]: 2020-02-01 00:17:32.527 EST [3518] LOG:  PostgreSQL 12.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit を起動しています
 2月 01 00:17:32 rhel8 postmaster[3518]: 2020-02-01 00:17:32.530 EST [3518] LOG:  IPv6アドレス"::1"、ポート5432で待ち受けています
 2月 01 00:17:32 rhel8 postmaster[3518]: 2020-02-01 00:17:32.530 EST [3518] LOG:  IPv4アドレス"127.0.0.1"、ポート5432で待ち受けています
 2月 01 00:17:32 rhel8 postmaster[3518]: 2020-02-01 00:17:32.536 EST [3518] LOG:  Unixソケット"/var/run/postgresql/.s.PGSQL.5432"で待ち受けています
 2月 01 00:17:32 rhel8 postmaster[3518]: 2020-02-01 00:17:32.541 EST [3518] LOG:  Unixソケット"/tmp/.s.PGSQL.5432"で待ち受けています
 2月 01 00:17:32 rhel8 postmaster[3518]: 2020-02-01 00:17:32.559 EST [3518] LOG:  ログ出力をログ収集プロセスにリダイレクトしています
 2月 01 00:17:32 rhel8 postmaster[3518]: 2020-02-01 00:17:32.559 EST [3518] ヒント:  ここからのログ出力はディレクトリ"log"に現れます。
 2月 01 00:17:32 rhel8 systemd[1]: Started PostgreSQL 12 database server.

[root@rhel8 data]#
```

- スーパーユーザでのPostgreSQLへの接続

```console
[root@rhel8 data]# su - postgres 
[postgres@rhel8 ~]$  
[postgres@rhel8 ~]$ psql
psql (12.1)
"help"でヘルプを表示します。

postgres=# SELECT now();
              now              
-------------------------------
 2020-02-01 00:22:40.184864-05
(1 行)

postgres=#
```

- initdbによりデフォルトで作成されたデータベースの確認

```console
postgres=# SELECT oid, datname FROM pg_database;
  oid  |  datname  
-------+-----------
 13451 | postgres
     1 | template1
 13450 | template0
(3 行)

postgres=#
```

- 新規データベース（mydb）の作成

```console
postgres=# CREATE DATABASE mydb;
CREATE DATABASE
postgres=# 
postgres=# SELECT oid, datname FROM pg_database;
  oid  |  datname  
-------+-----------
 13451 | postgres
 16384 | mydb
     1 | template1
 13450 | template0
(4 行)
```

- 外部からも接続できるようにする為の設定変更

```console
[postgres@rhel8 data]$ vim /var/lib/pgsql/12/data/postgresql.conf
```

```console:変更前
#listen_addresses = 'localhost'
```
```console:変更後
listen_addresses = '*'
```

- pg_hba.confのローカル接続の設定がデフォルトのまま(ident)の場合、localhostを指定すると接続できない

```console
[postgres@rhel8 data]$ psql -h localhost -p 5432 -U postgres -d mydb
psql: エラー: サーバに接続できませんでした: FATAL:  ユーザ"postgres"のIdent認証に失敗しました
[postgres@rhel8 data]$
```

- localhostを指定しないと接続できる

```console
[postgres@rhel8 data]$ psql -p 5432 -d mydb
psql (12.1)
"help"でヘルプを表示します。

mydb=# 
mydb=# \conninfo
データベース"mydb"にユーザ"postgres"として、"/var/run/postgresql"のソケットを介してポート"5432"で接続しています。
mydb=# 
```

# 4. 新規ロールの作成とDB接続設定

- OSユーザ（pgadm01）の作成&プロファイル設定

```
[root@rhel8 data]# useradd pgadm01
[root@rhel8 data]# 
[root@rhel8 data]# vim /home/pgadm01/.bash_profile
```

```console:以下を追加
PGDATA=/var/lib/pgsql/12/data
export PGDATA
```

- 新規role（pgadm01）の作成

```console
[root@rhel8 data]# su - postgres
[postgres@rhel8 ~]$ 
[postgres@rhel8 ~]$ psql 
psql (12.1)
"help"でヘルプを表示します。

postgres=# 
postgres=# CREATE ROLE pgadm01;
CREATE ROLE
postgres=# 
postgres=# \du
                                             ロール一覧
 ロール名 |                                   属性                                   | 所属グループ 
----------+--------------------------------------------------------------------------+--------------
 pgadm01  | ログインできません                                                       | {}
 postgres | スーパユーザ, ロール作成可, DB作成可, レプリケーション可, RLS のバイパス | {}

postgres=# 
postgres=# ALTER ROLE pgadm01 LOGIN;
ALTER ROLE
postgres=# 
postgres=# \du
                                             ロール一覧
 ロール名 |                                   属性                                   | 所属グループ 
----------+--------------------------------------------------------------------------+--------------
 pgadm01  |                                                                          | {}
 postgres | スーパユーザ, ロール作成可, DB作成可, レプリケーション可, RLS のバイパス | {}

postgres=# 
```

`createuserで作成した場合、デフォルトでログイン権限が付与されるが、create roleで作成した場合、デフォルトでログイン権限は付与されない。`

- データベース「mydb」の所有者をposgresからpgadm01に変更

```console
postgres=# ALTER DATABASE mydb OWNER TO pgadm01;
ALTER DATABASE
postgres=# 
postgres=# \l
                                         データベース一覧
   名前    |  所有者  | エンコーディング |  照合順序   | Ctype(変換演算子) |     アクセス権限      
-----------+----------+------------------+-------------+-------------------+-----------------------
 mydb      | pgadm01  | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | 
 postgres  | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | 
 template0 | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | =c/postgres          +
           |          |                  |             |                   | postgres=CTc/postgres
 template1 | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | =c/postgres          +
           |          |                  |             |                   | postgres=CTc/postgres
(4 行)

postgres=#
```

- pgadm01ユーザでmydbに接続

```console
postgres=# \q
[postgres@rhel8 ~]$ 
[postgres@rhel8 ~]$ exit
ログアウト
[root@rhel8 data]# 
[root@rhel8 data]# su - pgadm01
[pgadm01@rhel8 ~]$ 
[pgadm01@rhel8 ~]$ psql -p 5432 -d mydb
psql (12.1)
"help"でヘルプを表示します。

mydb=> 
```

# 5. スキーマ/テーブルの作成

- スキーマ（pgadm01）の作成

```
mydb=> CREATE SCHEMA pgadm01;
CREATE SCHEMA
mydb=>
```

- テーブル（test）の作成

```console
mydb=> CREATE TABLE test (
mydb(> name          varchar(50),
mydb(> subject       varchar(50),
mydb(> score         integer
mydb(> );
CREATE TABLE
mydb=> 
mydb=> \dt;
           リレーション一覧
 スキーマ | 名前 |    型    | 所有者  
----------+------+----------+---------
 pgadm01  | test | テーブル | pgadm01
(1 行)

mydb=>
```

# 6. テーブルのデータ操作の実施（INSERT/SELECT/UPDATE/DELETE）

- データの挿入（INSERT）

```console
mydb=> INSERT INTO test (name, subject, score) VALUES ('student1', 'math', 80);
INSERT 0 1
mydb=> 
mydb=> SELECT * FROM test;
   name   | subject | score 
----------+---------+-------
 student1 | math    |    80
(1 行)

mydb=> 
```

- データの選択（SELECT）

```console
mydb=> SELECT name, subject, score FROM test WHERE name = 'student1';
   name   | subject | score 
----------+---------+-------
 student1 | math    |    80
(1 行)

mydb=>
```

- データの更新（UPDATE）

```console
mydb=> UPDATE test SET score = 95 WHERE name = 'student1';
UPDATE 1
mydb=> 
mydb=> SELECT * FROM test;
   name   | subject | score 
----------+---------+-------
 student1 | math    |    95
(1 行)

mydb=> 
```

- データの削除（DELETE）

```console
mydb=> DELETE FROM test WHERE name = 'student1';
DELETE 1
mydb=> 
mydb=> SELECT * FROM test;
 name | subject | score 
------+---------+-------
(0 行)

mydb=>
```