Summary 先決條件和限制 Architecture Tools Epics 相關資源其他資訊

在與 Aurora 後勤相容的上設定甲骨文檔功能

由拉克許拉格哈夫（AWS）和阿努拉達中國（AWS）創建

：Conment PoC 或試驗	來源：Oracle	目標：Aurora PostgreSQL
R 類型重新架構	工作負載：Oracle	Technologies 移轉; 基礎架構; 資料庫
AWS 服務：Amazon S3; Amazon Aurora

Summary

在從 Oracle 到 Amazon Amazon Web Services (AWS) 雲端的 Amazon Aurora PostgreQ 相容版的遷移過程中，您可能會遇到多個挑戰。例如，遷移依賴於 OracleUTL_FILE實用程序始終是一個挑戰。在 Oracle PL/SQL 中，UTL_FILE套件用於檔案作業，例如讀取和寫入，以及與基礎作業系統搭配使用。所以此UTL_FILE公用程式適用於伺服器和用戶端電腦系統。

Amazon Aurora PostgreSQL 相容是受管資料庫產品。因此，您無法存取資料庫伺服器上的檔案。此模式會逐步解說如何整合 Amazon Simple Storage Service (Amazon S3) 和 Amazon Aurora PostgreSQL，以達到UTL_FILE功能。使用此整合，我們可以建立和取用檔案，而不需要使用協力廠商擷取、轉換和載入 (ETL) 工具或服務。

或者，您可以設定 Amazon CloudWatch 察監控和 Amazon SNS 通知。

建議您在生產環境中實作此解決方案之前，先徹底測試此解決方案。

先決條件和限制

先決條件

作用中的 AWS 帳戶
AWS Database Migration Service (AWS DMS) 專業
PL/pgSQL 編碼專業
Amazon Aurora PostgreSQL 相容叢集
S3 儲存貯體

限制

這種模式不提供作為 Oracle 替代品的功能UTL_FILE公用程式。不過，您可以進一步增強步驟和範例程式碼，以達成資料庫現代化目標。

產品版本

Amazon Aurora PostgreSQL 相容版本 11.9

Architecture

目標技術堆疊

Amazon Aurora PostgreSQL 相容
Amazon CloudWatch
Amazon Simple Notification Service (Amazon SNS)
Amazon S3

目標架構

下圖顯示解決方案的高層級表示方法。

檔案會從應用程式上傳到 S3 儲存貯體。
所以此aws_s3擴展使用 PL/pgSQL 訪問數據，並將數據上傳到與 Aurora 後兼容的。

Tools

Amazon Aurora PostgreSQL 相容— Amazon Aurora PostgreSQL 相容版本是與 PostgreSQL 相容的完全受管且符合 ACID 規範的關聯式資料庫引擎。它結合了高端商用資料庫的速度和可靠性，以及開放原始碼資料庫的成本效益。
AWS CLI— AWS Command Line Interface (AWS CLI) 是管理 AWS 服務的統一工具。只要一個工具來下載和設定，就可以從命令列控制多個 AWS 服務，並透過指令碼將服務自動化。
Amazon CloudWatch— Amazon CloudWatch 察監控 Amazon S3 資源和使用。
Amazon S3— Amazon Simple Storage Service (Amazon S3) 是網際網路儲存服務。在這種模式中，Amazon S3 提供了一個儲存層來接收和儲存檔案，以供與 Aurora PostgreQ 相容的叢集之間使用和傳輸。
aws_s3—aws_s3擴充套件整合了與 Amazon S3 和 Aurora 相容的功能。
Amazon SNS— Amazon Simple Notification Service (Amazon SNS) 協調與管理發佈商和用戶端之間的訊息傳遞或傳送。在此模式中，Amazon SNS 用於傳送通知。
pgAdmin— pgAdmin 是 Postgres 的開放原始碼管理工具。pgAdmin 4 提供用於建立、維護和使用資料庫物件的圖形化介面。

Code

為了實現所需的功能，該模式創建具有類似於UTL_FILE。所以此其他資訊區段包含這些函式的程式碼庫。

在代碼中，替換testaurorabucket取代為您的測試 S3 儲存貯體的名稱。Replaceus-east-1與測試 S3 儲存貯體所在的 AWS 區域相同。

Epics

