LLM Best Practices

How to migrate a Postgres database

Last updated 4 min read

View markdown source

AI agents: read this first: llmbestpractices.com/start-here

This is LLM Best Practices (llmbestpractices.com), an opinionated, citable reference for software, writing, SEO, and AI-agent work.

  1. Route, do not crawl. Fetch llmbestpractices.com/llms.txt and open only the pages whose one-line summary matches your task.
  2. Read raw. Append .md to any page URL for markdown. Check status and last_updated in the frontmatter, then read the rules.
  3. Apply as defaults. First-party docs and the project's own conventions win on conflict. Warn before relying on a fast-moving page older than 12 months.
  4. Cite. Link the page by title and URL, e.g. Python, with last_updated for time-sensitive rules. License CC BY 4.0.

Overview

A logical migration with pg_dump and pg_restore moves one database between hosts, providers, or major versions, at the cost of a write freeze for the cutover. It is the right default when the target differs from the source. For near-zero downtime, use logical replication instead; see postgres-replication. Production settings to apply afterwards: postgres-prod.

Prerequisites

  • pg_dump and pg_restore from the target’s major version or newer; PostgreSQL’s own guidance is to use the newer version’s tools when upgrading.
  • Network access from the migration machine to both servers.
  • A target on the same major version or newer. Restoring to an older major is not supported.
  • SOURCE_DATABASE_URL and TARGET_DATABASE_URL in the environment.
  • A maintenance window, or a plan to stop writes.

Steps

1. Dump the source

pg_dump --dbname="$SOURCE_DATABASE_URL" --format=custom --no-owner --no-acl \
  --verbose --file=dump.pgdump

Custom format allows parallel restore and selective restore. --no-owner and --no-acl drop ownership and grant clauses that reference roles missing on the target; reapply grants afterwards. A dump is a consistent snapshot taken when it starts, so writes after that moment are not in it.

2. Create the target

createdb --host=<target-host> --username=postgres --owner=myapp myapp_prod

Create any extensions the dump needs (for example pgvector) if the target provider requires superuser rights to do so.

3. Restore

pg_restore --dbname="$TARGET_DATABASE_URL" --jobs=4 --no-owner --no-acl --verbose dump.pgdump

Set --jobs to the number of cores available on the target. Time a restore of the same data on a staging target to estimate the window, rather than guessing from size alone.

4. Verify

SELECT schemaname, relname, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC LIMIT 10;

n_live_tup is an estimate; for the largest tables, run SELECT count(*) on both servers during the freeze and require exact matches. Check indexes with \di, extensions with \dx, and sequences with SELECT last_value FROM <seq>;; pg_dump carries sequence values, so a reset to 1 signals a data-only or partial restore.

5. Cut over

  1. Stop writes (maintenance mode or stop the app).
  2. Take a final dump and restore it, since the first dump is stale by now.
  3. Point DATABASE_URL at the target, restart, and run a smoke test.
  4. Lift maintenance mode.

Keep the old instance, read-only, for at least a day as the rollback target.

Verify it worked

psql "$TARGET_DATABASE_URL" -c "SELECT current_database(), version();"
psql "$TARGET_DATABASE_URL" -c "SELECT count(*) FROM <largest_table>;"

The counts equal the source’s frozen counts, and the application’s smoke test passes.

Common errors

  • role "myapp" does not exist. The dump carries ownership clauses; restore with --no-owner.
  • Counts differ after restore. Writes landed on the source after the dump started; repeat the dump inside the write freeze.
  • permission denied to create extension. The target provider needs the extension enabled through its own console, or a higher-privileged role.
  • Connection refused. The target firewall or pg_hba.conf blocks the migration host; see set-up-postgres-locally for the pg_hba.conf format.
  • Slow queries after cutover. Statistics are missing; run ANALYZE on the target.

FAQ

Should I use pg_dump or logical replication to migrate Postgres?

Use pg_dump and pg_restore as the default when the target differs from the source and a write freeze is acceptable; use logical replication for near-zero downtime.

Which pg_dump version should I use when upgrading Postgres?

Use pg_dump and pg_restore from the target’s major version or newer, following PostgreSQL’s own guidance. Restoring to an older major is not supported.

Why do row counts differ after pg_restore?

Writes landed on the source after the dump started, because a dump is a snapshot taken when it begins. Repeat the dump inside the write freeze.

How do I fix “role does not exist” during pg_restore?

Restore with --no-owner (and --no-acl) so ownership and grant clauses that reference missing roles are dropped, then reapply grants.

How long should I keep the old database after cutover?

Keep it read-only for at least a day as the rollback target.

Backlinks