pglogical을 사용한 PostgreSQL용 OCI Database의 양방향 복제

소개

OCI PostgreSQL의 논리적 복제를 사용하면 테이블의 복제 ID(일반적으로 기본 키)를 기반으로 데이터 객체 및 변경 사항을 복제할 수 있습니다. 게시-구독 모델을 사용합니다. 여기서 하나 이상의 가입자가 게시자 노드에 정의된 등록에 가입합니다. 가입자는 이러한 설명서에서 데이터를 가져오고 선택적으로 변경 사항을 다시 게시하여 캐스케이딩 복제 또는 보다 복잡한 토폴로지를 사용으로 설정할 수 있습니다.

논리적 복제는 논리적 디코딩을 사용하여 WAL(Write-Ahead Log)을 디코딩하여 행 레벨 변경 사항(INSERT, UPDATE 및 DELETE 작업)을 스트리밍합니다. 이러한 디코딩된 변경 사항은 OCI PostgreSQL의 물리적 스토리지 형식과 별개이며 논리적 데이터 작업만 나타내므로 다운스트림 시스템에 더 큰 유연성을 제공합니다.

이 자습서에서는 논리적 복제에 pglogical 확장을 사용하는 방법과 PostgreSQL용 OCI 데이터베이스의 두 데이터베이스 시스템 간에 양방향 복제를 사용으로 설정하는 방법에 대해 설명합니다.

양방향 백업

pglogical을 사용하는 양방향 복제(BDR)는 두 개 이상의 OCI PostgreSQL 데이터베이스 시스템이 서로의 변경 사항을 게시하고 구독한다는 것을 의미합니다. 즉, 각 OCI PostgreSQL 데이터베이스 시스템은 읽고 쓸 수 있으며 모든 변경 사항은 양방향으로 복제됩니다. 따라서 OCI PostgreSQL 데이터베이스 시스템 중 하나에서 수행된 수정 사항은 모두 미러링되고 구현됩니다.

양방향 복제는 일반적으로 여러 위치에서 사용자에게 대기 시간이 짧은 읽기 및 쓰기 액세스를 제공하는 지역 또는 글로벌 데이터베이스 아키텍처가 필요한 경우에 권장됩니다. 각 리전의 사용자 근처에 읽기/쓰기 인스턴스를 함께 배치하면 로컬에서 변경한 다음 다른 리전으로 복제하여 모든 노드에서 데이터가 동기화되도록 할 수 있습니다.

실제로 대부분의 사용 사례에서는 작은 테이블 하위 세트만 여러 영역에서 쓸 수 있어야 하지만 대부분의 데이터베이스는 단방향 복제를 통해 읽기 전용으로 유지되거나 중앙에서 관리될 수 있습니다.

그러나 양방향 복제는 복잡성을 유발합니다. 시스템 확장에 따라 데이터 일관성을 유지하고 복제 토폴로지를 관리하는 것은 어려울 수 있습니다.

애플리케이션의 주요 목표가 지역별 중단에 대한 읽기 확장성 및 복원성인 경우, 보다 단순하고 강력한 접근 방식은 동일한 리전 또는 여러 리전에 여러 읽기 복제본이 있는 PostgreSQL 인스턴스용 고가용성 OCI 데이터베이스를 배포하는 것입니다.

양방향 Pglogical 작업 방법

예를 들어, 다음과 같이 다중 구독 및 게시를 구성할 수 있습니다.

• 데이터베이스 시스템 1이 변경사항을 게시함 → 데이터베이스 시스템 2가 구독함

• 데이터베이스 시스템 2가 변경사항을 게시함 → 데이터베이스 시스템 1이 구독함

각 노드:

• 변경 사항을 다른 항목으로 보냅니다.

• 다른 유저의 변경 사항을 적용합니다.

• pglogical의 충돌 해결 설정을 사용하여 잠재적 충돌을 처리합니다.

OCI PostgreSQL의 리전 간 단방향 Pglogical 복제에 대한 자세한 내용은 지역 간 단방향 Pglogical 블로그를 참고하세요.