任務	描述	所有技能
設定 IAM 政策	建立 AWS Identity and Access Management (IAM) 政策，以授予對 S3 儲存貯體和其中物件的存取權限。如需程式碼，請參閱其他資訊區段。	AWS 管理員，資料庫管理員
將 Amazon S3 訪問角色添加到 Aurora PostgreSQL。	建立 IAM 角色以存取 Amazon S3 的讀取和寫入存取權。將角色附加到與 Aurora PostgreSQL 相容的叢集。如需詳細資訊，請參閱 AWS 文件。	AWS 管理員，資料庫管理員

任務	描述	所有技能
創建 aws_commons 擴展。	所以此`aws_commons`擴展是`aws_s3`延伸。	DBA, 開發人員
建立 aws_s3 擴充功能。	所以此`aws_s3`延伸與 Amazon S3 互動。	DBA, 開發人員

任務	描述	所有技能
測試將檔案從 Amazon S3 匯入至 Aurora。	若要測試將檔案匯入至與 Aurora PostgreQ 相容的內容，請建立範例 CSV 檔案並將其上傳至 S3 儲存貯體。根據 CSV 檔案建立資料表定義，並使用`aws_s3.table_import_from_s3`函數。	DBA, 開發人員
測試從 Aurora 郵件匯出檔案到 Amazon S3。	若要測試從 Aurora PostgreQL 相容的匯出檔案，請建立測試表格，填入資料，然後使用`aws_s3.query_export_to_s3`函數。	DBA, 開發人員

任務	描述	所有技能
建立公用程式綱要。	該模式將包裝函數保持在一起。執行下列命令以建立結構描述。 `CREATE SCHEMA utl_file_utility;`	DBA, 開發人員
建立檔案類型。	建立`file_type`類型，請使用以下程式碼。 `CREATE TYPE utl_file_utility.file_type AS ( p_path character varying(30), p_file_name character varying );`	DBA /開發人員
創建初始化函數。	所以此`init`函數初始化公共變量，如`bucket`或`region`。如需程式碼，請參閱其他資訊區段。	DBA /開發人員
創建包裝函數。	創建包裝函數`fopen`、`put_line`，以及`fclose`。如需程式碼，請參閱其他資訊區段。	DBA, 開發人員

任務	描述	所有技能
在寫入模式下測試包裝函數。	若要在寫入模式下測試包裝函數，請使用其他資訊區段。	DBA, 開發人員
在追加模式下測試包裝函數。	若要在追加模式下測試包裝函式，請使用其他資訊區段。	DBA, 開發人員

其他資訊

設定 (IAM) 政策

建立下列政策。

政策名稱 JSON

S3IntRead


{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "S3integrationtest",
            "Effect": "Allow",
            "Action": [
                "s3:GetObject",
                "s3:ListBucket"
            ],
            "Resource": [
         "arn:aws:s3:::testaurorabucket/*",
         "arn:aws:s3:::testaurorabucket"
            ]
        }
    ]
}

S3IntWrite


{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "S3integrationtest",
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",                
                "s3:ListBucket"
            ],
            "Resource": [                "arn:aws:s3:::testaurorabucket/*",                "arn:aws:s3:::testaurorabucket"
            ]
        }
    ]
}

創建初始化函數

若要初始化常見變數，例如bucket或region，建立init函數。


CREATE OR REPLACE FUNCTION utl_file_utility.init(
    )
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
BEGIN
      perform set_config
      ( format( '%s.%s','UTL_FILE_UTILITY', 'region' )
      , 'us-east-1'::text
      , false );

      perform set_config
      ( format( '%s.%s','UTL_FILE_UTILITY', 's3bucket' )
      , 'testaurorabucket'::text
      , false );
END;
$BODY$;

創建包裝函數

建立fopen、put_line，以及fclose包裝函數。

fopen


CREATE OR REPLACE FUNCTION utl_file_utility.fopen(
    p_file_name character varying,
    p_path character varying,
    p_mode character DEFAULT 'W'::bpchar,
    OUT p_file_type utl_file_utility.file_type)
    RETURNS utl_file_utility.file_type
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
declare
    v_sql character varying;
    v_cnt_stat integer;
    v_cnt integer;
    v_tabname character varying;
    v_filewithpath character varying;
    v_region character varying;
    v_bucket character varying;

