AlmaLinux8にPostgreSQL14をインストールする

# 環境

2021/9/30にPostgreSQL 14.0がリリースされたのでインストールしてみます。

- [PostgreSQL 14 Press Kit](https://www.postgresql.org/about/press/presskit14/ja/)

環境は以下のとおりです。

- AlmaLinux 8(firewalldは無効化しています)
- Postgres 14

これまでCentOSを使っていましたが、最新版のCentOS Linux 8が2021年末で終了するため、代替OSとなるAlmaLinuxに乗り換えています。

インストール手順は以下のPostgreSQL13の場合と若干変わっています。

- [CentOS 7.5にPostgreSQL13をインストールする](https://qiita.com/mkyz08/items/e9316fecd57bff6679cd)


# PostgreSQLのyumリポジトリをインストール

PostgreSQLのパッケージを確認します。
AlmaLinux用のPostgreSQLは13までしか提供されていないようです。

```
# dnf module list postgresql
determining the fastest mirror (10 hosts).. done.  100% [==================================================] 1.1 kB/s | 639  B     00:00 ETA
AlmaLinux 8 - BaseOS                                                                                         3.1 MB/s | 8.6 MB     00:02    
AlmaLinux 8 - AppStream                                                                                      5.4 MB/s | 9.4 MB     00:01    
AlmaLinux 8 - Extras                                                                                          10 kB/s |  10 kB     00:01    
AlmaLinux 8 - AppStream
Name                         Stream                  Profiles                            Summary                                             
postgresql                   9.6                     client, server [d]                  PostgreSQL server and client module                 
postgresql                   10 [d]                  client, server [d]                  PostgreSQL server and client module                 
postgresql                   12                      client, server [d]                  PostgreSQL server and client module                 
postgresql                   13                      client, server [d]                  PostgreSQL server and client module                 

Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalled
```

PostgreSQLのリポジトリパッケージをインストールします。
各OS用のリポジトリパッケージのURL一覧は、以下のサイトに記載されています。

https://yum.postgresql.org/repopackages.php

```
# dnf -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
～省略～
Running transaction
  Preparing        :                                                                                                                     1/1 
  Installing       : pgdg-redhat-repo-42.0-20.noarch                                                                                     1/1 
  Verifying        : pgdg-redhat-repo-42.0-20.noarch                                                                                     1/1 

Installed:
  pgdg-redhat-repo-42.0-20.noarch                                                                                                            

Complete!
```


# PostgreSQLのインストール

PostgreSQLをインストールします。
※拡張機能をコンパイルするときに使用するので「postgresql14-devel」も一緒を入れていますが、必須ではありません。

```
# dnf -qy module disable postgresql
# dnf -y install postgresql14 postgresql14-server postgresql14-contrib postgresql14-devel

～省略～
Installed:
  annobin-9.50-1.el8.x86_64                                                clang-11.0.0-1.module_el8.4.0+2107+39fed697.x86_64                
  clang-devel-11.0.0-1.module_el8.4.0+2107+39fed697.x86_64                 clang-libs-11.0.0-1.module_el8.4.0+2107+39fed697.x86_64           
  clang-tools-extra-11.0.0-1.module_el8.4.0+2107+39fed697.x86_64           cmake-filesystem-3.18.2-11.el8_4.x86_64                           
  compiler-rt-11.0.0-1.module_el8.4.0+2107+39fed697.x86_64                 emacs-filesystem-1:26.1-5.el8.noarch                              
  gcc-8.4.1-1.el8.alma.x86_64                                              gcc-c++-8.4.1-1.el8.alma.x86_64                                   
  glibc-devel-2.28-151.el8.x86_64                                          glibc-headers-2.28-151.el8.x86_64                                 
  kernel-headers-4.18.0-305.19.1.el8_4.x86_64                              libicu-60.3-2.el8_1.x86_64                                        
  libicu-devel-60.3-2.el8_1.x86_64                                         libomp-11.0.0-1.module_el8.4.0+2107+39fed697.x86_64               
  libomp-devel-11.0.0-1.module_el8.4.0+2107+39fed697.x86_64                libstdc++-devel-8.4.1-1.el8.alma.x86_64                           
  libxcrypt-devel-4.1.1-4.el8.x86_64                                       libxslt-1.1.32-6.el8.x86_64                                       
  llvm-11.0.0-2.module_el8.4.0+2107+39fed697.x86_64                        llvm-devel-11.0.0-2.module_el8.4.0+2107+39fed697.x86_64           
  llvm-libs-11.0.0-2.module_el8.4.0+2107+39fed697.x86_64                   llvm-static-11.0.0-2.module_el8.4.0+2107+39fed697.x86_64          
  llvm-test-11.0.0-2.module_el8.4.0+2107+39fed697.x86_64                   lz4-1.8.3-3.el8_4.x86_64                                          
  postgresql14-14.0-1PGDG.rhel8.x86_64                                     postgresql14-contrib-14.0-1PGDG.rhel8.x86_64                      
  postgresql14-devel-14.0-1PGDG.rhel8.x86_64                               postgresql14-libs-14.0-1PGDG.rhel8.x86_64                         
  postgresql14-server-14.0-1PGDG.rhel8.x86_64                              python3-lit-0.11.0-1.module_el8.4.0+2107+39fed697.noarch          
  python3-setuptools-39.2.0-6.el8.noarch                                  

Complete!
```

PostgreSQLは「/usr/pgsql-14/」以下にインストールされます。

```
# ls -l /usr/pgsql-14/
total 20
drwxr-xr-x 2 root root 4096 Oct  4 23:00 bin
drwxr-xr-x 3 root root   23 Oct  4 23:00 doc
drwxr-xr-x 6 root root 4096 Oct  4 23:00 include
drwxr-xr-x 5 root root 4096 Oct  4 23:00 lib
drwxr-xr-x 8 root root 4096 Oct  4 23:00 share
```


# PostgreSQLの自動起動

PostgreSQLを自動起動させるために以下のコマンドを実行します。

```
# systemctl enable postgresql-14.service
```

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

データベースクラスタを作成します。
rootユーザで実行しています。
データベースのファイルはデフォルトで「/var/lib/pgsql/14/data/」に作成されるのですが、これを今回は「/data/」以下に変更しています。
postgresql-14.serviceファイルの「PGDATA」環境変数によって指定できます。

```
# vi /usr/lib/systemd/system/postgresql-14.service

変更前）
Environment=PGDATA=/var/lib/pgsql/14/data/
変更後）
Environment=PGDATA=/data/

# systemctl daemon-reload
```

次にデータベースクラスタを作成するために以下のコマンドを実行します。

```
# PGSETUP_INITDB_OPTIONS="-E UTF8 --no-locale" /usr/pgsql-14/bin/postgresql-14-setup initdb
Initializing database ... OK
```

「/data/」以下に生成されていることを確認します。

```
# cat /data/PG_VERSION 
14
```


# PostgreSQLを起動する

起動前にPostgreSQLのbinディレクトリをパスに追加します。

```
# su - postgres

/var/lib/pgsql/.pgsql_profileにパスを追加します。

# vi /var/lib/pgsql/.pgsql_profile
PATH=/usr/pgsql-14/bin:$PATH
export PATH

PGDATAを修正します。

# vi /var/lib/pgsql/.bash_profile
#PGDATA=/var/lib/pgsql/14/data
PGDATA=/data

# source ~/.bash_profile
```

PostgreSQLの起動は「pg_ctl start」で行います。

```
$ pg_ctl start
waiting for server to start....2021-10-04 23:02:57.238 UTC [8178] LOG:  redirecting log output to logging collector process
2021-10-04 23:02:57.238 UTC [8178] HINT:  Future log output will appear in directory "log".
 done
server started
```
※　"systemctl start postgresql-14"でもOK


# データベース確認

作成したデータベースクラスタを確認してみます。
バージョンとデータベースの一覧を表示してみます。

```
$ psql -V
psql (PostgreSQL) 14.0
$ psql -l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
 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 rows)
```


# ユーザとデータベースの作成

ユーザ（"testuser"）とデータベース（"testdb"）を作成します。

```
$ createuser --login --pwprompt testuser
Enter password for new role: 
Enter it again:

$ createdb --owner=testuser testdb
```


# 外部からの接続を許可する

PostgreSQLはデフォルトではリモート接続できないので、設定を変更します。

```
# vi /data/postgresql.conf
※デフォルトでは「/var/lib/pgsql/14/data/postgresql.conf」

変更前）
#listen_addresses = 'localhost'
変更後）
listen_addresses = '*'
```

次にpg_hba.confを修正します。

```
# vi /data/pg_hba.conf
※デフォルトでは「/var/lib/pgsql/14/data/pg_hba.conf」

# "local" is for Unix domain socket connections only
local   testdb          testuser                                md5
→ローカルからtestdbへtestuserでmd5接続できるように1行追加。
local   all             all                                     peer
# IPv4 local connections:
host    all             all             192.168.10.0/24         md5
→192.168.10.0/24（今回のサーバの環境）から接続できるように1行追加。
host    all             all             127.0.0.1/32            ident
```

設定変更をリロードします。

```
$ pg_ctl reload
※　もしくはsystemctl reload postgresql-14でもいいはず。
```

postgresユーザのパスワードを変更する（変更しなくても良いですが）

```
$ psql
alter role postgres with password 'postgres';
```

リモートから接続を確認します（A5:SQLから接続確認しましたが、説明は省略）

ローカルからの接続確認は以下のように実行します。

```
$ psql testdb testuser

Password for user testuser: 
psql (13.0)
Type "help" for help.

testdb=> 
```

# テーブル作成

テーブルを作成して、データを投入してみます。

```
testdb=> create table test (id int, value text);
CREATE TABLE
testdb=> insert into test (id, value) values (1, 'test text');
INSERT 0 1
testdb=> select * from test;
 id |   value   
----+-----------
  1 | test text
(1 row)
```

「\d」でテーブルの一覧、「\du」でロールの一覧を表示します。

```
testdb=> \d
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | test | table | testuser
(1 row)

testdb=> \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 testuser  |                                                            | {}

```


# その他の設定変更

PostgreSQLは全体的に各種パラメータが小さめの値が設定されているように思えます。
共有バッファだけ128MBから512MB（総メモリの20%～40%ぐらいが良いらしい）に変えてみました。それ以外も変えたほうが良いパラメータはたくさんあるのですが、共有バッファも含めてシステム依存なので今回は変更しません。

共有バッファの変更方法は以下のとおりです。

```
# vi /data/postgresql.conf
※デフォルトでは「/var/lib/pgsql/14/data/postgresql.conf」

変更前）
shared_buffers = 128MB
変更後）
shared_buffers = 512MB
```

あとはPostgreSQLを再起動するだけです。


# ログの設定

ログの設定を以下のように変更しました。

```
$ vi /data/postgresql.conf
```

変更前は以下のとおり。

```
log_filename = 'postgresql-%a.log'
log_rotation_size = 0
#log_min_duration_statement = -1
#log_checkpoints = off
#log_connections = off
#log_disconnections = off
#log_lock_waits = off
```

変更後は以下のとおり。

```
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_size = 1GB
log_min_duration_statement = 500ms
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
```


# pgbenchでベンチマークを実行する

PostgreSQLではベンチマーク用のツールpgbenchが標準で同梱されています。
まずは、pgbenchでベンチマーク用のテーブルとデータを作成します。

```
$ pgbench -i -s 10 testdb
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
1000000 of 1000000 tuples (100%) done (elapsed 0.95 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 1.68 s (drop tables 0.00 s, create tables 0.00 s, client-side generate 0.98 s, vacuum 0.22 s, primary keys 0.47 s).
```

データの準備ができたら、次はベンチマークを実行します。

```
$ pgbench -c 10 -j 10 -t 2000 -N testdb
pgbench (14.0)
starting vacuum...end.
transaction type: <builtin: simple update>
scaling factor: 10
query mode: simple
number of clients: 10
number of threads: 10
number of transactions per client: 2000
number of transactions actually processed: 20000/20000
latency average = 1.778 ms
initial connection time = 10.700 ms
tps = 5625.815743 (without initial connection time)
```

"-c 10"はクライアント数が"10"、"-t 1000"は1クライアント当たりのトランザクション数を表します。

ベンチマーク実行後のキャッシュヒット率は以下のように見ることができます。

```
select relname,
   round(heap_blks_hit * 100 / (heap_blks_hit+heap_blks_read), 2)
   as cache_hit_ratio from pg_statio_user_tables
     where heap_blks_read > 0 order by cache_hit_ratio;

     relname      | cache_hit_ratio 
------------------+-----------------
 test             |           50.00
 pgbench_accounts |           66.00
 pgbench_branches |           82.00
 pgbench_tellers  |           95.00
 pgbench_history  |           99.00
(5 rows)
```

インデックスのキャッシュヒット率は以下のとおり。

```
select relname, indexrelname,
   round(idx_blks_hit * 100 / (idx_blks_hit + idx_blks_read), 2)
   as cache_hit_ratio from pg_statio_user_indexes
     where idx_blks_read > 0 order by cache_hit_ratio;

     relname      |     indexrelname      | cache_hit_ratio 
------------------+-----------------------+-----------------
 pgbench_tellers  | pgbench_tellers_pkey  |            0.00
 pgbench_branches | pgbench_branches_pkey |           50.00
 pgbench_accounts | pgbench_accounts_pkey |           98.00
(3 rows)
```


# 参考

- [PostgreSQL 14 Press Kit](https://www.postgresql.org/about/press/presskit14/ja/)
- [PostgreSQL 14.0 Documentation](https://www.postgresql.org/docs/14/index.html)
- [pgbenchの使いこなし by Let's Postgres](https://lets.postgresql.jp/documents/technical/contrib/pgbench)