8.4k

Migrate from Azure Database for PostgreSQL to Xata

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

Prerequisites

  • Azure Database for PostgreSQL instance
  • Access to Azure Portal
  • Xata account and project setup
  • Network access to your Azure database

Create Snapshot User

Connect to your Azure database 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 Access (Less Secure)

If your Azure database allows public access:

  1. Configure Firewall Rules:
    • Go to "Networking" in the left menu
    • Add your IP address to "Firewall rules"
    • Or temporarily add 0.0.0.0/0 for migration (remove after)

For better security, use private endpoints:

  1. Enable Private Endpoint:

    • Go to "Networking" in the left menu
    • Enable "Private endpoint connections"
    • Configure VNet integration

  2. Run Migration from Azure:

    • Use Azure VM, App Service, or Azure Functions
    • Ensure the service is in the same VNet as your database

Option 3: Azure Database for PostgreSQL Flexible Server

If you're using Flexible Server:

  1. Configure Network Access:
    • Go to "Networking" in the left menu
    • Choose "Private access (VNet Integration)" or "Public access"
    • Configure firewall rules accordingly

Get Connection String

Find Your Connection Details

  1. Go to Azure Portal → Your PostgreSQL Server
  2. Click "Connection strings" in the left menu
  3. Copy the connection string

Connection String Format

Azure connection strings typically look like this:

postgresql://xata_snapshot:your_password@your-server.postgres.database.azure.com:5432/postgres?sslmode=require

Azure-Specific Formatting

Note that Azure often uses this format for usernames:

postgresql://xata_snapshot@your-server:your_password@your-server.postgres.database.azure.com:5432/postgres?sslmode=require

SSL Configuration

Azure requires SSL. Always include SSL parameters:

postgresql://user:password@host:port/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-server:your_password@your-server.postgres.database.azure.com:5432/postgres?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 firewall rules in Azure Portal
    • Verify the server name is correct
    • Ensure the server is running

  2. SSL Connection Required:

    • Add ?sslmode=require to your connection string
    • Verify SSL is enabled on the Azure server

  3. Permission Denied:

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

  4. Parameter Changes Not Applied:

    • Wait for the server restart to complete
    • Verify parameters are set correctly in Azure Portal

  5. Username Format Issues:

    • Azure may require username@servername format
    • Check the exact format in Azure connection strings

Using Azure CLI

You can also configure Azure Database using Azure CLI:

# Install Azure CLI if not already installed
curl -sL https://aka.ms/InstallAzureCLIDeb | sudo bash

# Login to Azure
az login

# Set server parameters
az postgres server configuration set \
  --resource-group your-resource-group \
  --server-name your-server-name \
  --name wal_level \
  --value logical

az postgres server configuration set \
  --resource-group your-resource-group \
  --server-name your-server-name \
  --name max_replication_slots \
  --value 5

az postgres server configuration set \
  --resource-group your-resource-group \
  --server-name your-server-name \
  --name max_wal_senders \
  --value 10

# Restart the server
az postgres server restart \
  --resource-group your-resource-group \
  --name your-server-name

Security Best Practices

  1. Use Private Endpoints when possible
  2. Remove public access after migration
  3. Use strong passwords for replication users
  4. Limit firewall rules to specific IPs
  5. Enable Azure Monitor to track access

Azure Database for PostgreSQL Flexible Server

If you're using Flexible Server, the process is similar but with some differences:

Flexible Server Parameters

  1. Go to "Server parameters"
  2. Set the same parameters as above
  3. No restart required for Flexible Server

Flexible Server Networking

  1. Choose "Private access (VNet Integration)" for better security
  2. Or use "Public access" with firewall rules
  3. Configure Azure Private DNS if using private access

Next Steps