PostgreSQL レプリケーションの種類

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

ストリーミングレプリケーションは、データベースクラスタ単位でWAL（更新履歴）を利用し、
データの同期を実現します。

マスタ側で出力されたWALをスタンバイ側に転送し、スタンバイ側はWALをリカバリします。
スタンバイ側は設定により、参照用として利用することができます。

また、マスタとスタンバイでPostgreSQLのマイナーバージョンが違ってもレプリケーションできますが、
メジャーバージョンが違うとレプリケーションができません。
OSやCPUアーキテクチャが異なる場合にもレプリケーションができません。

ロジカルレプリケーション

ロジカルレプリケーションは、データベースやテーブル単位でWALを利用しデータの同期を実現します。
ストリーミングレプリケーションと同じくWALを利用したレプリケーションですが、
WALに記載されている変更情報を論理的なレベルに変換（デコード）してから、転送する仕組みになります。

そして、パブリケーションとサブスクリプションにより、レプリケーションの対象を制御することができます。
ストリーミングレプリケーションと違った仕組みにすることで、自由度の高いレプリケーションを実装することができます。

異なるメジャーバージョン間のレプリケーション
テーブル単位でのレプリケーション
スタンバイへの書き込み

一方、自由度が高くなることと引き換えに、ロジカルレプリケーションには制約事項と注意事項があります。

DDLコマンドと一部のオブジェクトがレプリケーションされない
スタンバイ側が書き込み可能であるため、マスタから転送されてきたデータが一意性制約などの制約に違反するとレプリケーションが停止する（コンフリクト）

引用はこちら

使用環境

使用する環境は以下です。

プライマリ側
OS:RHEL8
DB:PostgreSQL-15
HOST:primary
スタンバイ側
OS:RHEL8
DB:PostgreSQL-15
HOST:standby

設定準備

テラタームウィンドウ
今回はテラタームを使用してコマンドを打っていきますが、このようなコマンドツールを使用して複数の環境を操作する場合は、以下のようにウィンドウの背景色を変えると見やすいです。
設定方法は「設定」→ 「ウィンドウ」 →「背景」→ 色を決めることで変更できます。
PostgreSQL
プライマリもスタンバイもPostgreSQLがインストール済みの状態から始めます。

プライマリ側の設定

1.postgresql.conf

postgresql.conf は、PostgreSQL の基本的な設定ファイルです。
冗長化を行うと構成自体が変化するため、このファイルのうち以下の項目をいじっていきます。

listen_addresses : listen状態にするIPアドレスを指定
max_wal_senders : WAL senderプロセス数を定義。スタンバイサーバ以上にする必要があるようです。
synchronous_standby_names : スタンバイサーバのapplication_nameを定義します。

[postgres@primary data]$ find / -name postgresql.conf  2> /dev/null  これでファイルの位置を探せます。
/var/lib/pgsql/15/data/postgresql.conf 
[postgres@primary data]$ vi /var/lib/pgsql/15/data/postgresql.conf
listen_addresses = '*'
max_wal_senders = 10
synchronous_standby_names = 'standby'

2.pg_hba.conf

pg_hba.confは、PostgreSQL に接続するクライアントの認証に関する設定ファイルです。
このファイルで、スタンバイサーバからの接続を許可します。

