postgresql スタンバイサーバ の非同期レプリケーション

環境

postgresql 12.9
Docker 20.10.10

Dockerを使用し、2つのデータベースサーバを起動
ファイル単位のログシッピング、ストリーミングレプリケーションを分けて実行してみる

マスタサーバ

IPアドレス: 172.10.1.2

スタンバイサーバ

IPアドレス: 172.10.1.3

マスタサーバの起動・接続

# ボリューム作成
$ docker volume create master_db # マスタDB保存先
$ docker volume create standby_db # スタンバイDB保存先
$ docker volume create archive_wal # WALアーカイブ先

# ネットワーク作成
$ docker network create --subnet=172.10.1.0/24 sample_nw

# マスタDB コンテナ起動
$ docker run --net=sample_nw --ip=172.10.1.2 -e POSTGRES_PASSWORD=xxxxxxxx -v archive_wal:/archive_wal -v master_db:/var/lib/postgresql/data -d postgres:12.9-alpine

テストデータの追加

-- usersテーブル作成
create table users(
    id serial not null,
    name character varying
);

-- データ追加
insert into users(name) values ('A'),('B'),('C');

-- データ3件確認
postgres=# select * from users;
 id | name 
----+------
  1 | A
  2 | B
  3 | C

レプリケーションユーザ、認証情報の追加

-- ユーザ作成
postgres=# CREATE ROLE repl_user LOGIN REPLICATION RASSWORD 'xxxxxxxx';

-- 作成確認
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 repl_user | Replication                                                | {}
  • pg_hba.conf に認証情報の追加。スタンバイサーバからの接続を許可する
$ vi /var/lib/postgresql/data/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    replication     repl_user       172.10.1.3/32           md5     # 1行追加

ファイル単位のログシッピング

  • アーカイブされたWALを解読し、マスタサーバと同期を行う
  • アーカイブ先は、スタンバイサーバからアクセス出来るディスクに保存する必要がある
  • WALがアーカイブされるタイミングで同期されるため、遅延が発生する

マスタサーバ設定

## postgresql.conf

wal_level = replica # (デフォルト) 
archive_mode = on # WALアーカイブ有効化
archive_command = 'test ! -f /archive_wal/%f && cp %p /archive_wal/%f'  # アーカイブ時に実行するコマンド

スタンバイサーバを一時起動し設定

  • WALがアーカイブされているディレクトリにアクセスできるよう、archive_walを共有してマウントする
  • データベースを起動する前に、バックアップの取得や設定を行う必要があるため、bashコマンドで起動しターミナルへ接続する
$ docker run --net=sample_nw --ip=172.10.1.3 -e POSTGRES_PASSWORD=xxxxxxxx -v archive_wal:/archive_wal -v standby_db:/var/lib/postgresql/data -it postgres:12.9-alpine bash
  • pg_basebackupコマンドを使用し、マスタサーバよりバックアップ取得
# postgresユーザに切り替え
$ su postgres
# バックアップ取得
$ pg_basebackup -h 172.10.1.2 -p 5432 -U repl_user -D /var/lib/postgresql/data
  • スタンバイモードとして起動するため、standby.signalファイルを作成する
$ touch /var/lib/postgresql/data/standby.signal
  • アーカイブされたWALをコピーするため、restore_commandを設定する
## postgresql.conf

restore_command = 'cp /archive_wal/%f "%p"' # WALをアーカイブ先よりコピーする

スタンバイサーバの本起動

  • 一度コンテナを削除し、コマンドを指定せずに再度起動を行う
# コンテナID確認
$ docker ps
$ docker rm [コンテナID]

# スタンバイサーバ起動
$ docker run --net=sample_nw --ip=172.10.1.3 -e POSTGRES_PASSWORD=password -v archive_wal:/archive_wal -v standby_db:/var/lib/postgresql/data -d postgres:12.9-alpine
  • 最初に書き込んだデータが書き込まれているか確認
postgres=# select * from users;
 id | name 
----+------
  1 | A
  2 | B
  3 | C

新規データを追加しスタンバイサーバで確認

  • マスタサーバ 新規で1件レコード追加
postgres=# insert into users(name) values('D');
  • walがアーカイブされていないため、スタンバイサーバではまだ追加されていない
-- スタンバイサーバ
postgres=# select * from users;
 id | name 
----+------
  1 | A
  2 | B
  3 | C
postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/A000308
  • スタンバイサーバで新規データが追加されていることを確認
postgres=# select * from users;
 id | name 
----+------
  1 | A
  2 | B
  3 | C
  4 | D  # 追加されている
  • restore_commandが常時実行されているため、WALがアーカイブされていない時は、失敗ログが出力されていた
cp: can't stat '/archive_wal/00000001000000000000000B': No such file or directory
cp: can't stat '/archive_wal/00000002.history': No such file or directory

ストリーミングレプリケーション

  • ログシッピングと違い、WALの変更分をすぐにスタンバイへ適用する。そのため、ログシッピングより最新の状態で同期出来る。

スタンバイサーバを一時起動し設定

  • ログシッピングと同様に、設定のためbashコマンドで起動しターミナルへ接続する
$ docker run --net=sample_nw --ip=172.10.1.3 -e POSTGRES_PASSWORD=password -v standby_db:/var/lib/postgresql/data -it postgres:12.9-alpine bash
  • pg_basebackupコマンドを使用し、マスタサーバよりバックアップ取得
  • --write-recovery-confオプションで、standby.signalファイルの生成や、postgresql.auto.confファイルを生成し、ストリーミングレプリケーションの接続設定を同時に行える
# postgresユーザに切り替え
$ su postgres
# バックアップ取得
$ pg_basebackup -h 172.10.1.2 -p 5432 -U repl_user -D /var/lib/postgresql/data --write-recovery-conf

# postgresql.auto.conf ファイル確認
$ cat /var/lib/postgresql/data/postgresql.auto.conf

# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=repl_user password=xxxxxxxx host=172.10.1.2 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer 
krbsrvname=postgres target_session_attrs=any'

スタンバイサーバの本起動

  • こちらも同様に、一度コンテナを削除し、コマンドを指定せずに再度起動を行う
# コンテナID確認
$ docker ps
$ docker rm [コンテナID]

# スタンバイサーバ起動
$ docker run --net=sample_nw --ip=172.10.1.3 -e POSTGRES_PASSWORD=password -v archive_wal:/archive_wal -v standby_db:/var/lib/postgresql/data -d postgres:12.9-alpine
  • 最初に書き込んだデータが書き込まれているか確認
postgres=# select * from users;
 id | name 
----+------
  1 | A
  2 | B
  3 | C

新規データを追加しスタンバイサーバで確認

  • マスタサーバ 新規で1件レコード追加
postgres=# insert into users(name) values('D');
  • ログシッピングと違い、walのアーカイブを待たずスタンバイサーバで同期されている事を確認
-- スタンバイサーバ
postgres=# select * from users;
 id | name 
----+------
  1 | A
  2 | B
  3 | C
  4 | D  # 追加されている

ストリーミングレプリケーションの確認

postgres=# select usename,client_addr,sent_lsn,write_lsn,flush_lsn,replay_lsn from pg_stat_replication;
  usename  | client_addr | sent_lsn  | write_lsn | flush_lsn | replay_lsn 
-----------+-------------+-----------+-----------+-----------+------------
 repl_user | 172.10.1.3  | 0/D000318 | 0/D000318 | 0/D000318 | 0/D000318