PostgreSQL Migration Steps: Practical Guide

PostgreSQL powers some of the world’s most critical applications. When you need to migrate a Postgres database, you need database migration steps that account for its specific features and quirks.

This guide covers PostgreSQL-specific database migration steps that leverage Postgres tools and best practices. For general migration guidance, start with our database migration steps checklist.

PostgreSQL Database Migration Steps

Step 1: Assess Your Database

Before migrating, understand what you’re working with:

-- Database size
SELECT pg_size_pretty(pg_database_size(current_database()));

-- Table sizes
SELECT schemaname, tablename, 
       pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename))
FROM pg_tables 
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

-- Check for active connections
SELECT count(*) FROM pg_stat_activity WHERE datname = current_database();

Step 2: Create a Consistent Backup

Use pg_dump with appropriate flags:

# Full backup with compression
pg_dump -h $DB_HOST -U $DB_USER -d $DB_NAME \
  --format=custom \
  --compress=9 \
  --file=backup_$(date +%Y%m%d_%H%M%S).dump

# Schema only (for testing migrations)
pg_dump -h $DB_HOST -U $DB_USER -d $DB_NAME \
  --schema-only \
  --file=schema_backup.sql

Step 3: Handle Sequences Correctly

PostgreSQL sequences often cause migration issues:

-- Check current sequence values
SELECT sequencename, last_value 
FROM pg_sequences 
WHERE schemaname = 'public';

-- After migration, reset sequences if needed
SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));

Step 4: Manage Constraints During Migration

For large data migrations, temporarily disable constraints:

-- Disable triggers (including FK checks)
ALTER TABLE orders DISABLE TRIGGER ALL;

-- Run your migration
INSERT INTO orders SELECT * FROM legacy_orders;

-- Re-enable triggers
ALTER TABLE orders ENABLE TRIGGER ALL;

-- Verify constraints
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_id_fkey;

Step 5: Use PostgreSQL-Specific Features

Leverage Postgres capabilities for safer migrations:

-- Concurrent index creation (no table locks)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- Add column with default (instant in PG 11+)
ALTER TABLE users ADD COLUMN status VARCHAR(50) DEFAULT 'active';

-- Online column type changes (when possible)
ALTER TABLE users ALTER COLUMN age TYPE INTEGER USING age::INTEGER;

Step 6: Monitor PostgreSQL During Migration

-- Watch for blocking queries
SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid,
       blocked_activity.query AS blocked_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_locks blocking_locks 
  ON blocking_locks.locktype = blocked_locks.locktype
WHERE NOT blocked_locks.granted;

-- Check replication lag (if applicable)
SELECT client_addr, state, 
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
FROM pg_stat_replication;

Step 7: Verify with PostgreSQL Tools

# Restore to test database
pg_restore -h $STAGING_HOST -U $DB_USER -d test_db backup.dump

# Compare schemas
pg_dump --schema-only production_db > prod_schema.sql
pg_dump --schema-only test_db > test_schema.sql
diff prod_schema.sql test_schema.sql

Automating PostgreSQL Database Migration Steps

PostgreSQL migrations involve many commands that must run in sequence. Missing one step—like resetting sequences—causes subtle bugs that surface days later. For production-specific guidance, see database migration steps for production and our best practices guide.

Stew lets you document your PostgreSQL database migration steps as executable Markdown. Run each psql command with a click. Track progress automatically. Share proven procedures with your team.

Join the waitlist and make PostgreSQL migrations reliable.