AWS DMS Postgres: Migration Made Easy

In today’s dynamic business environment, companies often need to migrate their databases for many different reasons, ranging from scaling their operations to modernizing their technology stack or moving to the cloud to enjoy numerous benefits. 

However, database migration is usually a complex and resource-intensive process, which, if not handled properly, might lead to huge amounts of downtime and possible data loss. This is where AWS DMS comes in handy. 

In this blog, we’ll discuss AWS DMS Postgres migration, features of AWS DMS, how to use it to configure PostgreSQL as a target, its Limitations, and finally, a better alternative to it. So, let’s get started!

What is AWS DMS?

AWS Database Migration Service (AWS DMS) is a tool for moving data from a source to a target destination. Whether you are dealing with relational databases, data warehouses, NoSQL databases, or other data sources, AWS DMS can securely migrate your data to AWS Cloud destinations. 

Key Features of AWS DMS

1. Minimal Downtime: One significant advantage of AWS DMS is that it migrates the database while keeping the source database running. This translates to application availability for the continuity of business activities.
2. Supports various Connectors: It supports broad database support, including source and target databases such as Amazon Aurora, PostgreSQL, MySQL, MariaDB, Oracle, SQL Server, and SAP ASE. 
3. Continuous Data Replication: AWS DMS can replicate all changes continuously to a target database, guaranteeing consistency in data between the target and source databases.
4. High Availability: With its built-in support for multi-AZ deployments, AWS DMS can provide high availability and durability for the migration process.

How to Migrate Data from AWS DMS?

You can use AWS DMS to migrate your data to and from some of the most popular databases. The service supports two kinds of migrations:

• Homogeneous migrations, like Oracle to Oracle.
• Heterogeneous migrations between different database platforms, like PostgreSQL to Amazon Relational Database Service (Amazon RDS) for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition.

To put it simply, AWS DMS works in the following steps :

1. Creation of a Replication Instance: This will be used as the engine for your migration. You must configure appropriate computing and storage resources for this instance.
2. Configure Endpoints: Note the details for the connection to your Source and your Target databases.
3. Type of Migration: Choose whether to go for the Full load, CDC, or a mix. A full load transfers all the data simultaneously while the CDC replicates changes.
4. Start Migration Task: Configure the migration task with any transformation rules or filters and start it. AWS DMS will take over, migrating your data efficiently.

What is PostgreSQL?

PostgreSQL, more commonly known as Postgres, is an open-source relational database. It is very reliable, highly scalable, and supports advanced data types. It is widely used for everything from small web applications to large business-critical systems and even data warehousing.

Features of PostgreSQL

• Supports Various Data styles.
• Maintains Data Integrity.
• Reliability, Disaster Recovery.
• Robust access-control system.

What Versions of Postgres does AWS DMS support?

You can migrate data to PostgreSQL databases using AWS DMS in two ways: from another PostgreSQL database or another supported database. It supports the following versions of Postgres:

• PostgreSQL version 9.4 and higher (for versions 9.x), 10.x, 11.x, 12.x, 13.x, 14.x.
• DMS supports PostgreSQL 15.x in versions 3.5.1 and higher.
• DMS supports PostgreSQL 16.x in versions 3.5.3 and higher.

For more information about Targets in AWS DMS, see Targets for AWS DMS.

Configure Postgres as a target for AWS DMS

Security requirements for using a PostgreSQL database as an AWS Database Migration Service target. 

To connect Postgres as your target for AWS DMS, you must meet specific security requirements. For security reasons, the user account used for the migration must be registered in any PostgreSQL database used as the target.

PostgreSQL target endpoint requires some user permissions to run an AWS DMS migration, some of them are: 

CREATE USER newuser WITH PASSWORD 'your-password';
ALTER SCHEMA schema_name OWNER TO newuser;

or,

GRANT USAGE ON SCHEMA schema_name TO myuser;
GRANT CONNECT ON DATABASE postgres to myuser;
GRANT CREATE ON DATABASE postgres TO myuser;
GRANT CREATE ON SCHEMA schema_name TO myuser;
GRANT UPDATE, INSERT, SELECT, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA schema_name TO myuser;
GRANT TRUNCATE ON schema_name."BasicFeed" TO myuser;

Please refer to our previous blogs to learn how to configure Postgres as a source for AWS DMS. 