BEGIN
    /*initialize common variable */
    PERFORM utl_file_utility.init();
    v_region := current_setting( format( '%s.%s', 'UTL_FILE_UTILITY', 'region' ) );
    v_bucket :=  current_setting( format( '%s.%s', 'UTL_FILE_UTILITY', 's3bucket' ) );
    
    /* set tabname*/
    v_tabname := substring(p_file_name,1,case when strpos(p_file_name,'.') = 0 then length(p_file_name) else strpos(p_file_name,'.') - 1 end );
    v_filewithpath := case when NULLif(p_path,'') is null then p_file_name else concat_ws('/',p_path,p_file_name) end ;
    raise notice 'v_bucket %, v_filewithpath % , v_region %', v_bucket,v_filewithpath, v_region;
    
    /* APPEND MODE HANDLING; RETURN EXISTING FILE DETAILS IF PRESENT ELSE CREATE AN EMPTY FILE */
    IF p_mode = 'A' THEN
        v_sql := concat_ws('','create temp table if not exists ', v_tabname,' (col1 text)');
        execute v_sql;

        begin
        PERFORM aws_s3.table_import_from_s3 
            ( v_tabname, 
            '',  
            'DELIMITER AS ''#''', 
            aws_commons.create_s3_uri 
            (     v_bucket, 
                v_filewithpath ,
                v_region)
            );
        exception
            when others then
             raise notice 'File load issue ,%',sqlerrm;
             raise;
        end;
        execute concat_ws('','select count(*) from ',v_tabname) into v_cnt;

        IF v_cnt > 0 
        then
            p_file_type.p_path := p_path;
            p_file_type.p_file_name := p_file_name;
        else         
            PERFORM aws_s3.query_export_to_s3('select ''''', 
                            aws_commons.create_s3_uri(v_bucket, v_filewithpath, v_region)            
                              );

            p_file_type.p_path := p_path;
            p_file_type.p_file_name := p_file_name;        
        end if;
        v_sql := concat_ws('','drop table ', v_tabname);        
        execute v_sql;            
    ELSEIF p_mode = 'W' THEN
            PERFORM aws_s3.query_export_to_s3('select ''''', 
                            aws_commons.create_s3_uri(v_bucket, v_filewithpath, v_region)            
                              );
            p_file_type.p_path := p_path;
            p_file_type.p_file_name := p_file_name;
    END IF;    
    
EXCEPTION
        when others then
            p_file_type.p_path := p_path;
            p_file_type.p_file_name := p_file_name;
            raise notice 'fopenerror,%',sqlerrm;
            raise;
END;
$BODY$;

推桿線 (_L)


CREATE OR REPLACE FUNCTION utl_file_utility.put_line(
    p_file_name character varying,
    p_path character varying,
    p_line text,
    p_flag character DEFAULT 'W'::bpchar)
    RETURNS boolean
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
/**************************************************************************
* Write line, p_line in windows format to file, p_fp - with carriage return
* added before new line.
**************************************************************************/
declare
    v_sql varchar;
    v_ins_sql varchar;
    v_cnt INTEGER;
    v_filewithpath character varying;
    v_tabname  character varying;
    v_bucket character varying;
    v_region character varying;    

BEGIN
 PERFORM utl_file_utility.init();

