Ubuntu上でのPostgreSQLの設定

この記事のまとめ：

Ubuntu上でPostgreSQLサーバーを立てる方法についてまとめています。
- 1. PostgreSQLのインストールからデータベースへの初期接続
- 1. データベース、ユーザー、テーブルの新規作成
- 1. データベースへのリモート接続の許可

背景：

データベースを何かしら使えないと不便だったので最近勢いのあるPostgreSQLを導入してみました。

はじめに：本記事での表記について

本記事ではコマンドについて説明する機会が多々ありますが、それぞれどのターミナル上で実行しているかを区別するために文頭の記号ごとにそれぞれ下記を意味しています。

$  → Linuxターミナル上でのコマンド (一般ユーザーでのログイン中)
=# → PostgreSQL上でのコマンド (スーパーユーザーでログイン中)
=> → PostgreSQL上でのコマンド (一般ユーザーでのログイン中)

1. PostgreSQLのインストールからデータベースへの初期接続

PostgreSQLのインストール

まずはPostgreSQLをインストールします。Ubuntuだと下記でインストールできます。

$ sudo apt-get install postgresql

postgresユーザーのパスワード設定（OS上のユーザー）

PostgreSQLをインストールすると自動的にpostgresというユーザーがUbuntu上のユーザーとして作成されます。初期状態ではこのユーザーからのみPostgreSQLのデータベース (具体的に言うと、postgresというデータベースにpostgresというユーザーで)に接続ができます。

Ubuntu上にpostgresユーザーが作成されているかどうかの確認します。下記のコマンドを入力すると次のように表示されればpostgresユーザーが作成されているということです。

$ finger postgres
 
Login: postgres                         Name: PostgreSQL administrator
Directory: /var/lib/postgresql          Shell: /bin/bash
Never logged in.
No mail.
No Plan.

postgresユーザーにスイッチ(ログイン)するためにはパスワードの設定が必要です。下記のコマンドでパスワードの設定ができます。

$ sudo passwd postgres

postgresユーザーにスイッチします。

$ su - postgres

これでUbuntuにpostgresユーザーとしてログインした状態になっているはずですのでようやくPostgreSQLのデータベースに初めてログインできるようになり初期設定ができます。

初期状態でのデータベースへの接続

前節までの手続きを済ませ、Ubuntuにpostgresユーザーでログインしている状態で下記コマンドを入力することでpostgresデータベースにpostgresユーザーとしてログインできます。

$ psql

以降の手続きはPostgreSQLのコマンドを使ってデータベースの作成やユーザーの作成を行うことで、postgresユーザーにスイッチする必要がなくなくなります。

2. データベース、ユーザー、テーブルの新規作成

ユーザー（ロール）の追加

新規ユーザーの作成には、スーパーユーザー権限を持ったユーザー (e.g. postgres)でデータベースに接続している必要があります。スーパーユーザー権限のあるユーザーで接続したのちに下記のコマンドで新しい新規ユーザーを作成できます。

=# create role [ユーザー名] with login password '[パスワード]'

(参考) ユーザーのパスワード変更

スーパーユーザー権限のあるユーザーでログインをして、下記のコマンドでパスワードの設定を行います。

=# alter role [ユーザー名] with password '[パスワード]';

データベースの新規作成

データベースの作成には、スーパーユーザー権限を持ったユーザーでデータベースに接続している必要があります。権限のあるユーザーで接続したのちに下記のコマンドで新しいデータベースを作成できます。

=# create database [データベース名] owner [ユーザー名];

(参考) データベースのオーナー変更

データベースのオーナーを変更したい場合には下記のコマンドで変更ができます。

=# alter database [データベース名] owner to [ユーザー名];

(参考) データベース名の変更

作成したデータベース名を変更する場合には下記のコマンドで変更ができます。

=# alter database [現在のデータベース名] rename to [新しいデータベース名]

データベース上にテーブルの新規作成

テーブルとは実際にSQLを使ってデータを登録していくものです。テーブルはデータベース内に格納されており、一つのデータベースに複数のテーブルを持つことができます。