충돌 및 구성 관리

게시자(소스 데이터베이스)는 하나 이상의 발행물을 정의하고 변경 데이터(DML 작업)를 가입자(대상 데이터베이스)에게 보냅니다. 가입자는 여러 게시자에 연결하여 해당 변경 사항을 로컬 데이터베이스에 적용할 수 있습니다.

pglogical 확장은 다음 모드를 지원하는 pglogical.conflict_resolution 매개변수를 통해 고급 충돌 해결을 제공합니다.

• apply_remote(기본값: OCI Database for PostgreSQL)

• error

• keep_local

• last_update_wins

• first_update_wins

conflict_resolution에 대한 자세한 내용은 github의 공식 설명서(2차 사분면 구성 옵션)를 참조하십시오.

keep_local, last_update_wins 또는 first_update_wins를 사용하는 경우 게시자와 가입자 모두에서 track_commit_timestamps를 사용으로 설정해야 합니다. 이 설정을 사용으로 설정하면 작업 로드에 따라 측정 가능한 성능 오버헤드가 발생할 수 있습니다. 배치하기 전에 운용 환경의 성능 영향을 평가하는 것이 좋습니다.

OCI에서 양방향 복제 설정 PostgreSQL

PostgreSQL에 대해 OCI 데이터베이스에서 언어 확장을 사용으로 설정하려면 다음 자습서를 참조하십시오. OCI PostgreSQL에서 언어 확장을 사용으로 설정.

태스크 1: 전제 조건

pglogical 확장이 PostgreSQL용 OCI 데이터베이스에 생성되면 데이터베이스 시스템 #1 및 데이터베이스 시스템 #2에 대해 다음 권한을 부여합니다.

ALTER ROLE psql WITH REPLICATION;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_replication_origin_session_reset() TO psql; 
GRANT EXECUTE ON FUNCTION pg_catalog.pg_replication_origin_session_setup TO psql; 
GRANT ALL ON FUNCTION pg_catalog.pg_replication_origin_session_setup TO psql;

참고: psql은 이 자습서에서 데모용으로 특별히 생성된 샘플 복제 사용자입니다.

작업 2: 단방향 논리적 복제 구성

데이터베이스 시스템 1에서 다음을 수행합니다.

앞에서 설명한 필요 조건을 완료했으면 테스트 테이블을 생성하고 몇 개의 레코드를 삽입하여 Pglogical 워크플로우를 확인합니다.

CREATE TABLE test_table (
    id serial PRIMARY KEY,
    data text
);
 
INSERT INTO test_table
    (id, data)
VALUES
    (generate_series(1, 1000), 'Test');

제공자 노드를 만듭니다.

다음 단계는 pglogical 함수 create_node를 사용하여 노드를 생성하는 것입니다.

SELECT pglogical.create_node(
    node_name := 'provider1',
    dsn := 'host=primary.xxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
            port=5432
            user=psql
            password=xxxxx
            dbname=postgres'
);

다음은 create_node 함수에 대한 매개변수 정의입니다.

• node_name: 데이터베이스 시스템 #1에 만들 게시자의 이름을 지정합니다.
• host: 데이터베이스 시스템 #1의 FQDN(정규화된 도메인 이름)을 입력합니다.
• port_number: 데이터베이스 시스템 #1이 실행 중인 포트를 제공합니다.
• database_name: Pglogical 복제가 구성될 데이터베이스의 이름을 지정합니다.

다음 단계에서는 복제에 포함해야 하는 테이블을 식별합니다. pglogical은 복제 세트를 사용하여 복제할 테이블 및 작업(INSERT, UPDATE, DELETE)을 관리합니다. create_replication_set 함수를 사용하여 사용자 정의 복제 세트를 만들거나, 지정된 테이블의 모든 작업을 복제하는 미리 정의된 기본 세트를 사용할 수 있습니다. 테이블을 포함하려면 개별 테이블의 경우 replication_set_add_table를 사용하고, 특정 스키마의 모든 테이블을 추가하려면 replication_set_add_all_tables를 사용합니다.

