PostgreSQL インストールと初期設定

2019.10.11 (金)

インストール

2019-10-11現在の最新のPostgresは11系になるのかなと思っていたら、もう12が出てますね。
E.1. Release 12
ここでは最新入れると何かといろいろあるので11の方をインストールします。

古いバージョンの確認、アンインストール

現行でインストールされているものを確認します。

# yum list installed | grep postgresql

9系がインストールされているので、アンインストールします。何も出てこないときはPostgresqlがまだインストールされていないので、remove不要。

postgresql.x86_64                     9.2.24-1.el7_5                 @base      
postgresql-libs.x86_64                9.2.24-1.el7_5                 @base      
postgresql-server.x86_64              9.2.24-1.el7_5                 @base      
postgresql96.x86_64                   9.6.15-1PGDG.rhel7             @pgdg96    
postgresql96-contrib.x86_64           9.6.15-1PGDG.rhel7             @pgdg96    
postgresql96-devel.x86_64             9.6.15-1PGDG.rhel7             @pgdg96    
postgresql96-libs.x86_64              9.6.15-1PGDG.rhel7             @pgdg96    
postgresql96-server.x86_64            9.6.15-1PGDG.rhel7             @pgdg96

一応停止させます。

# systemctl stop postgresql-11.service

一応確認

# ps aux | grep postgres
root     15264  0.0  0.0 112720   968 pts/0    R+   21:36   0:00 grep --color=auto postgres

上記パッケージをアンインストールすればよいと思います。

# yum remove postgresql*

これでOKです。

PostgreSQLのインストール

パッケージの確認

# yum list | grep postgresql11

postgresql11.x86_64                     11.5-1PGDG.rhel7               @pgdg11  
postgresql11-contrib.x86_64             11.5-1PGDG.rhel7               @pgdg11  
postgresql11-libs.x86_64                11.5-1PGDG.rhel7               @pgdg11  
postgresql11-server.x86_64              11.5-1PGDG.rhel7               @pgdg11  
postgresql11-debuginfo.x86_64           11.5-1PGDG.rhel7               pgdg11   
postgresql11-devel.x86_64               11.5-1PGDG.rhel7               pgdg11   
postgresql11-docs.x86_64                11.5-1PGDG.rhel7               pgdg11   
postgresql11-llvmjit.x86_64             11.5-1PGDG.rhel7               pgdg11   
postgresql11-odbc.x86_64                11.01.0000-1PGDG.rhel7         pgdg11   
postgresql11-plperl.x86_64              11.5-1PGDG.rhel7               pgdg11   
postgresql11-plpython.x86_64            11.5-1PGDG.rhel7               pgdg11   
postgresql11-pltcl.x86_64               11.5-1PGDG.rhel7               pgdg11   
postgresql11-tcl.x86_64                 2.4.0-2.rhel7.1                pgdg11   
postgresql11-test.x86_64                11.5-1PGDG.rhel7               pgdg11     

ここらあたりをインストールすればいいと思う。
ここで何も出てこなかったら、次の項でリポジトリをインストールしておきます。

PostgreSQL11をインストールする

10以降はバージョン表記の方法が変わって小数点第二位は単にマイナーバージョンになっようです。9以前は小数点第三位がマイナーバージョンを表していました。ということで、9.5と9.6は11と12と同じぐらいの違いがあるということです。

さてインストールします。
リポジトリのインストールをしておきます。

# cd /usr/local/src
# curl -O https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
# curl -O https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# rpm -Uvh pgdg-redhat-repo-latest.noarch.rpm

リポジトリが取り込めたら、もう一度インストールリストを確認してみます。

# yum list | grep postgresql11

インストールします。

# yum -y install postgresql11-server;
# yum -y install postgresql11-devel;
# yum -y install postgresql11-libs;
# yum -y install postgresql11;
# yum -y install postgresql11-llvmjit;
# yum -y install postgresql11-contrib;
# yum -y install llvm5.0;
# yum -y install llvm5.0-libs;

これでOK。
確認します。インストールされたディレクトリを見てみます。

