Migrate to native partitioned tables in PostgreSQL 10 using AWS Database Migration Service
We’re excited to introduce AWS Database Migration Service (AWS DMS) version 2.4.3, which includes support for migrating data to native partitioned tables in PostgreSQL 10.
In this post, we discuss how you can use AWS DMS version 2.4.3 to migrate data from Oracle partitioned tables to PostgreSQL 10 natively partitioned tables. You can do so without using any special configuration. To perform schema conversion for partitioned tables in parallel with AWS DMS, you can work with AWS Schema Conversion Tool (AWS SCT), which now supports this conversion.
Partitioning refers to splitting what is logically one large table into smaller physical pieces. Partitioning can provide several benefits.
Before you use AWS DMS to migrate to partitioned tables as described in this post, you should be familiar with PostgreSQL partitioned tables. PostgreSQL introduced native partitioning support in version 10.
The following is an example of migrating data from an Oracle partitioned table to a PostgreSQL 10 native partitioned table. The source Oracle table is created with three partitions on the create date column. All partitions hold data based on respective date range values.
Step 1: Create an Oracle partitioned table using range partition criteria
The following code example shows how to create an Oracle partitioned table using range partition criteria, which is step 1.
Step 2: Create a PostgreSQL partition table using AWS SCT or manually
The following code example shows how to create a PostgreSQL partitioned table, which is step 2.
For the following steps, you can find the details in the PostgreSQL Table Partitioning topic in the PostgreSQL documentation.
Step 3: Create partitions
In the next step, you create partitions. Each partition’s definition must specify the bounds that correspond to the partitioning method and partition key of the parent table. The following code example demonstrates this step.
Step 4: Create an index on the key column or columns
In step 4, create an index on the key column or columns. At the same time, create any other indexes you might want for each partition. The following code example demonstrates this step.
Step 5: Insert a few records into the Oracle partitioned table
Next, you insert records into the Oracle partitioned table, as the following code example shows.
Step 6: Create an AWS DMS task to migrate from the Oracle partition table to the PostgreSQL partition table
In step 6, create an AWS DMS task to migrate from the Oracle partition table to the PostgreSQL partition table. To do so, use the following table mapping with DMS version 2.4.3.
Step 7: Check if the table migrated successfully after the task completed
Next, check whether the table migrated successfully after the AWS DMS task completed. The DMS task should complete without any failures and with data correctly migrated to the target PostgreSQL 10 partitioned table.
To confirm DMS migrated the records to the proper partitions on the target table, you can run the following queries.
To handle this migration properly, AWS DMS version 2.4.3 supports the new version of the PostgreSQL driver, psqlodbc-10.03.0000.
In the migration shown preceding, DMS retrieved PostgreSQL 10 partition table details and loaded the data properly.
As we demonstrate in this post, AWS DMS version 2.4.3 now supports migrating to PostgreSQL 10 natively partitioned tables. For more details, see the AWS DMS User Guide.
About the Author
Mahesh Kansara is a database engineer at Amazon Web Services. He works with our customers to provide guidance and technical assistance on various database and analytical projects, helping them improving the value of their solutions when using AWS.