はじめに
前回はCentOSにPostgreSQL-14をインストールしたので、
今回はレプリケーション機能を使ってみます。
前回の記事は コチラ
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 を参考にしてみてね。
問題なさそうだったら、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 を参考にしてね。
pg_basebackup コマンドのオプションは コチラ を参照。
/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ファイル回りとバックアップ・リストア回りも試してみたい。。
ぜひ他の記事も読んでください！