在Amazon RDS for PostgreSQL或Amazon Aurora PostgreSQL中使用触发器函数实现分区表与非分区表之间的数据复制

关键要点

本文将介绍如何使用触发器函数在Amazon Aurora PostgreSQL兼容版和Amazon RDS for PostgreSQL之间实现分区表与非分区表的数据复制。在将应用程序部分迁移至AWS时，客户需确保AWS与本地数据库之间的数据同步。通过创建触发器函数，可自动管理分区表与非分区表之间的数据变更。本文提供具体步骤来创建数据库、表和触发器。

在这篇文章中，我们展示了如何使用触发器函数在Amazon Aurora PostgreSQL兼容版和Amazon RDS for PostgreSQL之间复制分区表和非分区表的数据。

鲤鱼加速器

客户通常决定将部分应用程序及其相关的数据库对象迁移至AWS，这意味着需要在将整个应用程序迁移到AWS之前，保持从AWS到本地的同步。如果在AWS环境中修改或更新了数据库架构，数据复制工具可能无法直接将数据回传至源架构。例如，当使用Blu Age将IBM Db2 for iSeries数据库迁移至PostgreSQL时，PostgreSQL中的表将被分区。数据复制工具无法直接将分区表的变更复制回非分区表。本文描述了一种创建并自动填充与分区表兼容的副本的方法，以便将数据同步回源数据库。

虽然现有的数据迁移服务AWS DMS或Qlik Replicate等工具在将数据从PostgreSQL复制到不同的数据库引擎时并不支持从PostgreSQL的分区表复制到非分区表。例如，为了将数据从Amazon Aurora PostgreSQL或Amazon RDS for PostgreSQL复制回本地的IBM Db2 for iSeries，必须首先将分区表转换为非分区表。然后，非分区表再复制回本地的IBM Db2 for iSeries，直到整个数据库迁移至Amazon RDS for PostgreSQL或Amazon Aurora PostgreSQL。有关如何将本地IBM Db2 for iSeries数据库迁移至Amazon RDS for PostgreSQL或Amazon Aurora PostgreSQL的详细信息，请参见使用Blu Age和Qlik Replicate迁移IBM Db2 for iSeries数据库到Amazon Aurora PostgreSQL

解决方案概述

PostgreSQL分区 是指将一个逻辑上大型表拆分为更小的物理片段。在该解决方案中，我们使用触发器函数将数据从分区表复制到非分区表，实现插入、更新和删除操作。无论在分区表上进行何种数据被插入、删除或更新操作，触发器函数都会自动将这些更改同步至非分区表，这些数据随后可以用于使用AWS DMS或其他数据复制工具将数据复制回Db2 for iSeries数据库。

以下图示展示了使用触发器函数的数据复制架构。

在上述图示中，用户应用程序仅与分区表进行交互，触发器函数收集分区表中的变更并将其应用于非分区表，后者可作为将数据复制回本地数据库的数据源。

在您在分区表上部署触发器函数后，无需进行进一步操作。提交到分区表的事务会自动复制到非分区表中。

实施此解决方案需要以下高层步骤：

创建PostgreSQL数据库和架构创建一个带有主键的分区表创建一个非分区表创建插入函数和触发器创建更新函数和触发器创建删除函数和触发器执行数据复制测试

先决条件

您需要具备以下先决条件以实施该解决方案：

一个有效的AWS账户一个堡垒主机以访问数据库：Windows或Linux，托管在本地或AWS云中一个具有用户权限的RDS for PostgreSQL或Aurora PostgreSQL实例，可以创建和部署数据库对象。有关更多信息，请参见管理PostgreSQL用户和角色在堡垒主机上，需要有PostgreSQL客户端如pgAdmin客户端，psql或DBeaver，以便连接 RDS for PostgreSQL或Aurora PostgreSQL DB实例

创建PostgreSQL数据库和架构

连接到数据库实例后，完成以下步骤：

运行以下查询以创建演示数据库：Create database demo从默认数据库会话断开连接，并连接到新创建的演示数据库查询工具。运行以下查询以在演示数据库中创建一个demouser：

Create schema demouser

现在，在数据库实例中创建了名为demo的新数据库和名为demouser的新架构。

创建带有主键的分区表

完成以下步骤：

运行以下SQL命令在演示数据库中创建一个带有主键的表：

CREATE TABLE demousercitypart (id int4 NOT NULL PRIMARY KEYname varchar(30) NOT NULLstate varchar(20)population int4)PARTITION BY RANGE (id)

在demouser架构下创建了一张名为citypart的新表。

使用以下SQL命令创建分区表的各个分区：

CREATE TABLE demousercityid1 PARTITION OF demousercitypartFOR VALUES FROM (MINVALUE) TO (10)CREATE TABLE demousercityid2 PARTITION OF demousercitypartFOR VALUES FROM (10) TO (20)CREATE TABLE demousercityid3 PARTITION OF demousercitypartFOR VALUES FROM (20) TO (30)CREATE TABLE demousercityid4 PARTITION OF demousercitypartFOR VALUES FROM (30) TO (MAXVALUE)

创建带有主键的非分区表

运行以下代码以创建一个带有主键的非分区表：