まずはテーブルを新規作成したいデータベースに接続します。接続手順はすでに上述した通りです。

下記のコマンドで現在接続しているデータベースに新しいテーブルを作成することができます。

=> create table [テーブル名]
   ([カラム名] [データ型], [カラム名] [データ型]);

接続しているデータベースとは異なるデータベースにテーブルを新規作成することもできます。その際は下記のようにテーブル名の前にデータベース名を記載します。

=> create table [データベース名].[テーブル名]
   ([カラム名] [データ型], [カラム名] [データ型]);

カラムを記載する際に、データ型の後ろにprimary keyと記載することで、テーブル内で重複しない一意な値である制約を付けることができます。

参考

データ型 (https://www.postgresql.jp/document/9.6/html/datatype.html)
日付/時刻型データ型 (https://www.postgresql.jp/document/9.4/html/datatype-datetime.html)

テーブルにカラム (列)の追加

すでに作成してあるテーブルに新たにカラムを追加する場合には、下記のコマンドを使います。

=> alter table [テーブル名] add [カラム名] [データ型];

カラム名の変更

すでに作成してあるカラムのカラム名を変更する場合には、下記のコマンドを使います。

=> alter table [テーブル名] rename column [現在のカラム名] to [新しいカラム名];

カラムのデータ型の変更

すでに作成してあるカラムのデータ型を変更する場合には、下記のコマンドを使います。

=> alter table [テーブル名] alter [カラム名] type [データ型];

3. データベースへのリモート接続の許可

上記までで基本的な準備は終わっていますのでローカルでデータベースを操作する分には問題がありません。ここからはネットワーク上の異なる端末からデータベースにリモートで接続するための設定を行っていきます。

リモート接続する際にはユーザー認証が必要ですが、そのユーザーにパスワードが設定されていないとリモート接続はできません。もし、パスワード設定が済んでいない場合は、上述の方法で設定してください。

リモート接続の許可

初期設定ではローカルからの接続しか許可していませんので、すべてのアドレスからの接続を許可するようにPostgreSQL設定ファイル(/etc/postgresql/9.5/main/postgresql.conf)を変更します。

#listen_addresses = 'local' # 変更前
listen_addresses = '*'      # 変更後

ユーザー (ロール)の認証方法の追加

こちらも初期設定では、ローカルからの接続しか許可されていないようになっているので、リモートからスーパーユーザー(postgres)の接続は拒否し、その他のユーザーの接続はMD5での接続を許可するようにクライアントの認証設定ファイル(/etc/postgresql/9.5/main/pg_hba.conf)に下記を追加します。

# TYPE  DATABASE        USER            ADDRESS                 METHOD#
# Reject connections as a super user on the remote
host    all             postgres        0.0.0.0/0               reject
# TCP connections by md5
host    all             all             0.0.0.0/0               md5

また、ローカルからもピア接続しか許可されていませんのでDBへの接続ユーザーとUbuntuのユーザーアカウントが異なる場合に接続できません。なので、MD5のパスワード認証に変更します。

# TYPE  DATABASE        USER            ADDRESS                 METHOD
#local  all             all                                     peer    #変更前
local   all             all                                     md5     #変更後

これらの設定が終了したら、PostgreSQLを再起動します。

$ sudo /etc/init.d/postgresql restart

リモート接続コマンド

ここまで準備ができれば、リモート接続ができるようになっているはずです。下記のコマンドで接続します。

$ psql -h [ホスト名 or IPアドレス] -U [ユーザー名] -d [DB名]

参考

マニュアル

日本語で最も詳しく書いてあるマニュアルはこちら。 https://www.postgresql.jp/document/9.6/html/index.html コマンド一覧はこちら。文法がある程度わかってきたらこれで探した方が早いです。 https://www.postgresql.jp/document/9.6/html/reference.html

記事作成に当たって参考にしたサイト

今回は以上です。最後まで読んでいただき、ありがとうございます。

　← 気に入っていただければ応援ポチをお願いします！

Search This Blog