对数据库中存储的程序进行现代化改造,以使用 Amazon Aurora PostgreSQL 联合查询、pg_cron 和 Amazon Lambda

0
0
{"value":"作为数据库[迁移和现代化](https://docs.aws.amazon.com/prescriptive-guidance/latest/migration-retiring-applications/apg-gloss.html#apg.migration.terms)的一部分,您可以继续使用存储的程序和调度作业,将远程实例中的数据整合到集中式数据存储中。 [Amazon Schema Conversion Tool(Amazon SCT)](https://aws.amazon.com/dms/schema-conversion-tool/)可帮助您将传统的 Oracle 和 SQL Server 函数转换为其等效的开源函数。但是,如何继续使用存储的程序从远程数据库中提取数据呢? 您现有的 cron 作业怎么样? 如何处理存储的程序中的错误并通知数据库管理员? 您可以使用 [postgres_fdw](https://www.postgresql.org/docs/14/postgres-fdw.html)、 [pg_cron](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_pg_cron.html) 和 [Amazon_lambda](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/PostgreSQL-Lambda.html) 等 PostgreSQL 扩展实现此目的。在这篇博文中,我们演示了一种模式,它允许您对数据库进行现代化改造并重构现有代码。我们使用 [Amazon Aurora PostgreSQL 兼容版](https://aws.amazon.com/rds/aurora/postgresql-features/)数据库实例来说明这种模式。\n\n对数据库进行现代化改造没有一刀切的方法。您需要仔细规划自己的[转型之旅](https://docs.aws.amazon.com/whitepapers/latest/overview-aws-cloud-adoption-framework/your-cloud-transformation-journey.html),并制定明确的目标和成果。如果在数据库层处理某些逻辑符合您的业务需求,则可以考虑本文中介绍的方法。有关其他指导,请参阅[将 Oracle 数据库迁移到 Amazon Cloud](https://docs.aws.amazon.com/prescriptive-guidance/latest/migration-oracle-database/welcome.html) 和将 Microsoft SQL Server 数据库迁移到 Amazon Cloud。\n\n#### **PostgreSQL 扩展**\n\n在开始之前,我们看看我们的解决方案中使用的 PostgreSQL 扩展。\n\n```postgres_fdw```是一个外部数据封装器,用于访问远程 PostgreSQL 服务器中的数据。[Amazon Relational Database Service (Amazon RDS) for PostgreSQL](https://aws.amazon.com/rds/postgresql/) 和 Aurora PostgreSQL 支持此扩展。借助 ```postgres_fdw```,您可以实现[联合查询](https://aws.amazon.com/blogs/database/federated-query-support-for-amazon-aurora-postgresql-and-amazon-rds-for-postgresql/),以便从远程 PostgreSQL 数据库实例检索数据、将其存储在集中式数据库中或生成报告。\n\n[Amazon Lambda](https://aws.amazon.com/lambda/) 在高度可用的计算基础设施中运行代码,无需预调配或管理服务器和操作系统维护。Lambda 中的代码以函数形式组织,支持多种编程语言,例如 Python、Node.js、Java 和 Ruby。```aws_lambda```扩展提供从 Aurora PostgreSQL 调用 Lambda 函数的功能。此扩展还需要 ```aws_commons```扩展,它为 ```aws_lambda``` 和许多其他 PostgreSQL 的 Aurora 扩展提供帮助程序函数。如果存储过程中出现错误,您可以将错误消息发送到 Lambda 函数,然后使用 [Amazon Simple Notification Service](https://aws.amazon.com/sns)(Amazon SNS)向数据库管理员发送通知。\n\n您可以使用 ```pg_cron``` 来调度 SQL 命令,它使用与标准 CRON 表达式相同的语法。我们可以使用此扩展调度存储的程序并自动执行日常维护任务。\n\n#### **解决方案概览**\n\n\n源数据库由我们要检索并加载到报告数据库中的表和数据组成。```pg_cron``` 扩展根据预定义的计划运行存储的程序。存储的程序基于预定义的业务逻辑复制数据。如果遇到任何错误,它将调用 Lambda 函数,向订阅了 SNS 主题的用户发送错误通知。下图展示了该解决方案的架构和流程。\n\n![image.png](https://dev-media.amazoncloud.cn/5448b53896894514ac96d5b5c71ca3be_image.png)\n\n在这篇博文中,我们将引导您完成使用 [Amazon CloudFormation](http://aws.amazon.com/cloudformation) 创建资源、配置存储的程序和测试解决方案的步骤。\n\n\n#### **先决条件**\n\n\n请务必完成以下必备步骤:\n\n1. 设置 [Amazon 命令行界面](https://aws.amazon.com/cli/)(Amazon CLI)以运行用于与 Amazon 资源交互的命令。\n2. 拥有与您的 Amazon 账户中的资源进行交互的适当权限。\n\n#### **使用 Amazon CloudFormation 创建资源**\n\n此解决方案的 CloudFormation 模板部署了以下关键资源:\n\n- 用于源数据库和报告数据库的两个 Aurora PostgreSQL 集群,包含数据库表和存储的程序\n- 用于将错误消息转发到 Amazon SNS 的 Lambda 函数\n- 电子邮件通知的 SNS 主题\n- [Amazon Cloud9](https://aws.amazon.com/cloud9/) 实例,用于连接到数据库进行设置和测试。\n\n在运行此解决方案之前,使用 [Amazon 定价计算器](https://calculator.aws/#/)估算成本。部署的资源不符合免费套餐的条件,但如果您选择堆栈默认设置,假设您在一小时内清理了堆栈,则所产生的费用应低于 3.00 美元。\n\n要创建资源,请完成以下步骤:\n\n1. 通过从终端运行以下命令克隆 GitHub 项目:\n\n```\ngit clone https://github.com/aws-samples/amazon-aurora-postgresql-stored-proc-pgcron.git\ncd amazon-aurora-postgresql-stored-proc-pgcron\n```\n\n2. 使用以下代码部署 Amazon CloudFormation 资源。将 ```youreamil@example.com``` 替换为有效的电子邮件地址。\n\n```\naws cloudformation create-stack \\\n--stack-name AmazonAuroraPostgreSQLStoredProc \\\n--template-body \\\nfile://AmazonAuroraPostgreSQLStoredProc.yaml \\\n--parameters \\\nParameterKey=ErrorEmail,ParameterValue=\"youremail@example.com\" \\\n--capabilities CAPABILITY_IAM\n```\n\n资源预调配大约需要 15 到 20 分钟才能完成。您可以前往 Amazon CloudFormation 控制台并验证状态是否显示为 ```CREATE_COMPLETE```,从而确保成功部署堆栈。\n\n![image.png](https://dev-media.amazoncloud.cn/20a11467d03a469296ee727e3b40c1f1_image.png)\n\n创建堆栈时,您会收到一封确认订阅 SNS 的电子邮件。\n\n3. 在电子邮件中选择确认订阅。\n\n![image.png](https://dev-media.amazoncloud.cn/e0101ec41b164e21852dbe4659472e86_image.png)\n\n将打开一个浏览器窗口,其中包含您的订阅确认。\n\n#### **配置存储的程序**\n\n要配置存储的程序,请完成以下步骤:\n\n1. 在 Amazon Cloud9 控制台的 **Your environments**(您的环境)下,选择环境 ```PostgreSQLInstance```。\n2. 选择 **Open IDE**(打开 IDE)。\n这将打开一个 IDE,用于配置、部署和测试存储的程序。\n3. 在您的 Cloud9 终端中,运行以下命令以克隆存储库并安装所需的工具:\n\n```\ngit clone https://github.com/aws-samples/amazon-aurora-postgresql-stored-proc-pgcron.git \ncd amazon-aurora-postgresql-stored-proc-pgcron/scripts\nsh install-db-tools.sh\n```\n\n该脚本需要 5 分钟来安装所有必需的工具。在进入下一步之前,请确保安装已完成。\n\n![image.png](https://dev-media.amazoncloud.cn/d115abfdf3e342efa2c7bbddeb6ecb48_image.png)\n\n4. 运行以下命令初始化环境变量:\n\n```source ~/.bashrc```\n\n5. 通过运行以下 shell 脚本命令创建源数据库对象和报告数据库对象:\n\n```sh initialize-source-reporting-dbs.sh```\n\n此脚本创建 ```employee``` 表和 ```department``` 表,并在源数据库中插入一些示例记录。\n\n脚本在源数据库中创建数据库对象后,它会在 ```reporting``` 数据库中创建 ```employee``` 表以及 ```employee_sp```、```error_handler_sp``` 和 ```schedule_sp_job``` 存储的程序。作为最后一步,它将创建 ```postgres_fdw``` 扩展、外部服务器、用户映射和外部表,以便从源数据库中提取数据。要了解有关 ```postgres_fdw``` 的更多信息,请参阅 [PostgreSQL](https://www.postgresql.org/docs/14/postgres-fdw.html) 文档。\n\n```\nsh connect-source-db.sh\n\\dt\n\\d+ department\n\\d+ employee\n```\n\n![image.png](https://dev-media.amazoncloud.cn/dd1f3e0392ac4410846c91b62f48c390_image.png)\n\n```employee``` 表存储原始数据,其中可能包含空值和重复值。```department``` 表用作部门名称的查找表。\n\n7. 使用以下命令退出源数据库:\n\n```exit```\n\n8. 逐个运行以下命令,观察报告数据库中存储的程序和表:\n\n```\nsh connect-reporting-db.sh\n\\dfp\n\\d+ employee\n```\n\n![image.png](https://dev-media.amazoncloud.cn/407155585da0425bad5ed6944432f609_image.png)\n\n```employee_sp``` 存储的程序验证员工源表中的原始数据并将其复制到报告数据库中的员工表。```error_handler_sp``` 处理错误并向注册的电子邮件地址发送通知。```schedule_sp_job``` 通过创建 cron 作业自动调度 ```employee_sp```程序的运行。\n\n9. 使用以下命令退出数据库:\n\n```exit```\n\n#### **测试存储的程序**\n\n我们创建了所有必需的表和存储的程序之后,就可以测试解决方案了。运行以下 shell 脚本:\n\n```sh execute_sp.sh```\n\n这将调用报告数据库中的 ```employee_sp``` 存储的程序。它使用以下代码验证员工和部门数据并将其从源数据库复制到报告数据库中的 ```employee``` 表:\n\n```\ninsert into employee (employee_id,first_name,last_name,dob,badge_id,salary,dept_name)\n select employee_id, first_name, last_name,dob,replace(badge_id,''-'',''''),salary, dfdw.dept_name\n from employee_fdw efdw, department_fdw dfdw\n where efdw.dept_id = dfdw.dept_id\n and efdw.first_name is not null\n and efdw.last_name is not null\n and efdw.badge_id is not null\n and dfdw.dept_name is not null\n and efdw.salary>0;\n```\n\n逐个运行以下命令,验证报告数据库的 ```employee``` 表中插入的记录:\n\n```\nsh connect-reporting-db.sh\nselect * from employee;\n```\n\n![image.png](https://dev-media.amazoncloud.cn/0104a2270901467ea64308649414fa5d_image.png)\n\n使用以下命令退出数据库:\n\n```exit```\n\n#### **测试错误通知**\n\n源表可能包含重复的记录,我们不希望在报告数据库中插入重复的记录。您可以验证存储的程序在尝试将重复记录插入报告数据库的员工表时是否会引发错误并发送电子邮件通知。\n\n我们通过运行以下 shell 脚本来模拟错误场景:\n\n```sh execute_sp_using_duplicates.sh```\n\n该脚本在源数据库的 ```employee``` 表中插入一条重复的记录,然后运行 ```execute_sp.sh``` 调用 ```employee_sp()``` 存储的程序将数据从源数据库复制到远程数据库。\n\n在报告数据库中插入重复记录时,会发生主键冲突。此异常会在 ```exception``` 块中捕获,并调用 ```error_handler_sp``` 存储的程序。请参阅以下代码:\n\n```\nexception\nwhen others then\n call error_handler_sp('TIME: '||clock_timestamp()||' / PROCEDURE: '||v_proc_name||' \n / MESSAGE: '||v_message||' / EXCEPTION: '||v_error_exception||' / HINT: '||v_error_hint);\nend;\n```\n\n调用 ```error_handler_sp``` 存储的程序时,如果不存在,它将创建 ```aws_lambda``` 扩展。然后它将错误消息传递给调用该函数的 Lambda 函数 ```ExceptionLambda```。\n\nLambda 函数将错误消息发布到 SNS 主题。您会收到一封主题为“存储的程序错误”的电子邮件,通知您在尝试插入重复记录时出现异常。\n\n#### **调度您的存储的程序**\n\n在生产环境中,您可能希望调度存储的程序以自动方式运行。\n\n1. 运行以下 shell 脚本以调度存储的程序的运行:\n\n```\nsh schedule_pgcron_job.sh\n```\n\n该脚本刷新数据库对象以进行测试,并调用 ```schedule_sp_job``` 存储的程序。```schedule_sp_job``` 创建 ```pg_cron``` 扩展(如果 pg_cron 不存在),并调度每 10 分钟运行一次 ```employee_sp``` 存储的程序的 cron 作业。\n\n2. 在报告数据库中运行以下 SQL 查询,以确认 cron 作业的创建。我们使用 cron 表达式 ```*/10 * * * *``` 来允许作业每 10 分钟运行一次。\n\n```\nsh connect-reporting-db.sh\nselect * from cron.job;\n```\n\n3. 您可以使用以下 SQL 查询查看计划作业的状态:\n\n```\nselect jobid, username, status, return_message, start_time from cron.job_run_details;\n```\n\n10 分钟后,清理后的数据将填充到报告数据库的 ```employee``` 表中。\n\n4. 现在,您可以通过运行以下 SQL 命令来取消调度 cron 作业:\n\n```\nselect cron.unschedule ('Execute employee_sp');\n```\n\n使用 pg_cron,您可以定期调度 SQL 命令的执行以执行重复性任务。\n\n#### **清理**\n\n为避免产生持续的费用,请从 Amazon CloudFormation 控制台中[删除](https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/cfn-console-delete-stack.html) ```AmazonAuroraPostgreSQLStoredProc``` 堆栈来清理基础设施。删除作为本练习的先决条件而创建的任何其他资源。\n\n#### **结论**\n\n在这篇博文中,我们演示了如何使用 Aurora PostgreSQL 扩展(例如 ```postgres_fdw```、```pg_cron``` 和 ```aws_lambda```)对存储的程序进行现代化改造。Aurora PostgreSQL 扩展通过提供与商业数据库同等的功能来增强数据库开发体验。在规划现代化之旅时,请仔细考虑您的业务目标和成果。\n\n有关 Aurora 扩展的更多信息,请参阅[使用扩展和外部数据封装器](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Appendix.PostgreSQL.CommonDBATasks.html)。有关使用数据库触发器通过 Lambda 和 Amazon SNS 启用近实时通知的信息,请参阅[使用数据库触发器、Amazon Lambda 和 Amazon SNS 启用来自 Amazon Aurora PostgreSQL 的近实时通知](https://aws.amazon.com/blogs/database/enable-near-real-time-notifications-from-amazon-aurora-postgresql-by-using-database-triggers-aws-lambda-and-amazon-sns/)。\n\n告诉我们这篇博文对您的数据库现代化之旅有何帮助。\n\n**关于作者**\n\n![image.png](https://dev-media.amazoncloud.cn/709e806e18a149a5919cb922a5679267_image.png)\n\n**Prathap Thoguru** 是 Amazon Web Services 的一名企业解决方案构架师。他在 IT 行业拥有 15 年以上的经验,是一名已获 9 项 Amazon 认证的专业人员。他帮助客户将本地工作负载迁移到 Amazon Cloud。\n\n![image.png](https://dev-media.amazoncloud.cn/b4e3d00e74e34845987aa8f2eece5313_image.png)\n\n**Kishore Dhamodaran** 是 Amazon Web Services 的高级解决方案架构师。Kishore 利用他多年的行业和云经验,帮助客户制定云企业战略和迁移之旅。","render":"<p>作为数据库<a href=\"https://docs.aws.amazon.com/prescriptive-guidance/latest/migration-retiring-applications/apg-gloss.html#apg.migration.terms\" target=\"_blank\">迁移和现代化</a>的一部分,您可以继续使用存储的程序和调度作业,将远程实例中的数据整合到集中式数据存储中。 <a href=\"https://aws.amazon.com/dms/schema-conversion-tool/\" target=\"_blank\">Amazon Schema Conversion Tool(Amazon SCT)</a>可帮助您将传统的 Oracle 和 SQL Server 函数转换为其等效的开源函数。但是,如何继续使用存储的程序从远程数据库中提取数据呢? 您现有的 cron 作业怎么样? 如何处理存储的程序中的错误并通知数据库管理员? 您可以使用 <a href=\"https://www.postgresql.org/docs/14/postgres-fdw.html\" target=\"_blank\">postgres_fdw</a>、 <a href=\"https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_pg_cron.html\" target=\"_blank\">pg_cron</a> 和 <a href=\"https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/PostgreSQL-Lambda.html\" target=\"_blank\">Amazon_lambda</a> 等 PostgreSQL 扩展实现此目的。在这篇博文中,我们演示了一种模式,它允许您对数据库进行现代化改造并重构现有代码。我们使用 <a href=\"https://aws.amazon.com/rds/aurora/postgresql-features/\" target=\"_blank\">Amazon Aurora PostgreSQL 兼容版</a>数据库实例来说明这种模式。</p>\n<p>对数据库进行现代化改造没有一刀切的方法。您需要仔细规划自己的<a href=\"https://docs.aws.amazon.com/whitepapers/latest/overview-aws-cloud-adoption-framework/your-cloud-transformation-journey.html\" target=\"_blank\">转型之旅</a>,并制定明确的目标和成果。如果在数据库层处理某些逻辑符合您的业务需求,则可以考虑本文中介绍的方法。有关其他指导,请参阅<a href=\"https://docs.aws.amazon.com/prescriptive-guidance/latest/migration-oracle-database/welcome.html\" target=\"_blank\">将 Oracle 数据库迁移到 Amazon Cloud</a> 和将 Microsoft SQL Server 数据库迁移到 Amazon Cloud。</p>\n<h4><a id=\"PostgreSQL__4\"></a><strong>PostgreSQL 扩展</strong></h4>\n<p>在开始之前,我们看看我们的解决方案中使用的 PostgreSQL 扩展。</p>\n<p><code>postgres_fdw</code>是一个外部数据封装器,用于访问远程 PostgreSQL 服务器中的数据。<a href=\"https://aws.amazon.com/rds/postgresql/\" target=\"_blank\">Amazon Relational Database Service (Amazon RDS) for PostgreSQL</a> 和 Aurora PostgreSQL 支持此扩展。借助 <code>postgres_fdw</code>,您可以实现<a href=\"https://aws.amazon.com/blogs/database/federated-query-support-for-amazon-aurora-postgresql-and-amazon-rds-for-postgresql/\" target=\"_blank\">联合查询</a>,以便从远程 PostgreSQL 数据库实例检索数据、将其存储在集中式数据库中或生成报告。</p>\n<p><a href=\"https://aws.amazon.com/lambda/\" target=\"_blank\">Amazon Lambda</a> 在高度可用的计算基础设施中运行代码,无需预调配或管理服务器和操作系统维护。Lambda 中的代码以函数形式组织,支持多种编程语言,例如 Python、Node.js、Java 和 Ruby。<code>aws_lambda</code>扩展提供从 Aurora PostgreSQL 调用 Lambda 函数的功能。此扩展还需要 <code>aws_commons</code>扩展,它为 <code>aws_lambda</code> 和许多其他 PostgreSQL 的 Aurora 扩展提供帮助程序函数。如果存储过程中出现错误,您可以将错误消息发送到 Lambda 函数,然后使用 <a href=\"https://aws.amazon.com/sns\" target=\"_blank\">Amazon Simple Notification Service</a>(Amazon SNS)向数据库管理员发送通知。</p>\n<p>您可以使用 <code>pg_cron</code> 来调度 SQL 命令,它使用与标准 CRON 表达式相同的语法。我们可以使用此扩展调度存储的程序并自动执行日常维护任务。</p>\n<h4><a id=\"_14\"></a><strong>解决方案概览</strong></h4>\n<p>源数据库由我们要检索并加载到报告数据库中的表和数据组成。<code>pg_cron</code> 扩展根据预定义的计划运行存储的程序。存储的程序基于预定义的业务逻辑复制数据。如果遇到任何错误,它将调用 Lambda 函数,向订阅了 SNS 主题的用户发送错误通知。下图展示了该解决方案的架构和流程。</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/5448b53896894514ac96d5b5c71ca3be_image.png\" alt=\"image.png\" /></p>\n<p>在这篇博文中,我们将引导您完成使用 <a href=\"http://aws.amazon.com/cloudformation\" target=\"_blank\">Amazon CloudFormation</a> 创建资源、配置存储的程序和测试解决方案的步骤。</p>\n<h4><a id=\"_24\"></a><strong>先决条件</strong></h4>\n<p>请务必完成以下必备步骤:</p>\n<ol>\n<li>设置 <a href=\"https://aws.amazon.com/cli/\" target=\"_blank\">Amazon 命令行界面</a>(Amazon CLI)以运行用于与 Amazon 资源交互的命令。</li>\n<li>拥有与您的 Amazon 账户中的资源进行交互的适当权限。</li>\n</ol>\n<h4><a id=\"_Amazon_CloudFormation__32\"></a><strong>使用 Amazon CloudFormation 创建资源</strong></h4>\n<p>此解决方案的 CloudFormation 模板部署了以下关键资源:</p>\n<ul>\n<li>用于源数据库和报告数据库的两个 Aurora PostgreSQL 集群,包含数据库表和存储的程序</li>\n<li>用于将错误消息转发到 Amazon SNS 的 Lambda 函数</li>\n<li>电子邮件通知的 SNS 主题</li>\n<li><a href=\"https://aws.amazon.com/cloud9/\" target=\"_blank\">Amazon Cloud9</a> 实例,用于连接到数据库进行设置和测试。</li>\n</ul>\n<p>在运行此解决方案之前,使用 <a href=\"https://calculator.aws/#/\" target=\"_blank\">Amazon 定价计算器</a>估算成本。部署的资源不符合免费套餐的条件,但如果您选择堆栈默认设置,假设您在一小时内清理了堆栈,则所产生的费用应低于 3.00 美元。</p>\n<p>要创建资源,请完成以下步骤:</p>\n<ol>\n<li>通过从终端运行以下命令克隆 GitHub 项目:</li>\n</ol>\n<pre><code class=\"lang-\">git clone https://github.com/aws-samples/amazon-aurora-postgresql-stored-proc-pgcron.git\ncd amazon-aurora-postgresql-stored-proc-pgcron\n</code></pre>\n<ol start=\"2\">\n<li>使用以下代码部署 Amazon CloudFormation 资源。将 <code>youreamil@example.com</code> 替换为有效的电子邮件地址。</li>\n</ol>\n<pre><code class=\"lang-\">aws cloudformation create-stack \\\n--stack-name AmazonAuroraPostgreSQLStoredProc \\\n--template-body \\\nfile://AmazonAuroraPostgreSQLStoredProc.yaml \\\n--parameters \\\nParameterKey=ErrorEmail,ParameterValue=&quot;youremail@example.com&quot; \\\n--capabilities CAPABILITY_IAM\n</code></pre>\n<p>资源预调配大约需要 15 到 20 分钟才能完成。您可以前往 Amazon CloudFormation 控制台并验证状态是否显示为 <code>CREATE_COMPLETE</code>,从而确保成功部署堆栈。</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/20a11467d03a469296ee727e3b40c1f1_image.png\" alt=\"image.png\" /></p>\n<p>创建堆栈时,您会收到一封确认订阅 SNS 的电子邮件。</p>\n<ol start=\"3\">\n<li>在电子邮件中选择确认订阅。</li>\n</ol>\n<p><img src=\"https://dev-media.amazoncloud.cn/e0101ec41b164e21852dbe4659472e86_image.png\" alt=\"image.png\" /></p>\n<p>将打开一个浏览器窗口,其中包含您的订阅确认。</p>\n<h4><a id=\"_76\"></a><strong>配置存储的程序</strong></h4>\n<p>要配置存储的程序,请完成以下步骤:</p>\n<ol>\n<li>在 Amazon Cloud9 控制台的 <strong>Your environments</strong>(您的环境)下,选择环境 <code>PostgreSQLInstance</code>。</li>\n<li>选择 <strong>Open IDE</strong>(打开 IDE)。<br />\n这将打开一个 IDE,用于配置、部署和测试存储的程序。</li>\n<li>在您的 Cloud9 终端中,运行以下命令以克隆存储库并安装所需的工具:</li>\n</ol>\n<pre><code class=\"lang-\">git clone https://github.com/aws-samples/amazon-aurora-postgresql-stored-proc-pgcron.git \ncd amazon-aurora-postgresql-stored-proc-pgcron/scripts\nsh install-db-tools.sh\n</code></pre>\n<p>该脚本需要 5 分钟来安装所有必需的工具。在进入下一步之前,请确保安装已完成。</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/d115abfdf3e342efa2c7bbddeb6ecb48_image.png\" alt=\"image.png\" /></p>\n<ol start=\"4\">\n<li>运行以下命令初始化环境变量:</li>\n</ol>\n<p><code>source ~/.bashrc</code></p>\n<ol start=\"5\">\n<li>通过运行以下 shell 脚本命令创建源数据库对象和报告数据库对象:</li>\n</ol>\n<p><code>sh initialize-source-reporting-dbs.sh</code></p>\n<p>此脚本创建 <code>employee</code> 表和 <code>department</code> 表,并在源数据库中插入一些示例记录。</p>\n<p>脚本在源数据库中创建数据库对象后,它会在 <code>reporting</code> 数据库中创建 <code>employee</code> 表以及 <code>employee_sp</code>、<code>error_handler_sp</code> 和 <code>schedule_sp_job</code> 存储的程序。作为最后一步,它将创建 <code>postgres_fdw</code> 扩展、外部服务器、用户映射和外部表,以便从源数据库中提取数据。要了解有关 <code>postgres_fdw</code> 的更多信息,请参阅 <a href=\"https://www.postgresql.org/docs/14/postgres-fdw.html\" target=\"_blank\">PostgreSQL</a> 文档。</p>\n<pre><code class=\"lang-\">sh connect-source-db.sh\n\\dt\n\\d+ department\n\\d+ employee\n</code></pre>\n<p><img src=\"https://dev-media.amazoncloud.cn/dd1f3e0392ac4410846c91b62f48c390_image.png\" alt=\"image.png\" /></p>\n<p><code>employee</code> 表存储原始数据,其中可能包含空值和重复值。<code>department</code> 表用作部门名称的查找表。</p>\n<ol start=\"7\">\n<li>使用以下命令退出源数据库:</li>\n</ol>\n<p><code>exit</code></p>\n<ol start=\"8\">\n<li>逐个运行以下命令,观察报告数据库中存储的程序和表:</li>\n</ol>\n<pre><code class=\"lang-\">sh connect-reporting-db.sh\n\\dfp\n\\d+ employee\n</code></pre>\n<p><img src=\"https://dev-media.amazoncloud.cn/407155585da0425bad5ed6944432f609_image.png\" alt=\"image.png\" /></p>\n<p><code>employee_sp</code> 存储的程序验证员工源表中的原始数据并将其复制到报告数据库中的员工表。<code>error_handler_sp</code> 处理错误并向注册的电子邮件地址发送通知。<code>schedule_sp_job</code> 通过创建 cron 作业自动调度 <code>employee_sp</code>程序的运行。</p>\n<ol start=\"9\">\n<li>使用以下命令退出数据库:</li>\n</ol>\n<p><code>exit</code></p>\n<h4><a id=\"_138\"></a><strong>测试存储的程序</strong></h4>\n<p>我们创建了所有必需的表和存储的程序之后,就可以测试解决方案了。运行以下 shell 脚本:</p>\n<p><code>sh execute_sp.sh</code></p>\n<p>这将调用报告数据库中的 <code>employee_sp</code> 存储的程序。它使用以下代码验证员工和部门数据并将其从源数据库复制到报告数据库中的 <code>employee</code> 表:</p>\n<pre><code class=\"lang-\">insert into employee (employee_id,first_name,last_name,dob,badge_id,salary,dept_name)\n select employee_id, first_name, last_name,dob,replace(badge_id,''-'',''''),salary, dfdw.dept_name\n from employee_fdw efdw, department_fdw dfdw\n where efdw.dept_id = dfdw.dept_id\n and efdw.first_name is not null\n and efdw.last_name is not null\n and efdw.badge_id is not null\n and dfdw.dept_name is not null\n and efdw.salary&gt;0;\n</code></pre>\n<p>逐个运行以下命令,验证报告数据库的 <code>employee</code> 表中插入的记录:</p>\n<pre><code class=\"lang-\">sh connect-reporting-db.sh\nselect * from employee;\n</code></pre>\n<p><img src=\"https://dev-media.amazoncloud.cn/0104a2270901467ea64308649414fa5d_image.png\" alt=\"image.png\" /></p>\n<p>使用以下命令退出数据库:</p>\n<p><code>exit</code></p>\n<h4><a id=\"_171\"></a><strong>测试错误通知</strong></h4>\n<p>源表可能包含重复的记录,我们不希望在报告数据库中插入重复的记录。您可以验证存储的程序在尝试将重复记录插入报告数据库的员工表时是否会引发错误并发送电子邮件通知。</p>\n<p>我们通过运行以下 shell 脚本来模拟错误场景:</p>\n<p><code>sh execute_sp_using_duplicates.sh</code></p>\n<p>该脚本在源数据库的 <code>employee</code> 表中插入一条重复的记录,然后运行 <code>execute_sp.sh</code> 调用 <code>employee_sp()</code> 存储的程序将数据从源数据库复制到远程数据库。</p>\n<p>在报告数据库中插入重复记录时,会发生主键冲突。此异常会在 <code>exception</code> 块中捕获,并调用 <code>error_handler_sp</code> 存储的程序。请参阅以下代码:</p>\n<pre><code class=\"lang-\">exception\nwhen others then\n call error_handler_sp('TIME: '||clock_timestamp()||' / PROCEDURE: '||v_proc_name||' \n / MESSAGE: '||v_message||' / EXCEPTION: '||v_error_exception||' / HINT: '||v_error_hint);\nend;\n</code></pre>\n<p>调用 <code>error_handler_sp</code> 存储的程序时,如果不存在,它将创建 <code>aws_lambda</code> 扩展。然后它将错误消息传递给调用该函数的 Lambda 函数 <code>ExceptionLambda</code>。</p>\n<p>Lambda 函数将错误消息发布到 SNS 主题。您会收到一封主题为“存储的程序错误”的电子邮件,通知您在尝试插入重复记录时出现异常。</p>\n<h4><a id=\"_195\"></a><strong>调度您的存储的程序</strong></h4>\n<p>在生产环境中,您可能希望调度存储的程序以自动方式运行。</p>\n<ol>\n<li>运行以下 shell 脚本以调度存储的程序的运行:</li>\n</ol>\n<pre><code class=\"lang-\">sh schedule_pgcron_job.sh\n</code></pre>\n<p>该脚本刷新数据库对象以进行测试,并调用 <code>schedule_sp_job</code> 存储的程序。<code>schedule_sp_job</code> 创建 <code>pg_cron</code> 扩展(如果 pg_cron 不存在),并调度每 10 分钟运行一次 <code>employee_sp</code> 存储的程序的 cron 作业。</p>\n<ol start=\"2\">\n<li>在报告数据库中运行以下 SQL 查询,以确认 cron 作业的创建。我们使用 cron 表达式 <code>*/10 * * * *</code> 来允许作业每 10 分钟运行一次。</li>\n</ol>\n<pre><code class=\"lang-\">sh connect-reporting-db.sh\nselect * from cron.job;\n</code></pre>\n<ol start=\"3\">\n<li>您可以使用以下 SQL 查询查看计划作业的状态:</li>\n</ol>\n<pre><code class=\"lang-\">select jobid, username, status, return_message, start_time from cron.job_run_details;\n</code></pre>\n<p>10 分钟后,清理后的数据将填充到报告数据库的 <code>employee</code> 表中。</p>\n<ol start=\"4\">\n<li>现在,您可以通过运行以下 SQL 命令来取消调度 cron 作业:</li>\n</ol>\n<pre><code class=\"lang-\">select cron.unschedule ('Execute employee_sp');\n</code></pre>\n<p>使用 pg_cron,您可以定期调度 SQL 命令的执行以执行重复性任务。</p>\n<h4><a id=\"_230\"></a><strong>清理</strong></h4>\n<p>为避免产生持续的费用,请从 Amazon CloudFormation 控制台中<a href=\"https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/cfn-console-delete-stack.html\" target=\"_blank\">删除</a> <code>AmazonAuroraPostgreSQLStoredProc</code> 堆栈来清理基础设施。删除作为本练习的先决条件而创建的任何其他资源。</p>\n<h4><a id=\"_234\"></a><strong>结论</strong></h4>\n<p>在这篇博文中,我们演示了如何使用 Aurora PostgreSQL 扩展(例如 <code>postgres_fdw</code>、<code>pg_cron</code> 和 <code>aws_lambda</code>)对存储的程序进行现代化改造。Aurora PostgreSQL 扩展通过提供与商业数据库同等的功能来增强数据库开发体验。在规划现代化之旅时,请仔细考虑您的业务目标和成果。</p>\n<p>有关 Aurora 扩展的更多信息,请参阅<a href=\"https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Appendix.PostgreSQL.CommonDBATasks.html\" target=\"_blank\">使用扩展和外部数据封装器</a>。有关使用数据库触发器通过 Lambda 和 Amazon SNS 启用近实时通知的信息,请参阅<a href=\"https://aws.amazon.com/blogs/database/enable-near-real-time-notifications-from-amazon-aurora-postgresql-by-using-database-triggers-aws-lambda-and-amazon-sns/\" target=\"_blank\">使用数据库触发器、Amazon Lambda 和 Amazon SNS 启用来自 Amazon Aurora PostgreSQL 的近实时通知</a>。</p>\n<p>告诉我们这篇博文对您的数据库现代化之旅有何帮助。</p>\n<p><strong>关于作者</strong></p>\n<p><img src=\"https://dev-media.amazoncloud.cn/709e806e18a149a5919cb922a5679267_image.png\" alt=\"image.png\" /></p>\n<p><strong>Prathap Thoguru</strong> 是 Amazon Web Services 的一名企业解决方案构架师。他在 IT 行业拥有 15 年以上的经验,是一名已获 9 项 Amazon 认证的专业人员。他帮助客户将本地工作负载迁移到 Amazon Cloud。</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/b4e3d00e74e34845987aa8f2eece5313_image.png\" alt=\"image.png\" /></p>\n<p><strong>Kishore Dhamodaran</strong> 是 Amazon Web Services 的高级解决方案架构师。Kishore 利用他多年的行业和云经验,帮助客户制定云企业战略和迁移之旅。</p>\n"}
目录
亚马逊云科技解决方案 基于行业客户应用场景及技术领域的解决方案
联系亚马逊云科技专家
亚马逊云科技解决方案
基于行业客户应用场景及技术领域的解决方案
联系专家
0
目录
关闭
contact-us