Database Migration Service (MySQL to PostgreSQL)

0

Hello AWS Community,

I hope this message finds you well. I am currently facing some challenges during my MySQL to PostgreSQL migration on Amazon RDS. I have followed several steps to set up the migration process, and I'm encountering an issue I can't seem to resolve. Here is a detailed overview of what I've done so far:

  • MySQL RDS Instance:
  1. Creation Method: Standard Create
  2. Engine Type: MySQL
  3. Engine Version: MySQL 8.0.33
  4. Template: Free Tier
  5. Database Instance Identifier: sourceDB
  6. Master Username: admin
  7. Instance Configuration: Burstable classes - db.t3.micro
  8. Storage Type: gp2 - 20 GiB
  9. Storage Auto Scaling: Disabled
  10. Public Access: Yes
  • PostgreSQL RDS Instance:
  1. Creation Method: Standard Create
  2. Engine Type: PostgreSQL
  3. Engine Version: PostgreSQL 15.4-R1
  4. Template: Free Tier
  5. Database Instance Identifier: targetDB
  6. Master Username: postgres
  7. Instance Configuration: Burstable classes - db.t3.micro
  8. Storage Type: gp2 - 20 GiB
  9. Storage Auto Scaling: Disabled
  10. Public Access: Yes
  11. Performance Insights: Turned Off
  12. Enhanced Monitoring: Disabled
  • Replication Instance:
  1. Name: RIForMigration
  2. Instance Class: dms.t3.micro
  3. High Availability: Production Workload (Multi-AZ)
  4. Storage: 50GiB
  • Endpoints:
  1. Source Endpoint Name: sourceDB, Engine: MySQL
  2. Target Endpoint Name: targetDB, Engine: PostgreSQL
  • Database Migration Task:
  1. Task Identifier: DMTask
  2. Replication Instance: RIForMigration
  3. Source Database Endpoint: sourceDB
  4. Target Database Endpoint: targetDB
  5. Migration Type: Migrate Existing Data

Despite following these steps, the migration task's status remains "Ready." When I attempted to test the endpoint connection for the target endpoint, I received the following error message:

Test Endpoint failed: Application-Status: 1020912, Application-Message: Cannot connect to ODBC provider. Network error has occurred, Application-Detailed-Message: RetCode: SQL_ERROR SqlState: 08001 NativeError: 101 Message: FATAL: no pg_hba.conf entry for host "172.31.25.29", user "postgre", database "postgre", no encryption.

I have also tried using PostgreSQL version 14.7-R1, but the issue persists. Initially, when I tested the connection for the source endpoint, it was successful, but after some time, it started showing an error related to SSL.

Please note that currently I'm in N.Virginia Region and I'm using the same security group everywhere that allows all incoming traffic.

I would greatly appreciate any assistance or guidance on resolving this issue and successfully completing the migration. Your expertise is highly valued.

Thank you for your time and support.

Best regards,

Vamsi Siddhani

asked 8 months ago429 views
1 Answer
0
Accepted Answer

Hi

"Cannot connect to ODBC provider" is usually related to a SSL issue, Try to set ssl force = 0 on your Postgres instance.

See section "Requiring an SSL connection to a PostgreSQL DB instance" of https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Concepts.General.SSL.html for more details on how to deactivate ssl

Conversely, you can configure DMS to use SSL to maintain high security: see https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Security.SSL.html

Use of SSL by DMS is supported for both PostgreSQL and MySQL, with the limitation that MySQL does not support "require" option: see table on the page above

Best,

Didier

profile pictureAWS
EXPERT
answered 8 months ago
profile picture
EXPERT
reviewed 2 months ago
  • "Thank you very much, @Didier_Durand! Your solution worked for me."

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions