概要

postgreSQL14をインストールしてVPS上で起動させ、localで接続するまでの手順を以下にまとめる。

環境

centOS7.9
postgreSQL14
Windows11

手順

※ 今回はcentOS7.9でのセットアップ

1.リポジトリのダウンロード

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

※ nothing to do と出たら特段必要なし

2.　インストール

sudo yum -y install postgresql14 postgresql14-server

3.　CentOS起動時の自動起動設定

sudo systemctl enable postgresql-14

4.　DBの初期化

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

※ 初期化しないと使用できないため必須

5. firewallの設定

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

firewall-cmd --reload

※ PostgreSQLのデフォルトポートは5432のため5432ポートに穴をあける

6.postgreSQLの起動

sudo systemctl start postgresql-14

7. 起動確認

sudo systemctl status postgresql-14.service

※ Active: active (running)となっていれば問題なく起動している

外部ネットワークからPostgreSQLにアクセスする

1. configファイルの修正

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

上記のコマンドを叩いてconfigファイルを開いたら以下のように修正

# 変更前
#listen_addresses = 'localhost'

# 変更後
#listen_addresses = 'localhost'
listen_addresses = '*'

※ postgresql.confがどこにあるかわからないときはfind / -name postgresql.conf 2> /dev/nullを叩いて確認

2. 外部IPアドレスの許可設定

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

pg_hba.configファイルを開いたら以下を挿入する

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
host    all             all             0.0.0.0/0               md5

※ この設定だとセキュリティ上のリスクが高いため、使用する場合は注意。アクセスを制限するIPアドレスを指定することを推奨とのこと。
all を変更し、特定USERやDATABASEのみ、許可することも可能。←この辺は後々やる
以下も変えておく

修正前
# "local" is for Unix domain socket connections only
local   all             all                                     peer

修正後
# "local" is for Unix domain socket connections only
local   all             all                                     trust

peer認証をtrustに変更しておかないとsuperUser以外でログインしたときにpsql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432"のエラーが出る。

3. 接続許可するポート番号を変更

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

上記のコマンドを叩いてconfigファイルを開いたら以下のように修正

# 変更前
#port = 5432

# 変更後　　（port 5433 に変更されます）
port = 5433

(自分は5432で問題ないためここはやってない)

4. PostgreSQLを再起動し、設定変更を反映

sudo systemctl stop postgresql-14.service
sudo systemctl start postgresql-14.service

※ sudo systemctl restart postgresql-14.service だと再起動できないっぽい

ユーザー設定

1. postgresに接続

sudo -u postgres -i

-bash-4.2$ psql -U postgres

2. ユーザの作成

postgres=# CREATE USER {ユーザーネーム} WITH PASSWORD '{パスワード}' CREATEDB;
CREATE ROLE

3. ロールの確認

postgres=# \du

※ 先ほど作成したユーザーが確認できれば問題なし

4. DBの作成

postgres=# CREATE DATABASE {新規DB名} WITH OWNER {先ほど作成したユーザー名};
CREATE DATABASE

※ データベースが関連付いていないと、ログインできない仕様になっているため作成する

localでVPS上のpostgreSQLに接続してみる

※ 今回はWindows環境のセットアップ

1. postgreSQLのダウンロード

https://www.enterprisedb.com/downloads/postgres-postgresql-downloads からダウンロード

2. 環境変数にPATH追加

exeファイルがあるbinまでのパスを指定してユーザー環境変数のPATHに追加

C:\Program Files\PostgreSQL\14\bin

3. コマンドプロンプトでコマンドが叩けるか確認

psql --help

「psql は PostgreSQL の対話型ターミナルです。」等、認識されていないです以外のものが出れば問題なし

4. 外部から接続

psql -h {VPSのIPアドレス}  -U {ユーザー設定で作成したユーザー名} -d {ユーザー設定で作成したDB名}

DB名が出れば完了

testDB=>

戻りたいときは\q

参考