LinuxサーバーにPostgreSQL導入～外部サーバー接続まで

9254 ワード
PostgreSQL DB Node.js Node.js テキストリンク

今回の流れ

➀PostgreSQLの導入
➁ローカルアプリ接続（Node.js）
➂外部サーバーアプリ接続（Node.js）

➀PostgreSQLの導入

サーバーはさくらのVPSのCentOS8です。

まずは下記からOSに応じたリポジトリURLを取得します。
https://yum.postgresql.org/repopackages/
今回はCentOS 8 - x86_64のリンクをコピーします。

続いてサーバーにインストールしていきます。

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql13-server

上から順に、リポジトリの追加、デフォルトのPostgreSQLモジュール停止、PostgreSQLのインストールになります。
なお、オプションについては、q:メッセージ出力しない、y:すべてyes　なので、あってもなくてもいいです。

これで自動的にpostgresユーザができたと思うので、suして環境変数をチェックします。
私の環境では「PGDATA=/var/lib/pgsql/13/data」となっていました。

sudo su - postgres
env | grep PGDATA

続いて、初期データベースの作成

export PGSETUP_INITDB_OPTIONS="--encoding=UTF8 --locale=ja_JP.UTF-8 --lc-collate=ja_JP.UTF-8 --lc-ctype=ja_JP.UTF-8"
/usr/pgsql-13/bin/postgresql-13-setup initdb

データベースの起動
--nowをつけることで、enableとstartが同時にできます。

systemctl enable postgresql-13 --now

postgresユーザでDBに接続、データベース作成、ロール作成

psql
create database first_db
create role test login password 'test';

いったん抜けて、testユーザで接続しようと試みます。

psql -U test -d first_db
psql: error: FATAL:  Peer authentication failed for user "test"

エラーになってしまいました。
なんでもPostgreSQLはPeer認証というものにデフォルトでなっていて、Linuxのユーザ名とPostgreSQLのユーザ名が同じじゃないといけないそうです。
不便なので、そこを変えていきます。
/var/lib/pgsql/13/data/pg_hba.confをいじっていきます。

pg_hba.conf_bk
local   all             all                                     peer

↓

pg_hba.conf
local   all             postgres                                peer
local   all             all                                     md5

peerをmd5に変えています。
これでパスワード認証になります。

ただ、postgresユーザはデフォルトではパスワードが設定されておらず、パスワード認証にすると使えなくなってしまうので、peer認証のままにしてあります。
パスワード設定すればいいんですけどね

これでPostgreSQL再起動して、再度testユーザで接続

sudo systemctl restart postgresql-13
psql -U test -d first_db

パスワードを聞かれるので、設定した"test"を入力して確認OK

これでひとまずPostgreSQL導入完了とします！

➁ローカルアプリ接続（Node.js）

続いてNode.jsで作成したアプリからDBへの接続を試みます。

新しくディレクトリ作って、npm初期化して、pgモジュールをインストールします。

mkdir db_connect
cd $_
npm init -y
npm install pg

適当なファイル作ります。

index.js
const pg = require('pg');

// 接続先文字列
const connectionString = 'postgres://test:test@localhost:5432/first_db';

console.log(`開始 : ${connectionString}`);
const pool = new pg.Pool({
    connectionString: connectionString
});

pool.query('SELECT * FROM user')
.then(result => {
    // 結果データの表示
    if (result.rows) {
        result.rows.forEach((row) => {
            console.log(row);
        });
    }
})
.catch(err => {
    console.log('err: ', err);
})
.then(() => {
    console.log('切断');
    pool.end();
});

この辺はこちらの方の記事を参考に、というかほぼ丸パクリです。
「pg」パッケージを使ってローカルの PostgreSQL や Heroku Postgres に接続する

これを実行して結果が返ってきたのでOKとします。

$ node index.js
開始 : postgres://test:test@localhost:5432/first_db
{ user: 'test' }
切断

➂外部サーバーアプリ接続（Node.js）

最後に手元のローカルマシン（Windows）からLinux上のPostgreSQLに接続してみます。

Node.jsのファイルは➁で使ったものを流用してください。
ただし、接続先文字列だけ変更します。

postgres://test:test@[接続先サーバのアドレス]:5432/first_db

これを実行しただけだと接続エラーになってしまいます。

PostgreSQLはデフォルトではlocalhostからしか接続を受け付けていません。
ですので、この辺の設定をいじっていきます。

まず、リッスン先を*(すべて)にして、ポート5432を許可します。

/var/lib/pgsql/13/data/postgresql.conf_bk
#listen_addresses = 'localhost'
#port = 5432

↓

/var/lib/pgsql/13/data/postgresql.conf
listen_addresses = '*'
port = 5432

続いて、pg_hba.confに下記の1行を追加

/var/lib/pgsql/13/data/pg_hba.conf
host    first_db        test            [接続元アドレス]/32         md5

これでPostgreSQL再起動

sudo systemctl restart postgresql-13

あとはファイアーウォールなどで5432ポートを開放します。

さくらのVPSはブラウザからパケットフィルタリングの設定が変更できるので、そこから5432ポートを開放しました。

これで手元のマシンからnode index.jsとすると、接続に成功して結果が取得できました！