Modernize database stored procedures to use Amazon Aurora PostgreSQL federated queries, pg_cron, and Amazon Lambda

海外精选
海外精选的内容汇集了全球优质的亚马逊云科技相关技术内容。同时,内容中提到的“AWS” 是 “Amazon Web Services” 的缩写,在此网站不作为商标展示。
0
0
{"value":"As part of [migrating and modernizing](https://docs.aws.amazon.com/prescriptive-guidance/latest/migration-retiring-applications/apg-gloss.html#apg.migration.terms) your databases, you may continue to use your stored procedures and scheduling jobs that consolidate data from remote instances into your centralized data store. [Amazon Web Services Schema Conversion Tool (Amazon Web Services SCT)](https://aws.amazon.com/dms/schema-conversion-tool/) helps you convert your legacy Oracle and SQL Server functions to their open-source equivalent. But how do you continue to use your stored procedures to extract data from remote databases? How about your existing cron jobs? How do you handle errors in the stored procedures and notify the database administrators? The PostgreSQL Extensions such as [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), and [aws_lambda](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/PostgreSQL-Lambda.html) allow you to do just that.\n\nIn this post, we demonstrate a pattern which allows you to modernize your database and refactor your existing code. We use [Amazon Aurora PostgreSQL-Compatible Edition](https://aws.amazon.com/rds/aurora/postgresql-features/) database instance to illustrate this pattern.\n\nThere is no one size fits all approach to modernizing your databases. You need to carefully plan your [transformation journey](https://docs.aws.amazon.com/whitepapers/latest/overview-aws-cloud-adoption-framework/your-cloud-transformation-journey.html) with clear goals and outcomes. If handling some of your logic in the database layer suits your business needs, you may consider the approach presented in this post. Refer to [Migrating Oracle databases to the Amazon Web Services Cloud ](https://docs.aws.amazon.com/prescriptive-guidance/latest/migration-oracle-database/welcome.html)and[ Migrating Microsoft SQL Server databases to the Amazon Web Services Cloud](https://docs.aws.amazon.com/prescriptive-guidance/latest/migration-sql-server/welcome.html) for additional guidance.\n\n\n#### **PostgreSQL Extensions**\n\n\nBefore we begin, let’s go through the PostgreSQL extensions used in our solution.\n\npostgres_fdw is a foreign data wrapper used to access data in remote PostgreSQL servers. [Amazon Relational Database Service (Amazon RDS) for PostgreSQL](https://aws.amazon.com/rds/postgresql/) and Aurora PostgreSQL support this extension. With postgres_fdw, you can implement a [federated query](https://aws.amazon.com/blogs/database/federated-query-support-for-amazon-aurora-postgresql-and-amazon-rds-for-postgresql/) to retrieve data from a remote PostgreSQL database instance, store it in a centralized database, or generate reports.\n\n[Amazon Web Services Lambda](https://aws.amazon.com/lambda/) runs code in highly available compute infrastructure without provisioning or managing servers and operating system maintenance. The code in Lambda is organized as a function and supports many programming languages, such as Python, Node.js, Java, and Ruby. The aws_lambda extension provides the ability to invoke Lambda functions from Aurora PostgreSQL. This extension also requires the aws_commons extension, which provides helper functions to aws_lambda and many other Aurora extensions for PostgreSQL. If an error occurs in a stored procedure, you can send the error message to a Lambda function and send a notification to the DBAs using [Amazon Simple Notification Service](https://aws.amazon.com/sns) (Amazon SNS).\n\nYou can use pg_cron to schedule SQL commands and it uses the same syntax as standard CRON expression. We can schedule the stored procedures and automate routine maintenance tasks using this extension.\n\n\n#### **Solution overview**\n\n\nThe source database consists of the tables and data that we want to retrieve and load into the reporting database. The pg_cron extension runs the stored procedure according to a predefined schedule. The stored procedure copies the data based on the predefined business logic. If any errors are encountered, it invokes a Lambda function to send out the error notification to users subscribed to an SNS topic. The following diagram illustrates the solution architecture and flow.\n\n![image.png](https://dev-media.amazoncloud.cn/5faa21c5b3584bcd90767739094d2dce_image.png)\n\nIn this post, we walk you through the steps to create resources with [Amazon Web Services CloudFormation](http://aws.amazon.com/cloudformation), configure your stored procedures, and test the solution.\n\n\n#### **Prerequisites**\n\n\nMake sure you complete the following prerequisite steps:\n\n1. Set up the [Amazon Web Services Command Line Interface](https://aws.amazon.com/cli/) (Amazon Web Services CLI) to run commands for interacting with your Amazon Web Services resources.\n2. Have the appropriate permissions to interact with resources in your Amazon Web Services account.\n\n\n#### **Create resources with Amazon Web Services CloudFormation**\n\n\nThe CloudFormation template for this solution deploys the following key resources:\n\n- Two Aurora PostgreSQL clusters for the source and reporting databases, containing database tables and stored procedures\n- A Lambda function to relay the error message to Amazon SNS\n- An SNS topic for email notification\n- An [Amazon Web Services Cloud9](https://aws.amazon.com/cloud9/) instance to connect to the databases for setup and testing.\n \nUse the [Amazon Web Services Pricing Calculator](https://calculator.aws/#/)to estimate the cost before you run this solution. The resources deployed are not eligible for the Free Tier, but if you choose the stack defaults, you should incur costs less than $3.00, assuming that you clean up the stack in an hour.\n\nTo create the resources, complete the following steps:\n\n```\ngit clone https://github.com/aws-samples/amazon-aurora-postgresql-stored-proc-pgcron.git\ncd amazon-aurora-postgresql-stored-proc-pgcron\n\n1. Clone the GitHub project by running the following commands from your terminal:\n```\n\n2. Deploy Amazon Web Services CloudFormation resources with the following code. Replace youreamil@example.com with a valid email address.\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\nProvisioning the resources takes approximately 15–20 minutes to complete. You can ensure successful stack deployment by going to the Amazon Web Services CloudFormation console and verifying that the status shows as CREATE_COMPLETE.\n\n![image.png](https://dev-media.amazoncloud.cn/01ac316f7475483aaf5bfaa4017d1148_image.png)\n\nWhile the stack is being created, you receive an email to confirm an SNS subscription.\n\n3.Choose Confirm subscription in your email.\n\n![image.png](https://dev-media.amazoncloud.cn/41053fc2ed2d4ba891975ab709104ab0_image.png)\n\nA browser window opens with your subscription confirmation.\n\n\n#### **Configure your stored procedures**\n\n\nTo configure your stored procedures, complete the following steps:\n\n1. On the Amazon Web Services Cloud9 console, under Your environments, choose the environment PostgreSQLInstance.\n2. Choose Open IDE.\nThis opens an IDE, which you use to configure, deploy, and test your stored procedure.\n3. In your Cloud9 terminal, run the following commands to clone the repository and install the required tools:\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\nThe script takes 5 minutes to install all the necessary tools. Make sure that the installation is complete before you move to the next step.\n\n![image.png](https://dev-media.amazoncloud.cn/666b30dc73ea432e827e43736eef3fb3_image.png)\n\n4. Run the following command to initialize environment variables:\n\n```\nsource ~/.bashrc\n```\n\n5. Create the source and reporting database objects by running the following shell script command:\n\n\n```\nsh initialize-source-reporting-dbs.sh\n```\n\nThis script creates employee and department tables and inserts a few sample records in the source database.\n\nAfter the script creates the database objects in the source database, it creates an employee table and employee_sp, error_handler_sp, and schedule_sp_job stored procedures in the reporting database. As a final step, it creates the postgres_fdw extension, a foreign server, a user mapping, and foreign tables to pull the data from the source database. To learn more about postgres_fdw, refer [to the PostgreSQL documentation](https://www.postgresql.org/docs/14/postgres-fdw.html).\n\n6. Observe the tables and schema in the source database by running the following commands one by one:\n\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/e27c3311c08d4264b7966dca288d312c_image.png)\n\nThe employee table stores the raw data, which may contain null and duplicate values. The department table serves as a lookup table for department names.\n\n7. Exit from the source database using the following command:\n\n\n ```\nexit\n```\n\n8. Observe the stored procedures and table in the reporting database by running the following commands one by one:\n\n\n```\nsh connect-reporting-db.sh\n\\dfp\n\\d+ employee\n```\n\n![image.png](https://dev-media.amazoncloud.cn/dce46f6908f947c49847bb16f78cf40f_image.png)\n\nThe employee_sp stored procedure validates and copies the raw data from the employee source table to the employee table in the reporting database. error_handler_sp handles errors and sends out a notification to the registered email address. schedule_sp_job automatically schedules the run of the employee_sp procedure by creating a cron job.\n\n9. Exit from the database using the following command:\n\n```\nexit\n```\n\n\n#### **Test the stored procedures**\n\n\nAfter we create all the required tables and stored procedures, we’re ready to test the solution. Run the following shell script:\n\n```\nsh execute_sp.sh\n```\n\nThis invokes the employee_sp stored procedure in the reporting database. It validates and copies the employee and department data from the source database to the employee table in the reporting database using the following code:\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\nVerify the inserted records in the employee table of the reporting database by running the following commands one by one:\n\n```\nsh connect-reporting-db.sh\nselect * from employee;\n```\n\n![image.png](https://dev-media.amazoncloud.cn/bfaad810bd2a49b486dbc034dea08965_image.png)\n\nExit from the database using the following command:\n\n```\nexit\n```\n\n\n#### **Test error notifications**\n\n\nThe source table may contain duplicate records, and we don’t want to insert duplicate records into the reporting database. You can verify that the stored procedure throws an error and sends an email notification when an attempt is made to insert a duplicate record into the employee table of the reporting database.\n\nWe simulate an error scenario by running the following shell script:\n\n```\nsh execute_sp_using_duplicates.sh\n```\n\nThe script inserts a duplicate record in the employee table of the source database and runs execute_sp.sh to invoke the employee_sp() stored procedure to copy the data from the source database to the remote database.\n\nA primary key violation occurs when a duplicate record is inserted into the reporting database. This exception gets caught in the exception block, and the error_handler_sp stored procedure gets invoked. See the following code:\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\nWhen the error_handler_sp stored procedure is invoked, it creates the aws_lambda extension if it doesn’t exist. Then it passes the error message to the Lambda function ExceptionLambda, which invokes the function.\n\nThe Lambda function publishes the error message to the SNS topic. You receive an email with the subject “Stored Procedure Error” to notify you of the exception when attempting to insert duplicate records.\n\n\n#### **Schedule your stored procedure**\n\n\nIn the production environment, you may want to schedule your stored procedure to run in an automated manner.\n\n1. Run the following shell script to schedule running the stored procedure:\n\n```\nsh schedule_pgcron_job.sh\n```\n\nThe script refreshes the database objects for testing and invokes the schedule_sp_job stored procedure. schedule_sp_job creates the pg_cron extension if it doesn’t exist, and schedules a cron job that runs the employee_sp stored procedure every 10 minutes.\n\n2. Run the following SQL query in the reporting database to confirm the creation of cron job. We use the [cron expression](https://docs.aws.amazon.com/eventbridge/latest/userguide/eb-create-rule-schedule.html#eb-cron-expressions) */10 * * * * to allow the job to run every 10 minutes.\n\n```\nsh connect-reporting-db.sh\nselect * from cron.job;\n\n```\n\n3. You can review the status of the scheduled job using the following SQL query:\n\n```\nselect jobid, username, status, return_message, start_time from cron.job_run_details;\n```\n\nAfter 10 minutes, the cleansed data gets populated in the employee table of the reporting database.\n\n4. Now you can unschedule the cron job by running the following SQL command:\n\n```\nselect cron.unschedule ('Execute employee_sp');\n\n```\n\nWith pg_cron, you can schedule the execution of the SQL commands periodically to perform recurring tasks.\n\n\n#### **Clean up**\n\nTo avoid incurring ongoing charges, clean up your infrastructure by [deleting](https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/cfn-console-delete-stack.html) the AmazonAuroraPostgreSQLStoredProc stack from the Amazon Web Services CloudFormation console. Delete any other resources you may have created as a prerequisite for this exercise.\n\n\n#### **Conclusion**\n\n\nIn this post, we demonstrated how to modernize your stored procedures using Aurora PostgreSQL extensions such as postgres_fdw, pg_cron, and aws_lambda. Aurora PostgreSQL extensions enhance the database development experience by providing equivalent functionality to commercial databases. Carefully consider your business goals and outcomes when planning your modernization journey.\n\nFor more information about Aurora extensions, refer to [Working with extensions and foreign data wrappers](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Appendix.PostgreSQL.CommonDBATasks.html). For information on using database triggers to enable near real-time notifications through Lambda and Amazon SNS, refer to [Enable near real-time notifications from Amazon Aurora PostgreSQL by using database triggers, Amazon Web Services Lambda, and Amazon SNS](https://aws.amazon.com/blogs/database/enable-near-real-time-notifications-from-amazon-aurora-postgresql-by-using-database-triggers-aws-lambda-and-amazon-sns/).\nLet us know how this post helped with your database modernization journey.\n\n\n\n#### **About the Authors**\n\n\n\n![image.png](https://dev-media.amazoncloud.cn/62d8b27249504161a7e622fab13664bf_image.png)\n\n**Prathap Thoguru** is an Enterprise Solutions Architect at Amazon Web Services. He has 15 plus years of experience in the I.T. industry and he is a 9 x Amazon Web Services certified professional. He helps customers in migrating their on-premises workloads to Amazon Web Services Cloud.\n\n![image.png](https://dev-media.amazoncloud.cn/42db976e92124331ac5249124955c27e_image.png)\n\n**Kishore Dhamodaran** is a Senior Solutions Architect with Amazon Web Services. Kishore helps customers with their cloud enterprise strategy and migration journey, leveraging his years of industry and cloud experience.","render":"<p>As part of <a href=\"https://docs.aws.amazon.com/prescriptive-guidance/latest/migration-retiring-applications/apg-gloss.html#apg.migration.terms\" target=\"_blank\">migrating and modernizing</a> your databases, you may continue to use your stored procedures and scheduling jobs that consolidate data from remote instances into your centralized data store. <a href=\"https://aws.amazon.com/dms/schema-conversion-tool/\" target=\"_blank\">Amazon Web Services Schema Conversion Tool (Amazon Web Services SCT)</a> helps you convert your legacy Oracle and SQL Server functions to their open-source equivalent. But how do you continue to use your stored procedures to extract data from remote databases? How about your existing cron jobs? How do you handle errors in the stored procedures and notify the database administrators? The PostgreSQL Extensions such as <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>, and <a href=\"https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/PostgreSQL-Lambda.html\" target=\"_blank\">aws_lambda</a> allow you to do just that.</p>\n<p>In this post, we demonstrate a pattern which allows you to modernize your database and refactor your existing code. We use <a href=\"https://aws.amazon.com/rds/aurora/postgresql-features/\" target=\"_blank\">Amazon Aurora PostgreSQL-Compatible Edition</a> database instance to illustrate this pattern.</p>\n<p>There is no one size fits all approach to modernizing your databases. You need to carefully plan your <a href=\"https://docs.aws.amazon.com/whitepapers/latest/overview-aws-cloud-adoption-framework/your-cloud-transformation-journey.html\" target=\"_blank\">transformation journey</a> with clear goals and outcomes. If handling some of your logic in the database layer suits your business needs, you may consider the approach presented in this post. Refer to <a href=\"https://docs.aws.amazon.com/prescriptive-guidance/latest/migration-oracle-database/welcome.html\" target=\"_blank\">Migrating Oracle databases to the Amazon Web Services Cloud </a>and<a href=\"https://docs.aws.amazon.com/prescriptive-guidance/latest/migration-sql-server/welcome.html\" target=\"_blank\"> Migrating Microsoft SQL Server databases to the Amazon Web Services Cloud</a> for additional guidance.</p>\n<h4><a id=\"PostgreSQL_Extensions_7\"></a><strong>PostgreSQL Extensions</strong></h4>\n<p>Before we begin, let’s go through the PostgreSQL extensions used in our solution.</p>\n<p>postgres_fdw is a foreign data wrapper used to access data in remote PostgreSQL servers. <a href=\"https://aws.amazon.com/rds/postgresql/\" target=\"_blank\">Amazon Relational Database Service (Amazon RDS) for PostgreSQL</a> and Aurora PostgreSQL support this extension. With postgres_fdw, you can implement a <a href=\"https://aws.amazon.com/blogs/database/federated-query-support-for-amazon-aurora-postgresql-and-amazon-rds-for-postgresql/\" target=\"_blank\">federated query</a> to retrieve data from a remote PostgreSQL database instance, store it in a centralized database, or generate reports.</p>\n<p><a href=\"https://aws.amazon.com/lambda/\" target=\"_blank\">Amazon Web Services Lambda</a> runs code in highly available compute infrastructure without provisioning or managing servers and operating system maintenance. The code in Lambda is organized as a function and supports many programming languages, such as Python, Node.js, Java, and Ruby. The aws_lambda extension provides the ability to invoke Lambda functions from Aurora PostgreSQL. This extension also requires the aws_commons extension, which provides helper functions to aws_lambda and many other Aurora extensions for PostgreSQL. If an error occurs in a stored procedure, you can send the error message to a Lambda function and send a notification to the DBAs using <a href=\"https://aws.amazon.com/sns\" target=\"_blank\">Amazon Simple Notification Service</a> (Amazon SNS).</p>\n<p>You can use pg_cron to schedule SQL commands and it uses the same syntax as standard CRON expression. We can schedule the stored procedures and automate routine maintenance tasks using this extension.</p>\n<h4><a id=\"Solution_overview_19\"></a><strong>Solution overview</strong></h4>\n<p>The source database consists of the tables and data that we want to retrieve and load into the reporting database. The pg_cron extension runs the stored procedure according to a predefined schedule. The stored procedure copies the data based on the predefined business logic. If any errors are encountered, it invokes a Lambda function to send out the error notification to users subscribed to an SNS topic. The following diagram illustrates the solution architecture and flow.</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/5faa21c5b3584bcd90767739094d2dce_image.png\" alt=\"image.png\" /></p>\n<p>In this post, we walk you through the steps to create resources with <a href=\"http://aws.amazon.com/cloudformation\" target=\"_blank\">Amazon Web Services CloudFormation</a>, configure your stored procedures, and test the solution.</p>\n<h4><a id=\"Prerequisites_29\"></a><strong>Prerequisites</strong></h4>\n<p>Make sure you complete the following prerequisite steps:</p>\n<ol>\n<li>Set up the <a href=\"https://aws.amazon.com/cli/\" target=\"_blank\">Amazon Web Services Command Line Interface</a> (Amazon Web Services CLI) to run commands for interacting with your Amazon Web Services resources.</li>\n<li>Have the appropriate permissions to interact with resources in your Amazon Web Services account.</li>\n</ol>\n<h4><a id=\"Create_resources_with_Amazon_Web_Services_CloudFormation_38\"></a><strong>Create resources with Amazon Web Services CloudFormation</strong></h4>\n<p>The CloudFormation template for this solution deploys the following key resources:</p>\n<ul>\n<li>Two Aurora PostgreSQL clusters for the source and reporting databases, containing database tables and stored procedures</li>\n<li>A Lambda function to relay the error message to Amazon SNS</li>\n<li>An SNS topic for email notification</li>\n<li>An <a href=\"https://aws.amazon.com/cloud9/\" target=\"_blank\">Amazon Web Services Cloud9</a> instance to connect to the databases for setup and testing.</li>\n</ul>\n<p>Use the <a href=\"https://calculator.aws/#/\" target=\"_blank\">Amazon Web Services Pricing Calculator</a>to estimate the cost before you run this solution. The resources deployed are not eligible for the Free Tier, but if you choose the stack defaults, you should incur costs less than $3.00, assuming that you clean up the stack in an hour.</p>\n<p>To create the resources, complete the following steps:</p>\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\n1. Clone the GitHub project by running the following commands from your terminal:\n</code></pre>\n<ol start=\"2\">\n<li>Deploy Amazon Web Services CloudFormation resources with the following code. Replace youreamil@example.com with a valid email address.</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\n</code></pre>\n<p>Provisioning the resources takes approximately 15–20 minutes to complete. You can ensure successful stack deployment by going to the Amazon Web Services CloudFormation console and verifying that the status shows as CREATE_COMPLETE.</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/01ac316f7475483aaf5bfaa4017d1148_image.png\" alt=\"image.png\" /></p>\n<p>While the stack is being created, you receive an email to confirm an SNS subscription.</p>\n<p>3.Choose Confirm subscription in your email.</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/41053fc2ed2d4ba891975ab709104ab0_image.png\" alt=\"image.png\" /></p>\n<p>A browser window opens with your subscription confirmation.</p>\n<h4><a id=\"Configure_your_stored_procedures_85\"></a><strong>Configure your stored procedures</strong></h4>\n<p>To configure your stored procedures, complete the following steps:</p>\n<ol>\n<li>On the Amazon Web Services Cloud9 console, under Your environments, choose the environment PostgreSQLInstance.</li>\n<li>Choose Open IDE.<br />\nThis opens an IDE, which you use to configure, deploy, and test your stored procedure.</li>\n<li>In your Cloud9 terminal, run the following commands to clone the repository and install the required tools:</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>The script takes 5 minutes to install all the necessary tools. Make sure that the installation is complete before you move to the next step.</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/666b30dc73ea432e827e43736eef3fb3_image.png\" alt=\"image.png\" /></p>\n<ol start=\"4\">\n<li>Run the following command to initialize environment variables:</li>\n</ol>\n<pre><code class=\"lang-\">source ~/.bashrc\n</code></pre>\n<ol start=\"5\">\n<li>Create the source and reporting database objects by running the following shell script command:</li>\n</ol>\n<pre><code class=\"lang-\">sh initialize-source-reporting-dbs.sh\n</code></pre>\n<p>This script creates employee and department tables and inserts a few sample records in the source database.</p>\n<p>After the script creates the database objects in the source database, it creates an employee table and employee_sp, error_handler_sp, and schedule_sp_job stored procedures in the reporting database. As a final step, it creates the postgres_fdw extension, a foreign server, a user mapping, and foreign tables to pull the data from the source database. To learn more about postgres_fdw, refer <a href=\"https://www.postgresql.org/docs/14/postgres-fdw.html\" target=\"_blank\">to the PostgreSQL documentation</a>.</p>\n<ol start=\"6\">\n<li>Observe the tables and schema in the source database by running the following commands one by one:</li>\n</ol>\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/e27c3311c08d4264b7966dca288d312c_image.png\" alt=\"image.png\" /></p>\n<p>The employee table stores the raw data, which may contain null and duplicate values. The department table serves as a lookup table for department names.</p>\n<ol start=\"7\">\n<li>Exit from the source database using the following command:</li>\n</ol>\n<pre><code class=\"lang-\">exit\n</code></pre>\n<ol start=\"8\">\n<li>Observe the stored procedures and table in the reporting database by running the following commands one by one:</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/dce46f6908f947c49847bb16f78cf40f_image.png\" alt=\"image.png\" /></p>\n<p>The employee_sp stored procedure validates and copies the raw data from the employee source table to the employee table in the reporting database. error_handler_sp handles errors and sends out a notification to the registered email address. schedule_sp_job automatically schedules the run of the employee_sp procedure by creating a cron job.</p>\n<ol start=\"9\">\n<li>Exit from the database using the following command:</li>\n</ol>\n<pre><code class=\"lang-\">exit\n</code></pre>\n<h4><a id=\"Test_the_stored_procedures_163\"></a><strong>Test the stored procedures</strong></h4>\n<p>After we create all the required tables and stored procedures, we’re ready to test the solution. Run the following shell script:</p>\n<pre><code class=\"lang-\">sh execute_sp.sh\n</code></pre>\n<p>This invokes the employee_sp stored procedure in the reporting database. It validates and copies the employee and department data from the source database to the employee table in the reporting database using the following 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>Verify the inserted records in the employee table of the reporting database by running the following commands one by one:</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/bfaad810bd2a49b486dbc034dea08965_image.png\" alt=\"image.png\" /></p>\n<p>Exit from the database using the following command:</p>\n<pre><code class=\"lang-\">exit\n</code></pre>\n<h4><a id=\"Test_error_notifications_202\"></a><strong>Test error notifications</strong></h4>\n<p>The source table may contain duplicate records, and we don’t want to insert duplicate records into the reporting database. You can verify that the stored procedure throws an error and sends an email notification when an attempt is made to insert a duplicate record into the employee table of the reporting database.</p>\n<p>We simulate an error scenario by running the following shell script:</p>\n<pre><code class=\"lang-\">sh execute_sp_using_duplicates.sh\n</code></pre>\n<p>The script inserts a duplicate record in the employee table of the source database and runs execute_sp.sh to invoke the employee_sp() stored procedure to copy the data from the source database to the remote database.</p>\n<p>A primary key violation occurs when a duplicate record is inserted into the reporting database. This exception gets caught in the exception block, and the error_handler_sp stored procedure gets invoked. See the following 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>When the error_handler_sp stored procedure is invoked, it creates the aws_lambda extension if it doesn’t exist. Then it passes the error message to the Lambda function ExceptionLambda, which invokes the function.</p>\n<p>The Lambda function publishes the error message to the SNS topic. You receive an email with the subject “Stored Procedure Error” to notify you of the exception when attempting to insert duplicate records.</p>\n<h4><a id=\"Schedule_your_stored_procedure_230\"></a><strong>Schedule your stored procedure</strong></h4>\n<p>In the production environment, you may want to schedule your stored procedure to run in an automated manner.</p>\n<ol>\n<li>Run the following shell script to schedule running the stored procedure:</li>\n</ol>\n<pre><code class=\"lang-\">sh schedule_pgcron_job.sh\n</code></pre>\n<p>The script refreshes the database objects for testing and invokes the schedule_sp_job stored procedure. schedule_sp_job creates the pg_cron extension if it doesn’t exist, and schedules a cron job that runs the employee_sp stored procedure every 10 minutes.</p>\n<ol start=\"2\">\n<li>Run the following SQL query in the reporting database to confirm the creation of cron job. We use the <a href=\"https://docs.aws.amazon.com/eventbridge/latest/userguide/eb-create-rule-schedule.html#eb-cron-expressions\" target=\"_blank\">cron expression</a> */10 * * * * to allow the job to run every 10 minutes.</li>\n</ol>\n<pre><code class=\"lang-\">sh connect-reporting-db.sh\nselect * from cron.job;\n\n</code></pre>\n<ol start=\"3\">\n<li>You can review the status of the scheduled job using the following SQL query:</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>After 10 minutes, the cleansed data gets populated in the employee table of the reporting database.</p>\n<ol start=\"4\">\n<li>Now you can unschedule the cron job by running the following SQL command:</li>\n</ol>\n<pre><code class=\"lang-\">select cron.unschedule ('Execute employee_sp');\n\n</code></pre>\n<p>With pg_cron, you can schedule the execution of the SQL commands periodically to perform recurring tasks.</p>\n<h4><a id=\"Clean_up_269\"></a><strong>Clean up</strong></h4>\n<p>To avoid incurring ongoing charges, clean up your infrastructure by <a href=\"https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/cfn-console-delete-stack.html\" target=\"_blank\">deleting</a> the AmazonAuroraPostgreSQLStoredProc stack from the Amazon Web Services CloudFormation console. Delete any other resources you may have created as a prerequisite for this exercise.</p>\n<h4><a id=\"Conclusion_274\"></a><strong>Conclusion</strong></h4>\n<p>In this post, we demonstrated how to modernize your stored procedures using Aurora PostgreSQL extensions such as postgres_fdw, pg_cron, and aws_lambda. Aurora PostgreSQL extensions enhance the database development experience by providing equivalent functionality to commercial databases. Carefully consider your business goals and outcomes when planning your modernization journey.</p>\n<p>For more information about Aurora extensions, refer to <a href=\"https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Appendix.PostgreSQL.CommonDBATasks.html\" target=\"_blank\">Working with extensions and foreign data wrappers</a>. For information on using database triggers to enable near real-time notifications through Lambda and Amazon SNS, refer to <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\">Enable near real-time notifications from Amazon Aurora PostgreSQL by using database triggers, Amazon Web Services Lambda, and Amazon SNS</a>.<br />\nLet us know how this post helped with your database modernization journey.</p>\n<h4><a id=\"About_the_Authors_284\"></a><strong>About the Authors</strong></h4>\n<p><img src=\"https://dev-media.amazoncloud.cn/62d8b27249504161a7e622fab13664bf_image.png\" alt=\"image.png\" /></p>\n<p><strong>Prathap Thoguru</strong> is an Enterprise Solutions Architect at Amazon Web Services. He has 15 plus years of experience in the I.T. industry and he is a 9 x Amazon Web Services certified professional. He helps customers in migrating their on-premises workloads to Amazon Web Services Cloud.</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/42db976e92124331ac5249124955c27e_image.png\" alt=\"image.png\" /></p>\n<p><strong>Kishore Dhamodaran</strong> is a Senior Solutions Architect with Amazon Web Services. Kishore helps customers with their cloud enterprise strategy and migration journey, leveraging his years of industry and cloud experience.</p>\n"}
目录
亚马逊云科技解决方案 基于行业客户应用场景及技术领域的解决方案
联系亚马逊云科技专家
亚马逊云科技解决方案
基于行业客户应用场景及技术领域的解决方案
联系专家
0
目录
关闭