Target data types for PostgreSQL

The AWS DMS PostgreSQL database endpoint supports most PostgreSQL database data types. The following table illustrates the PostgreSQL database target data types supported using AWS DMS and the default mapping from the AWS DMS data types.

DATA TYPES – AWS DMS	DATA TYPES – POSTGRES
BOOLEAN	BOOLEAN
BLOB	BYTEA
BYTES	BYTEA
DATE	DATE
TIME	TIME
DATETIME	If the scale is from 0 through 6, then use TIMESTAMP. If the scale is from 7 through 9, then use VARCHAR (37).
INT1	SMALLINT
INT2	SMALLINT
INT4	INTEGER
INT8	BIGINT
NUMERIC	DECIMAL(P,S)
REAL4	FLOAT4
REAL8	FLOAT8
STRING	If the length is from 1 through 21,845, then use VARCHAR (length in bytes). If the length is 21,846 through 2,147,483,647, use VARCHAR (65535).
UNIT1	SMALLINT
UNIT2	INTEGER
UNIT4	BIGINT
UNIT8	BIGINT
WSTRING	If the length is from 1 through 21,845, use VARCHAR (length in bytes). If the length is 21,846 through 2,147,483,647, use VARCHAR (65535).
NCLOB	TEXT
CLOB	TEXT

Limitations of using PostgreSQL as a target for AWS DMS

These are the limitations of using Postgres as a target:

• During heterogeneous migrations, the JSON is converted to the Native CLOB data type internally.
• During Oracle to Postgres migration, if a column contains a NULL character (hex value U+0000), AWS DMS converts the NULL character to a space (hex value U+0020) due to a PostgreSQL limitation.
• AWS DMS does not support replication to a table with a unique index created with a coalesce function.
• DMS copies data from your source database but doesn’t migrate sequences to the target during the ongoing replication. If your tables use sequences, then update the value of NEXTVAL for each sequence in the target database after you stop the replication from the source database. 
• Active referential integrity constraints can cause task failure. This is because, in the full load phase, tables are out of sync while cached transactions for individual tables are being applied. 

Configure Postgres Using Hevo

Hevo helps you overcome these challenges with ease. Hevo Data is one of the best-automated tools for migrating and integrating your data into PostgreSQL; it supports other databases, too. Following is the method to configure PostgreSQL using Hevo Data:

1. Click DESTINATIONS in the Navigation Bar.
2. Click + CREATE in the Destinations List View.
3. In the Add Destination page, select PostgreSQL.
4. In the Configure your PostgreSQL Destination page, specify the following:

And that’s it! You are done exporting your data to Postgres. Hevo not only connects your source and destination in minutes but also provides various other features, a few of which are:

1. Multiple Workspaces within a Domain: This feature allows users to create multiple workspaces with the same domain name.
2. ELT Pipelines: Hevo’s no-code ELT Pipelines can quickly load vast volumes of data from diverse sources into your desired destination. Then, you can use Hevo’s platform to transform your data.  
3. In-built Transformations: Features like Python code-based and Drag-and-Drop Transformations in Hevo allow you to cleanse and prepare the data. 
4. Post-Load Transformations: After loading data to the Destination, you can transform it further for analysis by configuring dbt™ Models, creating SQL Models, and combining them in Workflows.
5. Historical Data Sync: This is for both Database and SaaS sources. 
6. Flexible Data Replication Options: You can replicate entire databases, specific tables, or individual columns. You can also customize the data that you want to load. 
7. On-Demand Credit and Usage-based Pricing: On-Demand Credit helps you continue loading data without interruption, even when your Events quota is exhausted. Hevo offers very competitive tier-based pricing along with 60+ free sources.  

Conclusion

In this blog, we discussed the steps for AWS DMS Postgres migration, its limitations, and an alternative to solve those limitations. To sum it up, AWS DMS is an effective tool for data migration with lots of potential, but it does not give users the best migration experience in some ways. 

These limitations can be manually overcome with some technical efforts. Despite these efforts, the process can be tedious and time-consuming.

Hevo Data makes this process easy with an automated two-step method that ensures seamless migration. Looking for the easiest way to migrate data to destinations like PostgreSQL? Try Hevo Data with their 14-day free trial.

Frequently Asked Questions