postgres_fdw 및 dblink 확장을 사용하여 PostgreSQL를 통해 OCI 데이터베이스에서 데이터베이스 간 액세스 사용

소개

PostgreSQL가 포함된 Oracle Cloud Infrastructure Database(PostgreSQL가 포함된 OCI 데이터베이스)는 postgres_fdw 및 dblink와 같은 확장을 사용하여 데이터베이스 간 연결을 지원합니다. 이러한 확장을 통해 PostgreSQL 데이터베이스에서 원활한 액세스 및 데이터 공유가 가능하며, 분석, 통합 쿼리 또는 레거시 통합 시나리오에 유용합니다.

dblink 란 무엇입니까?

DBlink는 원격 PostgreSQL 데이터베이스에서 SQL 문을 직접 실행할 수 있는 PostgreSQL 확장입니다. 결과 집합을 행으로 반환하거나 원격 서버에서 명령을 실행합니다.

postgres_fdw 란 무엇입니까?

postgres_fdw(외래 데이터 래퍼)는 데이터베이스가 원격 PostgreSQL 서버의 테이블에 로컬 테이블처럼 접속하고 질의할 수 있도록 해주는 PostgreSQL 확장입니다. dblink와 달리 원격 테이블을 로컬 객체로 매핑하고 더 많은 절차 제어를 제공합니다.

이 자습서에서는 각각에 대한 예와 함께 PostgreSQL 인스턴스가 포함된 OCI 데이터베이스에서 postgres_fdw 및 dblink 확장의 설정에 대해 알아봅니다.

목표

서로 다른 가상 클라우드 네트워크(VCN) 및 서로 다른 지역에 위치한 PostgreSQL 데이터베이스 시스템을 통해 두 OCI 데이터베이스 간에 안전하고 안정적인 네트워크 통신을 가능하게 합니다. 이렇게 설정하면 데이터베이스 연결이 VCN 경계에서 원활하게 작동합니다. 여기에는 프라이빗 뷰를 사용한 DNS 확인 경로 지정 및 설정을 위한 LPG(로컬 피어링 게이트웨이) 구성이 포함됩니다. 목표는 postgres_fdw 또는 dblink와 같은 데이터베이스 확장에 대해 VCN/지역 간 연결을 지원하는 것입니다.

필수 조건

  1. OCI 콘솔에서 확장을 사용으로 설정합니다.

    1. dblink 또는 postgres_fdw 확장자에 대해 사용으로 설정되는 구성 파일을 만듭니다.

      이미지

    2. 데이터베이스 시스템에 구성을 적용합니다.

      이미지

  2. 데이터베이스 레벨에서 확장을 사용으로 설정합니다.

    OCI 콘솔에서 확장을 사용으로 설정한 후 데이터베이스에 접속하고 다음 SQL 명령을 실행합니다.

     CREATE EXTENSION postgres_fdw;

 CREATE EXTENSION dblink;

작업 2: 서로 다른 VCN의 데이터베이스 시스템 간 네트워크 연결 구성

서로 다른 VCN에 있는 PostgreSQL 데이터베이스 시스템과 두 OCI 데이터베이스 간의 통신을 사용으로 설정하려면 다음 단계를 수행합니다.

  1. 로컬 피어링 게이트웨이(LPG) 생성.

    각 VCN에 LPG를 생성하여 이들 간의 피어링 연결을 설정합니다.

  2. 경로 테이블 구성

    1. 각 VCN에 대해 라우트 규칙을 포함하도록 LPG와 연결된 라우트 테이블을 업데이트합니다.

    2. 대상 CIDR 블록을 다른 대상 VCN의 주소 범위로 설정하고 다음 홉을 해당하는 LPG로 지정합니다.

  3. 프라이빗 뷰를 사용하여 DNS 분석 설정

    1. OCI 콘솔로 이동하여 프라이빗 뷰를 검색하여 두 VCN에 대한 DNS 구성에 액세스합니다.

    2. 각 VCN의 전용 뷰를 열고 영역 항목을 생성합니다.

  4. 각 데이터베이스 시스템에 대한 영역 레코드 만들기.

    각 VCN의 개인 뷰에서 다른 VCN에 있는 데이터베이스 시스템의 FQDN(정규화된 도메인 이름)을 나타내는 영역을 추가합니다.

  5. DNS 레코드 추가.

    1. 새 영역 항목 옆에 있는 3개의 점 아이콘을 누르거나 FQDN 링크를 누릅니다.

    2. 레코드 관리를 선택하고 레코드 추가를 누릅니다.

    3. 유형 A(IPv4 주소)를 선택하고 TTL을 500으로 설정한 다음 반대쪽 VCN에 있는 대상 데이터베이스 시스템의 기본 끝점 IP 주소를 입력합니다.

    4. 레코드 추가를 누른 다음 변경사항 게시를 누릅니다.

작업 3: 서로 다른 VCN 및 다른 지역의 데이터베이스 시스템 간 네트워크 연결 구성