CREATE TABLE demousercitynopart (id int4 NOT NULL PRIMARY KEYname varchar(30) NOT NULLstate varchar(20)population int4)

创建插入函数

运行以下代码以创建插入SQL函数。该函数将自动将数据从分区表插入到非分区表中。

CREATE OR REPLACE FUNCTION demouserfunctioninsertdata() RETURNS TRIGGER ASBODYBEGININSERT INTOdemousercitynopart(id name state population)VALUES(newid newname newstate newpopulation)RETURN newENDBODYLANGUAGE plpgsql

为插入函数创建触发器

此触发器是一种特殊类型的存储过程，当分区表中发生插入事件时自动运行。使用以下代码创建触发器：

CREATE TRIGGER triginsertdataAFTER INSERT ON demousercitypartFOR EACH ROWEXECUTE PROCEDURE demouserfunctioninsertdata()

创建更新函数

运行以下代码以创建更新SQL函数。该函数将自动更新分区表中的数据到非分区表中。

CREATE OR REPLACE FUNCTION demouserfunctionupdatedata() RETURNS TRIGGER ASBODYBEGINUPDATE demousercitynopartSET name = NEWname state=NEWstate population=NEWpopulation WHERE id = NEWidRETURN NEWENDBODYLANGUAGE plpgsql

为更新函数创建触发器

此触发器是一种特殊类型的存储过程，当分区表中发生更新事件时自动运行。使用以下代码创建触发器：

CREATE TRIGGER trigupdatedataAFTER UPDATE ON demousercitypartFOR EACH ROWEXECUTE PROCEDURE demouserfunctionupdatedata()

创建删除函数

运行以下代码以创建删除SQL函数。该函数将自动删除分区表中记录从非分区表中。

CREATE OR REPLACE FUNCTION demouserfunctiondeletedata() RETURNS TRIGGER ASBODYBEGINDELETE FROM demousercitynopart WHERE id = OLDidRETURN NEWENDBODYLANGUAGE plpgsql

为删除函数创建触发器

此触发器是一种特殊类型的存储过程，当分区表中发生删除事件时自动运行。使用以下代码创建触发器：

CREATE TRIGGER trigdeletedataAFTER DELETE ON demousercitypartFOR EACH ROWEXECUTE PROCEDURE demouserfunctiondeletedata()

以下截图展示了在使用pgAdmin进行解决方案部署后的数据库对象。

数据复制测试

完成以下步骤以测试解决方案：

运行以下命令在分区表中插入数据：

INSERT INTO demousercitypart(id name state population)VALUES (1 Amit TX 21000000)INSERT INTO demousercitypart(id name state population)VALUES (2 AmitU NY 21000)

运行以下查询以验证分区表和非分区表中数据的自动插入：

SELECT  FROM demousercitypartSELECT  FROM demousercitynopart

以下截图展示了在分区表中插入数据后，触发器函数自动运行并将数据插入到非分区表中。

运行以下命令更新分区表中的数据：

UPDATE demousercitypartSET name=AmitUpadhyay WHERE id=2

运行以下查询以验证分区表和非分区表中数据的自动更新：

SELECT  FROM demousercitypartSELECT  FROM demousercitynopart

以下截图展示了在分区表中更新数据后，触发器函数自动运行并更新非分区表中的数据。

运行以下命令从分区表中删除数据：

DELETE FROM demousercitypart WHERE id=2

运行以下查询以验证分区表和非分区表中数据的自动删除：

SELECT  FROM demousercitypartSELECT  FROM demousercitynopart

以下截图展示了在分区表中删除数据后，触发器函数自动运行并从非分区表中删除数据。

在确认从分区表到非分区表的数据复制工作正常后，您可以使用非分区表作为将数据复制回本地数据库的源，完成从本地数据库迁移到Amazon RDS for PostgreSQL或Amazon Aurora PostgreSQL的流程。

清理

使用PostgreSQL客户端连接到您的PostgreSQL DB集群，连接到RDS for PostgreSQL或Aurora PostgreSQL数据库实例，并运行以下查询以删除数据库：

DROP DATABASE demo

有关如何使用pgAdmin删除数据库的信息，请参见DROP DATABASE。

此外，如果您不再需要，请删除AWS资源堡垒主机和数据库实例。

结论

在本文中，我们展示了如何使用触发器自动化从分区表到非分区表的数据复制，以适用于Amazon RDS for PostgreSQL或Amazon Aurora PostgreSQL。通过此解决方案，分区表与非分区表之间的CDC变更数据捕获无需额外配置。本文还讨论了PostgreSQL的关键特性、与PostgreSQL触发器相关的不同操作、它们的语法和示例用例。

如果您有任何问题、意见或建议，请在评论区留言。

作者简介

Amit Upadhyay 是一位驻德克萨斯州的高级数据库顾问，工作于亚马逊网络服务，帮助客户制定策略、解决方案设计、概念验证和最佳实践，使客户在云计算旅程中获得最佳体验。在业余时间，他喜欢探索新文化、参加社交活动和跟踪最新的技术趋势。您可以在Linkedin上找到他。

Abhishek Pandey 是一位驻休斯顿的高级解决方案架构师，工作

使用 Amazon Personalize 和 Amazon Titan 文本嵌入技术在 Amazo

在生成性人工智能时代的开发生产力 云企业战略博客