The source database consists of the tables and data that we want to retrieve and load into the reporting database. We can schedule the stored procedures and automate routine maintenance tasks using this extension. You can use pg_cron to schedule SQL commands and it uses the same syntax as standard CRON expression. 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 (Amazon SNS). This extension also requires the aws_commons extension, which provides helper functions to aws_lambda and many other Aurora extensions for PostgreSQL.
The aws_lambda extension provides the ability to invoke Lambda functions from Aurora PostgreSQL.
#CONVERT DB2 STORED PROCEDURE TO AWS POSTGRESQL CODE#
The code in Lambda is organized as a function and supports many programming languages, such as Python, Node.js, Java, and Ruby. With postgres_fdw, you can implement a federated query to retrieve data from a remote PostgreSQL database instance, store it in a centralized database, or generate reports.ĪWS Lambda runs code in highly available compute infrastructure without provisioning or managing servers and operating system maintenance. Amazon Relational Database Service (Amazon RDS) for PostgreSQL and Aurora PostgreSQL support this extension. Postgres_fdw is a foreign data wrapper used to access data in remote PostgreSQL servers. PostgreSQL Extensionsīefore we begin, let’s go through the PostgreSQL extensions used in our solution. Refer to Migrating Oracle databases to the AWS Cloud and Migrating Microsoft SQL Server databases to the AWS Cloud for additional guidance. If handling some of your logic in the database layer suits your business needs, you may consider the approach presented in this post. You need to carefully plan your transformation journey with clear goals and outcomes. There is no one size fits all approach to modernizing your databases.
We use Amazon Aurora PostgreSQL-Compatible Edition database instance to illustrate this pattern. In this post, we demonstrate a pattern which allows you to modernize your database and refactor your existing code.
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, pg_cron, and aws_lambda allow you to do just that. AWS Schema Conversion Tool (AWS SCT) helps you convert your legacy Oracle and SQL Server functions to their open-source equivalent. As part of migrating and modernizing your databases, you may continue to use your stored procedures and scheduling jobs that consolidate data from remote instances into your centralized data store.
0 kommentar(er)
