8.4k

Migrate from GCP Cloud SQL to Xata

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

Prerequisites

  • GCP Cloud SQL PostgreSQL instance
  • Access to Google Cloud Console
  • Xata account and project setup
  • Network access to your Cloud SQL instance

Create Snapshot User

Connect to your Cloud SQL instance 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 IP (Less Secure)

If your Cloud SQL instance has a public IP:

  1. Authorize Your IP:
    • Go to your Cloud SQL instance → "Connections" → "Networking"
    • Add your IP address to "Authorized networks"
    • Or temporarily add 0.0.0.0/0 for migration (remove after)

For better security, use private IP:

  1. Enable Private IP:

    • Go to your Cloud SQL instance → "Connections" → "Networking"
    • Enable "Private IP"
    • Configure VPC peering if needed

  2. Run Migration from GCP:

    • Use Cloud Run, Compute Engine, or Cloud Functions
    • Ensure the service is in the same VPC as your Cloud SQL

Option 3: Cloud SQL Proxy

Use Cloud SQL Proxy for secure connections:

# Install Cloud SQL Proxy
curl -o cloud-sql-proxy https://dl.google.com/cloudsql/cloud-sql-proxy.linux.amd64
chmod +x cloud-sql-proxy

# Start the proxy
./cloud-sql-proxy --instances=your-project:your-region:your-instance

Get Connection String

Connection String Format

postgresql://xata_snapshot:your_password@your-cloudsql-ip:5432/your_database

Find Your Instance Details

  1. Go to Cloud SQL Console → Instances
  2. Click on your instance
  3. Note the Public IP address (if using public IP)
  4. Note the Private IP address (if using private IP)

SSL Configuration

Cloud SQL requires SSL by default. Include SSL parameters in your connection string:

postgresql://xata_snapshot:your_password@your-cloudsql-ip:5432/your_database?sslmode=require

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-cloudsql-ip: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:

    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 authorized networks
    • Verify the IP address is correct
    • Ensure the instance is running

  2. SSL Connection Required:

    • Add ?sslmode=require to your connection string
    • Or use Cloud SQL Proxy for secure connections

  3. Permission Denied:

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

  4. Database Flags Not Applied:

    • Wait for the instance restart to complete
    • Verify flags are set correctly in the console

Using Cloud SQL Proxy

If you're having connection issues, try using Cloud SQL Proxy:

# Start proxy
./cloud-sql-proxy --instances=your-project:your-region:your-instance

# Use localhost in connection string
export XATA_CLI_SOURCE_POSTGRES_URL="postgresql://xata_snapshot:your_password@localhost:5432/your_database"

# Run migration
xata clone start --source-url $XATA_CLI_SOURCE_POSTGRES_URL

Security Best Practices

  1. Use Private IP when possible
  2. Remove public IP access after migration
  3. Use strong passwords for snapshot users
  4. Limit authorized networks to specific IPs
  5. Enable Cloud Audit Logs to monitor access

Next Steps