Azure Database for PostgreSQL 유연한 서버에서 논리 복제 및 논리 디코딩

Azure Database for PostgreSQL 유연한 서버는 다음과 같은 논리적 데이터 추출 및 복제 방법론을 지원합니다.

1. 논리적 복제

   1. PostgreSQL 원시 논리 복제를 사용하여 데이터 개체를 복제합니다. 논리적 복제를 사용하면 테이블 수준 데이터 복제를 포함하여 데이터 복제를 세밀하게 제어할 수 있습니다.
   2. 논리 스트리밍 복제 및 데이터베이스의 초기 스키마 복사, TRUNCATE 지원, DDL 복제 기능 등과 같은 더 많은 기능을 제공하는 pglogical 확장을 사용합니다.

2. WAL(미리 쓰기 로그)의 콘텐츠를 디코딩하여 구현되는논리적 디코딩입니다.

논리적 복제와 논리적 디코딩 비교

논리 복제 및 논리 디코딩은 여러 유사성을 가집니다. 공통점은 다음과 같습니다.

• Postgres에서 데이터를 복제할 수 있습니다.

• 변경 내용의 소스로 WAL(미리 쓰기 로그)을 사용합니다.

• 논리 복제 슬롯을 사용하여 데이터를 보냅니다. 슬롯은 변경 내용의 스트림을 나타냅니다.

• 테이블의 REPLICA IDENTITY 속성을 사용하여 보낼 수 있는 변경 내용을 확인합니다.

• DDL 변경 내용을 복제하지 않습니다.

두 기술은 차이점이 있습니다:

논리적 복제:

• 복제할 테이블 또는 테이블 집합을 지정할 수 있습니다.

논리 디코딩:

• 데이터베이스의 모든 테이블에서 변경 내용을 추출합니다.

논리적 복제 및 논리적 디코딩을 위한 필수 조건

1. 포털의 매개 변수 페이지로 이동합니다.

2. wal_level 매개 변수를 logical으로 설정합니다.

3. pglogical 확장을 사용하려면 shared_preload_libraries 및 azure.extensions 매개 변수를 검색한 다음 드롭다운 목록 상자에서 pglogical를 선택합니다.

4. max_worker_processes 매개 변수 값을 최소 16으로 업데이트합니다. 그렇지 않으면 다음과 같은 WARNING: out of background worker slots문제가 발생할 수 있습니다.

5. 변경 내용을 저장하고 서버를 다시 시작하여 변경 내용을 적용합니다.

6. Azure Database for PostgreSQL 유연한 서버가 연결 리소스의 네트워크 트래픽을 허용하는지 확인합니다.

7. 관리 사용자 복제 권한을 부여합니다.

   ALTER ROLE <adminname> WITH REPLICATION;
   

8. 사용 중인 역할에 복제하는 스키마에 대한 권한이 있는지 확인합니다. 그렇지 않으면 Permission denied for schema와 같은 오류가 발생할 수 있습니다.

비고

복제 사용자를 일반 관리자 계정과 분리하는 것은 항상 좋은 방법입니다.

논리적 복제 및 논리적 디코딩 사용

네이티브 논리 복제를 사용하는 것이 Azure Database for PostgreSQL 유연한 서버에서 데이터를 복제하는 가장 간단한 방법입니다. SQL 인터페이스 또는 스트리밍 프로토콜을 사용하여 변경 내용을 사용할 수 있습니다. SQL 인터페이스를 사용하여 논리 디코딩을 사용하여 변경 내용을 사용할 수도 있습니다.

원시 논리 복제

논리적 복제는 게시자 및 구독자라는 용어를 사용합니다.

• 게시자는 데이터를 전송하는 Azure Database for PostgreSQL 유연한 서버 데이터베이스입니다.
• 구독자는 데이터를 수신하는 Azure Database for PostgreSQL 유연한 서버 데이터베이스입니다.

논리 복제를 시도하는 데 사용할 수 있는 몇 가지 샘플 코드는 다음과 같습니다.

1. 게시자 데이터베이스에 연결합니다. 테이블을 만들고 데이터를 추가합니다.

   CREATE TABLE basic (id INTEGER NOT NULL PRIMARY KEY, a TEXT);
   INSERT INTO basic VALUES (1, 'apple');
   INSERT INTO basic VALUES (2, 'banana');
   

2. 테이블에 대한 게시를 만듭니다.

   CREATE PUBLICATION pub FOR TABLE basic;
   

3. 구독자 데이터베이스로 연결합니다. 게시자와 동일한 스키마를 사용하여 테이블을 만듭니다.

   CREATE TABLE basic (id INTEGER NOT NULL PRIMARY KEY, a TEXT);
   

4. 이전에 만든 게시에 연결할 구독을 만듭니다.

   CREATE SUBSCRIPTION sub CONNECTION 'host=<server>.postgres.database.azure.com user=<rep_user> dbname=<dbname> password=<password>' PUBLICATION pub;
   

5. 이제 구독자로부터 테이블을 쿼리할 수 있습니다. 게시자로부터 데이터를 수신하는 것을 볼 수 있습니다.

   SELECT * FROM basic;
   

   게시자의 테이블에 더 많은 행을 추가하고 구독자에 대한 변경 내용을 볼 수 있습니다.

   데이터를 볼 수 없는 경우 역할의 azure_pg_admin 멤버인 사용자로 전환하고 테이블 콘텐츠를 확인합니다.

논리적 복제에 대한 자세한 내용은 PostgreSQL 설명서를 참조하세요.

동일한 서버의 데이터베이스 간에 논리적 복제 사용

동일한 Azure Database for PostgreSQL 유연한 서버에서 서로 다른 데이터베이스 간에 논리적 복제를 설정하려면 특정 지침에 따라 구현 제한을 방지합니다. 현재 동일한 명령 내에서 복제 슬롯을 만들지 않은 경우에만 동일한 데이터베이스 클러스터에 연결하는 구독을 만들 수 있습니다. 그렇지 않으면 CREATE SUBSCRIPTION 호출은 LibPQWalReceiverReceive 대기 이벤트에서 멈춥니다. 이 동작은 이후 릴리스에서 제거될 수 있는 Postgres 엔진 내의 기존 제한 때문입니다.

이 제한을 피하면서 동일한 서버에서 "원본" 데이터베이스와 "대상" 데이터베이스 간에 논리적 복제를 설정하려면 다음 단계를 수행합니다.

먼저 원본 데이터베이스와 대상 데이터베이스 모두에서 동일한 스키마를 사용하여 명명된 basic 테이블을 만듭니다.

-- Run this on both source and target databases
CREATE TABLE basic (id INTEGER NOT NULL PRIMARY KEY, a TEXT);

그런 다음 원본 데이터베이스에서 해당 테이블에 대한 게시를 생성하고, pg_create_logical_replication_slot 함수를 사용하여 논리 복제 슬롯을 별도로 생성합니다. 이 방법을 사용하면 슬롯이 구독과 동일한 명령으로 만들어질 때 일반적으로 발생하는 중단 문제를 방지할 수 있습니다. pgoutput 플러그인을 사용합니다.

-- Run this on the source database
CREATE PUBLICATION pub FOR TABLE basic;
SELECT pg_create_logical_replication_slot('myslot', 'pgoutput');

그런 다음 대상 데이터베이스에서 이전에 만든 게시에 대한 구독을 만듭니다. create_slot을 false로 설정하여 Azure Database for PostgreSQL 유연한 서버가 새 슬롯을 만들지 않도록 하고, 이전 단계에서 만든 슬롯 이름을 지정합니다. 이 명령을 실행하기 전에 연결 문자열의 자리 표시자를 실제 데이터베이스 자격 증명으로 바꿉니다.

-- Run this on the target database
CREATE SUBSCRIPTION sub
   CONNECTION 'dbname=<source dbname> host=<server>.postgres.database.azure.com port=5432 user=<rep_user> password=<password>'
   PUBLICATION pub
   WITH (create_slot = false, slot_name='myslot');

논리 복제를 설정한 후 원본 데이터베이스의 테이블에 새 레코드 basic 를 삽입한 다음 대상 데이터베이스에 복제되는지 확인하여 테스트합니다.

-- Run this on the source database
INSERT INTO basic SELECT 3, 'mango';

-- Run this on the target database
TABLE basic;

모든 항목이 올바르게 구성된 경우 대상 데이터베이스의 원본 데이터베이스에서 새 레코드가 표시되어 논리적 복제의 성공적인 설정이 확인됩니다.

pglogical 확장

다음은 공급자 데이터베이스 서버와 구독자에서 pglogical을 구성하는 예입니다. 자세한 내용은 pglogical 확장 설명서를 참조하세요. 또한 이전에 나열된 필수 구성 요소 작업을 완료해야 합니다.

