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

• 関連記事
  • CentOS-Linux-7にPostgreSQL 10をインストールしてみた

段取り

PostgreSQLのクライアント/サーバモデルといったシステム構成を活用して、VirtualBoxで構築した仮想マシンのゲストOS側のサーバプロセスに、ホストOS側のクライアント（フロントエンド）アプリケーションで接続して、操作することを試みる。謂わば、PostgreSQL版ゲストOS―ホストOS間通信を行う。

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

環境

ゲストOSの環境

$ 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）

外部通信受け入れ環境構築

ファイアウォール設定（ポートを通す）

$ 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ユーザアカウントにパスワードを設定

$ sudo passwd postgres

postgresユーザアカウントにログイン

$ su - postgres

postgresロールにパスワードを設定

*******の部分には前々項で設定したパスワードと同一のものを入力。

$ psql -c "ALTER ROLE postgres WITH PASSWORD '*******'"

※OSのpostgresユーザアカウントとPostgreSQLのpostgresロールは同名ではあるが異なる概念。

$PGDATA/postgresql.confを書き換え

$ vim -c 'start|:set number|:59' $PGDATA/postgresql.conf

- #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を書き換え（追記）

$ ip addr show scope global | grep inet | \
awk '{printf "host    all             all             %-18s      md5\n", $2}' \
>> $PGDATA/pg_hba.conf

データベースサーバを再起動して、設定を反映

$ pg_ctl restart

ポートフォワーディングを設定

1. VirtualBoxウィンドウの仮想マシンメニュー→設定をクリック。
2. ネットワークページに切り替え、高度を展開して、ポートフォワーディングボタンをクリック。
3. 右上の追加アイコンをクリックし、新しい行のホストポートを5437、ゲストポートを5432と入力して、OKボタンをクリックして、設定を反映する。

ホストOS（Windows）

SQL Shell（psql）とpgAdmin（GUI版PostgreSQL）というクライアントアプリケーションから、ゲストOS側のサーバプロセスに接続して、操作する。

SQL Shell (psql)

ダウンロードとインストール

1. PostgreSQLダウンロードページから、今回はVersion 10.6のWindows x86-64版をダウンロードする。
2. ダウンロードしたpostgresql-10.6-1-windows-x64.exeを実行し、セットアップウィザードの案内に従う。
3. 2の途中のSelect Componentsページで、Command Line Toolsと称するクライアントアプリケーションを除く全項目のチェックを外す。pgAdmin 4はこの段階では未だインストールしない。
   ☐ PostgreSQL Server
   ☐ pgAdmin 4
   ☐ Stack Builder
   ☑ Command Line Tools
4. 引き続きセットアップウィザードの案内に従ってインストールを行う。

スクリプトを作成

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

"%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に、設定したパスワードを入力して[ENTER]キー。

"%ALLUSERSPROFILE%\Microsoft\Windows\Start Menu\Programs\PostgreSQL 10\SQL Shell (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 Menu\Programs\PostgreSQL 10\SQL Shell (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でデータが新たに書き込まれたか確認

#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ダウンロードページから最新版の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ボタンをクリック。（*******は設定したパスワード）

• 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. 設定したパスワードを入力し、OKボタンをクリック。

データを表示

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

	city character varying(80)	temp_lo integer	temp_hi integer	prcp real	date date
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]キーで保存。

.\Vagrantfile

config.vm.network "forwarded_port", guest: 5432, host: 5437

ゲストOSとサーバプロセスを起動

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

> vagrant up
...
    default: 5432 (guest) => 5437 (host) (adapter 1)
...
> vagrant ssh
...
$ su - postgres
...
$ pg_ctl restart
...
サーバ起動完了

pgAdminで接続

1. pgAdminを起動。
2. ゲストOS側のサーバプロセスに接続。

参考

• 日本PostgreSQLユーザ会（JPUG）:
  • PostgreSQL 10.5文書: JPUG
• PostgreSQL 本家（英語）
  • PostgreSQL 10.6 Documentation: ポスグレ本家（英語）
• 仮想CentOS 7のPostgreSQL 9.5 Alpha 1にホストOSから接続: kenpg - my research and PostgreSQL