[postgres@primary ~]$ find / -name pg_hba.conf  2> /dev/null
/var/lib/pgsql/15/data/pg_hba.conf
[postgres@primary ~]$ vi /var/lib/pgsql/15/data/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                            METHOD
host    replication     all             [スタンバイ側のIPアドレス/**]           trust

設定を反映させます。

[root@primary data]# systemctl restart postgresql-15

'0.0.0.0' の状態を確認します。

[root@primary data]# ss -ano | grep 5432
u_str LISTEN     0      128                         /var/run/postgresql/.s.PGSQL.5432 88834                  * 0                    
u_str LISTEN     0      128                                        /tmp/.s.PGSQL.5432 88836                  * 0                    
tcp   LISTEN     0      128                                                   0.0.0.0:5432             0.0.0.0:*

スタンバイ側の設定

1.プライマリ-スタンバイ間の同期

[postgres@standby ~]$ rm -rf ${PGDATA}/*
[postgres@standby ~]$ pg_basebackup -R -D ${PGDATA} -h [プライマリ側のIPアドレス]

※繋がらない場合は、ファイアーウォールやこれまでの設定を見直してみましょう。

2.postgresql.auto.conf

pg_basebackup コマンドによって、postgresql.auto.confが自動生成されるため、
このファイルの一番後ろに「application_name=プライマリで設定したsynchronous_standby_names」を記載します。

[postgres@standby ~]$ vi /var/lib/pgsql/15/data/postgresql.auto.conf
[postgres@standby ~]$ cat /var/lib/pgsql/15/data/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=postgres passfile=''/var/lib/pgsql/.pgpass'' channel_binding=prefer host=[プライマリ側のIPアドレス] port=5432 sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any application_name=standby'

3.postgresql.conf

pg_basebackup の実行後は、プライマリサーバーの設定がスタンバイ側に反映されてしまうため、プライマリ側の未設定が必要な個所を以下のようにコメントアウトします。

[postgres@standby ~]$ vi /var/lib/pgsql/15/data/postgresql.conf
#listen_addresses = '0.0.0.0'
#max_wal_senders = 10
#synchronous_standby_names = 'standby'

4.レプリケーションの開始

**スタンバイ側**
[root@standby pgsql]# systemctl start postgresql-15

[postgres@standby ~]$ tail /var/lib/pgsql/15/data/log/postgresql-Wed.log
2022-10-26 10:29:46.781 EDT [3558] LOG:  listening on IPv6 address "::1", port 5432
2022-10-26 10:29:46.781 EDT [3558] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2022-10-26 10:29:46.783 EDT [3558] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-10-26 10:29:46.785 EDT [3558] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-10-26 10:29:46.788 EDT [3562] LOG:  database system was shut down in recovery at 2022-10-26 10:29:38 EDT
2022-10-26 10:29:46.788 EDT [3562] LOG:  entering standby mode
2022-10-26 10:29:46.790 EDT [3562] LOG:  redo starts at 0/2000028
2022-10-26 10:29:46.790 EDT [3562] LOG:  consistent recovery state reached at 0/3000000
2022-10-26 10:29:46.790 EDT [3558] LOG:  database system is ready to accept read-only connections
2022-10-26 10:29:46.905 EDT [3563] LOG:  started streaming WAL from primary at 0/3000000 on timeline 1


**プライマリ側**
[root@primary pgsql]# tail /var/lib/pgsql/15/data/log/postgresql-Wed.log
2022-10-26 10:26:56.105 EDT [4001] LOG:  checkpoint starting: time
2022-10-26 10:28:31.714 EDT [4001] LOG:  checkpoint complete: wrote 953 buffers (5.8%); 0 WAL file(s) added, 0 removed, 0 recycled; write=95.538 s, sync=0.065 s, total=95.609 s; sync files=249, longest=0.063 s, average=0.001 s; distance=4413 kB, estimate=4413 kB
2022-10-26 10:29:46.955 EDT [4382] LOG:  standby "standby" is now a synchronous standby with priority 1
2022-10-26 10:29:46.955 EDT [4382] STATEMENT:  START_REPLICATION 0/3000000 TIMELINE 1

接続試験

プライマリ側でSQLを実行し、スタンバイ側で確認

**プライマリ側**
[postgres@primary ~]$ psql -U postgres
psql (15.0)
"help"でヘルプを表示します。

postgres=# create table test_ta(code int);
CREATE TABLE
postgres=# insert into test_ta(code)

**スタンバイ側**
[postgres@standby ~]$ psql
psql (15.0)
"help"でヘルプを表示します。

postgres=# select i from generate_series(1, 10) as i;
 i
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 行)

プライマリ側で作成したテーブルがスタンバイ側で確認することができました！
それではこれで終わりにしたいと思います。

以上です！