Postgres Imports - Amazon DMS

Amazon Database Migration Service (DMS) provides a managed migration service that can handle complex database migrations with built-in monitoring, error handling, and data validation. This method is ideal for large, complex databases that require robust migration capabilities.

Overview

This migration method involves:

Setting up AWS DMS replication instance and endpoints

Configuring source and target database connections

Creating and running migration tasks with full load and CDC

Monitoring migration progress and performing cutover

NoteThis method requires an AWS account and will incur AWS DMS charges. Review AWS DMS pricing before proceeding.

NoteFor Aurora users: Consider the Aurora to PlanetScale CloudFormation & DMS tutorial for a fully automated approach using CloudFormation templates and Step Functions workflows instead of manual DMS setup.

Prerequisites

Before starting the migration:

Active AWS account with appropriate DMS permissions
Source PostgreSQL database accessible from AWS (consider VPC configuration)
Connection details for your PlanetScale for Postgres database from the console
Ensure the disk on your PlanetScale database has at least 150% of the capacity of your source database. If you are migrating to a PlanetScale database backed by network-attached storage, you can resize your disk manually by setting the “Minimum disk size.” If you are using Metal, you will need to select a size when first creating your database. For example, if your source database is 330GB, you should have at least 500GB of storage available on PlanetScale.
Understanding of your data transformation requirements (if any)
Network connectivity between AWS and both source and target databases

Step 1: Set Up AWS DMS

Create DMS Replication Instance

Navigate to the AWS DMS Console

Click “Create replication instance”

Configure the instance:

Name: planetscale-postgres-migration
Description: Migration to PlanetScale for Postgres
Instance class: dms.t3.medium (adjust based on your needs)
Engine version: Latest available
VPC: Select appropriate VPC
Multi-AZ: No (for cost savings, Yes for production)
Publicly accessible: Yes (if needed for connectivity)

Configure Security Groups

Ensure your replication instance can connect to:

Source PostgreSQL database (port 5432)
PlanetScale for Postgres (port 5432)
Internet for PlanetScale connectivity

Step 2: Create Source Endpoint

Configure PostgreSQL source endpoint:

In DMS Console, go to “Endpoints” > “Create endpoint”

Configure source endpoint:

Endpoint type: Source endpoint
Endpoint identifier: postgres-source
Source engine: postgres
Server name: your-postgres-host
Port: 5432
Database name: your-database-name
Username: your-username
Password: your-password

Advanced settings for PostgreSQL:

Extra connection attributes:
pluginName=test_decoding;
slotName=dms_slot_planetscale;
captureDDLs=false;
maxFileSize=32768;

Step 3: Create Target Endpoint

Configure PlanetScale for Postgres target endpoint:

Create target endpoint with PlanetScale connection details:

Endpoint type: Target endpoint
Endpoint identifier: planetscale-target
Target engine: postgres
Server name: [from PlanetScale console]
Port: [from PlanetScale console]
Database name: [from PlanetScale console]
Username: [from PlanetScale console]
Password: [from PlanetScale console]

SSL Configuration:

SSL mode: require

Step 4: Test Endpoints

Select your source endpoint and click “Test connection”

Select your target endpoint and click “Test connection”

Ensure both tests pass before proceeding

Step 5: Create Migration Task

Configure the migration task:

Go to “Database migration tasks” > “Create task”

Configure task settings:

Task identifier: postgres-to-planetscale
Replication instance: planetscale-postgres-migration
Source database endpoint: postgres-source
Target database endpoint: planetscale-target
Migration type: Migrate existing data and replicate ongoing changes

Task Settings

