Remote-to-Remote PostgreSQL Migration: Theory and Practice

A guide for anyone who needs to clone a PostgreSQL database from one server to another—whether you use Prisma, another ORM, or raw SQL. We explain the concepts first, then show concrete scripts you can adapt. No prior knowledge of our app is required.

Who this is for: Developers or ops people planning a one-off or repeatable database move (new host, new region, new environment). The theory applies to any relational DB; the code examples use PostgreSQL and Prisma and can be translated to other stacks.

---

Theory: What is “remote-to-remote” migration?

Remote-to-remote means copying a database from one already running database server (the source) to another already running server (the target). Both are “remote” in the sense that they live on a host you connect to over the network—as opposed to dumping on your laptop and restoring elsewhere.

You might do this when:

• Changing hosting or region — Moving from one cloud provider or region to another (e.g. new AWS region, new PaaS org).
• Disaster recovery or failover — Bringing up a replica or standby in a different datacenter.
• Environment cloning — Creating a staging or QA database that mirrors production.
• Compliance or isolation — Moving data to a new tenant or organisation boundary (e.g. new “org” in a multi-tenant platform).

In all cases the goal is the same: the target should end up with the same schema (tables, columns, constraints, indexes, enums, triggers) and the same data (rows) as the source, so you can point your application at the new database and keep running.

---

Theory: Schema vs data

A PostgreSQL database has two main parts:

1. Schema (structure) — Tables, columns, data types, primary keys, foreign keys, unique and check constraints, indexes, custom types (e.g. enums), sequences, triggers, functions. This is the “shape” of your data.
2. Data — The actual rows in those tables.

When you migrate:

• You must establish the schema on the target first (so tables and constraints exist).
• Then you copy the data in an order that respects foreign keys (parent rows before child rows).
• Finally you verify that the target matches the source (same tables, same row counts, and optionally same schema objects).

If you use an ORM or migration tool (e.g. Prisma, Flyway, Liquibase), the “schema” on the target is usually created by running your migrations against the new database. That way the target is defined by the same migration history as the rest of your project, not by a one-off dump.

---

Theory: Two ways to get schema + data to the target

Approach	How schema gets to target	How data gets to target	Best when
A. Deploy then copy	Run your migration tool (e.g. prisma migrate deploy) on the target	Application-level copy: connect to both DBs, read rows from source, insert into target in FK order	You want to avoid depending on pg_dump/pg_restore or your local Postgres client version. Works with any stack that can connect to both DBs.
B. Full dump/restore	pg_dump (schema + data) from source → pg_restore into target	Same dump file carries both	You’re comfortable with pg_dump and your client version is ≥ the server’s. One-shot clone.

Why Option A is often safer: pg_dump and pg_restore are tied to a specific PostgreSQL client version. If the server is newer (e.g. 17) and your laptop has 15, you can hit compatibility issues or subtle errors. With Option A, your app (e.g. Node + Prisma) talks to both databases using the same driver; the server version is what matters, not the tool on your machine.

---

Theory: Foreign keys and copy order

PostgreSQL (and any relational DB) enforces foreign key constraints: a row in a “child” table cannot reference a non-existent row in the “parent” table. So you must insert parents before children.

Example: if sessions has user_id → users.id, you must copy all users rows before any sessions rows. So the order of tables in your data copy script is critical. You need a list of tables in dependency order (topological order with respect to FKs). You can derive this from your schema or from the database’s information_schema and pg_catalog. In the code below we use a fixed list that we keep in sync with our schema.

---

Theory: PostgreSQL enums and “migrate-only” targets

PostgreSQL supports custom enum types. Once created, you can add new values with ALTER TYPE ... ADD VALUE. But you cannot add a value to an enum that doesn’t exist. If the target database was created only by running migrations (no full clone from source), it’s possible that an older migration never created a certain enum—e.g. it was added later in the app’s life. In that case, a migration that only does ALTER TYPE "ChartType" ADD VALUE 'TRANSIT_MOON' will fail on that target with “type does not exist”.

So the safe pattern in a migration is: if the enum doesn’t exist, create it with all values; else add the new values. That way the same migration works both on (a) a DB that already had the enum (e.g. cloned from production) and (b) a DB that was built from scratch by running migrations only.

---

Theory: Verification — row counts and schema diff

Copying data can fail silently (e.g. duplicate key skips, or a few rows error out). So you should verify the target.

1. Row counts — For each table, compare COUNT(*) on source vs target. If they match (and the set of tables matches), you have strong evidence that data was copied. This is cheap and easy to automate.
2. Schema diff — Compare not just tables but primary keys, foreign keys, unique constraints, indexes, check constraints, enum types, sequences, and triggers. That way you catch “target missing an index” or “target has different enum values”. Comparing by meaning (e.g. constraint clause, not constraint name) avoids false differences when constraint names are auto-generated differently on each DB.

Optional objects (e.g. a table used only by a trigger on the source) may exist only on one side. You can either apply the same trigger/table on the target, or explicitly exclude those from the schema comparison so they don’t cause a failure.

---

Why we did it (our case)

We had an existing app with a source database (current production) and a target database (new, empty) in a new organisation. Goal: clone schema and data to the target, verify parity, then point the app at the new DB—without long downtime or manual table-by-table copy. The theory above guided how we designed the scripts.

---

What we learned (short version)

1. Migration order matters. If migrations depend on each other (e.g. GDPR tables before a migration that adds columns), rename the migration folders so they run in the right order (e.g. 20251210_1_add_gdpr_tables, 20251210_2_add_error_message).
2. Enums and “migrate-only” DBs. If the target DB was created only by running Prisma migrations (no prior full clone), the DB might not have an enum that was added later in the schema. Our migration had to create the enum if it doesn’t exist, then add values—otherwise ALTER TYPE ... ADD VALUE fails.
3. Avoid pg_dump version mismatch. Using pg_dump/pg_restore from a client older than the server can cause subtle failures. We preferred a Node + Prisma data copy so we didn’t depend on local PostgreSQL client versions.
4. Copy order = FK order. Copy tables in dependency order (parents before children) to satisfy foreign keys. We maintain a single ordered list and use raw SQL with proper enum/JSONB casting.
5. Verify with row counts and schema. We run a row-count comparison and a separate schema comparison (PKs, FKs, indexes, constraints, enums, sequences, triggers) so we know the new DB is really in parity.
6. Triggers and optional objects. Things like user_deletion_trigger and user_deletion_logs may exist only on the source. Document them and apply the same SQL on the target (or exclude them from schema compare) so “diff” doesn’t mean “wrong.”
7. Special characters in passwords. Use URL-encoded passwords in DATABASE_URL (e.g. ^ → %5E, } → %7D).

---

Two ways to do it (practice)

Below we give concrete scripts for both approaches. You can reuse them for any PostgreSQL project; replace table lists and connection details with your own.

Option A: Deploy migrations on target, then copy data (recommended)

Theory in practice: Schema comes from your migration tool (e.g. Prisma); data is copied by an app that connects to both DBs in FK order; then we verify with row counts (and optionally schema diff). No dependency on local pg_dump version.

Steps: Deploy schema on target → copy data with a Node script → compare row counts (and optionally schema).

Option B: Full clone with pg_dump / pg_restore

Theory in practice: One binary dump carries schema + data. Restore into target. Your local pg_dump/pg_restore version should be ≥ the server’s PostgreSQL version.

Below we focus on Option A (full scripts), then give the exact commands for Option B.

---

Option A: Step-by-step with code

1. Prerequisites

• Node.js, tsx, and Prisma in your backend.
• Two env vars (or args): SOURCE_DATABASE_URL (old DB) and TARGET_DATABASE_URL (new, empty DB). Create the target DB in your host (e.g. dcdeploy) first.

2. Deploy schema on the target

We deploy Prisma migrations on the target, then run db push so any tables that exist in the schema but aren’t in migrations (e.g. profiles, charts_cache) are created.

Orchestration script: backend/scripts/deploy-migrate-compare.sh

#!/usr/bin/env bash
#
# 1) Deploy Prisma migrations to the NEW (target) database
# 2) Copy all data from OLD (source) remote to NEW (target) remote (Node script, no pg_dump)
# 3) Compare source vs target to verify parity
#
set -e

SOURCE_URL="${1:-$SOURCE_DATABASE_URL}"
TARGET_URL="${2:-$TARGET_DATABASE_URL}"

if [ -z "$SOURCE_URL" ] || [ -z "$TARGET_URL" ]; then
  echo "Usage: SOURCE_DATABASE_URL=... TARGET_DATABASE_URL=... $0"
  exit 1
fi

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
cd "$SCRIPT_DIR/.."

echo "=========================================="
echo "1) Deploy migrations to TARGET database"
echo "=========================================="
export DATABASE_URL="$TARGET_URL"
npx prisma migrate deploy

echo "1b) Sync schema (create any tables not in migrations)"
echo "=========================================="
npx prisma db push

echo "=========================================="
echo "2) Copy data: source remote → target remote"
echo "=========================================="
SOURCE_DATABASE_URL="$SOURCE_URL" TARGET_DATABASE_URL="$TARGET_URL" npx tsx scripts/migrate-remote-to-remote-data.ts

echo "=========================================="
echo "3) Compare source vs target"
echo "=========================================="
SOURCE_DATABASE_URL="$SOURCE_URL" TARGET_DATABASE_URL="$TARGET_URL" npx tsx scripts/compare-remote-databases.ts

echo "Done. New database is ready; point app to TARGET_DATABASE_URL."

Run from backend:

cd backend
export SOURCE_DATABASE_URL='postgresql://user:pass@old-host:port/olddb'
export TARGET_DATABASE_URL='postgresql://user:pass@new-host:port/newdb'   # URL-encode password if needed
./scripts/deploy-migrate-compare.sh

3. Data copy script (Node + Prisma, no pg_dump)

Theory in practice: We copy tables in FK order (parents first). For each row we build an INSERT; PostgreSQL requires enum columns to be cast explicitly (e.g. $1::"ChartType") and JSONB to be cast as ::jsonb, so we read column metadata from information_schema and add the right cast per column type. Duplicate key errors are treated as “already exists” (skip) so the script can be re-run safely.

Key ideas:

• Two PrismaClient instances (source and target).
• Tables listed in dependency order (users → audit_logs → sessions → …).
• For each table: SELECT * from source; for each row, build INSERT with $n::"EnumType" for enum columns and $n::jsonb for JSONB.
• Skip rows that already exist (by id) to allow re-runs.

File: backend/scripts/migrate-remote-to-remote-data.ts

/**
 * Copy data from one remote database to another (remote → remote).
 * Use after running Prisma migrations on the target. No pg_dump needed.
 *
 * Usage:
 *   SOURCE_DATABASE_URL="postgresql://..." TARGET_DATABASE_URL="postgresql://..." npx tsx scripts/migrate-remote-to-remote-data.ts
 */

import dotenv from 'dotenv';
import { PrismaClient } from '@prisma/client';

dotenv.config();

const sourceUrl = process.env.SOURCE_DATABASE_URL;
const targetUrl = process.env.TARGET_DATABASE_URL;

if (!sourceUrl || !targetUrl) {
  console.error('Set both SOURCE_DATABASE_URL and TARGET_DATABASE_URL');
  process.exit(1);
}

const sourcePrisma = new PrismaClient({ datasources: { db: { url: sourceUrl } } });
const targetPrisma = new PrismaClient({ datasources: { db: { url: targetUrl } } });

// Tables in FK order (adjust for your schema)
const TABLES_IN_ORDER = [
  'users',
  'audit_logs',
  'sessions',
  'password_resets',
  'notifications',
  'notification_preferences',
  'chart_settings',
  'payments',
  'subscriptions',
  'payment_webhook_logs',
  'payment_refunds',
  'data_export_requests',
  'data_deletion_requests',
  'consent_records',
  'profiles',
  'charts_cache',
  'matchings',
  'reports',
  'saved_transits',
  'aspect_relationships',
  'ai_predictions',
  'house_predictions',
  'ascendant_analyses',
];

async function migrateTable(tableName: string): Promise<{ migrated: number; skipped: number; errors: number }> {
  try {
    const rows = await sourcePrisma.$queryRawUnsafe(`SELECT * FROM "${tableName}"`);
    if (!Array.isArray(rows) || rows.length === 0) {
      return { migrated: 0, skipped: 0, errors: 0 };
    }

    const columns = await sourcePrisma.$queryRawUnsafe<
      Array<{ column_name: string; data_type: string; udt_name: string }>
    >(
      `SELECT column_name, data_type, udt_name FROM information_schema.columns 
       WHERE table_schema = 'public' AND table_name = $1
       ORDER BY ordinal_position`,
      tableName
    );
    const columnNames = columns.map((c) => c.column_name);
    const isEnum = (c: (typeof columns)[0]) => c.data_type === 'USER-DEFINED' && c.udt_name;
    const isJson = (c: (typeof columns)[0]) => c.data_type === 'jsonb' || c.udt_name === 'jsonb';
    let migrated = 0, skipped = 0, errors = 0;

    for (const row of rows as Record<string, unknown>[]) {
      try {
        const hasId = row.id !== undefined && row.id !== null;
        if (hasId) {
          const existing = await targetPrisma.$queryRawUnsafe<unknown[]>(
            `SELECT 1 FROM "${tableName}" WHERE id = $1 LIMIT 1`,
            row.id
          );
          if (Array.isArray(existing) && existing.length > 0) {
            skipped++;
            continue;
          }
        }

        const cols: string[] = [];
        const vals: unknown[] = [];
        const placeholders: string[] = [];
        let idx = 0;
        for (let i = 0; i < columnNames.length; i++) {
          const col = columnNames[i];
          if (row[col] === undefined) continue;
          cols.push(`"${col}"`);
          let val = row[col];
          if (isJson(columns[i]) && (typeof val === 'object' || Array.isArray(val))) {
            val = typeof val === 'string' ? val : JSON.stringify(val);
          }
          vals.push(val);
          const colMeta = columns[i];
          if (isEnum(colMeta)) {
            placeholders.push(`$${idx + 1}::"${colMeta.udt_name}"`);
          } else if (isJson(colMeta)) {
            placeholders.push(`$${idx + 1}::jsonb`);
          } else {
            placeholders.push(`$${idx + 1}`);
          }
          idx++;
        }
        await targetPrisma.$executeRawUnsafe(
          `INSERT INTO "${tableName}" (${cols.join(', ')}) VALUES (${placeholders.join(', ')})`,
          ...vals
        );
        migrated++;
      } catch (e: unknown) {
        const err = e as { message?: string };
        if (err?.message?.includes('duplicate key') || err?.message?.includes('unique constraint')) {
          skipped++;
        } else {
          errors++;
          if (errors <= 3) console.error(`   Row error in ${tableName}:`, err?.message ?? e);
        }
      }
    }
    return { migrated, skipped, errors };
  } catch (e: unknown) {
    console.error(`   Table ${tableName}:`, (e as Error).message);
    return { migrated: 0, skipped: 0, errors: 1 };
  }
}

async function main() {
  console.log('Connecting to source and target...');
  await sourcePrisma.$connect();
  await targetPrisma.$connect();
  console.log('✅ Connected\n');
  for (const table of TABLES_IN_ORDER) {
    const result = await migrateTable(table);
    const sym = result.errors > 0 ? '⚠️' : '✅';
    console.log(`${sym} ${table}: migrated=${result.migrated}, skipped=${result.skipped}, errors=${result.errors}`);
  }
  console.log('\nData copy finished. Run compare script to verify.');
  await sourcePrisma.$disconnect();
  await targetPrisma.$disconnect();
}

main().catch((e) => { console.error(e); process.exit(1); });

What to change for your project: set TABLES_IN_ORDER to your tables in FK order (parents first). You can derive the list from prisma migrate or your schema.

4. Row-count comparison script

Theory in practice: We verify parity with row counts (see “Verification” above): list tables in both DBs, then for each common table compare COUNT(*). If any table is missing on target or counts differ, we exit with failure so the migration is not considered successful.

File: backend/scripts/compare-remote-databases.ts

/**
 * Compare two remote databases: table list and row counts per table.
 * Usage: SOURCE_DATABASE_URL="..." TARGET_DATABASE_URL="..." npx tsx scripts/compare-remote-databases.ts
 */

import dotenv from 'dotenv';
import { PrismaClient } from '@prisma/client';

dotenv.config();

const sourceUrl = process.env.SOURCE_DATABASE_URL;
const targetUrl = process.env.TARGET_DATABASE_URL;

if (!sourceUrl || !targetUrl) {
  console.error('Set both SOURCE_DATABASE_URL and TARGET_DATABASE_URL');
  process.exit(1);
}

const sourcePrisma = new PrismaClient({ datasources: { db: { url: sourceUrl } } });
const targetPrisma = new PrismaClient({ datasources: { db: { url: targetUrl } } });

async function getTables(prisma: PrismaClient): Promise<string[]> {
  const rows = await prisma.$queryRawUnsafe<Array<{ tablename: string }>>(
    `SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND tablename NOT LIKE '_prisma%' ORDER BY tablename`
  );
  return rows.map((r) => r.tablename);
}

async function getRowCount(prisma: PrismaClient, table: string): Promise<number> {
  const rows = await prisma.$queryRawUnsafe<Array<{ count: bigint }>>(
    `SELECT COUNT(*) as count FROM "${table}"`
  );
  return Number(rows[0]?.count ?? 0);
}

async function main() {
  await sourcePrisma.$connect();
  await targetPrisma.$connect();

  const sourceTables = await getTables(sourcePrisma);
  const targetTables = await getTables(targetPrisma);
  const common = sourceTables.filter((t) => targetTables.includes(t));
  const onlyInSource = sourceTables.filter((t) => !targetTables.includes(t));

  if (onlyInSource.length > 0) {
    console.log('❌ Tables only in SOURCE:', onlyInSource);
  }

  let allMatch = true;
  for (const table of common.sort()) {
    const [sourceCount, targetCount] = await Promise.all([
      getRowCount(sourcePrisma, table),
      getRowCount(targetPrisma, table),
    ]);
    const match = sourceCount === targetCount;
    if (!match) allMatch = false;
    console.log(`  ${match ? '✅' : '❌'} ${table}: source=${sourceCount}, target=${targetCount}`);
  }

  if (allMatch && onlyInSource.length === 0) {
    console.log('✅ All compared tables have matching row counts.');
  } else {
    process.exit(1);
  }
  await sourcePrisma.$disconnect();
  await targetPrisma.$disconnect();
}

main().catch((e) => { console.error(e); process.exit(1); });

5. Schema comparison (optional but recommended)

Theory in practice: Row counts tell you “same number of rows”; they don’t tell you “same indexes or triggers”. So we run a schema diff: query both DBs for primary keys, foreign keys, unique constraints, indexes, check constraints (compared by table + clause, not by name), enums, sequences, and triggers. Any difference fails the run. We exclude optional objects (e.g. a logging table that exists only on source) so that “only in source” doesn’t wrongly fail the run.

File: backend/scripts/compare-remote-databases-schema.ts (concept)

• Query both DBs for: primary keys, foreign keys, unique constraints, indexes, check constraints (by table + clause), enum types, sequences, triggers.
• Diff the sets; if something exists only in source or only in target (and isn’t in the ignore list), exit with code 1.
• Full script lives in the repo at backend/scripts/compare-remote-databases-schema.ts.

Run after data copy:

SOURCE_DATABASE_URL='...' TARGET_DATABASE_URL='...' npx tsx scripts/compare-remote-databases-schema.ts

6. Migration order and enum migration (lessons in code)

Theory in practice: Migration tools run migrations in a defined order (usually by name/timestamp). If migration B depends on something migration A creates (e.g. a table), A must run first. Migration order: We had migrations that had to run in a specific order (e.g. GDPR tables before a later migration that referenced them). We renamed the folders so the timestamp prefix orders them, e.g.:

• 20251210_1_add_gdpr_tables
• 20251210_2_add_error_message
• 20251210_3_add_payment_tables

Enum that might not exist: (See “Theory: PostgreSQL enums” above.) On a DB that was created only by running migrations, the ChartType enum sometimes didn’t exist when we added new values. So we changed the migration to create the enum if missing, then add the values.

File: backend/prisma/migrations/20260214000000_add_transit_chart_types/migration.sql

-- Create ChartType enum if it does not exist (e.g. when DB was created from migrations only).
-- Then ensure TRANSIT_LAGNA and TRANSIT_MOON exist for transit house predictions.
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'ChartType') THEN
    CREATE TYPE "ChartType" AS ENUM (
      'D1_LAGNA', 'D1_RASI', 'D2_HORA', 'D3_DREKKANA', 'D7_SAPTAMSA',
      'D9_NAVAMSA', 'D10_DASAMSA', 'D12_DWADASAMSA', 'D16_SHODASAMSA',
      'D20_VIMSAMSA', 'D24_CHATURVIMSAMSA', 'D27_BHAMSA', 'D30_TRIMSAMSA',
      'D40_KHAVEDAMSA', 'D45_AKSHAVEDAMSA', 'D60_SHASHTYAMSA',
      'TRANSIT_LAGNA', 'TRANSIT_MOON'
    );
  ELSE
    ALTER TYPE "ChartType" ADD VALUE IF NOT EXISTS 'TRANSIT_LAGNA';
    ALTER TYPE "ChartType" ADD VALUE IF NOT EXISTS 'TRANSIT_MOON';
  END IF;
END
$$;

If you add new enum values in Prisma, keep in mind: on a “migrate-only” target, the enum might not exist yet—so “create if not exists, then add value” is safer.

7. Triggers and optional objects

Theory in practice: Triggers and their side effects (e.g. a log table) are part of the “schema” in a broad sense. If the source has them and the target doesn’t, the schema diff will show “only in source”. You can either apply the same SQL on the target (so behaviour and schema match) or exclude those objects from the comparison. We apply the trigger SQL so the target behaves like the source.

psql "$TARGET_DATABASE_URL" -f backend/scripts/add-user-deletion-trigger.sql

The schema comparison script can exclude optional tables/sequences (e.g. user_deletion_logs) so they don’t cause a “diff” failure.

---

Option B: pg_dump / pg_restore (one-shot clone)

Use when you want a single full clone and your local PostgreSQL client version is ≥ the server’s.

Prerequisites: pg_dump, pg_restore (and optionally psql) on PATH. If your default client is older than the server, set:

export PG_DUMP=/path/to/pg_dump   # e.g. /usr/local/opt/postgresql@17/bin/pg_dump
export PG_RESTORE=/path/to/pg_restore

One-shot (schema + data):

cd backend
export SOURCE_DATABASE_URL="postgresql://user:pass@old-host:port/olddb"
export TARGET_DATABASE_URL="postgresql://user:pass@new-host:port/newdb"
./scripts/pg-migrate-remote-to-remote.sh

Data-only (target already has schema from Prisma):

DATA_ONLY=1 ./scripts/pg-migrate-remote-to-remote.sh "$SOURCE_DATABASE_URL" "$TARGET_DATABASE_URL"

Passwords with special characters: URL-encode them in the URL (e.g. ^ → %5E, } → %7D).

---

After migration

1. Point the app to the new DB: set DATABASE_URL (e.g. in .env or in your host’s env) to TARGET_DATABASE_URL.
2. Run npx prisma generate in the backend.
3. Map backend and frontend URLs in your host (e.g. dcdeploy) to the new environment.

---

Summary and takeaways

Theory (what to remember):

• Remote-to-remote = copy from one live DB server to another (new host, region, or environment). You need the same schema and data on the target.
• Schema first, then data: Create structure (tables, constraints, enums, etc.) on the target, then copy rows in FK order so parent rows exist before child rows.
• Two strategies: (A) Deploy your migrations on the target and copy data with an app (avoids pg_dump version issues); (B) Use pg_dump/pg_restore when your client version matches or exceeds the server.
• Enums: On a migrate-only target, an enum might not exist yet; migrations should create-if-not-exists then add value so they work on both fresh and cloned DBs.
• Verification: Compare row counts (and optionally full schema: PKs, FKs, indexes, constraints, enums, sequences, triggers) so you know the target really matches the source.

Practice (what we did):

• We migrated remote → remote by deploying Prisma migrations on the target, then copying data with a Node script (enum + JSONB safe), then comparing row counts and optionally schema.
• We ordered migrations by folder naming, handled enums with create-if-not-exists in SQL, and verified with scripts. The same approach works for any Prisma/PostgreSQL project; adjust TABLES_IN_ORDER and optional/ignore lists to your schema.

All scripts referenced here live in this repo under backend/scripts/. The technical runbook is in REMOTE_TO_REMOTE_MIGRATION.md.