PostgreSQLは「ストリーミングレプリケーション」と呼ばれる構成を取ることで、DBの冗長構成を取ることができる。
ストリーミングレプリケーションの仕組みはこうだ。まず、構成は「マスター」と呼ばれる1台のDB更新・参照が可能なDBと、1台以上の参照のみ可能な「スタンバイ」で構成される。マスターにてDBの更新がされる際に出力される更新ログ「WAL (Write Ahead Log)」をスタンバイに転送し、DBに反映する。これによって、マスターとスタンバイの持つDBの内容が同一となるよう動作する。
今回、実際にPostgreSQLをインストールしてストリーミングレプリケーションを構成してみたので、その手順を記載する。
本記事ではPostgreSQL 10.xの手順を記載している。PostgreSQL 15.x版の手順は以下URLを参照すること。
環境
OSはRHEL 8を利用し、ディストリビューションでパッケージ提供されているPostgreSQLのバージョンをそのまま利用する。
- OS : Red Hat Enterprise Linux 8.2
- PostgreSQL : 10.6
マスターとスタンバイの2台にPostgreSQLをインストールし、ストリーミングレプリケーションを構成する。ホスト名やIPアドレスは以下図を参照いただきたい。
今回はマスターとスタンバイ両方で実施する作業と、片方のみで実施する作業がある。そのため、本記事で記載するプロンプトを以下の通り記載し、作業対象が判別できるようにした。
| プロンプト | 説明 |
|---|---|
| [Master/Standby] | マスターとスタンバイ両方で実施 |
| [Master] | マスターのみ実施 |
| [Standby] | スタンバイのみ実施 |
事前作業として、検証目的なので、firewalldとSELinuxは無効化しておこう。
[Master/Standby]# systemctl stop firewalld
[Master/Standby]# systemctl disable firewalld
[Master/Standby]# sed -ie 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
[Master/Standby]# reboot
ストリーミングレプリケーション構成手順概要
PostgreSQLでストリーミングレプリケーションを構成する手順の概要を以下に図示する。
①PostgreSQLインストール・初期セットアップ
1. dnfでPostgreSQLをインストール
まずは、PostgreSQLをインストールする。postgresql-serverがPostgreSQLのDBエンジン本体となり、依存関係でクライアントツール (posgresqlパッケージ) もインストールされる。それ以外に、各種管理用のスクリプトなどが含まれるpostgresql-contribパッケージをインストールしておく。
[Master/Standby]# dnf install postgresql-server postgresql-contrib -y
～(中略)～
依存関係が解決しました。
================================================================================
パッケージ Arch バージョン リポジトリー サイズ
================================================================================
インストール中:
postgresql-contrib x86_64 10.6-1.module+el8+2469+5ecd5aae dvd-AppStream 805 k
postgresql-server x86_64 10.6-1.module+el8+2469+5ecd5aae dvd-AppStream 5.1 M
依存関係のインストール中:
libpq x86_64 12.1-3.el8 dvd-AppStream 195 k
perl-Carp noarch 1.42-396.el8 dvd-BaseOS 30 k
perl-Exporter noarch 5.72-396.el8 dvd-BaseOS 34 k
perl-libs x86_64 4:5.26.3-416.el8 dvd-BaseOS 1.6 M
postgresql x86_64 10.6-1.module+el8+2469+5ecd5aae dvd-AppStream 1.5 M
uuid x86_64 1.6.2-42.el8 dvd-AppStream 63 k
モジュールストリームの有効化中:
postgresql 10
トランザクションの概要
================================================================================
インストール 8 パッケージ
～(以下略)～
2. postgresユーザーのパスワードを変更
PostgreSQLは原則postgresユーザーにて操作する。パッケージインストール後にpostgresユーザーのパスワードを設定しておこう。
[Master/Standby]# passwd postgres
3. 初期セットアップ
DBの初期データを作成するため、DB初期セットアップコマンドを実行する。/var/lib/pgsql/dataに初期状態のDBが作成される。
[Master/Standby]# postgresql-setup initdb
WARNING: using obsoleted argument syntax, try --help
WARNING: arguments transformed to: postgresql-setup --initdb --unit postgresql
* Initializing database in '/var/lib/pgsql/data'
* Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log
4. DBを起動
一度このタイミングでDBが問題なく起動することを確認しておこう。
[Master/Standby]# systemctl start postgresql
[Master/Standby]# systemctl enable postgresql
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /usr/lib/systemd/system/postgresql.service.
②マスターのDB初期設定
1. マスターにてWALアーカイブログディレクトリ作成
ストリーミングレプリケーションを構成するため、事前にWALアーカイブログを保存するディレクトリを作成する。今回は、/var/lib/pgsql/data/archivedirを保存場所とした。
[Master]$ su - postgres
[Master]$ mkdir /var/lib/pgsql/data/archivedir
[Master]$ chmod 700 /var/lib/pgsql/data/archivedir
[Master]$ ls -ld /var/lib/pgsql/data/archivedir
drwx------ 2 postgres postgres 6 6月 5 17:06 /var/lib/pgsql/data/archivedir
2. マスターにてレプリケーション用のDBユーザーを作成
レプリケーション用のDBユーザーを作成する。今回はdbreplというユーザーを作成した。
[Master]$ psql
postgres=# \du
ロール一覧
ロール名 | 属性 | 所属グループ
----------+------------------------------------------------------------------------------+--------------
postgres | スーパーユーザー, ロール作成可, DB作成可, レプリケーション可, RLS のバイパス | {}
postgres=# create user dbrepl replication login encrypted password 'P@ssw0rd';
CREATE ROLE
postgres=# \du
ロール一覧
ロール名 | 属性 | 所属グループ
----------+------------------------------------------------------------------------------+--------------
dbrepl | レプリケーション可 | {}
postgres | スーパーユーザー, ロール作成可, DB作成可, レプリケーション可, RLS のバイパス | {}
postgres=# \q
3. マスターにて各種設定を実施
次に、マスターにてDBのレプリケーションに必要な各種設定を実施する。設定前に、念のため変更前の設定ファイルをバックアップしておこう。
[Master]$ cp /var/lib/pgsql/data/postgresql.conf ~/postgresql.conf.org
[Master]$ cp /var/lib/pgsql/data/pg_hba.conf ~/pg_hba.conf.org
postgresql.conf
PostgreSQLの各種設定を変更するため、postgresql.confを更新する。以下に設定内容の概要を記載する。
| 設定項目 | 設定内容 |
|---|---|
| listen_addresses | DBが接続を受け付けるIPアドレスを指定する。今回はすべてのインタフェースにて許可するため* (all)を設定する。 |
| wal_level | replicaで指定する。 |
| synchronous_commit | DBに更新があった際に、どこまで更新が反映されたら応答を返すかを設定する。例えば、localの場合はマスターでWALがディスクに記録されたら応答を返す。onの場合はスタンバイに転送されたWALがディスクに記録されたのちに応答を返す。今回は、onで設定する。 |
| archive_mode | スタンバイのレプリケーションが長期間停止した際に、マスター側のWALが削除された場合は、WALアーカイブログを使う可能性があるため、WALアーカイブログを有効にする。ストリーミングレプリケーションの場合はalwaysに設定すればよさそうだ。 |
| archive_command | WALアーカイブログを出力時に、WALをコピーするためのコマンドを指定する。 |
| max_wal_senders | スタンバイから接続を許可する接続数の最大値を設定する。スタンバイ1台の構成であるため、10に設定すれば十分となる。 |
| wal_keep_segments | ストリーミングレプリケーション用に保持するWALのファイル数。今回は決め打ちで30とした。 |
| synchronous_standby_names | スタンバイのリストを記載する。スタンバイを識別する名称は、後述するスタンバイのrecovery.confファイルで設定したapplication_nameとなる。今回はフェイルオーバー時の設定簡素化のため、* (all) で設定する。 |
| hot_standby | ストリーミングレプリケーションを有効にするためonに設定する。 |
| lc_messages | PostgreSQLのエラーメッセージを日本語から英語に変更する場合は、Cで設定する。この変更は任意である。 |
[Master]$ cd $PGDATA
[Master]$ vi postgresql.conf
listen_addresses = '*'
↑★すべてのインタフェースにて接続を受け付ける
wal_level = replica ←★コメントアウトを外す
synchronous_commit = on ←★コメントアウトを外す
archive_mode = always ←★コメントアウトを外しalwaysに設定
archive_command = 'test ! -f /var/lib/pgsql/data/archivedir/%f && cp %p /var/lib/pgsql/data/archivedir/%f'
↑★コメントアウトを外し上記に設定
max_wal_senders = 10 ←★コメントアウトを外す
wal_keep_segments = 30 ←★コメントアウト外し30に設定
synchronous_standby_names = '*' ←★コメントアウトを外し* (all) に設定
hot_standby = on ←★コメントアウトを外す
lc_messages = 'C' ←★エラーメッセージを日本語から英語にするため変更
pg_hba.conf
PostgreSQLにスタンバイからの接続許可を行うため、pg_hba.confに追記する。
[Master]$ cd $PGDATA
[Master]$ vi pg_hba.conf
～(中略)～
host replication dbrepl 192.168.11.116/32 md5
host replication dbrepl 192.168.11.117/32 md5
4. マスターのDB再起動
設定反映するため、DBを再起動する。一部設定はリロードでは反映されないため、必ず再起動を実施しよう。
[Master]$ systemctl restart postgresql
==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ====
'postgresql.service'を再起動するには認証が必要です。
Authenticating as: root
Password:
==== AUTHENTICATION COMPLETE ====
③マスターのDBをスタンバイにコピー(ベースバックアップ取得)
1. マスターのDBをスタンバイにコピー(ベースバックアップ取得)
スタンバイはマスターからベースバックアップを取得することで、初期設定と初期データのコピーを行う。コピーはPostgreSQLのバックアップ機能であるベースバックアップを使用する。
ベースバックアップを取得するため、スタンバイのDBを一度停止する。
[Standby]# systemctl stop postgresql
DB初期化時に作成されたデータをすべて削除してから、pg_basebackupコマンドを用いてマスターのベースバックアップを直接コピーする。
[Standby]# su - postgres
[Standby]$ rm -rf /var/lib/pgsql/data/*
[Standby]$ pg_basebackup -D /var/lib/pgsql/data -h 192.168.11.116 -X stream -c fast -U dbrepl -W
パスワード: ←★DBレプリケーション用のユーザーのパスワードを指定
[Standby]$ echo $?
0 ←★0であることを確認
④スタンバイのDB設定(レプリケーション設定)
1. スタンバイにてレプリケーション設定を実施
ベースバックアップを行うとpostgresql.confやpg_hba.confも一緒にスタンバイへコピーされるため、追加で設定は不要となる。スタンバイ独自で設定が必要なファイルは、recovery.confとなる。
recovery.conf
recovery.confにはPostgreSQLをスタンバイとして動作させるための設定を記載する。以下に設定内容の概要を記載する。
| 設定項目 | 設定内容 |
|---|---|
| standby_mode | スタンバイで動作させるため、onを設定する。 |
| primary_conninfo | application_nameは任意の名前でよいが、今回はスタンバイのホスト名を設定する。user・passwordは前の手順で作成したレプリケーション用DBユーザーを指定する。host・portはマスターのIPアドレスとポート番号 (デフォルト5432) を指定する。 |
| recovery_target_timeline | ストリーミングレプリケーションの場合はlatestを設定する。 |
| restore_command | ストリーミングレプリケーションでWALアーカイブログを用いる場合のコピーコマンドを指定する。今回は、scpコマンドを利用する。scp実行時にパスワードを聞かれないようにするため、後程SSHキーの交換を行っておく。 |
[Standby]$ cd $PGDATA
[Standby]$ vi recovery.conf
standby_mode = 'on'
primary_conninfo = 'application_name=t1117psgl user=dbrepl password=''P@ssw0rd'' host=192.168.11.116 port=5432'
recovery_target_timeline = 'latest'
restore_command = 'scp -o StrictHostKeyChecking=no 192.168.11.116:$PGDATA/archivedir/%f "%p"'
2. マスターとスタンバイのSSHキーを互いに交換
スタンバイからマスターのアーカイブログを取得する際にscpコマンドを使うため、その際にパスワードを聞かれないよう、SSHキーの交換を行っておく。
[Master]$ ssh-keygen
[Master]$ ssh-copy-id 192.168.11.117
[Standby]$ ssh-keygen
[Standby]$ ssh-copy-id 192.168.11.116
3. スタンバイのDBを起動
以上で準備が整ったので、停止していたスタンバイのDBを起動する。
[Standby]$ systemctl start postgresql
==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ====
'postgresql.service'を開始するには認証が必要です。
Authenticating as: root
Password:
==== AUTHENTICATION COMPLETE ====
4. マスターにてレプリケーション状態確認
起動後、マスターにてレプリケーションの状態確認を行う。レプリケーションが開始されると、pg_stat_replicationテーブルにレコードが記載される。もし、なにも表示されない場合は、何らかの理由でレプリケーションが正常にできていないことを意味する。
[Master]$ psql -x -c "SELECT * FROM pg_stat_replication;"
-[ RECORD 1 ]----+------------------------------
pid | 3486
usesysid | 16384
usename | dbrepl
application_name | t1117psgl
client_addr | 192.168.11.117
client_hostname |
client_port | 51458
backend_start | 2022-06-05 17:37:57.698886+09
backend_xmin |
state | streaming
sent_lsn | 0/7000060
write_lsn | 0/7000060
flush_lsn | 0/7000060
replay_lsn | 0/7000060
write_lag |
flush_lag |
replay_lag |
sync_priority | 1
sync_state | sync ←★syncであることを確認
⑤レプリケーション動作確認
1. マスターにてDB及びテーブルを作成
それでは、実際にマスターにて更新した内容がストリーミングレプリケーションによってスタンバイ側に反映されることを確認しよう。
まず、テスト用DBとしてtestdbを作成する。
[Master]$ psql -l
データベース一覧
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権限
-----------+----------+------------------+-------------+-------------------+-----------------------
postgres | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
template0 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 行)
[Master]$ createdb testdb
[Master]$ psql -l
データベース一覧
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権限
-----------+----------+------------------+-------------+-------------------+-----------------------
postgres | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
template0 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
(4 行)
次に、testdbに対してテーブルmytableを作成する。mytableはidとnameの二つの列を持つシンプルなテーブルとしている。
[Master]$ psql -d testdb
testdb=# \dt
リレーションが見つかりませんでした。
testdb=# create table mytable (id integer primary key, name varchar(20));
CREATE TABLE
testdb=# \dt
リレーション一覧
スキーマ | 名前 | 型 | 所有者
----------+---------+----------+----------
public | mytable | テーブル | postgres
(1 行)
testdb=# \q
2. スタンバイにレプリケーションされることを確認
作成したテーブルmytableに対して、レコードを3行登録する。
[Master]$ psql -d testdb -c "insert into mytable (id, name) values (1, 'Ichiro');"
[Master]$ psql -d testdb -c "insert into mytable (id, name) values (2, 'Jiro');"
[Master]$ psql -d testdb -c "insert into mytable (id, name) values (3, 'Saburo');"
[Master]$ psql -d testdb -c "select * from mytable;"
id | name
----+--------
1 | Ichiro
2 | Jiro
3 | Saburo
(3 行)
スタンバイで確認すると、3行のレコードが存在することを確認できる。
[Standby]$ psql -d testdb -c "select * from mytable;"
id | name
----+--------
1 | Ichiro
2 | Jiro
3 | Saburo
(3 行)
なお、スタンバイは参照のみ可能となるため、以下の通りDBの更新を行うとエラーとなり失敗する点に注意しよう。
[Standby]$ psql -d testdb -c "insert into mytable (id, name) values (4, 'Shiro');"
ERROR: cannot execute INSERT in a read-only transaction
以上で、PostgreSQLをインストールしてストリーミングレプリケーションを構成できた。次回は、マスターとスタンバイのDBの役割を切り替えるフェイルオーバーの手順を記載する。
次回記事↓。
- PostgreSQLのDBを手動フェイルオーバーする手順 (ストリーミングレプリケーション構成)
