7.9k

Migrate from Self-Hosted PostgreSQL to Xata

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

Prerequisites

  • Self-hosted PostgreSQL instance
  • Access to PostgreSQL configuration files
  • Xata account and project setup
  • Network access to your PostgreSQL server

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

Network Configuration

Option 1: Public Access (Less Secure)

If your PostgreSQL server is accessible from the internet:

  1. Configure Firewall Rules:

    • Allow PostgreSQL port (5432) from your IP address
    • Or temporarily allow from anywhere (0.0.0.0/0) for migration

  2. Update PostgreSQL Configuration:

    • Edit postgresql.conf: Set listen_addresses = '*'
    • Edit pg_hba.conf: Add host all all 0.0.0.0/0 md5

For better security, use private network access:

  1. VPN or Private Network:

    • Connect via VPN to your private network
    • Use private IP addresses for connection
    • Ensure your migration machine is in the same network

  2. SSH Tunnel:

    ssh -L 5432:localhost:5432 user@your-server-ip

Option 3: Direct Server Access

If you have direct access to the server:

  1. Run migration locally on the PostgreSQL server
  2. Install Xata CLI on the server
  3. Use localhost for connection

Get Connection String

Connection String Format

Self-hosted PostgreSQL connection strings typically look like this:

postgresql://your_username:your_password@your-server-ip:5432/your_database

SSL Configuration

If your PostgreSQL server requires SSL:

postgresql://your_username:your_password@your-server-ip:5432/your_database?sslmode=require

If SSL is disabled:

postgresql://your_username:your_password@your-server-ip:5432/your_database?sslmode=disable

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

    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