💨

新卒3年目DBエンジニアが教えるDB基礎講座（Postgres編第4回：PostgreSQLにおけるレプリケーション）

2023/06/27に公開

PostgreSQL

tech

今回の内容

今回はPostgreSQLのインスタンスが最悪壊れてもまだ生きていけるための手段であるレプリケーションに関して紹介です。

レプリケーションにはいろいろな目的があります。
そこにはよく耳にするであろうIT-BCPの考えであったり、負荷分散などあるわけです。

よく勘違いされますが、レプリケーションはバックアップの代わりではありません。誤ったマスターデータが登録なり更新されてもバックアップから戻してください。

下記は今回のコンテンツです。
Hans-onまでやった後は最近なにかとみるNewSQLの話も絡んでみたり～てきな感じで今回もやっていきましょう。
**1.レプリケーションの種類
2.ストリーミングレプリケーション
3.logical replication
4.Hands-on
**

1.レプリケーションの種類

PostgreSQLには主に2種類のレプリケーションが存在します。
さっそく答えから言うと
・ストリーミングレプリケーション
・ロジカルレプリケーション
です。ここからマルチマスタレプリケーションというものもありますが、ここでは扱いませんのでPostgreSQLのつよつよ猫さんのプレゼンでも見てください。

とは言っても実際実務で使うのはロジカルレプリケーションがほとんどだと思いますので今回のハンズオンもロジカルレプリケーションだけを取り扱います。

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

とは言っても扱わないわけにはいかないのでストリーミングレプリケーションの概要を扱います。
ストリーミングレプリケーションは以下の特性を持っています。

・変更内容単位でのWAL伝達: ここでいうWAL（Write Ahead Log）とは、変更内容を記録するログのことで、これが変更内容単位で伝達されます。これにより、データの一貫性と復元能力が向上します。

・walsenderとwalreceiverによる運用: ストリーミングレプリケーションは、主に2つのプロセス、walsenderとwalreceiverによって実現されます。これらのプロセスの間での基本的な流れは以下のとおりです。

・walsenderがデータ変更の通知を受け取ります。
通知を受けたwalreceiverがレコードを同期的に書き込みます。
WALレコードの受け取りをstartupプロセスに通知します。このstartupプロセスが実際のリカバリ処理を行います。

・startupプロセスは、受け取ったWALレコードを読み取り、適用します。

・1:Nのマルチスタンバイ構成: ストリーミングレプリケーションの構成は、1つのプライマリと複数のスタンバイという形態をとります。

・同期と非同期の選択肢: スタンバイのデータ適応を待ってから書き込みを完了する同期モード、あるいはプライマリだけに書き込んで完了する非同期モード、どちらも選択可能です。

・プライマリの復旧: プライマリが落ちた場合でも、次の3つの方法のいずれかで復旧可能です。

1.promote_trigger_fileを使用: スタンバイのpostgresql.confでpromote_trigger_fileを設定している場合、このファイルを生成することでスタンバイがプライマリに昇格します。
　2.pg_ctl promoteコマンドを使用: このコマンドを使用すると、スタンバイがプライマリに昇格します。
　3.pg_promote関数を使用: psql -u postgres -c "SELECT pg_promote()"のように、この関数を使ってプロモート操作を行います。もし1分待っても反応がない場合、スタンバイはプライマリに昇格します。

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

ロジカルレプリケーションは、データベースのレプリケーションを行う際に用いられる技術で、publisher（発行者）とsubscriber（購読者）間でデータをやり取りします。ここでは、ロジカルレプリケーションの特性と管理方法について詳しく説明し、この技術をより効果的に活用するための情報を提供します。

ロジカルレプリケーションの特性と活用
ロジカルレプリケーションは、その構造上、データの書き込みと読み込みがどちらの端末でも可能という特徴を持っています。これはつまり、一方向のデータフローに限定されず、より複雑なデータ同期のシナリオをサポートすることが可能であるということを意味します。

ロジカルレプリケーションの基礎となるのは、「ロジカルデコーディング」という技術です。これにより、外部のシステムがWAL（Write Ahead Log）を解析できるようになります。また、subscriber側ではpgoutputというモジュールを用いて出力プラグインの結果を定期的に利用します。これにより、他のPostgreSQLインスタンスがデータの同期を効率的に行うことが可能になります。

レプリケーションの遅延と復旧のためのツール
ロジカルレプリケーションでは、データ同期の遅延を確認するためにpg_stat_replicationビューを活用することができます。これはデータベースの管理者がレプリケーションの状態をリアルタイムで監視するための有用なツールです。

