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

本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。

在與 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

目標架構

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

  1. 檔案會從應用程式上傳到 S3 儲存貯體。

  2. 所以此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_s3aws_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函數初始化公共變量,如bucketregion。如需程式碼,請參閱其他資訊區段。

DBA /開發人員
創建包裝函數。

創建包裝函數fopenput_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"             ]         }     ] }

創建初始化函數

若要初始化常見變數,例如bucketregion,建立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$;

創建包裝函數

建立fopenput_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 通知