Migrate from Heroku Postgres to Xata

Learn how to migrate your Heroku Postgres database to Xata using xata clone. Step-by-step instructions for enabling logical replication and configuring the migration.

Prerequisites

  • Heroku Postgres add-on
  • Heroku CLI installed
  • Xata account and project setup
  • Network access to your Heroku database

Install and Configure the Xata CLI

Install the Xata CLI:

curl -fsSL https://xata.io/install.sh | bash

Authenticate with your Xata account:

xata auth login

Get Connection String

Find Your Connection Details

  1. Using Heroku CLI:

    heroku config:get DATABASE_URL --app your-app-name
  2. Using Heroku Dashboard:

    • Go to Heroku Dashboard
    • Select your app
    • Go to "Resources" → "Heroku Postgres"
    • Click "Settings" → "View Credentials"

Connection String Format

Heroku connection strings typically look like this:

postgresql://your_username:your_password@your-heroku-host:5432/your_database?sslmode=require

Network Configuration

Public Access

Heroku Postgres databases are typically accessible from the internet:

  1. No additional configuration needed for public access
  2. SSL is required by default
  3. Connection limits may apply based on your plan

Private Access (Premium Plans)

If you're on a Premium plan with private access:

  1. Use Heroku Private Spaces
  2. Configure VPC peering if needed
  3. Run migration from within Heroku environment

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://your_username:your_password@your-heroku-host:5432/your_database?sslmode=require"

# 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:

    psql `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

Next Steps