使用 Amazon Aurora PostgreSQL 聯合查詢、pg_cron 和 Amazon Lambda

亞馬遜雲開發者發表於2023-04-30
SQL

作為資料庫遷移和現代化的一部分，您可以繼續使用儲存的程式和排程作業，將遠端例項中的資料整合到集中式資料儲存中。 Amazon Schema Conversion Tool（Amazon SCT）可幫助您將傳統的 Oracle 和 SQL Server 函式轉換為其等效的開源函式。但是，如何繼續使用儲存的程式從遠端資料庫中提取資料呢？ 您現有的 cron 作業怎麼樣？ 如何處理儲存的程式中的錯誤並通知資料庫管理員？ 您可以使用 postgres_fdw、 pg_cron 和 Amazon_lambda 等 PostgreSQL 擴充套件實現此目的。在這篇博文中，我們演示了一種模式，它允許您對資料庫進行現代化改造並重構現有程式碼。我們使用 Amazon Aurora PostgreSQL 相容版資料庫例項來說明這種模式。

亞馬遜雲科技開發者社群為開發者們提供全球的開發技術資源。這裡有技術文件、開發案例、技術專欄、培訓影片、活動與競賽等。幫助中國開發者對接世界最前沿技術，觀點，和專案，並將中國優秀開發者或技術推薦給全球雲社群。如果你還沒有關注/收藏，看到這裡請一定不要匆匆劃過，點這裡讓它成為你的技術寶庫！

對資料庫進行現代化改造沒有一刀切的方法。您需要仔細規劃自己的轉型之旅，並制定明確的目標和成果。如果在資料庫層處理某些邏輯符合您的業務需求，則可以考慮本文中介紹的方法。有關其他指導，請參閱將 Oracle 資料庫遷移到 Amazon Cloud 和將 Microsoft SQL Server 資料庫遷移到 Amazon Cloud。

PostgreSQL 擴充套件

在開始之前，我們看看我們的解決方案中使用的 PostgreSQL 擴充套件。

postgres_fdw是一個外部資料封裝器，用於訪問遠端 PostgreSQL 伺服器中的資料。Amazon Relational Database Service (Amazon RDS) for PostgreSQL 和 Aurora PostgreSQL 支援此擴充套件。藉助 postgres_fdw，您可以實現聯合查詢，以便從遠端 PostgreSQL 資料庫例項檢索資料、將其儲存在集中式資料庫中或生成報告。

Amazon Lambda 在高度可用的計算基礎設施中執行程式碼，無需預調配或管理伺服器和作業系統維護。Lambda 中的程式碼以函式形式組織，支援多種程式語言，例如 Python、Node.js、Java 和 Ruby。aws_lambda擴充套件提供從 Aurora PostgreSQL 呼叫 Lambda 函式的功能。此擴充套件還需要 aws_commons擴充套件，它為 aws_lambda 和許多其他 PostgreSQL 的 Aurora 擴充套件提供幫助程式函式。如果儲存過程中出現錯誤，您可以將錯誤訊息傳送到 Lambda 函式，然後使用 Amazon Simple Notification Service（Amazon SNS）向資料庫管理員傳送通知。

您可以使用 pg_cron 來排程 SQL 命令，它使用與標準 CRON 表示式相同的語法。我們可以使用此擴充套件排程儲存的程式並自動執行日常維護任務。

解決方案概覽

源資料庫由我們要檢索並載入到報告資料庫中的表和資料組成。pg_cron 擴充套件根據預定義的計劃執行儲存的程式。儲存的程式基於預定義的業務邏輯複製資料。如果遇到任何錯誤，它將呼叫 Lambda 函式，向訂閱了 SNS 主題的使用者傳送錯誤通知。下圖展示了該解決方案的架構和流程。

在這篇博文中，我們將引導您完成使用 Amazon CloudFormation 建立資源、配置儲存的程式和測試解決方案的步驟。

先決條件

請務必完成以下必備步驟：

  1. 設定 Amazon 命令列介面（Amazon CLI）以執行用於與 Amazon 資源互動的命令。
  2. 擁有與您的 Amazon 賬戶中的資源進行互動的適當許可權。

使用 Amazon CloudFormation 建立資源

此解決方案的 CloudFormation 模板部署了以下關鍵資源：

  • 用於源資料庫和報告資料庫的兩個 Aurora PostgreSQL 叢集，包含資料庫表和儲存的程式
  • 用於將錯誤訊息轉發到 Amazon SNS 的 Lambda 函式
  • 電子郵件通知的 SNS 主題
  • Amazon Cloud9 例項，用於連線到資料庫進行設定和測試。

