PostgreSQL 16 to 17 Zero-Downtime Upgrade via Logical Replication

Executive Summary

Technique: Zero-downtime PostgreSQL upgrade using logical replication
Downtime: 3-10 seconds (theoretical) vs 4-hour disasters (reality when things go wrong)
Resource Requirements: Double normal resources during migration
Complexity: High - multiple failure modes, requires precise timing
Success Rate: High when properly planned, catastrophic when rushed

Critical Prerequisites

Configuration Requirements

• wal_level = logical (requires PostgreSQL restart - plan downtime accordingly)
• max_replication_slots >= 10
• max_wal_senders >= 10
• max_logical_replication_workers >= 10
• max_worker_processes >= 20

FAILURE MODE: If wal_level isn't logical, upgrade starts with 20-minute downtime

Hardware Resource Requirements

Resource	Minimum Requirement	Failure Consequence
CPU	2x normal usage	Replication crawls, lag increases exponentially
Memory	2x normal allocation	WAL processing fills all available RAM
Storage	150% of source database	Migration fails mid-process, logs consume 800GB+
Network Latency	<5ms between source/target	Replication lag becomes unbearable
Disk I/O	Fastest available storage	Migration takes 8+ hours instead of 6

Primary Key Requirement

CRITICAL: Tables without primary keys break logical replication silently

• Detection: Query pg_tables joined with pg_constraint for missing primary keys
• Workaround: ALTER TABLE table_name REPLICA IDENTITY FULL (causes performance degradation)
• Impact: REPLICA IDENTITY FULL replicates entire rows, crushing network performance

Phase 1: Infrastructure Setup

PostgreSQL 17 Target Configuration

• Size Target Correctly: Identical specs to source results in "dying snail" migration speed
• AWS RDS Example: db.r5.xlarge minimum for production workloads
• Storage: Minimum 500GB allocated storage regardless of source size

Schema Migration Process

-- Schema-only dump (excludes data, permissions, ownership)
pg_dump --host=source-host --schema-only --no-privileges --no-owner source_db > schema.sql
psql --host=target-host --file=schema.sql target_db

Common Failure Points:

• Permission errors during schema application
• Missing extensions (pgcrypto, uuid-ossp, pg_stat_statements)
• Character encoding mismatches between versions

PgBouncer Configuration

Purpose: Enables instant connection redirection during switchover
Pool Mode: transaction (required for seamless switching)
Authentication: md5 with userlist file (plain text passwords fail)

# Production-tested configuration
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
reserve_pool_size = 5

Phase 2: Logical Replication Setup

Publication Creation (Source Database)

CREATE PUBLICATION upgrade_publication FOR ALL TABLES;

Subscription Creation (Target Database)

CREATE SUBSCRIPTION upgrade_subscription
CONNECTION 'host=source port=5432 dbname=prod user=replication_user password=secure_pass'
PUBLICATION upgrade_publication;

Initial Sync Monitoring

Duration Expectations:

• 100GB database: 6-8 hours
• Performance degradation on source: 30-40% during sync
• DO NOT run during high-traffic periods (Black Friday lesson learned)

Monitoring Queries:

-- Replication lag in seconds
SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) as lag_seconds;

-- Subscription status
SELECT subname, pid, received_lsn, latest_end_lsn FROM pg_stat_subscription;

Phase 3: Switchover Execution

Pre-Switchover Validation

Mandatory Checks (skipping these caused 2-hour outage):

• Replication lag < 0.5 seconds
• PgBouncer responding to admin commands
• Sequence synchronization script prepared
• Source database activity < normal threshold

Sequence Synchronization Problem

CRITICAL ISSUE: Logical replication doesn't sync sequence values
Consequence: Auto-increment IDs restart from 1, causing duplicate key errors
Solution: Pre-generate sequence sync commands before switchover

-- Generate sequence sync commands
SELECT 'SELECT setval(' || quote_literal(schemaname||'.'||sequencename) ||
       ', GREATEST((SELECT COALESCE(MAX('||column_name||'), 1) FROM '||table_name||'), ' ||
       'nextval('||quote_literal(schemaname||'.'||sequencename)||')));'
FROM pg_sequences ps
JOIN information_schema.columns c ON c.column_default LIKE '%'||ps.sequencename||'%';

Switchover Process