다음 명령은 공용 스키마의 모든 테이블을 기본 복제 세트에 추가합니다.

SELECT pglogical.replication_set_add_all_tables(
    'default',
    ARRAY['public']
);

복제 세트에 대한 자세한 내용은 Replication sets를 참조하십시오.

데이터베이스 시스템 2:

데이터베이스 시스템 #1에서와 같이 일치하는 테이블을 생성합니다.

데이터베이스 시스템 #1의 테이블 구조와 일치하도록 데이터베이스 시스템 #2에 테스트 테이블을 생성합니다.

CREATE TABLE test_table (
    id serial PRIMARY KEY,
    data text
);

가입자(Subscriber) 노드 생성:

그런 다음 데이터베이스 시스템 #1에서와 마찬가지로 pglogical 함수 create_node를 사용하여 데이터베이스 시스템 #2에 노드를 생성합니다.

SELECT pglogical.create_node(
    node_name := 'subscriber1',
    dsn := 'host=primary.xxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
            port=5432
            user=psql
            password=xxxxxx
            dbname=postgres'
);

다음은 create_node 함수에 대한 매개변수 정의입니다.

• node_name: 데이터베이스 시스템 #2에서 가입자의 이름을 정의합니다.
• host: 데이터베이스 시스템 #2의 FQDN(정규화된 도메인 이름)을 입력합니다.
• port_number: 데이터베이스 시스템 #2가 실행 중인 포트를 입력합니다.
• database_name: 가입이 생성될 데이터베이스의 이름을 제공합니다.

Pglogical 가입 생성:

다음 단계는 create_subscription 함수를 사용하여 데이터베이스 시스템 #1에서 데이터 복제를 시작하는 가입을 생성하는 것입니다.

subscription_name 매개변수는 구독에 대해 선택한 모든 이름일 수 있습니다. provider_dsn는 데이터베이스 시스템 #1에 대한 접속 문자열이며, replication_sets 매개변수는 사용할 복제 집합을 지정합니다. 이 예에서는 기본 복제 세트를 사용합니다.

synchronize_data 인수는 원본에서 기존 데이터를 복사할지 여부를 나타냅니다. 테스트에서는 모든 행이 이후 변경 사항과 함께 복사되도록 하기 때문에 true로 설정됩니다.

forward_origins 매개변수는 양방향 복제를 사용으로 설정하는 데 중요합니다. forward_origins := '{}'를 설정하면 노드 자체에서 시작된 변경 사항만 전달되고 다른 노드에서 이미 복제된 변경 사항은 전달되지 않습니다. 이렇게 하면 복제 프로세스가 무한 루프로 들어가는 것을 방지할 수 있습니다. 여기서 변경 사항은 계속해서 앞뒤로 복제됩니다.

SELECT pglogical.create_subscription(
    subscription_name := 'subscription1',
    provider_dsn := 'host=primary.xxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
                     port=5432
                     sslmode=require
                     dbname=postgres
                     user=psql
                     password=xxxxx',
    replication_sets := ARRAY['default'],
    synchronize_data := true,
    forward_origins := '{}'
);

구독 상태 확인:

다음 명령을 사용하여 데이터베이스 시스템 #2에 대한 가입 상태 및 기본 정보를 확인합니다.

select * from pglogical.show_subscription_status();
-[ RECORD 1 ]-----+----------------------------------------------------------------------------------------------------------------------------------------------------------------
subscription_name | subscription1
status            | replicating
provider_node     | provider1
provider_dsn      | host=primary.xxxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com port=5432 sslmode=require dbname=postgres user=psql password=xxxxxx
slot_name         | pgl_postgres_provider1_subscription1
replication_sets  | {default}
forward_origins   |

작업 3: 데이터베이스 시스템 #1에서 데이터베이스 시스템 #2로 복제 테스트