在執行此解決方案之前，使用 Amazon 定價計算器估算成本。部署的資源不符合免費套餐的條件，但如果您選擇堆疊預設設定，假設您在一小時內清理了堆疊，則所產生的費用應低於 3.00 美元。

要建立資源，請完成以下步驟：

  1. 透過從終端執行以下命令克隆 GitHub 專案：
git clone https://github.com/aws-samples/amazon-aurora-postgresql-stored-proc-pgcron.git
cd amazon-aurora-postgresql-stored-proc-pgcron
  1. 使用以下程式碼部署 Amazon CloudFormation 資源。將 youreamil@example.com 替換為有效的電子郵件地址。
aws cloudformation create-stack \
--stack-name AmazonAuroraPostgreSQLStoredProc \
--template-body \
file://AmazonAuroraPostgreSQLStoredProc.yaml \
--parameters \
ParameterKey=ErrorEmail,ParameterValue="youremail@example.com" \
--capabilities CAPABILITY_IAM

資源預調配大約需要 15 到 20 分鐘才能完成。您可以前往 Amazon CloudFormation 控制檯並驗證狀態是否顯示為 CREATE_COMPLETE，從而確保成功部署堆疊。

圖片

建立堆疊時，您會收到一封確認訂閱 SNS 的電子郵件。

  1. 在電子郵件中選擇確認訂閱。

圖片
將開啟一個瀏覽器視窗，其中包含您的訂閱確認。

配置儲存的程式

要配置儲存的程式，請完成以下步驟：

  1. 在 Amazon Cloud9 控制檯的 Your environments（您的環境）下，選擇環境 PostgreSQLInstance。
  2. 選擇 Open IDE（開啟 IDE）。 這將開啟一個 IDE，用於配置、部署和測試儲存的程式。
  3. 在您的 Cloud9 終端中，執行以下命令以克隆儲存庫並安裝所需的工具：
git clone https://github.com/aws-samples/amazon-aurora-postgresql-stored-proc-pgcron.git 
cd amazon-aurora-postgresql-stored-proc-pgcron/scripts
sh install-db-tools.sh

該指令碼需要 5 分鐘來安裝所有必需的工具。在進入下一步之前，請確保安裝已完成。

  1. 執行以下命令初始化環境變數：

source ~/.bashrc

  1. 透過執行以下 shell 指令碼命令建立源資料庫物件和報告資料庫物件：

sh initialize-source-reporting-dbs.sh

此指令碼建立 employee 表和 department 表，並在源資料庫中插入一些示例記錄。

指令碼在源資料庫中建立資料庫物件後，它會在 reporting 資料庫中建立 employee 表以及 employee_sp、error_handler_sp 和 schedule_sp_job 儲存的程式。作為最後一步，它將建立 postgres_fdw 擴充套件、外部伺服器、使用者對映和外部表，以便從源資料庫中提取資料。要了解有關 postgres_fdw 的更多資訊，請參閱 PostgreSQL 文件。

sh connect-source-db.sh
\dt
\d+ department
\d+ employee

employee 表儲存原始資料，其中可能包含空值和重複值。department 表用作部門名稱的查詢表。

  1. 使用以下命令退出源資料庫：
    exit
  2. 逐個執行以下命令，觀察報告資料庫中儲存的程式和表：
sh connect-reporting-db.sh
\dfp
\d+ employee

employee_sp 儲存的程式驗證員工源表中的原始資料並將其複製到報告資料庫中的員工表。error_handler_sp 處理錯誤並向註冊的電子郵件地址傳送通知。schedule_sp_job 透過建立 cron 作業自動排程 employee_sp程式的執行。

  1. 使用以下命令退出資料庫：
    exit

測試儲存的程式

我們建立了所有必需的表和儲存的程式之後，就可以測試解決方案了。執行以下 shell 指令碼：

sh execute_sp.sh

這將呼叫報告資料庫中的 employee_sp 儲存的程式。它使用以下程式碼驗證員工和部門資料並將其從源資料庫複製到報告資料庫中的 employee 表：

