【PostgreSQL】Debian に PostgreSQL をインストールして外部接続する方法

PostgreSQL

概要

2020年の8月からフィヨルドブートキャンプでプログラミング学習しているYusukeです。

今回、Debian に PostgreSQL をインストールして外部接続する方法をご説明いたします。

前提

サーバー：さくらVPS
PC：MacBook Air (11-inch, Early 2015)

手順

【LinuxにPostgreSQLをインストールする】
  1. リポジトリの追加
  2. クライアント証明書をインストール
  3. PostgreSQLをインストール
  4. ユーザー追加


【外部接続を許可する】
  1. postgresql.confを編集
  2. pg_hba.confを編集
  3. 編集後、サービスをリスタート


LinuxにPostgreSQLをインストールする前に

Debianにはバージョンによってそれぞれ下記のようなコードネームがあるのでまずはチェック！
※ちなみにDebianのコードネームはトイ・ストーリーのキャラクターで、Debian1.1だとbuzz（バズ・ライトイヤー）です笑

【コードネーム】

  • Debian10系：buster
  • Debian9系：stretch
  • Debian8系：jessie

【チェック方法】

# cat /etc/debian_version
=> 10.6


【LinuxにPostgreSQLをインストールする】

1. リポジトリの追加

まず、リポジトリのパスを記載するためのファイルをtouchで作成。

# sudo touch /etc/apt/sources.list.d/pgdg.list

次にリポジトリのパスを作成したファイルに記載する。
※下記は"deb http://apt.postgresql.org/pub/repos/apt/ buster-pgdg main"という文字列をechoでファイルに書き込んでいる。

# sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ buster-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

そして、認証キーを追加する。
※buster(コードネーム)の左には半角スペースがあること
※私はDebian10系なのでbuster(コードネーム)と記載

# apt/ buster-pgdg main

2. クライアント証明書をインストール

まず、PostgreSQLの公式リポジトリを信頼するためのキーを取得する。

# sudo apt-get -y install wget ca-certificates

下記は成功画面

Reading package lists... Done
Building dependency tree       
Reading state information... Done
ca-certificates is already the newest version (20190110).
ca-certificates set to manually installed.
wget is already the newest version (1.20.1-1.1).

0 upgraded, 0 newly installed, 0 to remove and 0 not upgraded.

成功したらkeyを自分のPCへ追加する。

# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

3. PostgreSQLをインストール

インストールする前にリポジトリをアップデート!

# sudo apt-get update
# sudo apt-get upgrade

アップデート後にいよいよインストールする。
※最後の数字はバージョン

# sudo apt-get install postgresql-10

インストールされているかPostgreSQLのバージョンを確認。

# psql --version
=> psql (PostgreSQL) 10.15

PostgreSQLをインストールすると、自動的にデータベースの管理ユーザー「postgres」ユーザーが作成されるので、postgresでPostgreSQLにログインできる。

# sudo su - postgres

4. ユーザー追加

まず、先ほど同様にrootでログインする。

# sudo su - postgres

ログイン後にユーザーを作成する。

# createuser --pwprompt --interactive 設定したいユーザー名

パスワードを聞かれるので2回好きなパスワードを入力して「y」を押す。

Enter password for new role: 
Enter it again: 
Shall the new role be a superuser? (y/n)

ユーザーを作成できたので、ログインする。
※Uの後はユーザー名を入力。私のユーザー名はyusukeに設定。

# psql -U yusuke -d postgres -h localhost

パスワードを入力すると無事にログイン完了！

postgres=#



続いて外部(自分のMac)からの接続を許可します。

【外部接続を許可する】

1. postgresql.confファイルを編集

まずは、postgresql.confファイルに入る。
※数字の10はPostgreSQLのバージョン

# vi /etc/postgresql/10/main/postgresql.conf

postgresql.confの59行目付近のlisten_addressesの記載を'localhost'から*に変更
※コメントアウトを外すのを忘れない。(頭の#は消す)

listen_addresses = '*'

2. pg_hba.confファイルを編集

まずは、pg_hba.confファイルに入る。
※数字の10はPostgreSQLのバージョン

# sudo vi /etc/postgresql/10/main/pg_hba.conf

次にpg_hba.confファイルの認証を受け付けるIPの範囲を追記する。
※私はローカルIPアドレスではなく、グローバルIPアドレスでできましたが、ローカルIPアドレスでもできる方はいます。

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             xxx.xxx.xxx.xxx/0       md5 #自分のIPアドレス+/0を追記

★ターミナルでのグローバルIPアドレスの調べ方

# curl inet-ip.info
=> xxx.xxx.xxx.xxx

3. 編集後、サービスをリスタート

ここまで編集できたらリスタートをする。

# /etc/init.d/postgresql restart

自分のMac側で以下のコマンドを実行し、外部接続を試す。

# psql -U ユーザー名 -d postgres -h ホスト名(さくらVPSの)

これで、postgresユーザーのパスワードを入力し、ターミナル画面で下記の画面になれば外部接続完了！

postgres=#


もし下記のエラーが出てしまったら

psql: error: could not connect to server: Connection refused
Is the server running on host "tk2-xxx-xxxxx.vs.sakura.ne.jp" (xxx.xx.xxx.xxx) and accepting
TCP/IP connections on port 5432?

対処法

  • さくらVPSでポート5432をパケットフィルタ設定で下記のように解放する。

参考URL：パケットフィルタ | さくらの VPS ドキュメント

f:id:yskmtg:20201224053650p:plain

以上