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

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


# ➀PostgreSQLの導入
サーバーはさくらのVPSのCentOS8です。

まずは下記からOSに応じたリポジトリURLを取得します。
https://yum.postgresql.org/repopackages/
今回はCentOS 8 - x86_64のリンクをコピーします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/638864/4e302567-2733-88b2-936d-928c9174ff24.png)

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

```
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 に接続する](https://neos21.net/blog/2018/12/09-01.html)

これを実行して結果が返ってきたので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`とすると、接続に成功して結果が取得できました！