Zenn
🐚

EC2上にPostgreSQL14をインストールしてpgAdmin4と接続するまで

2022/08/26に公開
AWS
PostgreSQL
EC2
tech

はじめに

ほたー　おはようございます! こんにちは!
うおとホタルです。
今回は個人開発でEC2上にPostgreSQLサーバーを作り、pgAdmin4に外部接続しましたので、そのステップをお話しします。
8月26日時点でArduino ESP32を使ったアラームを作っているところです。
9月25日を締め切りとしており、どのような結果になろうとも電子工作から学んだことをZennで公開しようと考えています。
具体的な達成目標は拙ブログ「まどろみみっくろん」に記事を書いております。気になる方は確認をお願い致します。
https://madoromimicron.com/2022/08/18/alarm_making_deadline/

接続までのステップは大きく分けて2つです。

  • EC2上にPostgreSQLをインストールして起動するまで
  • pgAdmin4に接続するまで

前提条件

  • EC2上にインストールするPostgreSQLのバージョンは14とする
  • AMIはAmazon Linux 2とする
  • インスタンスタイプはt3a.nanoとする
  • インパウンドルールに5432ポートと設定したセキュリティグループがEC2に当たっていること
  • EC2インスタンスにRloginからSSH接続できること
  • EC2インスタンスでスワップ領域を作っていること

t3a.nanoを使う理由は、オンデマンド料金がt3.nanoで4.96USD/月、t3a.nanoで4.45USD/月と少し安いことと、個人用で小規模なためです。t3よりt3aの方が安い理由はアベイラリティーゾーンがaで固定されるためです。仮にaで障害が発生したと仮定してもcやdに移動することはできません。
また自分はRloginというターミナルソフトを使ってEC2にSSH接続します。仕事でも使っています。他のターミナルソフトから接続している方はそちらの方法でSSH接続をお願いいたします。

スワップ領域は下記のQiita記事を参考に作成しました。
AmazonLinuxにswapを作成する。

EC2上にPostgreSQL14をインストールして起動するまで

PostgreSQL14インストール

EC2を起動してyum updateコマンドを実行済みという前提で進めます。
まずPostgreSQL14のパッケージがあることを以下のコマンドで確認します。

sudo amazon-linux-extras

赤線で引っ張った箇所にPostgreSQL14のパッケージがありましたのでインストールします。

sudo amazon-linux-extras install -y postgresql14

インストール後に青く光り成功したことが分かりました。再度 amazon-linux-extras コマンドを実行することでも確認できます。

続けてyumコマンドでpostgresql-server、postgresql-develをインストールするとPostgreSQLのインストールが完了します。

$ sudo yum install -y postgresql-server postgresql-devel
$ psql -V
psql (PostgreSQL) 14.3

PostgreSQLのバージョンが表示できていればインストール完了です。
実行後は sudo yum update コマンドを実行して更新します。

インストール出来たらinitdbして初期化を行います。
初期化を行うとPostgreSQLが起動できる状態になるので次のコマンドから起動します。成功したならenableして起動し続けるようにします。

$ sudo postgresql-setup initdb
$ sudo systemctl start postgresql
$ sudo systemctl enable postgresql

ここまで出来たらPostgreSQLのインストール完了です。

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

pgAdmin4に接続するにはデータベースの所有者postgesユーザーのパスワードが必要です。そのため次のコマンドでパスワードを設定します。パスワードに'postgres'と設定していますが、お好きなパスワードで構いません。

$ sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'postgres';"

EC2上にあるpostgresユーザーのパスワードも設定します。

sudo passwd postgres

パスワードを設定できたところで pgAdmin4 を開きます。サーバー設定を行い接続を試した結果、接続できないことがわかりました。理由はEC2上に立てたPostgreSQLはデフォルトではlocalhostからの接続のみ受け入れているためです。この状態では外部接続できないので問題を解決していきます。
ちなみにIPを公開していますが検証後終了させるので問題ありません。

