Manage Sequences in pglogical Replication for Disaster Recovery Setup in OCI Database with PostgreSQL

はじめに

このチュートリアルでは、プライマリ・システムとディザスタ・リカバリ(DR)システム間の論理双方向レプリケーション設定でPostgreSQL順序値を同期するためのカスタム・ソリューションの概要を示します。

問題の内容:

現在のセットアップでは、pglogical bidirectional replicationを構成しました。一部のレプリケート表は順序に依存しますが、これらの順序のlast_valueがプライマリ環境とDR環境の間でレプリケートされないことがわかりました。

これは想定された動作であり、pglogicalのバグとはみなされません。設計上、順序は非トランザクションとみなされ、本質的に競合を回避するためにノード固有とみなされるため、順序値はレプリケートされません。

このギャップに対処するために、プライマリ・システムとDRシステムの間で順序値を同期するカスタム・ソリューションを実装しました。具体的には、次のような手順を開発しています。

このアプローチにより、順序値が両方のシステム間で一貫性が保たれるようになるため、主にDR環境での潜在的なデータの整合性や競合の問題が回避されます。

目的

タスク1: pglogical双方向レプリケーションの設定

プライマリシステムとDRシステム間でpglogical双方向レプリケーションがすでに構成されていることを確認します。

まだ構成されていない場合は、公式ドキュメントに従って、「ソース・データベースでのpglogical Extension Replicationの設定」に従ってpglogicalを設定します。

タスク2: 必須拡張の有効化

プライマリ・システムとDRシステムの両方で、OCIコンソールから構成を作成して、次の拡張を有効にします。

DBSystemsに構成ファイルを適用したら、各DBSystemのpostgresデータベースに接続し、次の問合せを実行します。

CREATE EXTENSION IF NOT EXISTS pg_cron;
CREATE EXTENSION IF NOT EXISTS dblink;

タスク3: 手順の作成

postgresデータベースで、プライマリ・システムとDRシステムの両方にプロシージャを作成します。

プロシージャ定義:

CREATE OR REPLACE PROCEDURE sync_sequences_from_remote()
LANGUAGE plpgsql
AS $$
DECLARE
  -- Update the connection string with correct credentials and hostname
  conn_str TEXT := 'host=<hostname> user=<username> password=<password> dbname=postgres';

  row RECORD;
  remote_seq_query TEXT :=
    $_$SELECT schemaname, sequencename, last_value
       FROM pg_sequences
       WHERE schemaname !~ 'pg_catalog|cron|information_schema|pg_toast|pg_temp'
         AND last_value IS NOT NULL$_$;
  full_seq_name TEXT;
  setval_sql TEXT;
BEGIN
  FOR row IN
    SELECT * FROM dblink(conn_str, remote_seq_query)
      AS t(schemaname TEXT, sequencename TEXT, last_value BIGINT)
  LOOP
    full_seq_name := quote_ident(row.schemaname) || '.' || quote_ident(row.sequencename);
    setval_sql := FORMAT('SELECT setval(%L, %s, true);', full_seq_name, row.last_value);
    RAISE NOTICE 'Setting sequence: %', setval_sql;
    EXECUTE setval_sql;
  END LOOP;
END;
$$;

ノート:

タスク4: DRシステムでのpg_cronジョブの作成

次の問合せを実行して、pg_cronジョブを作成し、DRデータベース・システムで1分ごとに実行するプロシージャをスケジュールします。

SELECT cron.schedule('UPDATE SEQUENCE', '*/1 * * * *', $$ CALL sync_sequences_from_remote(); $$);

ノート:

タスク5: プライマリ・システムでのpg_cronジョブの作成と無効化の維持

次の問合せを実行して、pg_cronジョブを作成し、無効にします。

SELECT cron.schedule('*/1 * * * *', $$ CALL sync_sequences_from_remote() $$);

-- Disable the job (on Primary)
SELECT cron.alter_job(jobid := <job_id>, active := false);

タスク6: フェイルオーバーの処理

フェイルオーバーが発生した場合は、次のステップに従います。

  1. 新しいDR (以前のプライマリ)で、pg_cronジョブを有効にします。

  2. 新しいプライマリ(以前のDR)で、pg_cronジョブを無効化します。

これにより、同期プロセスは常に現在のDRシステムでのみ実行されます。

ノート:

確認

その他の学習リソース

docs.oracle.com/learnで他のラボを確認するか、Oracle Learning YouTubeチャネルで無料のラーニング・コンテンツにアクセスしてください。また、education.oracle.com/learning-explorerにアクセスして、Oracle Learning Explorerになります。

製品ドキュメントについては、Oracle Help Centerを参照してください。