1. Pause PgBouncer: PAUSE database_name;
2. Wait for zero lag: Timeout after 120 seconds
3. Sync sequences: Apply pre-generated commands
4. Redirect PgBouncer: Update config, RELOAD;
5. Resume traffic: RESUME database_name;

Total Switchover Time: 3 seconds when everything works, 4 hours when it doesn't

Critical Failure Modes and Recovery

Replication Lag Stuck at 30+ Minutes

Root Cause: Large query blocking WAL processing
Detection: Query pg_stat_activity for long-running transactions
Resolution:

• Kill blocking queries: SELECT pg_terminate_backend(pid);
• Increase workers: ALTER SYSTEM SET max_logical_replication_workers = 20;
• Postpone report generation until after migration

Switchover Script Failure

Emergency Rollback:

# Restore PgBouncer to source database
psql -h pgbouncer-host -p 6432 -U admin pgbouncer -c "PAUSE myapp;"
mv /etc/pgbouncer/pgbouncer.ini.backup /etc/pgbouncer/pgbouncer.ini
psql -h pgbouncer-host -p 6432 -U admin pgbouncer -c "RELOAD; RESUME myapp;"

Recovery Time: 60 seconds if PgBouncer cooperates

Duplicate Key Errors Post-Migration

Symptom: ERROR: duplicate key value violates unique constraint
Emergency Fix:

-- Set sequences higher than maximum values
SELECT setval('users_id_seq', (SELECT MAX(id) FROM users) + 1000);

Subscription Worker Crashes

Error: logical replication worker crashed
Common Causes:

• Data type incompatibility between PostgreSQL versions
• Schema modifications during migration
• Character encoding differences

Nuclear Recovery:

DROP SUBSCRIPTION upgrade_subscription;
-- Wait 30 seconds for cleanup
CREATE SUBSCRIPTION upgrade_subscription
CONNECTION '...' PUBLICATION upgrade_publication
WITH (copy_data = false);

Performance Optimization Post-Upgrade

PostgreSQL 17 Specific Settings

# Enhanced performance settings
vacuum_buffer_usage_limit = '2GB'
io_combine_limit = '128kB'
parallel_leader_participation = on
jit_above_cost = 100000

Statistics and Index Updates

-- Update optimizer statistics
ANALYZE VERBOSE;

-- Increase statistics for critical columns
ALTER TABLE large_table ALTER COLUMN indexed_column SET STATISTICS 1000;

Resource Requirements Summary

Phase	CPU Usage	Memory Usage	Storage Usage	Network Impact
Initial Sync	200% normal	200% normal	150% source size	High sustained traffic
Steady Replication	120% normal	150% normal	Stable	Low continuous traffic
Switchover	Minimal	Minimal	Stable	Burst during sequence sync

Upgrade Method Comparison

Method	Downtime	Complexity	Data Loss Risk	Resource Cost	Best Use Case
Logical Replication	3-10 seconds	High	None	2x during upgrade	Production requiring zero downtime
pg_upgrade	5-30 minutes	Medium	Low	1.5x storage	Small-medium DBs with maintenance windows
AWS Blue/Green	30-60 seconds	Low	None	2x during upgrade	AWS RDS managed solution
Dump/Restore	2-12+ hours	Low	Low	2x storage	Small databases or major restructuring

Cleanup and Decommissioning

Replication Cleanup

-- Target database
DROP SUBSCRIPTION upgrade_subscription;

-- Source database
DROP PUBLICATION upgrade_publication;
SELECT pg_drop_replication_slot('slot_name');

Decommissioning Timeline

• 24-48 hours: Keep PostgreSQL 16 running for emergency rollback
• 72 hours: Safe to decommission after stability validation
• Backup: Create final dump before decommissioning

Key Success Factors

1. Resource Allocation: Never underestimate hardware requirements
2. Primary Key Verification: Check all tables before starting
3. Sequence Preparation: Generate sync commands in advance
4. Emergency Procedures: Test rollback scripts before migration
5. Timing: Avoid high-traffic periods and concurrent deployments
6. Monitoring: Establish replication lag thresholds and alerts

Lessons from Production Failures

• First attempt: 4-hour outage from insufficient preparation
• Sequence issue: 20,000 user records lost due to duplicate keys
• Resource shortage: Migration failed at 2am with 800GB log files
• PgBouncer failure: Bypassed to direct database connections as emergency measure
• Schema changes: Colleague deployed during migration, broke replication worker

Success Rate: High when methodically planned, catastrophic when rushed or under-resourced