pgAdmin4に接続するまで

外部接続するための設定

まず認証を通すため pg_hba.conf にクライアント認証エントリを追加します。場所が分からない場合はfindコマンドで探します。

$ sudo find / -name pg_hba.conf
/var/lib/pgsql/data/pg_hba.conf

場所が分かったらvimコマンドで開いて以下のように編集して保存します。

# ↓コメントアウトする
#local   all             all                                     peer

# ↓追記
local   all             all                                     trust

# ↓コメントアウトする
#host    all             all             127.0.0.1/32            ident

# ↓追記
host    all             all             192.168.0.0/16          password

下のコマンドを実行することでPostgreSQLを再起動して適用させます。

sudo systemctl restart postgresql

次に postgresql.conf を編集してlocalhostの外に接続できるようにします。場所が分からない場合は先ほどと同様にfindコマンドで探します。

$ sudo find / -name postgresql.conf
/etc/postgresql-setup/upgrade/postgresql.conf
/var/lib/pgsql/data/postgresql.conf
/usr/lib/tmpfiles.d/postgresql.conf

/var/lib/pgsqlの下位にあるパスが正解です。
場所が分かったらvimコマンドで開き、以下を追記して保存します。

listen_addresses = '*'
port = 5432

PostgreSQLを再起動して適用させます。
この状態でpgAdmin4から接続できるか試したところ別のエラー文が表示されました。

画像のエラーメッセージに表示されているIPアドレスがpgAdmin4と接続できるIPアドレスです。
再度pg_hba.confを開いてhostのところに先ほどのIPアドレスを入れて編集して保存します。
PostgreSQLを再起動して適用させたら再度接続します。

よし、成功した!!

データベースのロケール変更

接続してからデータベースを作成すると1点気に食わない箇所が出てきました。
それはロケールが'en_US.UTF-8'であることです。
ロケールが違うと文字列のソート順序に影響があり、想定通りの動作にならなくなります。
英語だけでなく日本語も扱うのでロケールを ja_JP.UTF-8 にしたい。
そこでPostgreSQLにテンプレートを追加します。
EC2上からでしか追加できません。EC2上で以下のコマンドを実行してテンプレートを追加します。

sudo -u postgres psql -c "CREATE DATABASE template_ja_utf8 LC_COLLATE 'ja_JP.UTF-8' LC_CTYPE 'ja_JP.UTF-8' ENCODING 'UTF8' TEMPLATE template0;"

template0を使ってロケールが'ja_JP.UTF-8'のテンプレート template_ja_utf8 を新規作成しました。
PostgreSQLのデータベーステンプレートにはtemplate0とtemplate1があり、両方ともデータベースを作成する際のひな形として使われます。0と1の違いは0はまっさらなデータベースが作成され、1は1の設定を引き継いだデータベースが作成されます[1]。

作成できましたらpgAdmin4でデータベースを作成する際に定義タブからテンプレートにtemplate_ja_utf8を指定して保存ボタンを押します。
SQLタブを見るとロケールが'ja_JP.UTF-8'になったことが確認できました。

以上でEC2上にPostgreSQLをインストールしてpgAdmin4する作業を完了とします。

最後に

9月終わりにアラーム作りに関する記事を書きます。
25日の締め切りまでコワーキングスペースに行くなり自分を追い込んでいきます。
今回4か月ぶりにZennで記事を書き、マークダウン記法で記事を書くってこんな感じだったなと思い出しました。

以上になります。
それでは最後まで読んでいただきありがとうございました。ほた～

参考

AWSでWEBサーバーをたてよう（２）
Amazon Linux2にPostgreSQL11を入れる
PostgreSQLでデフォルトのEncoding・Collate・Ctypeなどを変更したい

脚注

  1. PostgreSQLのtemplate0とtemplate1の違いについて ↩︎

Discussion