8.4k

Migrate from AWS Aurora to Xata

Learn how to migrate your AWS Aurora PostgreSQL database to Xata using xata clone. Step-by-step instructions for configuring the migration.

Prerequisites

  • AWS Aurora PostgreSQL cluster
  • Access to AWS Console and RDS parameter groups
  • Xata account and project setup
  • Network access to your Aurora cluster

Create Snapshot User

Connect to your Aurora cluster and create a dedicated user for migration:

-- Create snapshot user (no REPLICATION privilege needed)
CREATE USER xata_snapshot WITH LOGIN PASSWORD 'your_secure_password';

-- Grant necessary privileges
GRANT SELECT ON ALL TABLES IN SCHEMA public TO xata_snapshot;
GRANT USAGE ON SCHEMA public TO xata_snapshot;

-- Grant privileges on future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO xata_snapshot;

Network Configuration

Option 1: Public Endpoint (Less Secure)

If your Aurora cluster has a public endpoint:

  1. Whitelist Your IP:
    • Go to your Aurora cluster → Security Groups
    • Add a rule allowing PostgreSQL (port 5432) from your IP address
    • Or temporarily allow from anywhere (0.0.0.0/0) for migration

For better security, run the migration from within AWS:

  1. EC2 Instance in Same VPC:

    • Launch an EC2 instance in the same VPC as your Aurora cluster
    • Install Xata CLI on the EC2 instance
    • Run the migration from there

  2. GitHub Actions with AWS Credentials:

    • Use GitHub Actions with AWS credentials
    • Configure the action to run in your VPC
    • Use the private endpoint for connection

Get Connection String

Aurora Connection String Format

postgresql://xata_snapshot:your_password@your-aurora-cluster-endpoint:5432/your_database

You can find the endpoint in the AWS RDS Console under your cluster details.

Aurora-Specific Considerations

  • Use the cluster endpoint (not individual instance endpoints)
  • Aurora Serverless v2 supports the same connection format
  • Multi-AZ deployments will automatically failover if needed

Initialize Xata Project

Set up your Xata project configuration:

xata init

Configure the Migration

Set up your clone configuration with optional anonymization:

# Set your source URL
export XATA_CLI_SOURCE_POSTGRES_URL="postgresql://xata_snapshot:your_password@your-aurora-cluster-endpoint:5432/your_database"

# Configure anonymization rules
xata clone config --source-url $XATA_CLI_SOURCE_POSTGRES_URL --mode prompt

Start the Migration

Begin the data transfer:

# Start the migration
xata clone start --source-url $XATA_CLI_SOURCE_POSTGRES_URL

Monitor Progress

Check the migration status:

xata clone status

Verification

After migration, verify your data:

  1. Connect to Xata Branch:

    xata branch url

  2. Check Data Integrity:

    -- Compare row counts
SELECT COUNT(*) FROM your_table;

-- Check sample data
SELECT * FROM your_table LIMIT 10;

  3. Test Relationships: Verify foreign key relationships work correctly

Troubleshooting

Common Issues

  1. Connection Refused:

    • Check security group rules
    • Verify the cluster endpoint is correct
    • Ensure the cluster is publicly accessible (if using public endpoint)

  2. Permission Denied:

    • Verify the snapshot user has correct privileges
    • Check that the user has sufficient permissions

  3. Cluster Parameter Group Issues:

    • Ensure your Aurora cluster supports the required features
    • Check if any cluster parameter group restrictions apply

  4. Aurora Serverless v1 Limitations:

    • Aurora Serverless v1 has limited support for certain operations
    • Consider upgrading to Aurora Serverless v2 if needed

SSL Configuration

If you encounter SSL issues, add SSL parameters to your connection string:

postgresql://xata_snapshot:your_password@your-aurora-cluster-endpoint:5432/your_database?sslmode=require

Aurora-Specific Features

Aurora Serverless v2

If you're using Aurora Serverless v2:

  • Automatic scaling won't affect the migration
  • Connection limits may apply during scaling events
  • Consider migration timing to avoid scaling operations

Multi-AZ Deployments

For Multi-AZ Aurora clusters:

  • Use the cluster endpoint for automatic failover
  • Migration will continue even if a failover occurs
  • Monitor cluster health during migration

Next Steps