PostgreSQL Upgrade and Migration with Practical Examples: A Complete Implementation Guide

PostgreSQL upgrades and migrations can be predictable, observable, and reversible when you follow a disciplined process. This guide shows how to plan and execute an upgrade with a focus on safety, verification, and recovery. You will learn to inventory versions, topology, and extensions to select a safe method; choose among dump/restore, in-place pg_upgrade, or logical migration based on your downtime tolerance and data size; run step-by-step procedures with concrete commands; verify outcomes using built-in views and commands; and prepare for common failure modes with clear rollback paths. Start with a narrow pilot on a non-production copy so you can measure time, space, and downtime needs before the real change. Keep your old database startable and your backups tested until you are satisfied with verification results.

Version and Environment Inventory

Before selecting a method, gather the facts that constrain your approach.

Confirm Client and Server Versions

Run these commands to capture exact versions:

# Shell
psql --version
pg_config --version

# SQL (from psql connected to the server)
SELECT version();

Record both the major version (for example, 14, 15, 16) and the minor patch level. Minor upgrades within the same major version typically require only a binary swap and restart. Major upgrades require one of the methods described in this guide.

Topology and Access

Identify whether you run a single instance or a primary with replicas. Confirm you have superuser or sufficient privileges to create roles, extensions, and replication objects. If you use connection pooling (PgBouncer, PgPool) or load balancers, note their configuration because they will need updates during cutover.

Extensions and Compatibility

List installed extensions and their versions:

\dx
SELECT extname, extversion FROM pg_extension ORDER BY extname;

Record required extensions. Plan to match or upgrade them on the target. If an extension is not available on the target version, you cannot complete an in-place or logical upgrade without alternatives. Pay special attention to PostGIS, TimescaleDB, Citus, and pg_partman, which often have version-specific compatibility matrices.

Data Size and Growth

Estimate data and largest relations to choose a method and size storage:

SELECT pg_size_pretty(pg_database_size(current_database())) AS db_size;

SELECT schemaname, relname,
       pg_size_pretty(pg_total_relation_size(format('%I.%I', schemaname, relname))) AS total
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(format('%I.%I', schemaname, relname)) DESC
LIMIT 10;

This helps choose between dump/restore (good for small datasets under ~100 GB) and pg_upgrade or logical migration (better for large datasets or tight windows).

Performance Hotspots and Long Transactions

Check for long-running transactions that could block maintenance:

SELECT pid, xact_start, now() - xact_start AS age, query
FROM pg_stat_activity
WHERE state = 'active' AND xact_start IS NOT NULL
ORDER BY xact_start;

Kill or wait for transactions older than your maintenance window threshold before starting.

Replication and WAL Readiness (for Logical Migration)

Check wal_level and capacity for slots and workers:

SHOW wal_level; -- should be 'logical' for logical replication
SHOW max_replication_slots;
SHOW max_wal_senders;
SHOW max_logical_replication_workers;
SHOW shared_preload_libraries;

If wal_level is not logical and you plan logical replication, schedule a restart after changing it. Ensure max_replication_slots accommodates at least one slot per database you will migrate plus a buffer.

Maintenance Window and Downtime Tolerance

Define acceptable read-only and write downtime. Use this to select a method from the comparison table below.

Upgrade Method Comparison

Safe Configuration Path

Select one primary method based on downtime tolerance and data size. Keep the others as fallback options.

Prerequisites for All Methods

• A tested backup of the source you can restore independently (pg_basebackup or pg_dump verified by pg_restore to a test instance).
• Free disk space for the target cluster and any transient artifacts (target data size plus 30% buffer).
• Matching locales and encodings unless you intentionally change them.
• An isolated pilot environment (for example, a restored copy on a separate host or directory) to practice and measure.

Path A: Dump/Restore (Simplest, Moderate Downtime)

Best for small to medium databases or when you want to refactor schema objects during the move.

1. Quiesce and Lock Down Writes

Put the application in maintenance mode, or temporarily restrict writes. Confirm no active write transactions:

SELECT count(*) FROM pg_stat_activity
WHERE state <> 'idle' AND query !~* '^(COPY|SELECT)';

