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.

PostgreSQL partitioning

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.

CREATE TABLE partition_schema.partition_test 
	(id NUMBER(10) NOT NULL,
	data_value VARCHAR2(50),
	create_dt DATE DEFAULT sysdate
	)
PARTITION BY RANGE(create_dt) (
  partition p1 values less than ('01-JAN-2016'), 
  partition p2 values less than ('01-JAN-2017'), 
  partition p3 values less than ('01-JAN-2018')
);

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.

CREATE TABLE IF NOT EXISTS partition_schema.partition_test(
id NUMERIC(38,0) NOT NULL,
data_value CHARACTER VARYING(25) NOT NULL,
create_date TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL,
) PARTITION BY RANGE (create_date)
WITH (
OIDS=FALSE
);

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.

CREATE TABLE partition_schema.PERIOD_2016 PARTITION OF partition_schema.partition_test
FOR VALUES FROM ('2015-01-01 00:00:00') TO ('2016-01-01 00:00:00');
 
CREATE TABLE partition_schema.PERIOD_2017 PARTITION OF partition_schema.partition_test
FOR VALUES FROM ('2016-01-01 00:00:00') TO ('2017-01-01 00:00:00');
 
CREATE TABLE partition_schema.PERIOD_2018 PARTITION OF partition_schema.partition_test
FOR VALUES FROM ('2017-01-01 00:00:00') TO ('2018-01-01 00:00:00');

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.

CREATE index on partition_schema.PERIOD_2016 (id);
CREATE index on partition_schema.PERIOD_2017 (id);
CREATE index on partition_schema.PERIOD_2018 (id);

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.

INSERT into partition_schema.partition_test VALUES (1,'data_1','2016-04-05');
INSERT into partition_schema.partition_test VALUES (1,'data_2','2017-04-05');
INSERT into partition_schema.partition_test VALUES (1,'data_3','2018-04-05');
COMMIT;

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.

{
    "rules": [
        {
            "rule-type": "selection",
            "rule-id": "1",
            "rule-name": "1",
            "object-locator": {
                "schema-name": "partition_schema ",
                "table-name": "partition_test"
            },
            "rule-action": "include"
        }
    ]
}

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.

SELECT * FROM 'partition_schema','partition_test';

SELECT * FROM partition_schema.PERIOD_2016;
SELECT * FROM partition_schema.PERIOD_2017;
SELECT * FROM partition_schema.PERIOD_2018;

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.

Summary

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.

AWS Database Blog