{
  "TargetMetadata": {
    "TargetSchema": "",
    "SupportLobs": true,
    "FullLobMode": false,
    "LobChunkSize": 0,
    "LimitedSizeLobMode": true,
    "LobMaxSize": 32,
    "InlineLobMaxSize": 0,
    "LoadMaxFileSize": 0,
    "ParallelLoadThreads": 0,
    "ParallelLoadBufferSize": 0,
    "BatchApplyEnabled": false,
    "TaskRecoveryTableEnabled": false,
    "ParallelApplyThreads": 0,
    "ParallelApplyBufferSize": 0,
    "ParallelApplyQueuesPerThread": 0
  },
  "FullLoadSettings": {
    "TargetTablePrepMode": "DROP_AND_CREATE",
    "CreatePkAfterFullLoad": false,
    "StopTaskCachedChangesApplied": false,
    "StopTaskCachedChangesNotApplied": false,
    "MaxFullLoadSubTasks": 8,
    "TransactionConsistencyTimeout": 600,
    "CommitRate": 10000
  },
  "Logging": {
    "EnableLogging": true,
    "LogComponents": [
      {
        "Id": "TRANSFORMATION",
        "Severity": "LOGGER_SEVERITY_DEFAULT"
      },
      {
        "Id": "SOURCE_UNLOAD",
        "Severity": "LOGGER_SEVERITY_DEFAULT"
      },
      {
        "Id": "TARGET_LOAD",
        "Severity": "LOGGER_SEVERITY_DEFAULT"
      }
    ]
  },
  "ControlTablesSettings": {
    "historyTimeslotInMinutes": 5,
    "ControlSchema": "",
    "HistoryTimeslotInMinutes": 5,
    "HistoryTableEnabled": false,
    "SuspendedTablesTableEnabled": false,
    "StatusTableEnabled": false,
    "FullLoadExceptionTableEnabled": false
  }
}

Step 6: Configure Table Mappings

Basic table mapping (migrate all tables):

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

Advanced table mapping with transformations:

{
  "rules": [
    {
      "rule-type": "selection",
      "rule-id": "1",
      "rule-name": "1",
      "object-locator": {
        "schema-name": "public",
        "table-name": "%"
      },
      "rule-action": "include"
    },
    {
      "rule-type": "transformation",
      "rule-id": "2",
      "rule-name": "2",
      "rule-target": "schema",
      "object-locator": {
        "schema-name": "public"
      },
      "rule-action": "rename",
      "value": "public"
    }
  ]
}

Step 7: Start Migration Task

Review all task configurations

Click “Create task” to start the migration

Monitor the task status in the DMS console

Step 8: Monitor Migration Progress

Key metrics to monitor:

Full load progress: Percentage of tables loaded
CDC lag: Latency between source and target
Error count: Any migration errors
Throughput: Records per second

Using CloudWatch:

Set up CloudWatch alarms for:

High CDC latency
Migration errors
Task failures

# CLI command to check task status
aws dms describe-replication-tasks \
    --filters Name=replication-task-id,Values=your-task-id

Step 9: Verify Data Migration

Check table counts and data integrity:

-- Run on both source and target databases
SELECT
    schemaname,
    tablename,
    n_tup_ins as estimated_rows,
    n_tup_upd as updated_rows,
    n_tup_del as deleted_rows
FROM pg_stat_user_tables
ORDER BY schemaname, tablename;

Validate specific data:

-- Compare checksums for critical tables
SELECT count(*), md5(string_agg(column_name::text, ''))
FROM your_important_table
ORDER BY primary_key;

Step 10: Prepare for Cutover

Monitor CDC lag:

Ensure CDC latency is minimal (under 5 seconds) before cutover:

-- Check DMS validation status
SELECT * FROM awsdms_validation_failures_v1;

Test application connectivity:

Create a read-only connection to PlanetScale for Postgres
Test critical application queries
Verify performance is acceptable

Step 11: Perform Cutover

When ready to switch to PlanetScale for Postgres:

Stop application writes to source database

Wait for CDC to catch up (monitor lag in DMS console)

Verify final data consistency

Update application connection strings to point to PlanetScale

Start application with new connections

Stop DMS task once satisfied with cutover

Stop the migration task:

aws dms stop-replication-task \
    --replication-task-arn arn:aws:dms:region:account:task:task-id

Step 12: Cleanup

After successful cutover:

Delete DMS task

Delete replication instance (to stop charges)

Remove source and target endpoints

Clean up security groups if created specifically for migration