insert into employee (employee_id,first_name,last_name,dob,badge_id,salary,dept_name)
        select employee_id, first_name, last_name,dob,replace(badge_id,''-'',''''),salary, dfdw.dept_name
        from employee_fdw efdw, department_fdw dfdw
        where efdw.dept_id = dfdw.dept_id
        and efdw.first_name is not null
        and efdw.last_name is not null
        and efdw.badge_id is not null
        and dfdw.dept_name is not null
        and efdw.salary>0;

逐個執行以下命令，驗證報告資料庫的 employee 表中插入的記錄：

sh connect-reporting-db.sh
select * from employee;

使用以下命令退出資料庫：

exit

測試錯誤通知

源表可能包含重複的記錄，我們不希望在報告資料庫中插入重複的記錄。您可以驗證儲存的程式在嘗試將重複記錄插入報告資料庫的員工表時是否會引發錯誤併傳送電子郵件通知。

我們透過執行以下 shell 指令碼來模擬錯誤場景：

sh execute_sp_using_duplicates.sh

該指令碼在源資料庫的 employee 表中插入一條重複的記錄，然後執行 execute_sp.sh 呼叫 employee_sp() 儲存的程式將資料從源資料庫複製到遠端資料庫。

在報告資料庫中插入重複記錄時，會發生主鍵衝突。此異常會在 exception 塊中捕獲，並呼叫 error_handler_sp 儲存的程式。請參閱以下程式碼：

exception
when others then
    call error_handler_sp('TIME:  '||clock_timestamp()||' / PROCEDURE:  '||v_proc_name||' 
        / MESSAGE:  '||v_message||' / EXCEPTION:  '||v_error_exception||' / HINT:  '||v_error_hint);
end;

呼叫 error_handler_sp 儲存的程式時，如果不存在，它將建立 aws_lambda 擴充套件。然後它將錯誤訊息傳遞給呼叫該函式的 Lambda 函式 ExceptionLambda。

Lambda 函式將錯誤訊息釋出到 SNS 主題。您會收到一封主題為“儲存的程式錯誤”的電子郵件，通知您在嘗試插入重複記錄時出現異常。

排程您的儲存的程式

在生產環境中，您可能希望排程儲存的程式以自動方式執行。

  1. 執行以下 shell 指令碼以排程儲存的程式的執行：

sh schedule_pgcron_job.sh

該指令碼重新整理資料庫物件以進行測試，並呼叫 schedule_sp_job 儲存的程式。schedule_sp_job 建立 pg_cron 擴充套件（如果 pg_cron 不存在），並排程每 10 分鐘執行一次 employee_sp 儲存的程式的 cron 作業。

  1. 在報告資料庫中執行以下 SQL 查詢，以確認 cron 作業的建立。我們使用 cron 表示式 /10 * 來允許作業每 10 分鐘執行一次。
sh connect-reporting-db.sh
select * from cron.job;
  1. 您可以使用以下 SQL 查詢檢視計劃作業的狀態：
select jobid, username, status, return_message, start_time from cron.job_run_details;

10 分鐘後，清理後的資料將填充到報告資料庫的 employee 表中。

  1. 現在，您可以透過執行以下 SQL 命令來取消排程 cron 作業：
select cron.unschedule ('Execute employee_sp');

使用 pg_cron，您可以定期排程 SQL 命令的執行以執行重複性任務。

清理

為避免產生持續的費用，請從 Amazon CloudFormation 控制檯中刪除 AmazonAuroraPostgreSQLStoredProc 堆疊來清理基礎設施。刪除作為本練習的先決條件而建立的任何其他資源。

結論

在這篇博文中，我們演示瞭如何使用 Aurora PostgreSQL 擴充套件（例如 postgres_fdw、pg_cron 和 aws_lambda）對儲存的程式進行現代化改造。Aurora PostgreSQL 擴充套件透過提供與商業資料庫同等的功能來增強資料庫開發體驗。在規劃現代化之旅時，請仔細考慮您的業務目標和成果。

有關 Aurora 擴充套件的更多資訊，請參閱使用擴充套件和外部資料封裝器。有關使用資料庫觸發器透過 Lambda 和 Amazon SNS 啟用近實時通知的資訊，請參閱使用[資料庫觸發器]、Amazon Lambda 和 Amazon SNS 啟用來自 Amazon Aurora PostgreSQL 的近實時通知。

告訴我們這篇博文對您的資料庫現代化之旅有何幫助。

關於作者

Prathap Thoguru是 Amazon Web Services 的一名企業解決方案構架師。他在 IT 行業擁有 15 年以上的經驗，是一名已獲 9 項 Amazon 認證的專業人員。他幫助客戶將本地工作負載遷移到 Amazon Cloud。

Kishore Dhamodaran 是 Amazon Web Services 的高階解決方案架構師。Kishore 利用他多年的行業和雲經驗，幫助客戶制定雲企業戰略和遷移之旅。

文章來源：https://dev.amazoncloud.cn/column/article/6309e07b76658473a32...

相關文章