【DB】PostgreSQL-14のレプリケーション機能を使ってみた

# はじめに

前回はCentOSにPostgreSQL-14をインストールしたので、
今回はレプリケーション機能を使ってみます。

前回の記事は [コチラ](https://qiita.com/onishi_820/items/cf3aaf7a7111357af0f2)

OS は CentOS でバージョンは以下の通り。
```
[root@db-server-01 ~]# cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)
```

今回は２台のDBサーバで同期を取るので、同じ状態のDBサーバを2台用意しといてください。
筆者は前回の記事の状態のサーバを２台用意してます。

# レプリケーション機能を使ってみた

## DBサーバ#01
まずはDBサーバ#01で作業をしていきます。

### postgresql.conf の修正
まずは postgresql.conf の修正から。
その前に念のためバックアップを作成しておきます。
postgresql.conf はデフォルトだと`/var/lib/pgsql/data/`とかにあるので注意。
※ 意図的に/database/にDBクラスターを作成してます。
```
[root@db-server-01 ~]# cp -p /database/data/postgresql.conf /database/data/postgresql.conf_bk
[root@db-server-01 ~]# ls -al /database/data/postgresql.conf*
-rw------- 1 postgres postgres 28782 Jul 21 03:17 /database/data/postgresql.conf
-rw------- 1 postgres postgres 28782 Jul 21 03:17 /database/data/postgresql.conf_bk
```

では postgresql.conf を修正。

```
[root@db-server-01 ~]# vi /database/data/postgresql.conf]

#60行目
listen_addresses = '*'

#205行目
wal_level = replica

#210行目
synchronous_commit = on

# 298行目
max_wal_senders = 10

#312行目
synchronous_standby_names = '*'
```

一応、表にもしておきます。
| 行数 | 修正前 | 修正後 | 参考 |
|:-:|:-:|:-:|:-:|
| 60  | #listen_addresses = 'localhost'  | listen_addresses = '*'  | https://postgresqlco.nf/doc/ja/param/listen_addresses/ |
| 205  | #wal_level = replica  | wal_level = replica  |https://postgresqlco.nf/doc/ja/param/wal_level/ |
| 210  | #synchronous_commit = on  | synchronous_commit = on  | https://postgresqlco.nf/doc/ja/param/synchronous_commit/ |
| 298  | #max_wal_senders = 10  |  max_wal_senders = 10  | https://postgresqlco.nf/doc/ja/param/max_wal_senders/ |
| 312  | #synchronous_standby_names = ''  | synchronous_standby_names = '*' | https://postgresqlco.nf/doc/ja/param/synchronous_standby_names/ |

diff コマンドで差分確認するとこんな感じ
```
[root@db-server-01 ~]# diff /database/data/postgresql.conf_bk /database/data/postgresql.conf
60c60
< #listen_addresses = 'localhost'               # what IP address(es) to listen on;
---
> listen_addresses = '*'               # what IP address(es) to listen on;
205c205
< #wal_level = replica                  # minimal, replica, or logical
---
> wal_level = replica                   # minimal, replica, or logical
210c210
< #synchronous_commit = on              # synchronization level;
---
> synchronous_commit = on               # synchronization level;
298c298
< #max_wal_senders = 10         # max number of walsender processes
---
> max_wal_senders = 10          # max number of walsender processes
312c312
< #synchronous_standby_names = ''       # standby servers that provide sync rep
---
> synchronous_standby_names = '*'       # standby servers that provide sync rep
```

### pg_hba.conf の修正
続いて、pg_hba.conf を修正します。
こちらもとりあえずバックアップを作成

```
[root@db-server-01 ~]# cp -p /database/data/pg_hba.conf /database/data/pg_hba.conf_bk
[root@db-server-01 ~]# ls -al /database/data/pg_hba.conf*
-rw------- 1 postgres postgres 4789 Jul 21 03:17 /database/data/pg_hba.conf
-rw------- 1 postgres postgres 4789 Jul 21 03:17 /database/data/pg_hba.conf_bk
```

では、修正してきます。
```
[root@db-server-01 ~]# vi /database/data/pg_hba.conf

#最終行に下記を追加（接続許可の設定
host    replication     repl_user       192.168.255.129/32      md5
host    replication     repl_user       192.168.255.130/32      md5
```

`192.168.255.129`は、DBサーバ#01のIPアドレスです。
`192.168.255.130`は、DBサーバ#02のIPアドレスです。
各々のサーバのIPアドレスにして下さい。

`repl_user`というのは、接続を許可するユーザです。
この後作成します。

diff コマンドで差分確認
```
[root@db-server-01 ~]# diff /database/data/pg_hba.conf_bk /database/data/pg_hba.conf
98a99,100
> host    replication     repl_user       192.168.255.129/32      md5
> host    replication     repl_user       192.168.255.130/32      md5
```

では、レプリケーション用のユーザ（ロール）を作成します。
postgresユーザに切り替えて`createuser`コマンドを実行。
```
[root@db-server-01 ~]# su - postgres
[postgres@db-server-01 ~]$ createuser --replication -P repl_user
Enter password for new role:        ## パスワード一回目
Enter it again:                     ## パスワード二回目
```

ここでエラーが発生した人は [connection failed](#connection-failed) を参考にしてみてね。

問題なさそうだったら、postgresql-14 を再起動します。
```
[postgres@db-server-01 ~]$ exit

[root@db-server-01 ~]# systemctl restart postgresql-14
```

DBサーバ#01 の作業は終了です。

## DBサーバ#02

まずは postgresql-14 を停止して、ファイルをすべて削除します。
```
[root@db-server-02 ~]# systemctl stop postgresql-14
[root@db-server-02 ~]# rm -rf /database/data/*
```

アカウントを切り替えて`pg_basebackup`コマンドを実行！
```
[root@db-server-02 ~]# su - postgres
[postgres@db-server-02 ~]$pg_basebackup -R -h 192.168.255.129 -U repl_user -D /database/data -P
Password:
26941/26941 kB (100%), 1/1 tablespace
```

ここでエラーが発生した人は [No route to host](#no-route-to-host) を参考にしてね。

pg_basebackup コマンドのオプションは [コチラ](https://www.postgresql.jp/docs/9.4/app-pgbasebackup.html) を参照。

/database/data/を確認してみる。
```
[postgres@db-server-02 ~]$ls -al /database/data/
total 204
drwx------. 20 postgres postgres   4096 Jul 21 04:41 .
drwxr-xr-x   4 postgres postgres     32 Jul 18 05:10 ..
-rw-------   1 postgres postgres    225 Jul 21 04:41 backup_label
-rw-------   1 postgres postgres 137646 Jul 21 04:41 backup_manifest
drwx------   5 postgres postgres     41 Jul 21 04:41 base
-rw-------   1 postgres postgres     30 Jul 21 04:41 current_logfiles
drwx------   2 postgres postgres   4096 Jul 21 04:41 global
drwx------   2 postgres postgres     32 Jul 21 04:41 log
drwx------   2 postgres postgres      6 Jul 21 04:41 pg_commit_ts
drwx------   2 postgres postgres      6 Jul 21 04:41 pg_dynshmem
-rw-------   1 postgres postgres   4925 Jul 21 04:41 pg_hba.conf
-rw-------   1 postgres postgres   1636 Jul 21 04:41 pg_ident.conf
drwx------   4 postgres postgres     68 Jul 21 04:41 pg_logical
drwx------   4 postgres postgres     36 Jul 21 04:41 pg_multixact
drwx------   2 postgres postgres      6 Jul 21 04:41 pg_notify
drwx------   2 postgres postgres      6 Jul 21 04:41 pg_replslot
drwx------   2 postgres postgres      6 Jul 21 04:41 pg_serial
drwx------   2 postgres postgres      6 Jul 21 04:41 pg_snapshots
drwx------   2 postgres postgres      6 Jul 21 04:41 pg_stat
drwx------   2 postgres postgres      6 Jul 21 04:41 pg_stat_tmp
drwx------   2 postgres postgres      6 Jul 21 04:41 pg_subtrans
drwx------   2 postgres postgres      6 Jul 21 04:41 pg_tblspc
drwx------   2 postgres postgres      6 Jul 21 04:41 pg_twophase
-rw-------   1 postgres postgres      3 Jul 21 04:41 PG_VERSION
drwx------   3 postgres postgres     60 Jul 21 04:41 pg_wal
drwx------   2 postgres postgres     18 Jul 21 04:41 pg_xact
-rw-------   1 postgres postgres    337 Jul 21 04:41 postgresql.auto.conf
-rw-------   1 postgres postgres  28770 Jul 21 04:41 postgresql.conf
-rw-------   1 postgres postgres      0 Jul 21 04:41 standby.signal
```

postgresql-14 を起動します。
```
[postgres@db-server-02 ~]$ exit
[root@db-server-02 ~]# systemctl start postgresql-14
```
## DBサーバ#01

DBサーバ#01 で DB に接続して同期状態を確認してみる。
```
[postgres@db-server-01 ~]$ psql
postgres=# select * from pg_stat_replication;
 pid  | usesysid |  usename  | application_name |   client_addr   | client_hostname | client_port | backend_start | backend_xmin |   state   | sent_lsn  | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
------+----------+-----------+------------------+-----------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------+-------------------------------
 4848 |    16384 | repl_user | walreceiver      | 192.168.255.130 |                 |       59322 | 2023-07-21 04:44:53.702684-07 || streaming | 0/3000060 | 0/3000060 | 0/3000060 | 0/3000060  |           |           |            |             1 | sync       | 2023-07-21 04:45:23.758623-07
(1 row)
```

`sync_state`が`sync`になっていれば OK！！
ちなみに`async`は非同期状態。

`select * from pg_stat_replication;`で何も出力されていない場合は、
同期でも非同期でもない状態です。

## 動作確認
試しにロールを作成してみます。

**DBサーバ#01**
```
postgres=# CREATE ROLE test;
CREATE ROLE
postgres=# DROP ROLE test;
DROP ROLE
```

**DBサーバ#02**
```
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 repl_user | Replication                                                | {}
 test      | Cannot login                                               | {}

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

同期できてそう！！


# エラー対応
自分が遭遇したエラーを記載しておきます。
参考までに。

## connection failed
repl_user を作成しようと createuser コマンドを実行したところ下記のエラー。。。
```
[postgres@db-server-01 ~]$ createuser --replication -P repl_user
Enter password for new role:
Enter it again:
createuser: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?
```

サービスが起動していないことが原因でした。
` Active: inactive (dead) `になっておる。。
```
[root@db-server-01 ~]# systemctl status postgresql-14
● postgresql-14.service - PostgreSQL 14 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since Fri 2023-07-21 03:14:37 PDT; 26min ago
     Docs: https://www.postgresql.org/docs/14/static/
  Process: 1100 ExecStart=/usr/pgsql-14/bin/postmaster -D ${PGDATA} (code=exited, status=0/SUCCESS)
  Process: 1077 ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 1100 (code=exited, status=0/SUCCESS)

Jul 21 03:09:27 db-server-01 systemd[1]: Starting PostgreSQL 14 database server...
Jul 21 03:14:37 db-server-01 systemd[1]: Stopped PostgreSQL 14 database server.
Hint: Some lines were ellipsized, use -l to show in full.
```

ということで起動します。
```
[root@db-server-01 ~]# systemctl start postgresql-14
[root@db-server-01 ~]# systemctl status postgresql-14
● postgresql-14.service - PostgreSQL 14 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2023-07-21 03:57:26 PDT; 7s ago
     Docs: https://www.postgresql.org/docs/14/static/
  Process: 3278 ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 3284 (postmaster)
    Tasks: 8
   CGroup: /system.slice/postgresql-14.service
           tq3284 /usr/pgsql-14/bin/postmaster -D /database/data/
           tq3286 postgres: logger

Jul 21 03:57:26 db-server-01 systemd[1]: Started PostgreSQL 14 database server.
Hint: Some lines were ellipsized, use -l to show in full.
```

再度 createuser を実施してみる。
```
[postgres@db-server-01 ~]$ createuser --replication -P repl_user
Enter password for new role:
Enter it again:

```

あれ・・・？
応答が返ってこない。。
一旦 Ctrl + C を押してDBに接続して、ロールを確認すると作成されてない。
```
[postgres@db-server-01 ~]$ psql
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
```

これの原因としては、`synchronous_standby_names = '*'`のせい。

このパラメータは同期先のスタンバイサーバを設定しているのですが、
現在はどことも同期できておらず、「repl_userを作成した」という情報をスタンバイサーバに送れないので応答が返ってきていないのです。

またやり直しかーと思いきや、実はDBサーバ#01ではロールが作成されてます。

一度 postgresql-14 を再起動してから再度確認すると。。ロールが存在します。
```
[postgres@db-server-01 ~]$ exit
[root@db-server-01 ~]# systemctl restart postgresql-14
[root@db-server-01 ~]# su - postgres
[postgres@db-server-01 ~]$ psql
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 repl_user | Replication                                                | {}
```

とりあえずロール作れたのでOK！

## No route to host

pg_basebackup コマンドを実行したところエラーが発生。。
```
[postgres@db-server-02 ~]$pg_basebackup -R -h 192.168.255.129 -U repl_user -D /database/data -P
pg_basebackup: error: connection to server at "192.168.255.129", port 5432 failed: No route to host
        Is the server running on that host and accepting TCP/IP connections?
```

192.168.255.129（DBサーバ#01）の 5432 ポートに接続できへんぞ！と怒っていそうです。

まずは、192.168.255.129 への通信できるか ping で試してみます。
packet loss が 0% ですし問題なさそう。
```
[root@db-server-02 ~]$ping 192.168.255.129
PING 192.168.255.129 (192.168.255.129) 56(84) bytes of data.
64 bytes from 192.168.255.129: icmp_seq=1 ttl=64 time=0.530 ms
64 bytes from 192.168.255.129: icmp_seq=2 ttl=64 time=0.631 ms
^C
--- 192.168.255.129 ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 2005ms
rtt min/avg/max/mdev = 0.530/0.614/0.683/0.069 ms
```

ということは 5432 ポートが開いてないということなので、firewalld を確認してみる。
```
[root@db-server-02 ~]$systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)
   Active: active (running) since Fri 2023-07-21 03:09:36 PDT; 1h 18min ago
     Docs: man:firewalld(1)
 Main PID: 660 (firewalld)
    Tasks: 2
   CGroup: /system.slice/firewalld.service
           mq660 /usr/bin/python2 -Es /usr/sbin/firewalld --nofork --nopid
```

`Active: active (running)`になっていたのでこの子を停止します。
**DBサーバ#01 と DBサーバ#02 の両方で停止します。**
※今回はDBの学習のため脳死で停止してますが、停止しても問題ないか確認してくださいね・・。

ついでに自動起動もオフに。
```
[root@db-server-02 ~]# systemctl stop firewalld
[root@db-server-02 ~]# systemctl disable firewalld
Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@db-server-02 ~]# systemctl is-enabled firewalld
disabled
```

念のため、SELinuxを確認する。
Disabled になってるし大丈夫そう！
```
[root@db-server-02 ~]# getenforce
Disabled
```

再度 pg_basebackup を実行したところ問題なくいけました。
```
[root@db-server-02 ~]# su - postgres
[postgres@db-server-02 ~]$pg_basebackup -R -h 192.168.255.129 -U repl_user -D /database/data -P
Password:           ## createuser の時に設定した repl_user のパスワードを入力
26941/26941 kB (100%), 1/1 tablespace
```

以上

# さいごに
最後までご覧いただきありがとうございました。

無事にレプリケーション機能を使えたので良かったです。

次はwalファイル回りとバックアップ・リストア回りも試してみたい。。

ぜひ他の記事も読んでください！