이제 데이터베이스 시스템 번호1(소스)과 데이터베이스 시스템 번호2(대상) 간에 복제가 설정되었으므로 데이터베이스 시스템 번호1의 변경사항이 데이터베이스 시스템 번호2에 자동으로 복사되는지 테스트해 보겠습니다.

1. 초기 데이터 확인

   데이터베이스 시스템 #2에서 test_table 테이블을 확인합니다.

   postgres=> SELECT COUNT(*) FROM test_table;
    count 
   -------
     1000
   (1 row)
   

   이렇게 하면 테이블에 이미 1000개의 행이 있음을 확인할 수 있습니다.

2. INSERT 테스트

   데이터베이스 시스템 #1에 새 행 추가:

   postgres=> INSERT INTO test_table VALUES (10000, 'initial load');
   

3. 데이터베이스 시스템 #1에서 확인:

   postgres=> SELECT * FROM test_table WHERE id = 10000;
     id   |     data     
   -------+--------------
   10000 | initial load
   

4. 이제 데이터베이스 시스템 #2를 확인하십시오. 복제가 작동함을 보여주는 동일한 행이 나타납니다.

   postgres=> SELECT * FROM test_table WHERE id = 10000;
     id   |     data     
   -------+--------------
   10000 | initial load
   

5. DELETE 테스트

   데이터베이스 시스템 #1에서 동일한 행을 삭제합니다.

   postgres=> DELETE FROM test_table WHERE id = 10000;
   

6. 두 시스템 모두에서 확인 - 행이 두 시스템에서 모두 사라집니다.

   (0 rows)
   

7. UPDATE 테스트

   데이터베이스 시스템 #1에서 기존 행 업데이트:

   postgres=> UPDATE test_table SET data = 'Initial load' WHERE id = 1;
   

   데이터베이스 시스템 #1 확인:

     id   |     data     
   -------+--------------
     1 | Initial load
   

   데이터베이스 시스템 #2 확인 - 업데이트된 값도 여기에 있습니다.

이러한 테스트를 통해 INSERT, UPDATE 및 DELETE 작업이 데이터베이스 시스템 #1 → 데이터베이스 시스템 #2에서 올바르게 복제되었는지 확인합니다.

작업 4: 양방향 논리적 복제 구성

이전 작업은 데이터베이스 시스템 #1에서 데이터베이스 시스템 #2로의 단방향 복제를 설정합니다. 다음으로는 역방향으로 복제를 사용으로 설정하는 추가 명령도 실행됩니다.

데이터베이스 시스템 2:

데이터베이스 시스템 #1에서와 마찬가지로 복제 세트를 설정합니다.

SELECT pglogical.replication_set_add_all_tables(
    'default',
    ARRAY['public']
);

데이터베이스 시스템 #1:

데이터베이스 시스템 #1에서 데이터베이스 시스템 #2에서 변경사항을 인출하기 시작하도록 구독을 생성합니다.

SELECT pglogical.create_subscription(
    subscription_name := 'subscription2',
    provider_dsn := 'host=primary.xxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
                     port=5432
                     sslmode=require
                     dbname=postgres
                     user=psql
                     password=xxxxx',
    replication_sets := ARRAY['default'],
    synchronize_data := false,
    forward_origins := '{}'
);

이전 명령에서 synchronize_data 인수는 이전과 달리 false로 설정되었습니다. 이는 데이터베이스 시스템 #2의 기존 데이터를 데이터베이스 시스템 #1로 복사할 필요가 없기 때문입니다. 두 테이블 모두 이미 동일한 데이터를 포함하고 있기 때문입니다.

이 명령을 사용하면 양방향 복제 설정이 완료되고 한 서버의 모든 변경사항이 다른 서버로 복제됩니다. 그런 다음 데이터베이스 시스템 #1에서 가입 상태를 확인합니다.

데이터베이스 시스템 #1의 가입 상태를 확인합니다.

다음 명령을 사용하여 데이터베이스 시스템 #1에 대한 가입 상태 및 기본 정보를 확인합니다.