# Cleanup commands
aws dms delete-replication-task --replication-task-arn your-task-arn
aws dms delete-replication-instance --replication-instance-arn your-instance-arn
aws dms delete-endpoint --endpoint-arn your-source-endpoint-arn
aws dms delete-endpoint --endpoint-arn your-target-endpoint-arn

Troubleshooting

Common Issues:

Connectivity problems:

Check security groups and network ACLs
Verify endpoint configurations
Test network connectivity from replication instance

Performance issues:

Increase replication instance size
Adjust parallel load settings
Monitor source database performance

Data type mapping issues:

Review DMS data type mapping
Use transformation rules for custom mappings

Large object (LOB) handling:

{
  "TargetMetadata": {
    "SupportLobs": true,
    "FullLobMode": true,
    "LobChunkSize": 32768,
    "LimitedSizeLobMode": false
  }
}

Performance Optimization:

Parallel loading: Increase MaxFullLoadSubTasks
Batch apply: Enable for better target performance
Memory allocation: Increase replication instance size
Network optimization: Use placement groups for better network performance

Cost Optimization

Instance sizing: Start with smaller instances and scale up if needed
Multi-AZ: Disable for dev/test migrations
Task lifecycle: Delete resources immediately after successful migration
Data transfer: Consider AWS region placement to minimize transfer costs

Schema Considerations

Before migration, review:

PostgreSQL version compatibility

Extension support limitations

Third-party enhancement restrictions

Next Steps

After successful migration:

Set up monitoring and alerting for PlanetScale for Postgres

Optimize application performance with the new database

Plan for ongoing maintenance and backup strategies

Consider implementing additional PlanetScale features

For simpler migrations, consider pg_dump/restore or WAL streaming methods. If you encounter issues during migration, please reach out to support for assistance.

Need help?

Get help from the PlanetScale Support team, or join our GitHub discussion board to see how others are using PlanetScale.

Pricing

Platform

​Overview

​Prerequisites

​Step 1: Set Up AWS DMS

​Create DMS Replication Instance

​Configure Security Groups

​Step 2: Create Source Endpoint

​Configure PostgreSQL source endpoint:

​Advanced settings for PostgreSQL:

​Step 3: Create Target Endpoint

​Configure PlanetScale for Postgres target endpoint:

​SSL Configuration:

​Step 4: Test Endpoints

​Step 5: Create Migration Task

​Configure the migration task:

​Task Settings

​Step 6: Configure Table Mappings

​Basic table mapping (migrate all tables):

​Advanced table mapping with transformations:

​Step 7: Start Migration Task

​Step 8: Monitor Migration Progress

​Key metrics to monitor:

​Using CloudWatch:

​Step 9: Verify Data Migration

​Check table counts and data integrity:

​Validate specific data:

​Step 10: Prepare for Cutover

​Monitor CDC lag:

​Test application connectivity:

​Step 11: Perform Cutover

​Stop the migration task:

​Step 12: Cleanup

​Troubleshooting

​Common Issues:

​Performance Optimization:

​Cost Optimization

​Schema Considerations

PostgreSQL version compatibility

Extension support limitations

Third-party enhancement restrictions

​Next Steps

​Need help?

Overview

Prerequisites

Step 1: Set Up AWS DMS

Create DMS Replication Instance

Configure Security Groups

Step 2: Create Source Endpoint

Configure PostgreSQL source endpoint:

Advanced settings for PostgreSQL:

Step 3: Create Target Endpoint

Configure PlanetScale for Postgres target endpoint:

SSL Configuration:

Step 4: Test Endpoints

Step 5: Create Migration Task

Configure the migration task:

Task Settings

Step 6: Configure Table Mappings

Basic table mapping (migrate all tables):

Advanced table mapping with transformations:

Step 7: Start Migration Task

Step 8: Monitor Migration Progress

Key metrics to monitor:

Using CloudWatch:

Step 9: Verify Data Migration

Check table counts and data integrity:

Validate specific data:

Step 10: Prepare for Cutover

Monitor CDC lag:

Test application connectivity:

Step 11: Perform Cutover

Stop the migration task:

Step 12: Cleanup

Troubleshooting

Common Issues:

Performance Optimization:

Cost Optimization

Schema Considerations

Next Steps

Need help?