서로 다른 VCN 및 다른 지역에 위치한 PostgreSQL 데이터베이스 시스템과 두 OCI 데이터베이스 간의 통신을 사용으로 설정합니다.

  1. DRG(동적 경로 지정 게이트웨이) 생성

    각 지역에 DRG를 생성하여 해당 VCN에 연결합니다.

  2. 경로 테이블 구성

    1. 각 VCN에 대해 DRG와 연관된 경로 테이블을 업데이트합니다.

    2. 다른 VCN의 대상 CIDR 블록이 있는 경로 규칙을 추가하고 다음 홉을 연결된 DRG로 지정합니다.

  3. 프라이빗 뷰를 사용하여 DNS 분석 설정

    1. OCI 콘솔로 이동하여 프라이빗 뷰를 검색하여 각 VCN의 DNS 설정에 액세스합니다.

    2. 각 VCN에 대한 개인 뷰를 열고 영역 항목을 생성합니다.

  4. 각 데이터베이스 시스템에 대한 영역 레코드 만들기.

    각 VCN의 전용 뷰에서 다른 VCN에 있는 데이터베이스 시스템의 FQDN을 나타내는 영역을 추가합니다.

  5. DNS 레코드 추가.

    1. 새 영역 항목 옆에 있는 3개의 점 아이콘을 누르거나 FQDN 링크를 누릅니다.

    2. 레코드 관리를 선택하고 레코드 추가를 누릅니다.

    3. 유형 A(IPv4 주소)를 선택하고 TTL을 500으로 설정한 다음 반대 데이터베이스 시스템의 기본 끝점 IP 주소를 입력합니다.

    4. 레코드 추가를 누른 다음 변경사항 게시를 누릅니다.

작업 4: 예제로 postgres_fdw 확장을 구성합니다.

  1. 외부 데이터 래퍼(FDW) 서버 생성.

    CREATE SERVER 명령은 postgres_fdw 래퍼를 사용하여 fdw_serv이라는 외부 서버를 정의합니다. 접속 세부정보(예: 소스 PostgreSQL 데이터베이스의 호스트, 데이터베이스 이름 및 포트)를 지정합니다. 이는 대상 PostgreSQL 인스턴스 내의 소스 데이터에 액세스하기 위한 논리적 참조 역할을 합니다.

     CREATE SERVER <fdw_servername> FOREIGN DATA WRAPPER <fdw_servername> OPTIONS (host 'primary.xxxxxx.FQDN_Remote_source_DBSystem.... oci.oraclecloud.com', dbname 'remote_source_dbname', port '5432');

  2. 사용자 매핑.

    CREATE USER MAPPING 명령은 대상 PostgreSQL 사용자(arvindya)를 소스 데이터베이스 인증서와 연관시킵니다. 이렇게 하면 대상 서버가 정의된 외부 서버에 접속할 때 대상 인증이 허용됩니다. 이렇게 하면 실행된 질의 대상이 소스 서버에서 올바르게 인증됩니다.

     CREATE USER MAPPING FOR <user_name> SERVER <fdw_servername> OPTIONS (user <user_name>, password <'user_password'>);

  3. 스키마 소스 임포트.

    IMPORT FOREIGN SCHEMA 명령은 소스 서버의 공용 스키마에서 대상 데이터베이스로 모든 테이블을 외래 테이블로 임포트합니다. 이러한 테이블은 로컬로 나타나지만 소스 데이터베이스에서 실시간으로 데이터를 패치(fetch)합니다.

     IMPORT FOREIGN SCHEMA <schema_name> FROM SERVER <fdw_server_name> INTO public;

예:

-- Create extension
CREATE EXTENSION postgres_fdw;

-- Create FDW Server
CREATE SERVER fdw_serv FOREIGN DATA WRAPPER postgres_fdw OPTIONS (
    host 'primary.xxxxxx.Remote Source DBSystem FQDN.....xxxxx.oci.oraclecloud.com',
    dbname 'source',
    port '5432'
);

-- User Mapping
CREATE USER MAPPING FOR arvindya SERVER fdw_serv OPTIONS (
    user '<user_name>',
    password '<user_password>'
);

-- Import Schema from source
IMPORT FOREIGN SCHEMA public FROM SERVER fdw_serv INTO public;

-- Validate Data
SELECT * FROM emp;

dblink_connect 함수는 제공된 접속 문자열을 사용하여 대상 PostgreSQL 데이터베이스에서 소스 PostgreSQL 데이터베이스로 접속을 설정합니다. 여기에는 데이터베이스 이름, 호스트, 포트, 유저 이름 및 암호와 같은 파라미터가 포함됩니다. 연결은 이후 dblink 작업에 사용되는 고유한 이름(myconn)으로 식별됩니다. 성공한 연결은 원격 질의에 대한 준비 상태를 나타내는 OK를 반환합니다.

SELECT dblink_connect ('Conn_name', 'dbname=<Remote_source_DBName> port=5432 host=primary.xxxxxx..Remote_source_DbSystem FQDN..xxxx.oci.oraclecloud.com user=<user_name> password=<'user_password'>);

예:

CREATE DATABASE target;
\c target
CREATE EXTENSION dblink;

-- Establish Connection
SELECT dblink_connect ('myconn', 'dbname=source port=5432 host=primary.xxxxxx..Remote_source_DbSystem FQDN..xxxx.oci.oraclecloud.com user=<user_name> password=<user_password>');

-- Select from Source
SELECT * FROM dblink('myconn', 'SELECT * FROM testing') AS t(a int);

-- Execute SQL remotely
SELECT * FROM dblink_exec('myconn', 'create table dblinking_table (id int)');
SELECT * FROM dblink_exec('myconn', 'insert into dblinking_table values(generate_series(1,10))');
SELECT * FROM dblink_exec('myconn', 'delete from dblinking_table where id <= 5');

-- Final Validation
SELECT * FROM dblink('myconn', 'SELECT * FROM dblinking_table') AS t(a int);

승인

추가 학습 자원

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

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