What you will learn
This walkthrough uses a custom-format archive and an offline final cutover. It is suitable when a logical copy fits the available maintenance window. Roles, server configuration, and external dependencies require separate handling.
Before you begin
Use client tools compatible with the source and destination versions; an older pg_dump cannot dump a newer server. Prepare destination extensions, encoding, locale requirements, roles, tablespaces, and sufficient storage. Configure TLS and protected credentials for both endpoints.
1. Inventory the migration boundary
Record databases, extensions, owners, privileges, scheduled jobs, foreign servers, large objects, and application connection strings. Decide whether to preserve ownership or remap it deliberately. A single pg_dump does not carry cluster-wide roles or server settings.
2. Create an inspectable archive
pg_dump -h host1.sample.com -U backup_user -d tutorial -Fc -f tutorial.dump
pg_restore --list tutorial.dump > tutorial.contents
Check exit status and stderr, then inspect the archive list. pg_dump takes a consistent database snapshot, but writes committed after that snapshot are not in the archive. Stop application writers for the final offline cutover copy.
3. Restore into a new destination database
createdb -h host2.sample.com -U db_owner tutorial_new
pg_restore -h host2.sample.com -U db_owner -d tutorial_new \
--exit-on-error --single-transaction tutorial.dump
This example expects required source owners/roles to exist and the restoring account to have the necessary rights. If you choose --no-owner or --no-acl, rebuild the intended ownership/grants explicitly afterward. Do not restore blindly over the only accepted copy.
4. Validate and switch the service
ANALYZE;
SELECT count(*) FROM public.events;
Use actual application tables and compare counts, constraints, sequences, representative values, and critical query behavior. Point a test instance at tutorial_new. Keep writers stopped until final acceptance, then switch the service configuration and recycle its pools.
Example output
The following is an illustrative, normalized lab result, not output captured from a live customer system. Your versions, addresses, timings, and row counts will differ.
Illustrative restore acceptance record:
Archive inspection: completed
Restore exit status: 0
Restore errors: 0
Required extensions: present
Sample table source count: 100
Sample table target count: 100
Sequence-generated INSERT: passed
These are summarized checks, not a pg_restore transcript. Use the real source row count at the agreed snapshot boundary; equal counts do not replace key/value and application tests.
Verify the result
The restore completes without ignored errors, the expected schemas/extensions are present, and application read/write tests succeed. Verify sequence-generated inserts, privileges, timezone behavior, and scheduled jobs rather than relying on a table count alone.
Troubleshooting
Missing-owner errors indicate an incomplete role plan. Missing-extension errors require compatible extension installation. Permission failures after a successful restore often involve schema USAGE, sequence grants, or object ownership.
Recovery and next steps
Before destination writes, restore the old service endpoint if validation fails. After writes start, retain and reconcile them before returning to the old database. Keep the original archive and restore logs through the acceptance period.