How to Migrate a Database Between Providers

Two strategies, pick by downtime budget

There are really only two ways to move a relational database, and the right one depends on how much downtime you can tolerate:

1. 1Dump and restore — simple, reliable, requires a maintenance window proportional to your data size.
2. 2Logical replication — near-zero downtime, more moving parts, the standard for production cutovers.

This guide covers PostgreSQL and MySQL, the two most common managed engines. PandaStack offers managed PostgreSQL (14.x / 16.x), MySQL (5.7 / 8.x), MongoDB, and Redis, so the destination examples use those, but the techniques are provider-agnostic.

Before you start: take stock

• Engine and exact version on both sides. Restoring a 16.x dump into a 14.x server can fail; match major versions or restore to an equal-or-newer one.
• Extensions (pg_stat_statements, postgis, uuid-ossp). The target must have them available.
• Size and row counts per table — your validation baseline.
• Connection limits — PandaStack free tier allows 50 connections, Pro 300, Premium 1000. Size accordingly.

-- Postgres: list installed extensions on the source
SELECT extname, extversion FROM pg_extension;
-- Row count baseline
SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC;

Strategy 1: Dump and restore (PostgreSQL)

Use the custom format (-Fc) — it's compressed and supports parallel restore.

# Dump from source
pg_dump --no-owner --no-acl -Fc "$SOURCE_URL" -f source.dump

# Restore into the new managed database (parallel jobs speed it up)
pg_restore --no-owner --no-acl -j 4 -d "$DEST_URL" source.dump

--no-owner --no-acl strips ownership/grants that won't exist on the new server — re-create roles separately. For large databases, -j 4 parallelizes the restore across four workers.

Strategy 1: Dump and restore (MySQL)

# Consistent snapshot without locking the whole DB
mysqldump --single-transaction --routines --triggers \
  --set-gtid-purged=OFF "$SOURCE_DB" > dump.sql

mysql "$DEST_DB" < dump.sql

--single-transaction gives a consistent snapshot for InnoDB without locking; --routines --triggers includes stored procedures and triggers people routinely forget.

Strategy 2: Near-zero-downtime with logical replication

When a maintenance window isn't acceptable, replicate live, then flip. For PostgreSQL:

-- 1. On the source: create a publication
CREATE PUBLICATION mig_pub FOR ALL TABLES;

First, copy the schema only (no data) to the destination:

pg_dump --schema-only --no-owner "$SOURCE_URL" | psql "$DEST_URL"

Then subscribe on the destination, which performs an initial copy and then streams changes:

-- 2. On the destination: subscribe
CREATE SUBSCRIPTION mig_sub
  CONNECTION 'host=source-host dbname=app user=repl password=...'
  PUBLICATION mig_pub;

Monitor lag until the destination is caught up:

SELECT * FROM pg_stat_subscription;

When lag is near zero, cut over: stop writes on the source, let the last changes drain, repoint your app's DATABASE_URL, then drop the subscription. Total write-downtime is seconds.

The cutover sequence

1. 1Lower app-side connection pool or enable read-only mode.
2. 2Stop writes to the source.
3. 3Confirm replication lag is zero (or finish the final dump).
4. 4Reset sequences if needed (logical replication doesn't sync them):

SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));

1. 1Repoint DATABASE_URL to the new database. On PandaStack, attaching the new managed DB to your app auto-injects the connection string — redeploy and you're on the new database.
2. 2Resume writes.

Validation: don't trust, verify

A migration is not done when the restore finishes. It's done when the data matches.

-- Compare row counts table by table
SELECT 'users' AS t, count(*) FROM users
UNION ALL SELECT 'orders', count(*) FROM orders;

Go further with checksums on critical tables:

-- Postgres: a cheap content checksum
SELECT md5(string_agg(id::text || updated_at::text, '' ORDER BY id))
FROM orders;

Run the same query on source and destination; the hashes must match. Also verify: sequences/auto-increment values, foreign key integrity, extension presence, and that your app's smoke tests pass against the new DB.

Common pitfalls

Rollback plan

Keep the old database running and writable until you've validated for a full business cycle. Your rollback is simply repointing DATABASE_URL back. Don't decommission the source for at least a few days.

References

• [PostgreSQL — Logical Replication](https://www.postgresql.org/docs/current/logical-replication.html)
• [PostgreSQL pg_dump documentation](https://www.postgresql.org/docs/current/app-pgdump.html)
• [MySQL mysqldump documentation](https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html)
• [KubeBlocks documentation](https://kubeblocks.io/docs/release-1.0/user_docs/overview/introduction)

---

Database migrations reward paranoia: baseline counts, validate with checksums, keep the source alive. PandaStack's managed databases (Postgres, MySQL, MongoDB, Redis) auto-wire DATABASE_URL into your app, so the cutover step is a redeploy. Provision a target on the [free tier](https://dashboard.pandastack.io) and rehearse your migration before doing it for real.