# ls -al /usr/pgsql-11
合計 24
drwxr-xr-x   6 root root 4096 10月 12 16:19 .
drwxr-xr-x. 15 root root 4096 10月 12 16:19 ..
drwxr-xr-x   2 root root 4096 10月 12 16:19 bin
drwxr-xr-x   3 root root 4096 10月 12 16:19 doc
drwxr-xr-x   3 root root 4096 10月 12 16:19 lib
drwxr-xr-x   8 root root 4096 10月 12 16:19 share

入ってます。
IDを確認します。

# id postgres
uid=26(postgres) gid=26(postgres) groups=26(postgres)

これもOK。
バージョン確認。

# psql --version
psql (PostgreSQL) 11.5

ユーザー確認

# tail /etc/passwd
...
postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash 

ユーザーをスイッチします。

# su - postgres
(ここでスイッチしている筈)

ここまで出来たらインストール完了。

初期設定

ここでは以下の設定をしてゆきます。
root権限相当で操作します。

  • データベースクラスタの作成
  • postgresql.confの設定
  • スーパーユーザの環境変数設定
  • データベースの接続確認
  • ユーザー、パスワードなどの設定(Localhostの接続、remoteの接続)

データベースクラスタの作成

# PGSETUP_INITDB_OPTIONS="-E UTF8 --locale=C" /usr/pgsql-11/bin/postgresql-11-setup initdb
Initializing database ... OK

前回に何らかのエラーでこけた場合に、時々‘/var/lib/pgsql/11/data//log’: File existsというエラーが出て初期化できないことありますが、rm -fR /var/lib/pgsql/11/data/logで削除しちゃえば問題なしです。
確認。

# ls -al /var/lib/pgsql/11/data
合計 124
drwx------ 20 postgres postgres  4096 10月 12 16:44 .
drwx------  4 postgres postgres  4096 10月 12 16:44 ..
-rw-------  1 postgres postgres     3 10月 12 16:44 PG_VERSION
drwx------  5 postgres postgres  4096 10月 12 16:44 base
drwx------  2 postgres postgres  4096 10月 12 16:44 global
drwx------  2 postgres postgres  4096 10月 12 16:44 log
drwx------  2 postgres postgres  4096 10月 12 16:44 pg_commit_ts
drwx------  2 postgres postgres  4096 10月 12 16:44 pg_dynshmem
-rw-------  1 postgres postgres  4269 10月 12 16:44 pg_hba.conf
-rw-------  1 postgres postgres  1636 10月 12 16:44 pg_ident.conf
drwx------  4 postgres postgres  4096 10月 12 16:44 pg_logical
drwx------  4 postgres postgres  4096 10月 12 16:44 pg_multixact
drwx------  2 postgres postgres  4096 10月 12 16:44 pg_notify
drwx------  2 postgres postgres  4096 10月 12 16:44 pg_replslot
drwx------  2 postgres postgres  4096 10月 12 16:44 pg_serial
drwx------  2 postgres postgres  4096 10月 12 16:44 pg_snapshots
drwx------  2 postgres postgres  4096 10月 12 16:44 pg_stat
drwx------  2 postgres postgres  4096 10月 12 16:44 pg_stat_tmp
drwx------  2 postgres postgres  4096 10月 12 16:44 pg_subtrans
drwx------  2 postgres postgres  4096 10月 12 16:44 pg_tblspc
drwx------  2 postgres postgres  4096 10月 12 16:44 pg_twophase
drwx------  3 postgres postgres  4096 10月 12 16:44 pg_wal
drwx------  2 postgres postgres  4096 10月 12 16:44 pg_xact
-rw-------  1 postgres postgres    88 10月 12 16:44 postgresql.auto.conf
-rw-------  1 postgres postgres 23847 10月 12 16:44 postgresql.conf

OKです。
これで最もミニマルな設定は完了したので、起動させてみます。

# systemctl start postgresql-11.service

これで起動シているはずです。statusで確認しましょう。
自動起動の設定をします。

systemctl enable postgresql-11.service

postgresql.confの設定

軽く使う場合は特にここを調整する必要はないです。チューニングが必要な場合は、ここを触るケースが多くなってくると思います。

$ su - postgres
$ cd /var/lib/pgsql/11/data/
$ cp -p postgresql.conf postgresql.conf.org
$ cat /var/lib/pgsql/11/data/postgresql.conf
$ vi postgresql.conf