1. 공급자와 구독자 데이터베이스 서버 모두에서 데이터베이스에 pglogical 확장을 설치합니다.

   \c myDB
   CREATE EXTENSION pglogical;
   

2. 복제 사용자가 서버 관리 사용자(서버를 만든 사용자)가 아닌 경우 역할에 사용자 멤버 자격을 azure_pg_admin 부여하고 사용자에게 REPLICATION 및 LOGIN 특성을 할당합니다. 자세한 내용은 pglogical 설명서를 참조하세요.

   GRANT azure_pg_admin to myUser;
   ALTER ROLE myUser REPLICATION LOGIN;
   

3. 공급자(원본/게시자) 데이터베이스 서버에서 공급자 노드를 만듭니다.

   select pglogical.create_node( node_name := 'provider1',
   dsn := ' host=myProviderServer.postgres.database.azure.com port=5432 dbname=myDB user=myUser password=<password>');
   

4. 복제 세트를 만듭니다.

   select pglogical.create_replication_set('myreplicationset');
   

5. 데이터베이스의 모든 테이블을 복제 세트에 추가합니다.

   SELECT pglogical.replication_set_add_all_tables('myreplicationset', '{public}'::text[]);
   

   다른 방법으로, 특정 스키마(예: testUser)의 테이블을 기본 복제 집합에 추가할 수도 있습니다.

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

6. 구독자 데이터베이스 서버에서 구독자 노드를 만듭니다.

   select pglogical.create_node( node_name := 'subscriber1',
   dsn := ' host=mySubscriberServer.postgres.database.azure.com port=5432 dbname=myDB user=myUser password=<password>' );
   

7. 동기화 및 복제 프로세스를 시작하는 구독을 만듭니다.

   select pglogical.create_subscription (
   subscription_name := 'subscription1',
   replication_sets := array['myreplicationset'],
   provider_dsn := 'host=myProviderServer.postgres.database.azure.com port=5432 dbname=myDB user=myUser password=<password>');
   

8. 구독 상태를 확인합니다.

   SELECT subscription_name, status FROM pglogical.show_subscription_status();
   

주의

Pglogical은 현재 자동 DDL 복제를 지원하지 않습니다. 를 사용하여 pg_dump --schema-only초기 스키마를 수동으로 복사할 수 있습니다. 함수를 사용하여 pglogical.replicate_ddl_command 공급자 및 구독자에서 DDL 문을 동시에 실행할 수 있습니다. 여기에 나열된 확장의 다른 제한 사항을 알아둡니다.

논리 디코딩

스트리밍 프로토콜 또는 SQL 인터페이스를 통해 논리적 디코딩을 사용할 수 있습니다.

스트리밍 프로토콜

스트리밍 프로토콜을 사용하여 변경 사항을 수신하는 것이 더 바람직한 경우가 많습니다. 사용자 고유의 소비자 또는 커넥터를 만들거나 Debezium과 같은 타사 서비스를 사용할 수 있습니다.

스트리밍 프로토콜 pg_recvlogical을 사용하는 예제는 wal2json 설명서를 참조하세요. pg_recvlogical 스트리밍 프로토콜을 사용하는 예제입니다.

SQL 인터페이스

다음 예제에서는 wal2json 플러그 인과 함께 SQL 인터페이스를 사용합니다.

1. 슬롯을 만듭니다.

   SELECT * FROM pg_create_logical_replication_slot('test_slot', 'wal2json');
   

2. SQL 명령을 부여합니다. 다음은 그 예입니다.

   CREATE TABLE a_table (
      id varchar(40) NOT NULL,
      item varchar(40),
      PRIMARY KEY (id)
   );
   
   INSERT INTO a_table (id, item) VALUES ('id1', 'item1');
   DELETE FROM a_table WHERE id='id1';
   

3. 변경 내용을 이용합니다.

   SELECT data FROM pg_logical_slot_get_changes('test_slot', NULL, NULL, 'pretty-print', '1');
   

   출력은 다음과 같이 표시됩니다.

   {
         "change": [
         ]
   }
   {
         "change": [
                  {
                           "kind": "insert",
                           "schema": "public",
                           "table": "a_table",
                           "columnnames": ["id", "item"],
                           "columntypes": ["character varying(40)", "character varying(40)"],
                           "columnvalues": ["id1", "item1"]
                  }
         ]
   }
   {
         "change": [
                  {
                           "kind": "delete",
                           "schema": "public",
                           "table": "a_table",
                           "oldkeys": {
                                 "keynames": ["id"],
                                 "keytypes": ["character varying(40)"],
                                 "keyvalues": ["id1"]
                           }
                  }
         ]
   }
   

