今回の流れ
➀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をいじっていきます。
local all all peer
↓
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
適当なファイル作ります。
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を許可します。
#listen_addresses = 'localhost'
#port = 5432
↓
listen_addresses = '*'
port = 5432
続いて、pg_hba.confに下記の1行を追加
host first_db test [接続元アドレス]/32 md5
これでPostgreSQL再起動
sudo systemctl restart postgresql-13
あとはファイアーウォールなどで5432ポートを開放します。
さくらのVPSはブラウザからパケットフィルタリングの設定が変更できるので、そこから5432ポートを開放しました。
これで手元のマシンからnode index.jsとすると、接続に成功して結果が取得できました！