2. Export Roles and Globals

pg_dumpall --globals-only > globals.sql

3. Export Each Database (Custom Format Is Parallel-Restore Friendly)

pg_dump -Fc -j 4 -d yourdb > yourdb.dump

Use -j to match CPU cores for faster dump. For very large tables, consider --table to split dumps.

4. Prepare the Target Cluster

Initialize the new PostgreSQL version and start it. Create empty databases and required roles:

psql -f globals.sql
createdb yourdb

5. Restore

pg_restore -j 4 -d yourdb yourdb.dump

Monitor progress with pg_restore -l yourdb.dump | wc -l to see object count.

6. Post-Restore Maintenance

Refresh statistics:

vacuumdb --all --analyze-in-stages

7. Cutover

Point the application to the new instance. Keep the old instance read-only for a defined rollback window (for example, 48 hours).

Rollback

If validation fails before writes resume, point the application back to the old instance and resume service. Since no new writes occurred, there is no data divergence.

Path B: In-Place Major Upgrade with pg_upgrade (Short Downtime)

Best for medium to large databases on the same host where you can install both old and new PostgreSQL binaries.

1. Prepare New Binaries and a New Data Directory

Install the new PostgreSQL version alongside the old. Initialize a new cluster with the same encoding and locale:

initdb -D /path/to/newdatadir -E UTF8 --locale=en_US.utf8

Match the source locale exactly. Use locale -a to list available locales.

2. Stop the Old Server Cleanly

Ensure no active processes are attached, then stop the service:

systemctl stop postgresql@14-main   # example for version 14

Verify with pg_ctl status -D /path/to/olddatadir.

3. Run pg_upgrade

Example without hard links (safer; more space):

pg_upgrade \
  -b /usr/lib/postgresql/14/bin \
  -B /usr/lib/postgresql/16/bin \
  -d /var/lib/postgresql/14/main \
  -D /var/lib/postgresql/16/main \
  -U postgres \
  -j 4

Review the output. If successful, pg_upgrade generates helper scripts such as analyze_new_cluster.sh and delete_old_cluster.sh.

4. Analyze and Reindex as Needed

Warm up statistics:

./analyze_new_cluster.sh

If you changed collation or are moving across a system collation change (for example, glibc upgrade), plan to reindex affected objects. When in doubt, prioritize reindexing user-visible btree indexes for critical tables:

REINDEX TABLE CONCURRENTLY orders;
REINDEX TABLE CONCURRENTLY customers;

5. Start the New Server and Verify

Start the service using the new binaries and new data directory:

systemctl start postgresql@16-main

6. Cutover

Point the application at the upgraded instance.

7. Cleanup

After the rollback window, remove the old data directory with the generated script:

./delete_old_cluster.sh

Rollback

If pg_upgrade fails, the old cluster remains intact; start it and resume service. If issues are detected after starting the new cluster but before resuming writes, stop the new server and restart the old server.

Notes

The -k flag can reduce time and space by hard-linking files, but it ties old and new data directories to the same storage. Prefer the default copy mode for clearer rollback boundaries.

Path C: Logical Migration with Publications/Subscriptions (Minimal Downtime)

Best when you need to keep reads and writes online and can manage a brief cutover.

Assumptions

• Source version and target support built-in logical replication (PostgreSQL 10+).
• wal_level=logical on the source, and you have capacity for at least one replication slot.

1. Prepare Target Cluster

Initialize and start the new version. Create roles and empty databases required for migration. Install required extensions on the target before creating the subscription.

2. Ensure Source Settings (Restart the Source If You Change These)

ALTER SYSTEM SET wal_level = 'logical';
ALTER SYSTEM SET max_replication_slots = 10;
ALTER SYSTEM SET max_logical_replication_workers = 4;
-- Reload or restart to apply
SELECT pg_reload_conf(); -- requires restart for wal_level

3. Create a Publication on the Source

For all user tables in a database:

-- Connect to the source database
GRANT USAGE ON SCHEMA public TO PUBLIC; -- ensure target has access as needed
CREATE PUBLICATION pub_all FOR ALL TABLES;

