8.4k

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

Replicate Database Roles

Before migrating your data, you'll need to replicate the database roles from your source database to your Xata branch.

Extract Roles from Source Database

Connect to your source database and extract the role definitions:

-- Extract role definitions (run this on your source database)
SELECT 
    'CREATE ROLE ' || rolname || ' WITH ' ||
    CASE WHEN rolcanlogin THEN 'LOGIN ' ELSE 'NOLOGIN ' END ||
    CASE WHEN rolsuper THEN 'SUPERUSER ' ELSE 'NOSUPERUSER ' END ||
    CASE WHEN rolinherit THEN 'INHERIT ' ELSE 'NOINHERIT ' END ||
    CASE WHEN rolcreaterole THEN 'CREATEROLE ' ELSE 'NOCREATEROLE ' END ||
    CASE WHEN rolcreatedb THEN 'CREATEDB ' ELSE 'NOCREATEDB ' END ||
    CASE WHEN rolreplication THEN 'REPLICATION ' ELSE 'NOREPLICATION ' END ||
    CASE WHEN rolbypassrls THEN 'BYPASSRLS ' ELSE 'NOBYPASSRLS ' END ||
    'CONNECTION LIMIT ' || COALESCE(rolconnlimit, -1) || ';' as create_role_statement
FROM pg_roles 
WHERE rolname NOT LIKE 'pg_%'
ORDER BY rolname;

Apply Roles to Xata Branch

  1. Connect to your Xata branch:

    psql `xata branch url`

  2. Execute the role creation statements from the previous step

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