クラウド上にPostgreSQLサーバを作る方法

インターネット上に自分専用のポスグレ（PostgreSQL）データベースサーバがあると便利だから、作りたい！

そんな時の作り方を紹介します。

今回やること

Linux(Ubuntu)サーバをEC2を使って作成して、PostgreSQLをインストールします。

AWS的にはRDSでPostgreSQLを準備するのが王道ですが、個人の練習用なら無料枠で使えるt2.microインスタンスのEC2に直接インストールした方がコスト削減できます。

Linuxサーバインスタンスを作成してSSH接続
1. EC2インスタンス作成
2. SSHでリモート接続確認
PostgreSQLインストール
1. 接続設定変更
接続確認
1. Linuxマシンから接続確認
2. Windowsマシンから接続確認
トラブルシューティング
1. Cannot connect to server on host ‘IPアドレス’: Connection timed out

Linuxサーバインスタンスを作成してSSH接続

次のものを使いますので、準備します。

使うもの

AWSアカウント（GCP派の方はEC2をGCEに読み替えれば大体同じ感じで・・・）
SSHクライアント（私はTera Term使っています）
A5:SQL Mk-2（最後の接続確認しない場合は不要です）

AWSアカウントをお持ちでない方は、こちらでアカウント作成方法を紹介しています
Tera Termのインストール方法は、こちらの記事の後半で紹介しています
A5:SQL Mk-2の使い方は、こちらの記事をご覧ください

EC2インスタンス作成

まずはAWSのマネジメントコンソールにログインして、EC2インスタンスを立ち上げます。

Linux系のOSならなんでもよいですが、今回はUbuntuを使います。CentOS系を使うよっていう方は一部コマンドが違うので読み替えてください。

インスタンスタイプの選択

インスタンスのタイプは必要なサイズを指定しましょう。

今回は練習なので無料枠内で作れるt2.microにします。

いろいろ細かい設定ができるのですが、今回は割愛して、一気に起動ボタンをポチっとしちゃいます。

SSH接続用のキーペア設定

私はいつも使っているキーペアがあるのでそれを選択します。

まだ作っていない方は新しいキーペアを作成して先に進めます。

インスタンス確認

起動するとインスタンスリストに登場します。今回名前の設定を忘れてしまったようなので適当に命名しておきます。

SSHでリモート接続確認

WindowsパソコンからTeraTermを使って接続してみます。接続先はこのIPアドレスか、またはその下のパブリックIPv4 DNSでもOKです。

TeraTermを起動して接続先やプロトコルを指定します。

今回はEC2のOSをUbuntuにしたので、ユーザ名も「ubuntu」を指定します。認証は先程のキーペアを使いますので、RSA/DSA/ECDSA/ED25519鍵を使うを選択して、秘密鍵ファイルを指定してOKボタンを押すと接続できます。

このように接続できれば第一関門突破です。

PostgreSQLインストール

PostgreSQLをインストールします。ここからはTeraTermでSSH接続して作業します。

ubuntu@ip-172-31-39-181:~$ sudo apt update
ubuntu@ip-172-31-39-181:~$ sudo apt install postgresql postgresql-contrib -y

postgresユーザが作成されますので、スイッチします。

ubuntu@ip-172-31-39-181:~$ sudo su postgres
postgres@ip-172-31-39-181:/home/ubuntu$ whoami
postgres
postgres@ip-172-31-39-181:/home/ubuntu$

psqlコマンドでPostgreSQLに接続します。

\conninfo で接続先データベースとユーザを確認してみます。postgresデータベースにpostgresユーザで接続できているのを確認することができます。

psqlを終了する場合は、\q です。

postgres@ip-172-31-39-181:/home/ubuntu$ psql
psql (12.7 (Ubuntu 12.7-0ubuntu0.20.04.1))
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
postgres=# \q
postgres@ip-172-31-39-181:/home/ubuntu$

接続設定変更

PostgreSQLは初期状態だとローカルホストからしか接続できない設定になっていますので、リモートから接続するには、もうひと手間必要です。

postgresユーザのパスワード設定

psqlでローカル接続して、postgresユーザのパスワードを変更します。

postgres=# ALTER USER postgres PASSWORD 'パスワード';

実際にはこんな感じでセットします。

postgresql.conf の変更

すべてのアクセスを受け付けるように設定を変更します。

ubuntu@ip-172-31-39-181:/etc/postgresql/12/main$ sudo vi postgresql.conf

listen_addresses = 'localhost'         # what IP address(es) to listen on;
↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ 
listen_addresses = '*'                  # what IP address(es) to listen on;

ファイルが見つからない場合

findコマンドで検索してしまいましょう。

ubuntu@ip-172-31-39-181:~$ find / -name 'postgresql.conf' 2>/dev/null
/etc/postgresql/12/main/postgresql.conf

pg_hba.conf の変更

ubuntu@ip-172-31-39-181:/etc/postgresql/12/main$ sudo vi pg_ctl.conf

この部分が、ローカルホストのみ受付になっていますので、接続を許可するIPアドレス範囲をCIDR形式で記載すればOKです。

今回はIP範囲指定なしのフルオープン（0.0.0.0/0）にします。

# IPv4 local connections:
host    all             all             127.0.0.1/32        md5
↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ 
host    all             all             0.0.0.0/0           md5

PostgreSQLを再起動

設定ファイルの編集が終わったら、再起動して設定を有効にします。

ubuntu@ip-172-31-39-181:/etc/postgresql/12/main$ sudo systemctl status postgresql

これでサーバ側の設定は完了です。ちゃんと設定できたかリモート接続して確認してみましょう。

接続確認

Linuxマシンから接続確認

Linuxマシンから接続確認するには、別のマシンからpsqlでリモート接続します。

IPアドレスはさきほど環境構築したサーバのIPアドレス、またはホスト名を指定します。

$ psql -h 172.31.39.181 -U postgres

psqlコマンドでよく使うオプション

オプション	説明
-h	接続先ホストIP、またはホスト名
-p	接続先ポート番号。デフォルトの5432の場合は指定不要
-U	PostgreSQLサーバに接続する際のユーザ名