If you need finer control, publish selected tables instead:

CREATE PUBLICATION pub_core FOR TABLE orders, customers, products;

4. Create a Subscription on the Target

Replace connection parameters as appropriate:

-- On target database
CREATE SUBSCRIPTION sub_all
  CONNECTION 'host=SOURCE_HOST port=5432 dbname=DB user=REPL_USER password=SECRET'
  PUBLICATION pub_all
  WITH (copy_data = true, create_slot = true, enabled = true);

This copies existing data and begins streaming changes. Use copy_data = false if you will load an initial snapshot via pg_dump/pg_restore and only want change capture.

5. Monitor Synchronization

SELECT subname, relid::regclass AS table, status,
       last_msg_send_time, last_msg_receipt_time,
       bytes_lag
FROM pg_stat_subscription;

Wait for initial table sync to complete (status = 'r' for ready) and for replication lag to stay near zero.

6. Handle Sequences Before Cutover

Logical replication does not automatically keep sequence values in sync. Just before cutover, refresh sequences on the target using values from the source. A practical approach: extract values from the source and apply with a script that runs setval on the target.

# On source: generate setval commands
psql -d DB -Atc "
SELECT format('SELECT setval(%L, %s, true);', sequence_schema||'.'||sequence_name, last_value)
FROM information_schema.sequences
WHERE sequence_schema NOT IN ('pg_catalog', 'information_schema');
" > sync_sequences.sql

# On target: apply
psql -d DB -f sync_sequences.sql

7. Cutover

Quiesce writes on the source (application maintenance mode or database-level restriction):

ALTER SYSTEM SET default_transaction_read_only = on;
SELECT pg_reload_conf();

Wait for all write transactions on the source to finish:

SELECT count(*) FROM pg_stat_activity
WHERE state <> 'idle' AND query ~* 'INSERT|UPDATE|DELETE|MERGE';

On the target, wait for replay to catch up:

SELECT pg_sleep(1)
FROM generate_series(1,30)
WHERE (SELECT COALESCE(SUM(bytes_lag),0) FROM pg_stat_subscription) = 0;

Point the application to the target and resume traffic.

8. Decommission Replication

When confident, drop the subscription on the target and publication on the source:

DROP SUBSCRIPTION sub_all;
-- On source once you are done with rollback window
DROP PUBLICATION pub_all;

Rollback

If you detect issues before resuming writes on the target, keep the application on the source and drop the subscription, or leave it to retry later. After writes resume on the target, rolling back to the source requires a separate reverse-sync or restore-from-backup plan. Avoid destructive changes on the source until you finalize acceptance.

Verification and Diagnostics

Verification proves that the new instance is correct and healthy. Run these checks before declaring success.

Core Correctness Checks

• Server version and parameters:

SELECT version();
SHOW server_version_num;
SHOW data_directory;

• Extensions installed and versions:

\dx

• Schema diffs (schema-only dumps):

pg_dump --schema-only -d source_db > source_schema.sql
pg_dump --schema-only -d target_db > target_schema.sql
diff -u source_schema.sql target_schema.sql

Review any differences. Expected differences include version-specific system objects.

• Row counts for key tables (spot checks):

SELECT 'orders' AS table, count(*) FROM orders
UNION ALL
SELECT 'customers', count(*) FROM customers
UNION ALL
SELECT 'products', count(*) FROM products;

For large tables, use pg_class.reltuples for a fast estimate:

SELECT relname, reltuples::bigint AS est_rows
FROM pg_class
WHERE relname IN ('orders', 'customers', 'products');

Performance Readiness

• Analyze to refresh statistics:

vacuumdb --all --analyze-in-stages

• Check for slow queries after upgrade and compare plans:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 12345;

Compare with plans from the source if you captured them during pilot.

• Monitor background tasks and autovacuum activity:

SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Replication Health (Logical Migration)

• Subscription status and lag:

SELECT * FROM pg_stat_subscription;

• Conflicts or apply errors are reported in server logs; keep log level to at least WARNING during migration.

