PostgreSQL 10とVirtualBoxで、Windows（ホストOS兼クライアント）→CentOS（ゲストOS兼サーバ）データ送信してみた

- 関連記事
    - [CentOS-Linux-7にPostgreSQL 10をインストールしてみた](/items/9204027e4c356830f91d)

#段取り
[PostgreSQLのクライアント/サーバモデルといったシステム構成](https://www.postgresql.jp/document/10/html/tutorial-arch.html)を活用して、VirtualBoxで構築した仮想マシンのゲストOS側のサーバプロセスに、ホストOS側のクライアント（フロントエンド）アプリケーションで接続して、操作することを試みる。謂わば、PostgreSQL版ゲストOS―ホストOS間通信を行う。

先ず、ゲストOSを、外部（ホストOS側のクライアントアプリケーション）からの通信の受け入れが可能な環境にする。
次に、ホストOSに、**SQL Shell**（psql）と**pgAdmin**（GUI版PostgreSQL）というクライアントアプリケーションをインストールして、そこからゲストOS側のサーバプロセスに接続して、操作することを試みる。

#環境
##ゲストOSの環境
```shell-session
$ cat /etc/redhat-release; arch; locale | head -n1; users
CentOS Linux release 7.6.1810 (Core) 
x86_64
LANG=ja_JP.utf8
vagrant vagrant

$ psql --version
psql (PostgreSQL) 10.6
```
##ホストOSの環境
> Windows 7 Home Premium, Service Pack 1, x86-64
> Windows PowerShell 5.1.14409.1018
> Oracle VM VirtualBox 5.2.26
> Vagrant 2.2.0

```"%windir%\system32\cmd.exe"
> ver & echo %PROCESSOR_ARCHITECTURE% & chcp

Microsoft Windows [Version 6.1.7601]
AMD64
現在のコード ページ: 932

> powershell -command "$PSVersionTable.PSVersion.ToString()"
5.1.14409.1018

> vagrant --version
Vagrant 2.2.0
```
#ゲストOS（CentOS）
##外部通信受け入れ環境構築
###ファイアウォール設定（ポートを通す）
```shell-session
$ sudo systemctl start firewalld.service
$ sudo systemctl status firewalld.service
...
$ sudo systemctl enable firewalld.service
...
$ sudo systemctl is-enabled firewalld.service
enabled
$ sudo firewall-cmd --permanent --add-service=postgresql
success
$ sudo firewall-cmd --reload
success
$ sudo firewall-cmd --list-all | grep -o postgresql
postgresql
```
###`postgres`ユーザアカウントにパスワードを設定
```shell-session
$ sudo passwd postgres
```
###`postgres`ユーザアカウントにログイン
```shell-session
$ su - postgres
```
###`postgres`ロールにパスワードを設定
`*******`の部分には[前々項](###`postgres`ユーザアカウントにパスワードを設定)で設定したパスワードと同一のものを入力。

```shell-session
$ psql -c "ALTER ROLE postgres WITH PASSWORD '*******'"
```
※OSの`postgres`ユーザアカウントとPostgreSQLの`postgres`ロールは同名ではあるが異なる概念。
###`$PGDATA/postgresql.conf`を書き換え
```shell-session
$ vim -c 'start|:set number|:59' $PGDATA/postgresql.conf
```
```diff
- #listen_addresses = 'localhost'         # what IP address(es) to listen on;
+ listen_addresses = '*'          # what IP address(es) to listen on;
```
-（59行目）を+に書き換え（先頭の`#`を削除して、値を`*`に）して、[ESC]キー→`:wq`で保存・終了。
###`$PGDATA/pg_hba.conf`を書き換え（追記）
```shell-session
$ ip addr show scope global | grep inet | \
awk '{printf "host    all             all             %-18s      md5\n", $2}' \
>> $PGDATA/pg_hba.conf
```
###データベースサーバを再起動して、設定を反映
```shell-session
$ pg_ctl restart
```
###ポートフォワーディングを設定
1. VirtualBoxウィンドウの**仮想マシン**メニュー→**設定**をクリック。
2. **ネットワーク**ページに切り替え、**高度**を展開して、**ポートフォワーディング**ボタンをクリック。
3. 右上の追加アイコンをクリックし、新しい行の**ホストポート**を`5437`、**ゲストポート**を`5432`と入力して、**OK**ボタンをクリックして、設定を反映する。

#ホストOS（Windows）
**SQL Shell**（psql）と**pgAdmin**（GUI版PostgreSQL）というクライアントアプリケーションから、ゲストOS側のサーバプロセスに接続して、操作する。
##SQL Shell (psql)
###ダウンロードとインストール
1. [PostgreSQLダウンロードページ](https://www.enterprisedb.com/downloads/postgres-postgresql-downloads)から、今回はVersion **10.6**の**Windows x86-64**版をダウンロードする。
2. ダウンロードした`postgresql-10.6-1-windows-x64.exe`を実行し、セットアップウィザードの案内に従う。
3. 2の途中の**Select Components**ページで、**Command Line Tools**と称するクライアントアプリケーションを除く全項目のチェックを外す。**pgAdmin 4**はこの段階では未だインストールしない。<br />
&#x2610; PostgreSQL Server<br />
&#x2610; pgAdmin 4<br />
&#x2610; Stack Builder<br />
&#x2611; Command Line Tools
4. 引き続きセットアップウィザードの案内に従ってインストールを行う。

###スクリプトを作成
1. [Windows]キー+[R]キーを押して**ファイル名を指定して実行**を開き、**名前**欄に`notepad "%USERPROFILE%\Documents\weather.sql"`と入力して、**OK**ボタンをクリック。
2. 「新しく作成しますか？」メッセージが表示されたら、**はい**ボタンをクリック。
3. 開いたメモ帳に下記のスクリプト内容を入力し、[CTRL]キー+[S]キーを押して保存。

```sql:"%USERPROFILE%\Documents\weather.sql"
-- -*- mode: sql; sql-product: postgres; coding: cp932-dos; -*-
DROP TABLE IF EXISTS weather;
-- 2.3. 新しいテーブルの作成
-- https://www.postgresql.jp/document/10/html/tutorial-table.html
CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- 最低気温
    temp_hi         int,           -- 最高気温
    prcp            real,          -- 降水量
    date            date
);
-- 2.4. テーブルに行を挿入
-- https://www.postgresql.jp/document/10/html/tutorial-populate.html
INSERT INTO weather
    VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
    VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
INSERT INTO weather (date, city, temp_hi, temp_lo)
    VALUES ('1994-11-29', 'Hayward', 54, 37);
-- 2.5. テーブルへの問い合わせ
-- https://www.postgresql.jp/document/10/html/tutorial-select.html
SELECT * FROM weather;

```
###設定
1. [Windows]キー+[R]キーを押して**ファイル名を指定して実行**を開き、**名前**欄に`"%ALLUSERSPROFILE%\Microsoft\Windows\Start Menu\Programs\PostgreSQL 10"`と入力して、**OK**ボタンをクリック。
2. 開いたフォルダー内の**SQL Shell (psql)**を右クリックして、メニューから**プロパティ**を開く。
3. **ショートカット**タブの**作業フォルダー**欄に`"%USERPROFILE%\Documents"`と入力して、**OK**ボタンをクリックして、設定を反映。

###SQL Shell (psql) からゲストOS側のサーバプロセスに接続
1. ゲストOSが稼動中であることを確認。
2. **SQL Shell (psql)**を実行。
3. **Server**には何も入力せず[ENTER]キー。
4. **Database**には何も入力せず[ENTER]キー。
5. **Port**には`5437`と入力して[ENTER]キー。
6. **Username**には何も入力せず[ENTER]キー。
7. **Client Encoding**には何も入力せず[ENTER]キー。
8. **Password**に、[設定したパスワード](###`postgres`ユーザアカウントにパスワードを設定)を入力して[ENTER]キー。

```"%ALLUSERSPROFILE%\Microsoft\Windows\Start&nbsp;Menu\Programs\PostgreSQL&nbsp;10\SQL&nbsp;Shell&nbsp;(psql).lnk"
Server [localhost]:
Database [postgres]:
Port [5432]: 5437
Username [postgres]:
Client Encoding [SJIS]:
Password for user postgres:
psql (10.6)
Type "help" for help.

postgres=#
```
###スクリプトを呼び出し、データを新たに書き込む
1. `\i weather.sql`と入力して[ENTER]キー（スクリプトを呼び出す）。
2. `\q`と入力して[ENTER]キー（終了）。

```"%ALLUSERSPROFILE%\Microsoft\Windows\Start&nbsp;Menu\Programs\PostgreSQL&nbsp;10\SQL&nbsp;Shell&nbsp;(psql).lnk"
postgres=# \i weather.sql
psql:weather.sql:2: NOTICE:  table "weather" does not exist, skipping
DROP TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
     city      | temp_lo | temp_hi | prcp |    date
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
 San Francisco |      43 |      57 |    0 | 1994-11-29
 Hayward       |      37 |      54 |      | 1994-11-29
(3 rows)


postgres=# \q
```

###ゲストOSの`psql`でデータが新たに書き込まれたか確認
```shell-session:#postgresユーザアカウントにログイン済みゲストOS#
$ psql -c "SELECT * FROM weather"
     city      | temp_lo | temp_hi | prcp |    date    
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
 San Francisco |      43 |      57 |    0 | 1994-11-29
 Hayward       |      37 |      54 |      | 1994-11-29
(3 行)
```
##pgAdmin（GUI版PostgreSQL）
###ダウンロードとインストール
1. [pgAdminダウンロードページ](https://www.pgadmin.org/download/)から最新版の**pgAdmin**（本記事の投稿時現在は**pgAdmin 4 v4.2**）をダウンロードする。
2. ダウンロードした**pgadmin4-4.2-x86.exe**を実行し、セットアップウィザードの案内に従ってインストールを行う。

###pgAdminを起動
1. ゲストOSが稼動中であることを確認。
2. [Windows]キー+[R]キーを押して**ファイル名を指定して実行**を開き、**名前**欄に`"%ALLUSERSPROFILE%\Microsoft\Windows\Start Menu\Programs\pgAdmin 4\pgAdmin 4 v4.lnk"`と入力して、**OK**ボタンをクリック。

###設定
1. 左ペインの**Servers**を右クリックし、メニューから**Create**→**Server...**をクリック。
2. 下記の通りに設定し、**OK**ボタンをクリック。（`*******`は[設定したパスワード](###`postgres`ユーザアカウントにパスワードを設定)）

- **General**タブ
    - Name: `CentOS7-PG10`
- **Connection**タブ
    - Host: `localhost`
    - Port: `5437`
    - Maintenance database: `postgres`
    - Username: `postgres`
    - Password: `*******`


###ゲストOS側のサーバプロセスとの接続状態を一旦解除して再接続
####切断
1. **CentOS7-PG10**を右クリックし、メニューから**Disconnect Server**をクリック。
2. **OK**ボタンをクリック。

####接続
1. **CentOS7-PG10**を右クリックし、メニューから**Connect Server**をクリック。
2. [設定したパスワード](###`postgres`ユーザアカウントにパスワードを設定)を入力し、**OK**ボタンをクリック。

###データを表示
1. 下記の通りに下の階層を展開して行く。<br />
CentOS7-PG10<br />
└Databases<br />
　└postgres<br />
　　└Schemas<br />
　　　└public<br />
　　　　└Tables<br />
　　　　　└weather<br />
2. **weather**を右クリックし、メニューから**View/Edit Data**→**All Rows**をクリック。
3. 下の表が表示されたか確認。

||city<br /><font color="silver">character varying(80)</font>|temp_lo<br /><font color="silver">integer</font>|temp_hi<br /><font color="silver">integer</font>|prcp<br /><font color="silver">real</font>|date<br /><font color="silver">date</font>|
|:-:|---|--:|--:|--:|---|
|1|San Francisco|46|50|0.25|1994-11-27|
|2|San Francisco|43|57|0|1994-11-29|
|3|Hayward|37|54|[null]|1994-11-29|

#`vagrant ssh`
`vagrant ssh`コマンド経由でゲストOSにアクセスして、そこのサーバプロセスに、**pgAdmin**で接続する。
##Vagrantfileを書き換え
1. **Vagrantfile**があるフォルダーを表示。
2. **Vagrantfile**をメモ帳等を開き、下記の設定を書き加え、[CTRL]キー+[S]キーで保存。

```ruby:.\Vagrantfile
config.vm.network "forwarded_port", guest: 5432, host: 5437
```

##ゲストOSとサーバプロセスを起動
1. ゲストOSが停止中であることを確認。
2. **Vagrantfile**があるフォルダーウィンドウ上部のアドレスバーに`powershell`と入力して[ENTER]キー。
3. 下記の通りコマンド入力。

```shell-session
> vagrant up
...
    default: 5432 (guest) => 5437 (host) (adapter 1)
...
> vagrant ssh
...
$ su - postgres
...
$ pg_ctl restart
...
サーバ起動完了
```
##pgAdminで接続
1. [**pgAdmin**を起動](###pgAdminを起動)。
2. [ゲストOS側のサーバプロセスに接続](###ゲストOS側のサーバプロセスとの接続状態を一旦解除して再接続)。

#参考
- [日本PostgreSQLユーザ会（JPUG）](https://www.postgresql.jp/):
    - [PostgreSQL 10.5文書](https://www.postgresql.jp/document/10/html/): JPUG
- [PostgreSQL 本家](https://www.postgresql.org/)（英語）
    - [PostgreSQL 10.6 Documentation](https://www.postgresql.org/docs/10/): ポスグレ本家（英語）
- [仮想CentOS 7のPostgreSQL 9.5 Alpha 1にホストOSから接続](https://kenpg.bitbucket.io/blog/201507/10.html): kenpg - my research and PostgreSQL