PostgreSQL를 사용하여 OCI 데이터베이스에서 재해 복구 설정을 위한 사전 논리적 복제에서 시퀀스 관리

소개

이 자습서에서는 기본 시스템과 DR(재해 복구) 시스템 간의 언어 양방향 복제 설정에서 PostgreSQL 시퀀스 값을 동기화하기 위한 사용자 정의 솔루션을 간략하게 설명합니다.

문제 설명:

현재 설정에서는 언어적 양방향 복제를 구성했습니다. 복제된 테이블 중 일부는 시퀀스에 의존하지만 이러한 시퀀스의 last_value는 기본 환경과 DR 환경 간에 복제되지 않습니다.

이것은 예상된 동작이며 pglogical에서 버그로 간주되지 않습니다. 설계상 시퀀스 값은 복제되지 않습니다. 충돌을 방지하기 위해 시퀀스가 트랜잭션이 아닌 본질적으로 노드별로 고려되기 때문입니다.

이러한 격차를 해소하기 위해 기본 시스템과 DR 시스템 간에 시퀀스 값을 동기화하는 사용자 정의 솔루션을 구현했습니다. 특히 다음과 같은 프로시저를 개발하고 있습니다.

이 접근 방식을 사용하면 시퀀스 값이 두 시스템에서 일관성을 유지하므로 주로 DR 환경에서 잠재적인 데이터 무결성 또는 충돌 문제를 방지할 수 있습니다.

목표

작업 1: Pglogical 양방향 복제 설정

기본 시스템과 DR 시스템 간에 사전 논리적 양방향 복제가 이미 구성되어 있는지 확인합니다.

아직 구성하지 않은 경우 공식 설명서에 따라 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: Failover 처리

페일오버가 발생할 경우 다음 단계를 수행합니다.

  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를 참조하십시오.