4. 사용이 완료되면 슬롯을 삭제합니다.

   SELECT pg_drop_replication_slot('test_slot');
   

논리 디코딩에 대한 자세한 내용은 PostgreSQL 설명서: 논리적 디코딩을 참조하세요.

Monitor

논리 디코딩을 모니터링해야 합니다. 사용하지 않는 복제 슬롯을 삭제합니다. 슬롯은 변경 사항이 읽힐 때까지 Postgres WAL 로그와 관련 시스템 카탈로그를 계속 보유합니다. 구독자 또는 소비자가 실패하거나 부적절하게 구성된 경우 사용되지 않은 로그가 쌓여 스토리지를 채웁니다. 또한 로그가 이용되지 않으면 트랜잭션 ID 래핑의 위험이 증가합니다. 두 경우 모두 서버를 사용할 수 없게 될 수 있습니다. 따라서 논리 복제 슬롯을 지속적으로 사용해야 합니다. 논리 복제 슬롯을 더 이상 사용하지 않는 경우 즉시 삭제합니다.

active 보기의 pg_replication_slots 열은 슬롯에 연결된 소비자가 있는지 여부를 나타냅니다.

SELECT * FROM pg_replication_slots;

값이 정상 임계값을 초과하면 알리도록 최대 사용된 트랜잭션 ID 및 스토리지 사용 메트릭에 대한 경고를 설정합니다.

제한점

• 논리 복제 제한 사항은 여기에서 설명한 대로 적용됩니다.

• 슬롯 및 HA 장애 조치(failover) - PostgreSQL 16 및 이전 버전에서 Azure Database for PostgreSQL HA(고가용성) 사용 서버를 사용하는 경우 장애 조치(failover) 이벤트 중에 논리적 복제 슬롯이 유지되지 않습니다. 논리적 복제 슬롯을 유지하고 장애 조치(failover) 후 데이터 일관성을 보장하려면 PG 장애 조치(failover) 슬롯 확장을 사용하고 지원 설정(예: hot_standby_feedback = on)을 구성합니다. 이 확장을 사용하도록 설정하는 방법에 대한 자세한 내용은 설명서를 참조하세요.

논리 복제 슬롯에 대한 페일오버 지원

PostgreSQL 17 이상 버전의 경우 슬롯 동기화는 기본적으로 지원됩니다. 올바른 PostgreSQL 구성(, sync_replication_slots)을 사용하도록 설정하면 장애 조치(hot_standby_feedbackfailover) 후 논리 복제 슬롯이 자동으로 유지되며 확장이 필요하지 않습니다.

비고

슬롯 동기화를 사용하도록 설정한 후에는 장애 조치(failover) 옵션을 사용하도록 설정된 논리적 복제 슬롯만 대기 상태로 동기화됩니다.

중요합니다

해당 구독자가 더 이상 존재하지 않는 경우 주 서버에서 논리 복제 슬롯을 삭제해야 합니다. 그렇지 않으면 WAL 파일이 스토리지를 채우는 기본 데이터베이스에 누적됩니다. 스토리지 사용량이 95%에 도달하거나 사용 가능한 용량이 5GiB 미만이면 주 서버가 자동으로 읽기 전용 모드로 전환됩니다. 스토리지 임계값이 특정 한도를 초과하고 논리 복제 슬롯이 사용되지 않는 경우(사용할 수 없는 구독자 때문에) Azure Database for PostgreSQL 유연한 서버는 사용하지 않는 논리 복제 슬롯을 자동으로 삭제합니다. 이 작업을 통해 누적된 WAL 파일이 해제되며 스토리지가 채워지는 상황으로 인해 서버를 사용할 수 없게 되는 문제를 방지합니다.

관련 콘텐츠

• Azure Database for PostgreSQL의 방화벽 규칙입니다.
• 공용 액세스 권한이 있는 Azure Database for PostgreSQL에 대한 네트워킹 개요입니다.
• Azure Database for PostgreSQL의 가상 네트워크 통합
• 확장 사용 방법
• Azure Database for PostgreSQL의 고가용성