また、WALの管理にはレプリケーションスロットという機能を活用します。この機能を用いることで、WALのバックアップや復旧がより容易になります。これは大規模なデータベースを運用する際には特に役立つ機能です。

なお、スロットがいっぱいになった場合、以下のようなコマンドでスロットを削除する必要があります。

SELECT drop_replication_slot('mysub');

そしてスロットの再作成には、以下のようなコマンドを使用します。

SELECT pg_create_logical_replication_slot('mysub', 'pgoutput');

しかし注意点として、このコマンドを実行すると、レプリケーションスロットが削除された以降のデータは同期されません。そのため、削除後のデータも必要な場合には、スナップショットを取得してから全体の再構築を行う必要があります。

これらの管理と操作方法を理解することにより、ロジカルレプリケーションをより適切に管理し、その強力な機能を最大限に活用することができます。これにより、データの一貫性を保ちつつ、高いパフォーマンスを達成することが可能になります。

4.Hands-on

ではここではpublisherの発行とsubscriberの発行をしていきます。
この記事を参考にしながら少し捕捉しながら進めます。

1.wal_levelの確認
もしデフォルトのreplicaならlogicalにpostgresql.confで書き換え再起動

postgres=# show wal_level;
 wal_level
-----------
 logical

2.コピー元作る
Node1(publisher側でコピー元を作ります。

postgres=# create database sourcedb;
postgres=# \c sourcedb;
postgres=# \c sourcedb;

sourcedb(#   "id" serial8 NOT NULL,
sourcedb(#   "card_no" varchar(255),
sourcedb(#   "full_name" varchar(500),
sourcedb(#   "gender" varchar(1),
sourcedb(#   "address" text,
sourcedb(#   CONSTRAINT "tb_students_pkey" PRIMARY KEY ("id")
sourcedb(# );
CREATE TABLE

sourcedb=# CREATE TABLE "core"."tb_subjects" (
sourcedb(#   "id" serial8 NOT NULL,
sourcedb(#   "name" varchar(500),
sourcedb(#   "credit" int8,
sourcedb(#   "outline" text,
sourcedb(#   CONSTRAINT "tb_subjects_pkey" PRIMARY KEY ("id")
sourcedb(# );
CREATE TABLE

3.Replicationするのロール作成
DBに対してREPLICATIONの権限と全部の権限を与えます。

sourcedb=# CREATE ROLE rep_role WITH REPLICATION LOGIN PASSWORD 'rep$$169#123';
CREATE ROLE

sourcedb=# GRANT ALL PRIVILEGES ON DATABASE sourcedb TO rep_role; 
GRANT
sourcedb=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA core TO rep_role;
GRANT
sourcedb=# GRANT USAGE ON SCHEMA core TO rep_role;
GRANT

4.Node01でpg_hbaの設定

host    sourcedb        rep_role        xx.xx.xx.xx/32           md5
host    replication     rep_role        xx.xx.xx.xx/32           md5

5.Node02でコピー先を作成

postgres=# create database targetdb;
CREATE DATABASE
postgres=# \c targetdb 
You are now connected to database "targetdb" as user "postgres".
targetdb=# create schema core;
CREATE SCHEMA

targetdb=# CREATE TABLE "core"."tb_students" (
targetdb(# "id" serial8 NOT NULL,
targetdb(# "card_no" varchar(255),
targetdb(# "full_name" varchar(500),
targetdb(# "gender" varchar(1),
targetdb(# "address" text,
targetdb(# CONSTRAINT "tb_students_pkey" PRIMARY KEY ("id")
targetdb(# );
CREATE TABLE

targetdb=# CREATE TABLE "core"."tb_subjects" (
targetdb(# "id" serial8 NOT NULL,
targetdb(# "name" varchar(500),
targetdb(# "credit" int8,
targetdb(# "outline" text,
targetdb(# CONSTRAINT "tb_subjects_pkey" PRIMARY KEY ("id")
targetdb(# );

6.publicationのロール作成

sourcedb=# CREATE PUBLICATION logical_pub;
CREATE PUBLICATION
sourcedb=# ALTER PUBLICATION logical_pub ADD TABLE core.tb_students;
ALTER PUBLICATION
sourcedb=# ALTER PUBLICATION logical_pub ADD TABLE core.tb_subjects;
ALTER PUBLICATION

7.subscriberの作成

Targetdb=# CREATE SUBSCRIPTION logical_sub CONNECTION 'host=10.148.0.2 port=5432 password=rep$$169#123 user=rep_role d
bname=sourcedb' PUBLICATION logical_pub WITH (create_slot = false);
CREATE SUBSCRIPTION