いろいろ細かい設定が必要な場合は、POstgreSQL Parameters of your systemを参考にして設定してみてください。また詳しい解説はこちらのページでやっております。初動はデフォルトのままでほぼ大丈夫です。

スーパーユーザの環境変数設定

インストールするとpostgresというユーザーが自動的に作成されています。

# tail /etc/passwd
...
postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash
...

習慣的にpostgresをスーパーユーザーとして使うケースが多いので、その慣例に従いいます。
また、環境変数の設定は、通常、設定ファイル ~/.bash_profile で行いますが、postgres ユーザの ~/.bash_profile はパッケージのインストール時に上書きされてしまいます。そのため、~/.bash_profile から設定ファイル ~/.pgsql_profile を読み込むようになっており、環境変数の設定は ~/.pgsql_profile で行います。

# su - postgres
$ vi ~/.pgsql_profile

たぶんパスワードなしでスィッチできると思います。.pgsql_profileがない場合は作成します。

PATH=/usr/pgsql-11/bin:$PATH
MANPATH=/usr/pgsql-11/share/man:$MANPATH
PGDATA=/var/lib/pgsql/12/data
export PATH MANPATH PGDATA

反映

$ source .pgsql_profile

データベースの接続確認

postgresユーザーのまま実行します。

$ psql -l
                                        データベース一覧
   名前    |  所有者  | エンコーディング | 照合順序 | Ctype(変換演算子) |     アクセス権限      
-----------+----------+------------------+----------+-------------------+-----------------------
 postgres  | postgres | UTF8             | C        | C                 | 
 template0 | postgres | UTF8             | C        | C                 | =c/postgres          +
           |          |                  |          |                   | postgres=CTc/postgres
 template1 | postgres | UTF8             | C        | C                 | =c/postgres          +
           |          |                  |          |                   | postgres=CTc/postgres
(3 行)

デフォルトでこの3つのDBが作成されていたら成功です。

一応ログインもしてみる。postgres=#というプロンプトが出て待ち状態になればOK。ログインしてから\lをたたいても同じ結果が出力できます。

$ psql
psql (11.5)
"help" でヘルプを表示します。

postgres=# \l

\qでログアウト。

ユーザー、パスワードなどの設定(Localhostの接続、remoteの接続)

例によってインストールしたての状態ではlocalhostの接続しかできないようになっています。これらの接続ユーザーやリモートホストの設定などをしてゆきます。

  • パスワード暗号化アルゴリズムの設定
  • 接続用ユーザの作成
  • ユーザーのパスワードの作成
  • Databaseの作成
  • スキーマの作成

パスワード暗号化アルゴリズムの設定

PostgreSQL10以降ではデフォルトのパスワードの暗号化アルゴリズムがMD5の他にSCRAM-SHA-256が使えるようになりました。MD5は正直暗号化のアルゴリズムとしては弱すぎ。
古いクライアントマシンがSCRAM-SHA-256に対応していない場合があるので注意が必要ですが、できるだけ最新のものにしておいた方がよいです。

/var/lib/pgsql/11/data/postgresql.conf

を修正します。90行目あたりに、以下のパラメータがあるので修正します。

#password_encryption = md5              # md5 or scram-sha-256
↓
password_encryption = scram-sha-256              # md5 or scram-sha-256

リロードしてから確認します。

$ pg_ctl reload
$ postgres -C password_encryption
scram-sha-256

これでOK。以後sha-256で暗号化されます。

接続用ユーザの作成

接続用のユーザーを作成します。用途によっていろいろですが、ここでは典型的なものを設定します。
postgresがすでに存在しますが、権限が多すぎるのでここでは別のユーザーを別途作成します。
現行のユーザーや権限を確認します。psqlでログインしてから\duで確認します。

$ psql
postgres=# \du
                                              ロール一覧
 ロール名 |                                    属性                                    | 所属グループ 
----------+----------------------------------------------------------------------------+--------------
 postgres | スーパーユーザ, ロール作成可, DB作成可, レプリケーション可, RLS のバイパス | {}

デフォルトではpostgresユーザーのみが存在するので、新しくユーザーを追加します。
testuserを作成します。

postgres=# CREATE ROLE testuser LOGIN;