/* check if temp table already exist */

 v_tabname := substring(p_file_name,1,case when strpos(p_file_name,'.') = 0 then length(p_file_name) else strpos(p_file_name,'.') - 1 end );

 v_sql := concat_ws('','select count(1) FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace where n.nspname like ''pg_temp_%''' 
                         ,' AND pg_catalog.pg_table_is_visible(c.oid) AND Upper(relname) = Upper( '''
                         ,  v_tabname ,''' ) ');
  
 execute v_sql into v_cnt;
  
  IF v_cnt = 0 THEN
         v_sql := concat_ws('','create temp table ',v_tabname,' (col text)');
        execute v_sql;
        /* CHECK IF APPEND MODE */
        IF upper(p_flag) = 'A' THEN
            PERFORM utl_file_utility.init();                        
            v_region := current_setting( format( '%s.%s', 'UTL_FILE_UTILITY', 'region' ) );
            v_bucket :=  current_setting( format( '%s.%s', 'UTL_FILE_UTILITY', 's3bucket' ) );
            
            /* set tabname*/            
            v_filewithpath := case when NULLif(p_path,'') is null then p_file_name else concat_ws('/',p_path,p_file_name) end ;            
            
            begin
               PERFORM aws_s3.table_import_from_s3 
                     ( v_tabname, 
                          '',  
                       'DELIMITER AS ''#''', 
                        aws_commons.create_s3_uri 
                           ( v_bucket, 
                               v_filewithpath, 
                               v_region    )
                    );
            exception
                when others then
                    raise notice  'Error Message : %',sqlerrm;
                    raise;
            end;    
        END IF;    
    END IF;
    /* INSERT INTO TEMP TABLE */              
    v_ins_sql := concat_ws('','insert into ',v_tabname,' values(''',p_line,''')');
    execute v_ins_sql;
    RETURN TRUE;
    exception
            when others then
                raise notice  'Error Message : %',sqlerrm;
                raise;
END;
$BODY$;

fclose


CREATE OR REPLACE FUNCTION utl_file_utility.fclose(
    p_file_name character varying,
    p_path character varying)
    RETURNS boolean
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
DECLARE
    v_filewithpath character varying;
    v_bucket character varying;
    v_region character varying;
    v_tabname character varying;
    v_sql character varying;
BEGIN
      PERFORM utl_file_utility.init();
  
    v_region := current_setting( format( '%s.%s', 'UTL_FILE_UTILITY', 'region' ) );
    v_bucket :=  current_setting( format( '%s.%s', 'UTL_FILE_UTILITY', 's3bucket' ) );

    v_tabname := substring(p_file_name,1,case when strpos(p_file_name,'.') = 0 then length(p_file_name) else strpos(p_file_name,'.') - 1 end );
    v_filewithpath := case when NULLif(p_path,'') is null then p_file_name else concat_ws('/',p_path,p_file_name) end ;

    raise notice 'v_bucket %, v_filewithpath % , v_region %', v_bucket,v_filewithpath, v_region ;
    
    /* exporting to s3 */
    perform aws_s3.query_export_to_s3
        (concat_ws('','select * from ',v_tabname,'  order by ctid asc'), 
            aws_commons.create_s3_uri(v_bucket, v_filewithpath, v_region)
        );
    v_sql := concat_ws('','drop table ', v_tabname);
    execute v_sql;    
    RETURN TRUE;
EXCEPTION 
       when others then
     raise notice 'error fclose %',sqlerrm;
     RAISE;
END;
$BODY$;

測試您的設置和包裝函數

使用以下匿名程式碼塊來測試您的設定。

測試寫入模式

下列程式碼會寫入名為s3inttest在 S3 儲存貯體。


do $$
declare
l_file_name varchar := 's3inttest' ;
l_path varchar := 'integration_test' ;
l_mode char(1) := 'W';
l_fs utl_file_utility.file_type ;
l_status boolean;

begin
select * from
utl_file_utility.fopen( l_file_name, l_path , l_mode ) into l_fs ;
raise notice 'fopen : l_fs : %', l_fs;

select * from
utl_file_utility.put_line( l_file_name, l_path ,'this is test file:in s3bucket: for test purpose', l_mode ) into l_status ;
raise notice 'put_line : l_status %', l_status;

select * from utl_file_utility.fclose( l_file_name , l_path ) into l_status ;
raise notice 'fclose : l_status %', l_status;

end;
$$

測試附加模式

下列程式碼會將行附加到s3inttest檔案 (在上一個測試中建立)。


do $$
declare
l_file_name varchar := 's3inttest' ;
l_path varchar := 'integration_test' ;
l_mode char(1) := 'A';
l_fs utl_file_utility.file_type ;
l_status boolean;

begin
select * from
utl_file_utility.fopen( l_file_name, l_path , l_mode ) into l_fs ;
raise notice 'fopen : l_fs : %', l_fs;


select * from
utl_file_utility.put_line( l_file_name, l_path ,'this is test file:in s3bucket: for test purpose : append 1', l_mode ) into l_status ;
raise notice 'put_line : l_status %', l_status;

select * from
utl_file_utility.put_line( l_file_name, l_path ,'this is test file:in s3bucket : for test purpose : append 2', l_mode ) into l_status ;
raise notice 'put_line : l_status %', l_status;

select * from utl_file_utility.fclose( l_file_name , l_path ) into l_status ;
raise notice 'fclose : l_status %', l_status;

end;
$$

Amazon SNS 通知

或者，您可以在 S3 儲存貯體上設定亞馬 Amazon CloudWatch 監控和 Amazon SNS 通知。如需詳細資訊，請參閱「」監控 Amazon S3和設定 Amazon SNS 通知。

您的瀏覽器已停用或無法使用 Javascript。

您必須啟用 Javascript，才能使用 AWS 文件。請參閱您的瀏覽器說明頁以取得說明。

文件慣用形式

從內部部署的 MySQL 遷移到