select * from pglogical.show_subscription_status();
-[ RECORD 1 ]-----+----------------------------------------------------------------------------------------------------------------------------------------------------------------
subscription_name | subscription2
status            | replicating
provider_node     | subscriber1
provider_dsn      | host=primary.xxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com port=5432 sslmode=require dbname=postgres user=psql password=xxxxx
slot_name         | pgl_postgres_subscriber1_subscription2
replication_sets  | {default}
forward_origins   |

작업 5: 양방향 복제 테스트

먼저 양방향 복제가 올바르게 작동하는지 확인하기 위해 두 데이터베이스 시스템에 행을 추가합니다.

1. 데이터베이스 시스템 #1에 행 삽입:

   데이터베이스 시스템 #1에서 다음 명령을 실행합니다.

   postgres=# INSERT INTO test_table VALUES (1001, 'Initial_load');
   INSERT 0 1
   

2. 데이터베이스 시스템 #1에 삽입된 행을 확인합니다.

   postgres=# SELECT * FROM test_table WHERE id = 1001;
     id  |     data     
   ------+--------------
   1001 | Initial_load
   (1 row)
   

3. 데이터베이스 시스템 #2에서 복제 확인:

   이제 데이터베이스 시스템 #2에서 행이 복제되었는지 확인합니다.

   postgres=> SELECT * FROM test_table WHERE id = 1001;
     id  |     data     
   ------+--------------
   1001 | Initial_load
   (1 row)
   

   데이터베이스 시스템 #1의 삽입이 데이터베이스 시스템 #2로 복제되었음을 보여줍니다.

4. 데이터베이스 시스템 #2에 행 삽입:

   데이터베이스 시스템 #2에서 다음 명령을 실행합니다.

   postgres=> INSERT INTO test_table VALUES (1002, 'Initial_load');
   INSERT 0 1
   

5. 데이터베이스 시스템 #2에 삽입된 행을 확인합니다.

   postgres=> SELECT * FROM test_table WHERE id = 1002;
     id  |     data     
   ------+--------------
   1002 | Initial_load
   (1 row)
   

6. 데이터베이스 시스템 #1에서 복제 확인:

   이제 데이터베이스 시스템 #1에서 행이 다시 복제되었는지 확인합니다.

   postgres=# SELECT * FROM test_table WHERE id = 1002;
     id  |     data     
   ------+--------------
   1002 | Initial_load
   (1 row)
   

   이 테스트는 데이터베이스 시스템 #1과 데이터베이스 시스템 #2 간의 양방향에서 데이터 변경사항이 성공적으로 복제되었음을 확인합니다.

논리적 복제 지연 모니터

전체 설정이 완료되면 복제 로그를 모니터하여 복제 슬롯, 지연 및 기타 관련 측정 단위를 추적해야 합니다.

select * from pg_stat_replication;

이 명령은 OCI PostgreSQL에서 복제의 현재 상태를 반환합니다.

결론

이 자습서에서는 pglogical을 사용하여 PostgreSQL 양방향 복제의 구성을 안내합니다. 양방향 복제는 데이터베이스 인프라에 복잡성을 가중시키고 추가적인 노력이 필요하므로 사용 사례에 적합한지 확인하는 것이 중요합니다. 애플리케이션에 여러 리전에 걸쳐 여러 개의 작성기가 필요하고 데이터베이스 샤딩이 불가능한 경우 양방향 복제가 이상적인 솔루션입니다.

승인

• 작성자 - Kaviya Selvaraj(Senior Member Technical Staff)

추가 학습 자원

docs.oracle.com/learn에서 다른 랩을 탐색하거나 Oracle Learning YouTube 채널에서 더 많은 무료 학습 콘텐츠에 액세스하세요. 또한 education.oracle.com/learning-explorer를 방문하여 Oracle Learning Explorer가 되십시오.

제품 설명서는 Oracle Help Center를 참조하십시오.

---

제목 및 저작권 정보

Bi-directional replication in OCI Database for PostgreSQL using pglogical

G43214-01

Copyright ©2025,

Oracle and/or its affiliates.