Go to Qiita Advent Calendar 2024 Top
LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@onishi_820(大西 諒介)inウィーベイス株式会社

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

Posted at

はじめに

前回は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ファイル回りとバックアップ・リストア回りも試してみたい。。

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

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?