使用 PostgreSQL 管理 OCI 数据库中用于灾难恢复设置的 pglogical Replication 中的序列

简介

本教程概述了用于在主系统与灾难恢复 (Disaster Recovery，DR) 系统之间的逻辑双向复制设置中同步 PostgreSQL 序列值的定制解决方案。

问题陈述：

在当前设置中，我们配置了逻辑双向复制。某些复制的表依赖于序列；但是，我们发现这些序列的 last_value 不会在主环境和 DR 环境之间复制。

这是预期的行为，在 pglogical 中不被视为 bug。根据设计，序列值不会被复制，因为序列被视为非事务性的，并且本质上是特定于节点的，以避免冲突。

为了弥补这一差距，我们实施了一个定制解决方案来同步主系统和 DR 系统之间的序列值。具体而言，我们正在开发一种程序：

从主数据库检索每个相关序列的 last_value。
更新 DR 端的相应序列以匹配主序列。
使用 pg_cron 扩展每隔一分钟自动执行一次。

此方法可确保序列值在两个系统中保持一致，从而防止主要在 DR 环境中出现潜在的数据完整性或冲突问题。

目标

使用 PostgreSQL 管理 OCI Database 中 DR 设置的 pglogical 复制中的序列。

任务 1：设置 pglogical 双向复制

确保已在主系统和 DR 系统之间配置 pglogical 双向复制。

If not yet configured, follow the official documentation to set up pglogical accordingly: Set up pglogical Extension Replication in the Source Database.

任务 2：启用必需的扩展

在主系统和 DR 系统中，通过从 OCI 控制台创建配置来启用以下扩展。

pg_cron
dblink

在 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;
$$;

注:

安全地将 <hostname>、<username> 和 <password> 替换为实际值。

使用 <hostname> 表示数据库系统的 FQDN。

任务 4：在 DR 系统中创建 `pg_cron` 作业

运行以下查询以创建 pg_cron 作业，并安排该过程在 DR 数据库系统上每隔一分钟运行一次。

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

注:

确保 pg_cron 作业仅在 DR 系统上启用。

根据需要调整作业计划。

任务 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：处理故障转移

如果发生故障转移，请执行以下步骤：

在新的 DR（以前为 primary）中，启用 pg_cron 作业。
在新的主服务器（以前称为 DR）中，禁用 pg_cron 作业。

这可确保同步过程始终仅在当前 DR 系统上运行。

注:
有用的命令：
-- List all scheduled jobs and get job ID
SELECT jobid, schedule, command, active FROM cron.job;

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

-- Enable a job
SELECT cron.alter_job(jobid := <job_id>, active := true);
根据特定于环境的要求调整过程和作业定义。

在将过程部署到生产环境之前，请在开发环境中全面测试该过程。

确认

作者 - Arvind Yadav（首席技术人员）

使用 PostgreSQL 管理 OCI 数据库中用于灾难恢复设置的 pglogical Replication 中的序列

简介

目标

任务 1：设置 pglogical 双向复制

任务 2：启用必需的扩展

任务 3：创建过程

任务 4：在 DR 系统中创建 pg_cron 作业

任务 5：在主系统中创建 pg_cron 作业并保持禁用状态

任务 6：处理故障转移

相关链接

确认

更多学习资源

任务 4：在 DR 系统中创建 `pg_cron` 作业

任务 5：在主系统中创建 `pg_cron` 作业并保持禁用状态