WindowsからPostgreSQLに繋ぐ
前書き
前回はWindows上のVirtualBoxで入れたUbuntuにPostgreSQLをインストールし、簡単なテーブルを作成・表示させてみました。
今回はそのテーブルをWindowsから見てみようという記事です。
Ubuntu側の設定
PostgreSQLの設定
前回はUbuntu上のpsqlコマンドから同じマシン上のPostgreSQLサーバーに接続してSQLを叩いていました。現在のPostgreSQLの設定はこのタイプの接続しか許可していません。別のマシン上のアクセスを許可する設定をしていきます。
この設定は設定ファイルに書かれているので、ターミナル/sshで接続したコマンドプロンプトから、viコマンドを使ってファイルを編集する必要があります。ただ、viはとても癖のあるエディタで機能も少ないので、それを改良したvimを先にインストールします。操作感は同じですが、ハイライトされるなどの機能が違います。
$ sudo apt install -y vimインストールが終わると、同じviコマンドでも、動きが変わってきます。ではまず、/etc/postgresql/10/main/pg_hba.confを編集して、別のマシンからの接続設定を追加します。
$ sudo vi /etc/postgresql/10/main/pg_hba.confviの操作方法(最小限)
| キー操作 | 意味 |
|---|---|
| ↑ | 上に移動 |
| ↓ | 下に移動 |
| ← | 左に移動 |
| → | 右に移動 |
| i | 入力モードに入り、以降[ESC]で戻るまで打ち込んだ文字を挿入します。 |
| x | カーソル位置の文字を削除します。 |
| dd | 1行削除します。 |
| [ESC] | 入力モードから戻ります。 |
| ZZ | ファイルを保存して終了します。 |
| :q! | ファイルを保存しないで終了します。 |
上を参考にどうにかして、ファイル末尾付近に移動して「ココ追加」の1行を追加してみてください。vimでググるとよりいろいろな操作方法を知る事ができますが、覚えることが多いので今は最小限でいいです。
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
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
# ↓ココ追加
host all all 10.0.2.2/32 md5
# ↑ココ追加
# IPv6 local connections:
host all all ::1/128 md5
# 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
host replication all ::1/128 md5
"/etc/postgresql/10/main/pg_hba.conf" 102L, 4790C 90,10 末尾追加したのは、「(ホストから転送されてきた)ホスト自身からの接続の場合、パスワードで認証する」という設定です。10.0.2.2はVirtualBoxがホストに割り当てている仮想ネットワーク上のアドレスです。編集しているpg_hba.confファイルのフォーマットについての詳細は、
20.1. pg_hba.confファイル (PostgreSQLのマニュアル)
を御覧ください。よく分からないと思いますが、丁寧に説明しているとそれぞれ1つの記事になってしまうので、今回は割愛します。
参考: VirtualBoxのネットワークについて
参考: pg_hba.confファイルの認証設定
次にpostgresql.confを編集し、TCP/IPで待ち受ける(listenする)IPアドレスを追加します(デフォルトではTCP/IPで待ち受けていない＝IPアドレスでの接続を待ち受けていない)。
$ sudo vi /etc/postgresql/10/main/postgresql.confここでも「ココ追加」と書かれている1行を、どうにかして追加してください。
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
#listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# ↓ここ追加
listen_addresses = '*' # what IP address(es) to listen on;
# ↑ここ追加
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
#superuser_reserved_connections = 3 # (change requires restart)
unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
# (change requires restart)
#unix_socket_group = '' # (change requires restart)
#unix_socket_permissions = 0777 # begin with 0 to use octal notation
# (change requires restart)
#bonjour = off # advertise server via Bonjour
<c/postgresql/10/main/postgresql.conf" 664L, 23154C 64,15-10 8%完了すると、Ubuntuが持っているネットワークI/F全てで、ポート5432の接続待ちをする設定が追加されます。後はPostgreSQLサービスを再起動して新しい設定を読み込ませるだけです。
$ sudo service postgresql restartVirtualBoxの設定
sshのときと同様にポートフォワードの設定が必要です。Ubuntuを動かしているVirtualBox上のメニューから、[デバイス]>[ネットワーク]>[ネットワーク設定]を開きます。
[高度]>[ポートフォワーディング]を押します。
上記のようにゲストIPを10.0.2.15でホスト/ゲストポートを5432に設定し、名前を適当につけて[OK]を押します。
すると、ホスト側の5432ポートに来た接続は、ゲスト側のIP10.0.2.15のポート5432に転送されます。この設定がない場合、ファイヤーウォール(NAT)に弾かれて、ホストからゲストにPostgreSQLの標準通信ができません。
Windows側の設定
PostgreSQLクライアントを導入
PostgreSQLクライアントの選定
PostgreSQLの無料クライアントはいくつか種類があります。
最も軽いのはUbuntuで使用したpsql(サーバー同梱)です。これはバッチ実行などをする際にはよく使いますが、コマンドから直に何かしたいとき以外はあまり使いません。
PostgreSQL専用のGUIクライアント兼管理ツールにpgadminがあります。これも管理をする際には使いやすいツールです。
今回は、他のDBでも汎用に使用できるGUIのSQLエディタである、DBeaverをご紹介します。これは元々主にJava開発のIDEとして使用されるeclipseのプラグインとして実装されたものです。日本ではこのような種類ではA-5が有名ですが、世界的には現在このDBeaverが無料汎用SQLエディタでNo.1です。
DBeaverのインストール方法
DBeaverのインストールにはいくつか方法があります。
- インストーラを使用する
- zipを展開してjdkの設定を自分でする
- chocolateyを使用してインストールする
今回はchocolateyを使用して、インストールしますが、いずれの方法でも構いません。
Chocolateyとは
以下のリンク先のソフトです。
Chocolatey Software | Chocolatey – The package manager for Windows
Windows用のコマンドラインベースのパッケージ管理ツールに該当します。Ubuntuのaptのような使い勝手のツールです。便利なので、私も可能なときはchocolateyでアプリをインストールしています。便利な点はいくつかありますが、
- まとめてソフトのアップデートを出来ること
- インストールしたアプリの一覧をエクスポートし、他のPCにもまとめてインストールすることが出来ること
- 共通の依存パッケージを複数入れなくて良くなったりすること
くらいでしょうか…
逆にイケてない点は
- アプリ独自で更新機能を持っているソフトはどっちでアップデートする迷う(DBeaverもそうですが)
- ユーザーの少ないパッケージは作りが適当で、うまくインストール/アンインストールできない場合がある
辺りです。
Chocolateyのインストール
インストール方法はあらゆるケースを網羅した資料が英語だけど↓にあります。
Chocolatey Software | Installation
私は以下の手順でインストールしています。
- Windowsキーを押す
- cmdと打ち込んでCtrl+Shift+[Enter]
- ユーザーアカウント制御(UAC)の問い合わせに[OK]
- 立ち上がった管理者権限のコマンドプロンプトで以下を実行
@"%SystemRoot%\System32\WindowsPowerShell\v1.0\powershell.exe" -NoProfile -InputFormat None -ExecutionPolicy Bypass -Command " [System.Net.ServicePointManager]::SecurityProtocol = 3072; iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))" && SET "PATH=%PATH%;%ALLUSERSPROFILE%\chocolatey\bin"終わるとchocolateyが使えるようになります。コマンドラインが不得意な人は、続けてchoco install chocolateyguiでGUI版もインストールできます。
DBeaverのインストール
管理者権限のコマンドプロンプトから
cinst dbeaver -yするだけでインストールは終了します。
Windowsから接続
DBeaverの起動
初回起動時に出てくるポップアップは全部キャンセルします。一度しか見れないメッセージだと間違えるかもしれないので。
左上の新しい接続ボタンを押します。
PostgreSQLのゾウさんを選択して[次へ]
DatabaseとUserとPasswordを記述して、[テスト接続]
まだドライバがないため、ダウンロードするか聞いてくれるので[ダウンロード]
ダウンロードが完了すると、正しく設定されていれば上のようなテスト接続成功画面が出てきます。
満足して[OK]→[終了]してください。
元の画面に戻ると、データベースナビゲータで選べるデータベースが追加されてます。開いてみてください。
ポチポチ押していくと、前回作成したデータベースやテーブルがグラフィカルに表示されてることが分かります。ここでexample_tableをダブルクリックすると…
何やら分かりやすく整理されてテーブルの定義が表示されています。上部中央のデータタブをクリックすると…
当然のようにExcel表のような画面が表示されています。SQLを覚えてpsqlでコマンド叩かなくてもExcelが使える人なら簡単な編集は出来てしまいます。もちろんSQLも便利に使えるソフトです。でも、これでデータベースも怖くなくなりそうじゃないですか？
まとめ
WindowsのVirtualBox上で動くUbuntuに入れたPostgreSQLサーバーを外部からTCP/IPで接続できるように設定し、WindowsからDBeaverを使ってアクセスできるようになった。
次回は、Ubuntu上でVisual Studio codeをインストールし、pythonの開発環境を整えたいと思います(↓)。