Amazon Aurora PostgreSQL

Overview

This page provides the required information for setting up replications using Amazon Aurora PostgreSQL as a:

• Source

• Target

Versions Supported

Amazon Aurora PostgreSQL is supported as source and target systems in Refresh, Mirroring, and Synchronization with the following version combinations:

• Amazon Aurora PostgreSQL version 3.x with PostgreSQL 11

• Amazon Aurora PostgreSQL version 4.x with PostgreSQL 12

• Amazon Aurora PostgreSQL version 13.x with PostgreSQL 13

• Amazon Aurora PostgreSQL version 14.x with PostgreSQL 14

• Amazon Aurora PostgreSQL version 15.x with PostgreSQL 15

Prerequisites

• For versions prior to Syniti Replicate 10.7.0.21, ensure that you download the appropriate PostgreSQL .NET Data Provider from the PostgreSQL website. For Syniti Replicate 10.7.0.21 or later versions, the NpgSQL driver is included with the installer.

• Ensure that you have the Npgsql (file name: Npgsql.dll) assembly in your path name. For example, C:\Npgsql-3.2.6-net40\Npgsql.dll

• The provider version number stored in the DBReplicator.exe.config file (in the Syniti Replicate hyper folder) must match the provider version that you are using. To change the version of the provider, specify the newVersion in the DBReplicator.exe.config file as follows:

<dependentAssembly>
    <assemblyIdentity name="Npgsql" publicKeyToken="5d8b90d52f46fda7" culture="neutral">
    </assemblyIdentity>
    <bindingRedirect oldVersion="2.2.3.0" newVersion="2.2.3.0">
    </bindingRedirect>
</dependentAssembly>

Driver Matrix

Driver Matrix valid for previous versions of Syniti Replicate 10.7.0.21

Install the driver DLL using the Microsoft Global Assembly Cache Tool (GAC). If the provider is not registered, you will receive the following error when attempting to use the provider:

System.IO.FileNotFoundException: Could not load file or assembly 'Npgsql

For more information about this error, refer to our Knowledge Base.

Limitations

Limitations apply on CDC replication when using PostgreSQL as a source. Although these are listed in the link Amazon Aurora PostgreSQL database or Amazon AWS documentation, a subset is listed below as these restrictions apply specifically to use of PostgreSQL with Syniti Replicate.

The following limitations apply when using PostgreSQL as a source for AWS:

• A captured table must have a primary key. If a table doesn't have a primary key, AWS Database Migration Service (DMS) ignores DELETE and UPDATE record operations for that table.

• Timestamp with a time zone type datatype is not supported.

• AWS DMS ignores an attempt to update a primary key segment. In these cases, the target identifies the update as one that didn't update any rows. However, because the results of updating a primary key in PostgreSQL are unpredictable, no records are written to the exceptions table.

• Replication of multiple tables with the same name but where each name has a different case (for example, table1, TABLE1, and Table1) can cause unpredictable behavior, and therefore AWS DMS does not support it.

• AWS DMS does not support change processing of TRUNCATE operations.

• The OID LOB data type is not migrated to the target.

• If your source is an on-premises PostgreSQL database or a PostgreSQL database on an Amazon EC2 instance, ensure that the test_decoding output plugin (found in the Postgres contrib package) is installed on your source endpoint. For more information about the test-decoding plugin, see the PostgreSQL documentation.

• AWS DMS doesn't support replication of partitioned tables. When a partitioned table is detected, the following occurs:

  • The endpoint reports a list of parent and child tables.

  • AWS DMS creates the table on the target as a regular table with the same properties as the selected tables.

  • If the parent table in the source database has the same primary key value as its child tables, a duplicate key error is generated.

Note

To replicate partitioned tables from a PostgreSQL source to a PostgreSQL target, you first need to manually create the parent and child tables on the target. You then define a separate task to replicate to those tables. In such cases, you set the Task Configuration to Truncate before loading.

Connection Properties

Required Information

Optional Information