ロールやグループ、パスワードが設定されていない空のユーザーが作成されます。間違って作成してしまったときは削除すればよいです。DROP ROLE testuser;
続けて、パスワードの設定をします。

postgres=# \password testuser
新しいパスワードを入力してください: (パスワード入力)
もう一度入力してください:  (パスワード入力)

これで設定されます。

パスワードの設定は CREATE ROLE や ALTER ROLE 文でもできますが、パスワードが画面に表示されてしまい、設定によってはログにも出力されてしまうので、\password コマンドを使ったほうがいいでしょう。

ということらしいので\passwordを使いましょう。

データベースの作成

データベースの作成をします。データベースもデフォルトのものを使わないで新しいものを作ります。testuserと紐付ける形になるのでtestdbを作成します。

postgres=# CREATE DATABASE testdb OWNER testuser;
CREATE DATABASE

\lで新しくDBが作成されているか確認してください。

postgres-# \l
                                        データベース一覧
   名前    |  所有者  | エンコーディング | 照合順序 | Ctype(変換演算子) |     アクセス権限      
-----------+----------+------------------+----------+-------------------+-----------------------
 postgres  | postgres | UTF8             | C        | C                 | 
 template0 | postgres | UTF8             | C        | C                 | =c/postgres          +
           |          |                  |          |                   | postgres=CTc/postgres
 template1 | postgres | UTF8             | C        | C                 | =c/postgres          +
           |          |                  |          |                   | postgres=CTc/postgres
 testdb    | testuser | UTF8             | C        | C                 | 
(4 行)

これでOK。
ログアウトしてから、testdbでつなぎ直してみます。

$ psql testdb
testdb=#

testdbのプロンプト待ちになったらOKです。

文字コードの設定

日本語を扱う場合は文字コードの設定をする必要があります。新規の場合はUTF-8を設定しておけば問題ないです。古いアプリケーションに合わせる場合はその他の文字コードが必要になるかもしれませんが、ここでは割愛します。
OSを日本語で設定した場合など親切に最初からデフォルトがUTF-8になっていたりする場合がありますが、デフォルトがLATIN1になっている場合は、文字コードの変更を行いましょう。

スキーマの設定

現行のスキーマの確認は\dn+で確認できます。

postgres=# \dn+
                           スキーマ一覧
  名前  |  所有者  |     アクセス権限     |          説明          
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         | 
(1 行)

デフォルトではpublicというスキーマのみ設定されています。データベースを作成するときは(小規模なプロジェクトなどでは特に必要ないかもしれませんが)スキーマも別途定義しておいた方がよいと思います。
スキーマという言葉はDBの世界では比較的幅の広い言葉ですがPostgreSQLでのスキーマは、名前空間という風にいったん理解しておけばいいと思います。

testというスキーマを作成して、先ほど作成したユーザー(testuser)を所有者にしてみます。

CREATE SCHEMA AUTHORIZATION test;
ALTER SCHEMA test OWNER TO testuser;

確認します。

testdb=# \dn+
                           スキーマ一覧
  名前  |  所有者  |     アクセス権限     |          説明          
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         | 
 test   | testuser |                      | 
(2 行)

testuserが所有者のtestスキーマを作成しました。
ここではスキーマについて詳しくは解説しませんが、データベース作成直後の状態では、データベースにはpublicというスキーマだけが存在する形となります。また、publicスキーマには、どのユーザでもオブジェクトを作成することができます。そして、全てのオブジェクトはデフォルトでpublicスキーマに作成されます。ですので、search_pathパラメータで指定されるスキーマの検索パスにも含まれています。そのため、やろうと思えばpublicスキーマにデフォルトで存在する関数名と同じ名前の関数を作成し、ほかのユーザに実行させたりできてしまいます。本格的な運用に際しては別のスキーマを設定した方がよいです。スキーマの詳しい設定や解説はこちら。
Wordpressのブログや小規模なプロジェクトでは、改めてスキーマの設定をする必要がないかもしれません。デフォルトでも十分動きますしその他の設定でセキュリティーは十分担保できます。

起動・再起動・停止・ステータス

起動

# systemctl start postgresql-11.service

再起動

# systemctl restart postgresql-11.service

停止

# stemctl stop postgresql-11.service

ステータス確認

# stemctl status postgresql-11.service