Verification Summary Table

Failure Modes and Recovery

Anticipate where things break and how to recover without data loss.

Common Issues by Method

Dump/Restore

• Failure: Restore errors due to missing extensions.
• Action: Install or remove/replace the extension; rerun restore.
• Failure: Role or ownership mismatches.
• Action: Restore globals first; map owners with pg_restore --no-owner and post-fix GRANT/ALTER OWNERSHIP.
• Rollback: If you have not resumed writes, continue using the source and retry later.

pg_upgrade

• Failure: Binary mismatch or wrong paths.
• Action: Re-run with correct -b/-B and data directory flags; verify both versions are accessible.
• Failure: Incompatible extension left installed.
• Action: Drop or upgrade the extension on the source before retrying; consult extension release notes.
• Failure: Collation-related index errors.
• Action: Reindex affected objects on the target after upgrade; test on pilot to identify which.
• Rollback: Start the old cluster; restore original service configuration.

Logical Migration

• Failure: Replication worker errors on DDL changes during sync.
• Action: Freeze schema during initial sync; if DDL is required, apply compatible DDL to both sides in order.
• Failure: Persistent replication lag.
• Action: Increase resources for apply workers; ensure network stability; avoid long transactions on the source; check max_logical_replication_workers.
• Failure: Sequences out of sync after cutover.
• Action: Run a sequence sync script using setval just before enabling writes on target.
• Rollback: Before enabling writes on the target, keep traffic on the source and drop the subscription. After enabling writes, rollback needs a reverse replication or restore plan.

General Recovery Practices

• Keep verified backups and a restore-tested snapshot from immediately before the upgrade.
• Do not delete the old cluster or change its data until the rollback window ends.
• Keep connection strings, service files, and firewall rules ready to flip back quickly.
• Maintain a runbook with all commands and the exact versions used.

Operations Checklist

Use this checklist to run a pilot and then production. Adjust durations to your environment.

Preflight (1-2 Days Before)

• [ ] Take and verify a fresh backup (restore to a test instance).
• [ ] Inventory versions, extensions, data size, and largest tables.
• [ ] Choose method: dump/restore, pg_upgrade, or logical.
• [ ] Prepare target binaries and empty cluster.
• [ ] Confirm disk space headroom (target + 30% buffer).
• [ ] Prepare maintenance window and communication plan.

Pilot on a Copy

• [ ] Rehearse the chosen method end-to-end on a restored copy.
• [ ] Measure elapsed time for each step and required storage.
• [ ] Document any extension or collation adjustments.
• [ ] Practice verification steps and record expected outputs.

Execution Day

• [ ] Announce start; put apps in maintenance or read-only mode as needed.
• [ ] Ensure no long-running write transactions on source.
• [ ] Run the selected migration steps.
• [ ] Run verification: version, extensions, schema diffs, row counts, stats.
• [ ] For logical migration, confirm lag is zero before cutover.
• [ ] Enable application against the target.

Rollback Criteria

• [ ] Predefined errors or mismatches that trigger rollback (examples: schema diff on critical tables, sequence inconsistencies, replication apply errors).
• [ ] If rollback, restart the old instance and revert connection strings.

Post-Upgrade

• [ ] Monitor slow queries and CPU/IO; run targeted EXPLAIN ANALYZE on key paths.
• [ ] Complete analyze and any required reindexing.
• [ ] Keep old instance read-only and start decommission timer (for example, 48 hours).
• [ ] Remove publication/subscription or old data directory after acceptance.

Conclusion

A safe PostgreSQL upgrade is built on inventory, a carefully chosen method, explicit verification, and a clear rollback path. Start with a narrow pilot to measure space, time, and downtime. For small databases, dump/restore is simple and resilient. For larger databases with tight windows, in-place pg_upgrade is fast and preserves files. When downtime must be minimal, logical migration lets you copy and catch up before a short cutover. Whichever path you choose, keep the old instance runnable until checks pass and your team is confident. Capture exact commands and timings from your pilot to predict production behavior. With these steps, you can upgrade predictably, validate outcomes, and recover safely if something goes wrong.