目標

postgresql 14をwindowsとlinuxにインストールし、外部からのアクセスを可能にする。

動作環境

• postgresql ver 14
• pgadmin 4
• windows 10
• AlmaLinux 9

postgresql

RDB（リレーショナルデータベース）です。データベースの代表はmysqlですが、商用可能、ライセンスが緩いのはpostgresqlなのでこちらを使用しました。
個人的にはmariadbも利用しているのですがpostgresqlの方が難しい気がします。

install

windows

windowsのインストーラーがありますので次へをポチポチすれば問題ないと思います。

1. 1.公式ダウンロードページからダウンロードをしてください。
   

設定が必要そうな部分のみ表示しています

2. 2.Stack Builderは今回使用しませんので外しておきます
3. 3.パスワードを設定します。忘れないようにメモしておいてください
4. 4.ポートとロケールを設定します。今回変更はありません。

起動

インストールが終わったので起動してみます
windowsキーを押して「psql」と入力するとSQL shell（psql）が表示されるので起動してください その後コマンド入力画面が開くので値を入力します。今回のインストールでデフォルトから変更していないため、最後のパスワード以外は「Enter」キーでスキップします。
画像と同じようにpostgres=#と表示されれば、正常な動作、ログインまで完了しています。

Almalinux

almalinux9にpostgresql-14をインストールします。
インストール手順

1. 1.アップデートしておく
2. 2.リポジトリインポート
3. 3.古いモジュールを無効化する
4. 4.インストールを実行する
5. 5.postgresを初期化する

postgresqlをインストールする

基本的には公式通りの手順でインストールを行いました。 最新情報は公式にあるのでそこを確認する。　

アップデート

sudo dnf -y update

リポジトリインポート

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

古いモジュールを無効化

sudo dnf -qy module disable postgresql

postgresql14をインストール

sudo dnf install -y postgresql14-server

データベースを初期化

sudo /usr/pgsql-14/bin/postgresql-14-setup initdb

自動起動

sudo systemctl enable postgresql-14

実行

sudo systemctl start postgresql-14

ログイン

初期化後はpostgresのアカウントが追加されているためこのアカウントでログインをする

sudo -u postgres psql

外部からのアクセス設定

先ほどまで、ローカルからログインまで確認ができました。便利に構築したいため外部からのアクセスを許可させます。
外部アクセスが不要になればpg_hba.confとpostgresql.conf、ファイヤーウォールを元に戻しておいてください。
外部アクセスの許可手順

1. 1.postgresqlのアカウント作成
2. 2.configファイルの編集
3. 3.ファイヤーウォール5432の解放

postgresqlアカウント追加

postgresqlにログインしアカウントを作成します。外部アクセスの場合はパスワードが必要になりますので必ず設定してください。
ユーザー名「userpiyo」とパスワード「hoge」は変更してください

sudo -u postgres psql
create role userpiyo with superuser createdb createrole login password 'hoge'

ちなみにローカルからのアクセスがpeerになっているのでpg_hba.confの部分を変更しないとパスワード設定しているユーザーではログインできません。

local   all             all                                     peer
#変更後↓
# "local" is for Unix domain socket connections only
local   all             all                                     scram-sha-256

configファイル編集

pg_hba.confとpostgresql.confの二つのを修正します。configファイルは「/var/lib/pgsql/14/data」の中に入っています。

アクセスするIPの許可

pg_hba.confを変更します。 どこに記述してもいいのですが分かりやすいところに記述してください。
今回は「# IPv6 local connections:」の下に記述しました。

vi /var/lib/pgsql/14/data/pg_hba.conf

# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
#アクセスを許可するグローバルipまたはローカルipアドレスを設定します(111.111.111.111/32)
host    all             all             111.111.111.111/32         scram-sha-256
#/32は必要です。

アクセスするIPの許可

postgresql.confを変更します。

vi /var/lib/pgsql/14/data/postgresql.conf

コメントアウトを取り除き　アスタリスクに変更してください。

#59行目当たり
listen_addresses = '*'

ファイヤーウォールの解放

ポート5432 tcp を解放します。 permanentを付けることでサーバ再起動しても解放された状態になります。付けないと逆にサーバーの再起動したらポートが閉じてしまいます。せっかくアクセスできたのに再起動したら繋がらなくなった時はこれが原因かも

firewall-cmd --zone=public --add-port=5432/tcp --permanent 

再起動

postgresql14を再起動します。

systemctl restart postgresql-14.service

pgAdmin

最初はコマンド操作はとっつきにくいのでGUIアプリで操作します。アプリはpgAdminを利用します。 windowsの場合は先ほどの手順でpostgresqlをインストールした場合は既にインストールされています。 インストールされてない場合はこちらからダウンロードしてください。

起動

windowsキーを押して「pgadmin」と入力するとpgAdmin4が表示されるので起動します。 初回起動時マスターパスワードの設定を聞かれるので絶対に忘れないようにメモを取って設定を行います。このパスワードを忘れると今後かなり面倒な事になります。また、先ほどまでpostgresqlの設定を行いましたがこのパスワードとは全く関係ありません。ご注意ください。

リモート接続設定

postgresql14のサーバにpgAdminでアクセスするための設定を行います。
サーバーのip,port,データベースのテーブル,ユーザー名、パスワードを入力します

1. 1.右クリックでサーバを追加します。Nameの部分は管理しやすい名前で入力してください。

1. 2.データベースにアクセスするための情報を入力します。
   「serverip ,メインのデータベース　、　ポート　、 ユーザー名 , パスワード」

ログインできない場合

• サーバのポートは解放されていますか？
• ユーザーにパスワードを設定していますか？
• 外部からのアクセス設定を行いpostgresqlの再起動をしましたか

操作してみる

実際にデータベースを作成し、データを追加してみます。

データベースの作成

データベース名を入力します

テーブルの追加

テーブルを追加しテーブル名を入力します

ここで必ずどれかをプライマリーキーに設定してください。 全てのテーブルを表示させます データー行を追加し、データを入力後、サーバに反映させます。

クエリー詳細

クエリーの書き方、追加削除等のコマンドを覚えるために、ここを見ることをおすすめします。

まとめ

postgresqlの構築、操作について、遠隔で操作ができなかったり、pgAdminを使用して直観的にデーターを追加することができました。
コマンドを覚えるのは大変だけどソフトを使えば視覚的に確認ができ、テーブルデータも見やすいので導入して損はないです。